Move database from local SQL Express server to centralized SQL Server

View a printable version of the following instructions.

Perform on local computer:

1. Stop SQL Server service on local computer

2. Copy ZeraWare database files to centralized SQL Server. The two files are named SMT_Data.MDF and SMT_Log.LDF, and the default install location for them is C:\Program Files\ZeraWare.

Perform on central server:

1. Connect to local instance of SQL Server using Management Studio.
2. Under Object Explorer, right-click the SQL Server instance to host the database, and select Attach Database.
3. When the Attach Databases window pops up, click the Add button under the Databases to attach window.
4. Browse for the file SMT_Data.MDF.
5. Click OK to attach the database.
6. Open a New Query window and paste the following text into it:

USE SMT
GO
sp_dropuser 'smt_user'
GO
exec sp_addlogin N'smt_user', N'SMT_user1', 'SMT', 'us_english'
GO

EXEC sp_grantdbaccess N'smt_user', N'smt_user'
GO
exec sp_addrolemember N'db_datareader', N'smt_user'
exec sp_addrolemember N'db_owner', N'smt_user'

7. Click the Execute menu option under the Query Menu. This will remove the old user info from the local machine and replace it with a user on the Central server.

Perform on local computer:

1. Open Windows Notepad, under Program Files\Accesories on the start menu.

2. Open the ZeraWare configuration file named Zeraware.exe.config, located under C:\Program Files\ZeraWare. (Make sure you change the file filter to show all files, instead of just Text Documents)

3. Find the following line and change (local)\ZeraWare to the name of the SQL instance located on the central server:
<add key="ConnectionString" value="Data Source=(local)\ZeraWare;Initial Catalog=SMT;user id=smt_user;password=SMT_user1"/>
ie. If the central server is named PRODSQLSERVER has a default instance, simple change it to the name of the server
<add key="ConnectionString" value="Data Source=PRODSQLSERVER;Initial Catalog=SMT;user id=smt_user;password=SMT_user1"/>

4. Run ZeraWare and verify the program can connect to the remote database.