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 GO RESTORE DATABASE my_database_copy FROM DISK='D:\backup\my_real_database.bak' WITH 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 GO RESTORE DATABASE my_real_database FROM DISK='D:\backup\my_real_database.bak' WITH REPLACE
There are more options on restoring database, such as using logs and differential backups, but that will be another post.