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

KB ID: 1250
Product: Veeam Backup & Replication
Version: All
Published:
Last Modified: 2017-11-02

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


To associate Veeam with the restored database, please use the DBConfig Utility to change the DB and settings that Veeam is connecting to.
Please refer to this user guide page on using the DBConfig Utility:
https://helpcenter.veeam.com/docs/backup/hyperv/using_dbconfig_utility.html?ver=95

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 -S parameter to log into the SQL instance. You would use this 
command to log into both the source and target instance.

 Sqlcmd -S <servername\InstanceName>     eg:  sqlcmd -S .\VeeamSQL2012

User-added image
Next connect to the master database

 
Use Master
Go

 
User-added image

We can then use the following command to detach the database
sp_detach_db ‘databasename’    
Go


eg: sp_detach_db ‘VeeamBackup’
go

User-added image

To attach the database to the new instance after following the previously listed steps to connect to the new server/instance use the following command.

 
sp_attach_db 'database name','location of mdf','location of logs'
go


eg: sp_attach_db 'VeeamBackup','C:\Program Files\Microsoft SQL Server\MSSQL11.VEEAMSQL2012\MSSQL\DATA\Veeambackup.mdf','C:\Program Files\Microsoft SQL Server\MSSQL11.VEEAMSQL2012\MSSQL\DATA\VeeamBackup_log.ldf'

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.

 
5 / 5 (13 votes cast)

Couldn't find what you were looking for?

Below you can submit an idea for a new knowledge base article.

Request new content

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