I was recently doing some production restores to a new environment and noticed something a bit strange. I scripted out the restore commands and was running them one after another. As I was watching the paint dry I noticed in my script that I had a typo in one of my commands. I decided to stop the restore, fix it and re-run.
I got a fairly normal error stating that the files were still there and could not be overwritten without either “with replace” or “with move”. As a server admin, I didn’t like either of those boring options so I did a DROP on the database that was currently in the “Restoring…” state and kicked off the restore again. Bam, same error. So I did the drop again, and this time went to the file system and deleted the data and log files. Running the restore this third time worked just fine.
This struck me as quite odd. I didn’t have a lot of time to look into it, so I tried to recreate the issue at home. Drop’s usually get rid of the files, so I put on my testing gloves.
I first assumed that a database drops delete files off the filesystem. Ding that is correct.
Second, I assumed that my issue must have been because the database was in “restoring…” mode. EEEnnn!(or however you would spell the buzzer sound) incorrect.
I have SSD at home so the only way I could get any of my testing databases to stay in restoring mode for more than a millisecond was with no recovery. The drop command worked just fine and the files were gone. For a split second, I had almost given up, unable to re-create the issue, but that wouldn’t make for a very interesting blog post.
I decided I needed to get a good amount of data into my database. First I tried a simple “go 100000” with a simple insert command, that bought me a whole extra millisecond on my restore command. Next, to beef up my database I decided to get fancy.
INSERT INTO b(bloby) SELECT * FROM OPENROWSET(BULK N'C:SQLServer2008R2SP1-KB2528583-x64-ENU.exe', SINGLE_BLOB) as d; go 10
Blobs (aka image or the non-deprecated varbinary(max)) are a bit inefficient to store in SQL or any RDBMS. 10 copies of the service pack inside my db gave me enough time to cancel the restore command while it was in progress.
RESTORE DATABASE [Mandy] FROM DISK = N'C:dbMandy.bak' WITH FILE = 1, NOUNLOAD, STATS = 10
Cancel, run a drop command and the file is gone. Again, I was stumped but since I still had that bug in my head I tried running the same sequence of events but running the drop command from another spid. Sure enough I couldn’t re-run the database command because the drop on another spid didn’t delete the files.
Msg 3142, Level 16, State 1, Line 1 File "Mandy" cannot be restored over the existing "C:dbMandy.mdf". Reissue the RESTORE statement using WITH REPLACE to overwrite pre-existing files, or WITH MOVE to identify an alternate location. Msg 3142, Level 16, State 1, Line 1 File "Mandy_log" cannot be restored over the existing "C:dbMandy_log.ldf". Reissue the RESTORE statement using WITH REPLACE to overwrite pre-existing files, or WITH MOVE to identify an alternate location. Msg 3119, Level 16, State 1, Line 1 Problems were identified while planning for the RESTORE statement. Previous messages provide details. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
Personally, I think the intuitive approach would be to delete the files from the filesystem at the point the restore is canceled. That is how a copy/paste works and a canceled restore is useless so MSSQL may as well clean up the steaming pile of database.
So to recreate the issue:
1. make a big database with blob
2. back up the database
3. drop the database
4. start a restore
5. cancel the restore query
6 open new query window(spid) and drop the database that is “restoring…”
7. re-run the restore (without replace)
The important thing to note is, if you get the “with move” or “with replace” message, you probably have some files still hanging around that you should delete. Either that, or you are doing the restore in the wrong environment and SQL Server is begging you not to do that.