Restoring a backup of one database to another (that already exists)?

Question

Hi,

I am using Microsoft SQL Server Standard (64-bit) version 11.0.7507.2. I have a production database named YICSQLNJ.

Some time ago, I backed up this database to a file named C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\YICSQLNJ_20211224.bak. Wanting to create a test database that I could work with without risking actual data, I restored this backup to a new database named PK_YIC_NJ using the following command:

RESTORE DATABASE PK_YIC_NJ FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\YICSQLNJ_20211224.bak'
WITH MOVE 'YICSQLNJ' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PK_YIC_NJ.mdf',
MOVE 'YICSQLNJ_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PK_YIC_NJ_log.ldf'

This worked fine at the time. Now, I have a more recent backup that I would like to restore to my test database. My question is, now that PK_YIC_NJ already exists and has data in it, will the command above still work (assuming I update the name of the .bak file to reflect the more recent backup)? Or should I first delete PK_YIC_NJ before I do this?

Any suggestions will be greatly appreciated.

Thanks in advance,
Paul

Answer 1

There are two options:

1.) You drop that database before and then restore it from your backup
2.) Add "With Replace" to your RESTORE command
RESTORE DATABASE PK_YIC_NJ FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\YICSQLNJ_20211224.bak' WITH REPLACE

https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-ver16#REPLACEoption
https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-ver16#examples

Answer 2

Thank you Bjorn !