SQL Tip: Restore Database With Different Name

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 response

  1. […] while ago, I wrote an article here on how to restore a complete database with a different database name. That is actually restoring a database from a full backup. This time, in addition to that, I want […]

Leave a Reply