CODEWITHSUNDEEP
sql-serverbackuprestore
Chris Magnuson
Chris Magnuson

Reputation: 5939

How to stop SQL server restore from overwriting database?

I have a ELMAH database that I want to script the restore of using the following:

RESTORE DATABASE [Elmah] 
FROM DISK = N'E:\Elmah_backup_2012_11_02_030003_1700702.bak' 
WITH FILE = 1,  
MOVE N'Elmah' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Elmah.mdf',  
MOVE N'Elmah_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Elmah.ldf',  
NOUNLOAD, STATS = 10
GO

Even though I am not including WITH REPLACE each time I execute this statement it restores over the existing database.

I will always drop all databases before this operation and I never want this code to accidentally restore a database over one in production.

How do I change this code so that it will never overwrite an existing database?

I am actually doing this through SMO objects but the principle and results are the same so I am hoping to keep this simplified to just the TSQL necessary in the hopes that I can generalize that information to what needs to be set on the appropriate SMO.Restore object.

Upvotes: 0

Views: 2912

Answers (1)

marc_s
marc_s

Reputation: 754220

You need to (a) give the restored database a new logical name, and (b) you need to define new physical file names, so the existing ones won't be overwritten. Try something like this:

RESTORE DATABASE [Elmah_Restored]       <== new (and unique) logical database name
FROM DISK = N'E:\Elmah_backup_2012_11_02_030003_1700702.bak' 
WITH FILE = 1,  
MOVE N'Elmah' TO 
     N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Elmah_restored.mdf',   <== new (and unique) physical file name
MOVE N'Elmah_log' TO 
     N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Elmah_restored_log.ldf',   <== new (and unique) physical file name
NOUNLOAD, STATS = 10
GO

Upvotes: 1

Related Questions