Creating new database from a backup of another Database on the same server?

sql

backup

restore

database

byAnton Shumikhin·Jan 7, 2025

To clone a SQL Server database on the same server, execute a RESTORE DATABASE command while making sure to use distinct target filenames. Use WITH MOVE to allocate unique paths for data and log files to avoid confusion or clashes.

Here is the SQL approach:

RESTORE DATABASE NewDB
FROM DISK = 'C:\\Backup\\OldDB.bak'
WITH MOVE 'OldDBData' TO 'C:\\Data\\NewDB.mdf',   -- Fun fact: DB files prefer to be unique, just like us!
     MOVE 'OldDBLog' TO 'C:\\Data\\NewDB.ldf',    -- Always remember to log your moves. The DB files like it.
     REPLACE;                 -- Adios, current format! You've been REPLACED!

This command copies OldDB into NewDB, ensuring safe relocation with no file path clashes. Ensure to replace OldDBData, OldDBLog, file paths, and also the database names in accordance with the specifics of your setup for a seamless procedure.

Step-by-Step Guide and Scenarios

Graphical Approach Using SQL Server Management Studio

If you prefer a visual approach, SSMS is an excellent tool to ease backup and restoration operations:

Right-click on the Databases section in SSMS.
Select Restore Database... option.
Choose Device in the Source panel and browse for the .bak file.
Navigate to the Files tab and rename the Restore As columns to unique names for data and log files.
Go to the Options section, select Overwrite the existing database (WITH REPLACE).
Press OK to restore the database.

Common Mistakes & Solutions

Avoiding common mistakes gives you a seamless restore operation:

Similar filenames: Always modify the destination filenames to avoid overwriting original MDF and LDF files.
Access permissions: Validate your SQL Server instance has required read/write access over the backup file and the destination paths.
Server Operation: Consider the load and time. Restoring large databases can be a resource-intensive operation.
Compatibility: Make sure the backup compatibility level aligns with the SQL Server instance version.

Tricks & Tips for Backup and Restore

When it comes to restoring to a new location for the same server, keep these pointers in mind:

The RESTORE DATABASE SQL command used in conjunction with MOVE not only renames but is essential to define new files paths.
Other keywords such as NOUNLOAD and STATS deliver control over backup media and provide an insight into the restore operation progress respectively.
There's no need to create a fresh database; the RESTORE command, combined with REPLACE will create or overwrite an existing database.
The initial database files remain untouched; MOVE merely alters the restore operation's delivery path.

Good Practices for Restoring Databases

Ensure a smooth restoration with these tips:

Double-check destination: Confirm that the data and log file paths for your new database are correct before running the restore command.
Clear Naming: Be clear with filenames related to the new database to avoid ambiguity.
System Conflict: Steer clear of names that could conflict with system databases.
Test Run: Always perform a test restore in a non-production environment to validate the process.