How to migrate a SQL database

KB ID: 1448
Products: Veeam Backup & Replication
Version: 5.x, 6.x, 7.x, 8.x, 9.x
Published:
Last Modified: 2016-04-29

Purpose

This step-by-step article describes how to move Microsoft SQL Server user databases and the most common SQL Server components between computers that are running SQL Server.
 
The steps that are described in this article assume that you will not move the master, model, tempdb or msdb system databases. The steps provide different options for you to transfer logins and the most common components contained in the master and msdb databases.
 
For information about the specific items not transferred when you follow the steps in this article, see the "More information" section.
 
Note: For Microsoft SQL Server 2008, refer to the "Managing Metadata When Making a Database Available on Another Server Instance" topic at the following in SQL Server 2008 Books on Line Web site:  http://msdn.microsoft.com/en-us/library/ms187580.aspx
 
Note: Data migration from SQL Server 2000 to Microsoft SQL Server 2000 (64-bit) is supported. You can attach a 32-bit database to a 64-bit database by using the sp_attach_db system stored procedure or the sp_attach_single_file_db system stored procedure, or by using backup and restore in the 32-bit Enterprise Manager. You can move databases back and forth between the 32-bit and the 64-bit versions of SQL Server. You can also migrate data from SQL Server 7.0 by using the same methods. However, downgrading data to SQL Server 7.0 from SQL Server 2000 (64-bit) is not supported. A description of each method follows.

Solution

In Veeam Backup & Replication v8 and higher the DBConfig Utility can be used to change the DB that Veeam is attempting to connect to, rather than editing the registry. http://helpcenter.veeam.com/backup/80/vsphere/index.html?dbconfig_utility.html

If SQL instance that Veeam Backup & Replication will connect to uses SA authentication, the DBConfig utility must be used.

Here is the Microsoft KB describing the procedure: http://support.microsoft.com/kb/314546

Please ensure that the account you are installing Veeam Backup on has at least db_owner rights on this database after it is moved.
 
After your database is moved successfully, install Veeam Backup & Replication on the desired machine and point it to the database that you have just moved.

If Veeam Backup & Replication is already installed, please change the following registry keys:

HKEY_LOCAL_MACHINE\SOFTWARE\VeeaM\Veeam Backup and Replication\SqlDatabaseName
HKEY_LOCAL_MACHINE\SOFTWARE\VeeaM\Veeam Backup and Replication\SqlInstanceName
HKEY_LOCAL_MACHINE\SOFTWARE\VeeaM\Veeam Backup and Replication\SqlServerName

 
HKEY_LOCAL_MACHINE\SOFTWARE\ Wow6432Node\VeeaM\Veeam Backup and Replication\SqlDatabaseName
HKEY_LOCAL_MACHINE\SOFTWARE\ Wow6432Node\VeeaM\Veeam Backup and Replication\SqlInstanceName
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\VeeaM\Veeam Backup and Replication\SqlServerName
 

5 / 5 (29 votes cast)

Report a typo on this page:

Please select a spelling error or a typo on this page with your mouse and press CTRL + Enter to report this mistake to us. Thank you!

Orphus system