UBQ000205:  Microsoft SQL "WITH MOVE" Option

UBQ ID Number: UBQ000205
Last Modified: 2010-01-12

Summary:

This document details the process of restoring a Microsoft SQL database to an alternate location (or with an alternate database name) using UltraBac.

Details:

UltraBac has the ability to move Microsoft SQL databases to either an alternate SQL server or to the same server (renaming the Databases) by using the SQL "WITH MOVE" option when restoring.

 

To use the "WITH MOVE" option, the exact database file names (both the data and log files) will need to be known. This information can be found in the backup index, after the database name. Using the "Northwind" database as an example, the database file names are "Northwind" and "Northwind_log".

 

ub_93_knowledgebase205_dbfiles.jpg

Fig. 1 - Names of the database files.

Performing the Restore

In this example, the restore will be moving a database to a different SQL Server. When using the "WITH MOVE" option, only one database can be restored per operation.

The drive and the path where the new files will go does not have to be the same as the original, but the path must exist before the restore is performed. When restoring to the same server the same path where the original files are can be used, but the actual file names MUST be changed and an alternate database name must be supplied or the original database / database files will be over written.

 

  1. Launch the Restore by clicking the “Restore tab, then selecting an option from "Locate Backup Index."

  2. Load the index for restore.

  3. Select the database for restore.

  4. Click "Action"/"Restore this Backup."

  5. In the UltraBac SQL Agent Restore Options dialog, change the "Restore Destination" to the target server name.

  6. Click "SQL MOVE Parameters."

 

ub_93_knowledgebase205_sqlmove.jpg

Fig. 2 - SQL MOVE Parameters dialog.

 

  1. In the "Alternate database name" box, enter the desired database name.

  2. In the "Restore to path" box, type the new path for the database.

  3. If the "Database files and logs" section did not auto-populate, click "Add."

  4. In the "Original Database File Name" box, type in the original file name (without typing the .mdf).

  5. In the "Restore As" box, type the path of the file and the file name (making sure to rename the file if needed) using .mdf/.ldf.

  6. Repeat steps 9-11 for the transaction logs.

  7. Click "Next"/"Restore"

 

When the restore is finished there will be a new database with the "Alternate database name" on the SQL server specified.

More Information:

see UltraBac User Manual:  SQL Agent