Using Management Studio Express to Migrate a Veeam Backup database to a new SQL server

KB ID: 1250
Products: Veeam Backup & Replication
Version: 5.x, 6.x, 7.x, 8.x, 9.x
Published:
Last Modified: 2017-01-31

Challenge

How do I move the Veeam Backup SQL database to a new SQL Server using either SQL Server Management Studio or the Command Prompt?

Solution

Microsoft has provided us with options for relocation of databases and their associated transaction logs, as well as tools to perform these operations with ease and reliability in both small and large computing environments. Regardless if you have five servers or a complex infrastructure with hundreds of servers, this process will allow for the seamless movement of databases from any compatible operating system.

Moving a database by using detach-and-attach operations involves the following stages:

1) Detaching the database.
Detaching a database removes it from the instance of the Microsoft SQL Server Database Engine, but leaves the database intact with its data files and transaction log files. To detach the database using SQL Management Studio, you will expand the Instance, right-click on the Database you wish to Detach, click Tasks, and then click Detach. Be sure you have selected the correct database performing the Detach action.

2) Moving the database files to the other server or disk.

The database is traditionally stored in the Data location of the SQL Server program folder on the local disk. Each instance contains a “.#” suffix following the MSSQL folder, the default instance contains a 1, followed by each other instance which increases by 1 respectively. In the data folder, you will see the.mdf (database) and .ldf (transactional logs) files.
Both of these files will need to be relocated to their new server They do not necessarily have to be placed in a similar location - they can be placed anywhere as long as the SQL Server has direct access to the storage. It is actually Microsoft's best practice to place these files on different storage volumes as the transaction log file grows indefinitely, and can become significantly larger than the database file.

3) Attaching the database by specifying the new location of the moved file or files.

Once you have successfully logged into the SQL Server instance where you would like the database to be attached to, you will need to expand the instance, right-click on the Database node, and click Properties. Here, you will find the Attach option. Next you can browse to the new location where our original database files now reside. Only the .mdf files will be displayed in this view. You will have the option to verify the location for the .ldf files after click OK.

Detaching the database

User-added image


Locating the Database Files

User-added image

Attaching the database
User-added image


After your database is moved successfully, make sure to change the following registry keys on your Veeam Backup & Replication server:

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

For 64-bit Installations, the following registry keys should also be updated:

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
 

 

More Information

If the Management Studio is not available, we can perform these same steps using the SQLCMD from the Command Prompt. We will need to use the –d parameters to define the database which we would like to log into locally.

User-added image

We can then execute this statement to detach the database:

exec sp_detach_db @dbname='VeeamBackup'


    , @skipchecks='false'


    , @keepfulltextindexfile='true';


GO


User-added image

Once it has been successfully detached, we can then manually move the files into their new location.

User-added image

Then we can perform this command to login to the remote server:


SQLCMD -S 192.168.85.3\SQLEXPRESS

Followed by this command to perform the attach operation:

EXEC sp_attach_db @dbname = N'VeeamBackup',


 @filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\VeeamBackup.mdf',


@filename2 = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\VeeamBackup_log.ldf';


GO

User-added image

Now, you can perform this task programmatically or without the help of Microsoft SQL Management Studio.

 
4 / 5 (10 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