I need to move SQL Server to a new NT server - what are my options?

A. There are several options :-

Option 1 : NT Copy

1. Install SQL Server on the New Server in the same install path and make sure the data location is also in the same directory as the Old Server. (If you can't make the path the same then you'll have to manually change device names - see movedevice.txt faq entry)
2. Stop the New and the Old SQL Servers
3. Copy all the .dat files from Old Server to the New Server into the same Data folder. This includes all the dat files - master.dat, msdb_data.dat, msdb_log.dat, and all your user databases dat files. The system databases will all be overwritten.
4. Start the New SQL Server
5. Connect using isql_w and login to the SQL Server as "sa"
6. Run the below:
sp_dropserver old_server_name
sp_addserver new_server_name, local
(see changeservername.txt for more details and what to do with SQL 7)
7. This will move the databases fine, but you might have problems with your SQL Server logins. Standard logins will work ok, but NT integrated logins will need to be reconfigured.
- Start SQL Security Manager and do the following:
- Revoke the local/domain groups and local/domain users from the New 
SQL Server.
- Then add all the local groups and local users using NT User manager 
on the New SQL Server machine.
- Then Grant the local groups and local users you just created above 
on the New Server using SQL Server Security Manager.
- Then Grant the domain users/groups access to SQL Server

Option 2. Transfer Manager
1. Install SQL Server on the new server. Create all databases as needed.
2. Transfer from source to target server using transfer manager.

Option 3. Dump/Load
1. Install SQL Server on the new server. Create all databases as needed. They must have segments in the same order for SQL 6.5 and below, so use sp_help_revdatabase on the source server to create a script to be run on the target.
2. Dump and restore the databases across. Could either be via the network or using a tape.

Option 4. Detach/Attach (SQL 7.0 only)
As per option 3, but this time use sp_detach_db and sp_attach_db stored procedures. See books-online for details.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.