How do I move the Veeam Backup SQL database to a new SQL Server using either SQL Server Management Studio or the Command Prompt?
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
Locating the Database Files
Attaching the database
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:
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
Next connect to the master database
We can then use the following command to detach the database
eg: sp_detach_db ‘VeeamBackup’
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.
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'
Then we can perform this command to login to the remote server:
Now, you can perform this task programmatically or without the help of Microsoft SQL Management Studio.