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?


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.

  • 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). – Aaron Bertrand Aug 3 '13 at 13:33

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 !!).
  • update stats on all tables with fullscan

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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