Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer
    
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 !!).
  • 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 http://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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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