5

My database server is being decommissioned at work. We have a new database server to take its place. Both servers have the same operating system and SQL Server 2008. I need to move all my databases and data over to the new server.

What is the best way to do this to ensure as few problems as possible?

My data is not just tables, views and stored procedures. There are also several SQL Server Agent jobs and several database maintenance plans and scheduled backups.

The system is not being used at night, so I have a late night opportunity to take the database offline, if need be.

Can I just copy the MSSQL directory over to the new server? Or should I just backup each database individually? If I do backup each database, should I back up the system databases (like master, model and msdb) too?

3 Answers 3

5

Doing a backup and restore will be a good way to copy over all the user databases.

  1. Backup DBs on the source server.
  2. Copy the backup file to the destination server
  3. Then restore the DBs onto the destination server.

The the system DBs are a bit different. Follow this link for one possible way. Then again, it depends what you need from the system tables. The master contains your SQL logins, which you can script out. Here is a link for scripting out the logins.

1
  • You can also skip the copy step if your network is resilient enough to support backing up directly to the new server (or to a share that both can access) or restoring directly from the backup on the old server (or from a share that both can access). Commented Aug 3, 2013 at 13:33
3

I don't see a need (nor I do it when migrating server) to do a restore of master (or system databases) on the destination server - unless you are trying to achieve something that you have not mentioned in your question.

I find it easy to use sp_help_revlogin to just move logins from one server to another.

so your migration steps would be like below: Server A = Old server ; Server B is NEW server.

  • script out logins from server A.
  • script out jobs from server A.
  • Backup and restore databases from Server A to Server B.
  • Recreate logins scripted out from server A to Server B.
  • Sync up orphan users (if any).
  • Recreate jobs scripted out from server A to server B. Make sure to move packages if any as well.
  • change compatibility level (if moving from lower to higher version - You dont need this step as sql server version is same in your case !!).
  • run DBCC UPDATEUSAGE
  • update stats on all tables with fullscan
  • Run DBCC CHECKDB ('<db_name_goes_here>' ) WITH ALL_ERRORMSGS,NO_INFOMSGS, DATA_PURITY

You can refer to my script at https://dba.stackexchange.com/a/41179/8783 (make sure you do a backup restore and not detach/attach method - now learned it !!).

Note: I have answered a sort of similar question here.

Edit: 08/22/2017

A more elegant and automated way of migrating database or entire server is to use dbatools e.g. start-dbamigration or other functions depending on your needs.

0

The time required to perform a backup, move the database, and restore it can be significant for a large database. To reduce database downtime when moving to a new server, it is more correct to first transfer a separate full backup without stopping the database, then backup the tail of the transaction log and restore the tail.

  1. Execute a full backup of the server
BACKUP DATABASE [database_name] TO DISK = 'path_to_folder\full_backup.bak'
  1. Transfer full_backup.bak to the new server.
  2. Restore full_backup.bak on the new server, but DO NOT switch it online (WITH NORECOVERY)
RESTORE DATABASE [database_name] FROM DISK = 'folder_path\full_backup.bak' WITH NORECOVERY
  1. Execute a backup of the tail of the transaction log. Please note - this command will shut down your origin server. (WITH NORECOVERY)
BACKUP LOG [database_name] TO DISK = 'folder_path\log_tail.trn' WITH NORECOVERY
  1. Move log_tail.trn to the new server
  2. Restore log_tail.trn to a new server, and switch the database to online mode (WITH RECOVERY)
RESTORE LOG [database_name] FROM DISK = 'folder_path\log_tail.trn' WITH RECOVERY

Since log_tail.trn is smaller than full_backup.bak, manipulations in 4,5,6 steps will be very fast.

Tail log backup via SSMS

Backup the Tail of the Transaction Log

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.