SQL Tip: Restore Database With Different Name

Sometimes you want to have a copy of an existing database on the same database server (hopefully not on a production server, but hey it’s your system). The quick and dirty solution is to have the backup of the said database and restore it with a different name. Here is a quick tip, how to restore a database with a different name in SQL Server.

The easiest way to do this is using the SQL command:

USE master
RESTORE DATABASE my_database_copy FROM DISK='D:\backup\my_real_database.bak'
   MOVE 'my_real_database' TO 'D:\mssql\my_database_copy.mdf',
   MOVE 'my_real_database_log' TO 'D:\mssql\my_database_copy_log.ldf'

If you just want to replace (overwrite) the existing database with a full backup file, the command is even easier. The keyword here is REPLACE.

USE master
RESTORE DATABASE my_real_database FROM DISK='D:\backup\my_real_database.bak'

There are more options on restoring database, such as using logs and differential backups, but that will be another post.

Photo by Roman Synkevych on Unsplash

One thought on “SQL Tip: Restore Database With Different Name

Leave a Reply