SQL Server Migration – How to Move a Database to a New Server

migrationsql serversql-server-2008

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?

Best Answer

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

Backup DBs on the source server.
Copy the backup file to the destination server
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.

Related Solutions

Sql-server – Difficulty in moving a SQL Server database

If you wanted to make life easier, according to the Version and Edition Upgrades page, there exists an edition upgrade for SQL Server 2008 R2 Express to SQL Server 2008 R2 Developer. You could just perform an edition upgrade and call it a day.

Alternatively, if you wanted to keep with your original plan of installing a new instance and migrating the database...

If you can't find files on your system, try installing Everything. It is an extremely fast indexing application that will run in your notification area.

Once you find the backup file, try running the following command to see if SQL Server can access it:

RESTORE HEADERONLY FROM DISK = 'drive:\path\to\mybackup.bak'

RESTORE FILELISTONLY FROM DISK = 'drive:\path\to\mybackup.bak'

If that does not work, then try moving the file to a location that the SQL Server service account has access to. If you don't plan on accessing any network resources with SQL Server, it would be easiest to run SQL Server as local system on your workstation.

Once your Developer instance can see the .bak file, you can either use the Restore Database task in SSMS (Right-click Databases in Object Explorer and select Restore Database) or you can script it. Just remember in either option you should change the destination path to the data and log files so they reside in the new default data path of the SQL Developer instance (or some other directory on your workstation). By default, it'll try putting them back in the original location when the backup was taken.

Here is an example restore script you could modify (use the RESTORE FILELISTONLY details for the logical file names):

RESTORE DATABASE [MyDB] FROM DISK = 'drive:\path\to\mybackup.bak' WITH MOVE 'logicalDataFilename' TO 'drive:\path\to\physicalDataFilename.mdf', MOVE 'logicalLogFilename' TO 'drive:\path\to\physicalLogFilename.ldf'

SQL Server Migration – Moving Database from One Server to Another

Are you getting the error on the destination server?

Is the path present on the destination server?

You need to make sure that the paths defined in your CREATE DATABASE script exist on the destination server or you need to create them there.

Best Answer

Related Solutions

Sql-server – Difficulty in moving a SQL Server database

SQL Server Migration – Moving Database from One Server to Another

Related Question