September 24, 2014 at 5:35 pm
When doing a sql database restore from backup, I thought the 'with replace' option was required to overwrite an existing database.
However, I found out (the hard way), that it will overwrite even without replace .
--drop database _tmpdb
create database _tmpdb
use _tmpdb
create table _tmptable (x int)
insert into _tmptable select 1
backup database _tmpdb to disk = 'D:\_tmpdb.bak' with init
insert into _tmptable select 2
select * from _tmptable -- 1,2
use master
restore database _tmpdb from disk = 'D:\_tmpdb.bak' -- overwrites without error or warning
select * from _tmpdb.._tmptable -- 1
what's going on here ?
September 24, 2014 at 10:55 pm
Quick question, is the database in SIMPLE recovery mode?
😎
September 25, 2014 at 12:45 am
Eirikur Eiriksson (9/24/2014)
Quick question, is the database in SIMPLE recovery mode?😎
there is no any relation to database recovery mode
September 25, 2014 at 12:57 am
To add to Eirikur Eiriksson's comment, if it's not SIMPLE recovery it won't overwrite.
Eg. Add this after the create database statement:
ALTER DATABASE [_tmpdb] SET RECOVERY FULL
GO
It will not overwrite.
If you then take a log backup it will restore and overwrite without the REPLACE keyword.
According to books online, REPLACE is there to allow you to overwrite a database that is different to the one in the backup file. If it's the same database that has been backed up, then no problem, it will overwrite without question if it's on the same server where the backup was taken.
September 25, 2014 at 6:23 am
Eirik - YES, they are in SIMPLE recovery !
Andrew - It is a different server however - backup taken on PRODUCTION, while restore done on TEST
but the logical & physical & database names are exactly same.
yeah, i guess the answer was simple 🙂
September 25, 2014 at 6:29 am
waeva (9/25/2014)
Andrew - It is a different server however - backup taken on PRODUCTION, while restore done on TESTbut the logical & physical & database names are exactly same.
It's not about the logical file names or database name. It's about whether it's the same database.
If the database on test that you were overwriting had previously been created by restoring a backup of prod, then the database in the backup is the same database (internal IDs the same) as the one on the server and hence in simple recovery (no need to backup the tail of the log) a restore will overwrite the existing database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 25, 2014 at 6:37 am
Gail - yes, it's the same db. Lower environments were created using Production database backup.
September 25, 2014 at 9:38 am
waeva (9/25/2014)
yeah, i guess the answer was simple 🙂
Nicely put!
😎
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply