SQL Server: Simple database restore with overwrite

Question

What I'm trying to do is backup one of our production databases and restore them as our dev database (overwriting what's been there).

That's how I back up:

backup database [authfx] to disk = N'f:\db_backups\authfx\authfx-latest.bak' 
 with
 noformat, 
 init, 
 name = N'authfx Latest Full Database Backup', 
 skip, 
 norewind, 
 nounload, 
 stats = 1;

Now when I try to restore other database using

restore database [dev-authfx] from disk = N'f:\db_backups\authfx\authfx-latest.bak' 
 with 
 file = 1, 
 nounload, 
 replace, 
 stats = 1;

it barks all over the place:

Msg 1834, Level 16, State 1, Line 10
The file 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\authfx.mdf' cannot be overwritten. It is being used by database 'authfx'.
Msg 3156, Level 16, State 4, Line 10
File 'authfx' cannot be restored to 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\authfx.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 10
The file 'F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\authfx_log.ldf' cannot be overwritten. It is being used by database 'authfx'.
Msg 3156, Level 16, State 4, Line 10
File 'authfx_log' cannot be restored to 'F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\authfx_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 10
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 10
RESTORE DATABASE is terminating abnormally.

I understand that I need to add with move and specify where original data and log files go for the dev database, but is there any way I can omit this and just say: restore this database from this other backup, do not touch anything else and overwrite what's been here previously.

Answer 1

Unfortunately not. SQL Server will always attempt to restore to exactly what is recorded in the backup file, unless you explicitly specify otherwise (with MOVE in this case).

The REPLACE option only works if you're replacing files owned by the database you're restoring (which makes sense, You may have said you're fine killing DB_A by restoring over it, but SQL doesn't really know how you feel about killing DB_B as well).

If this is something you'll be doing regularly then, then write a small sql script with everything stated, save it and use that every time you want to do the restore. This is how I do our overnight restores for dev/UAT/Training from prod.

Answer 2

RESTORE DATABASE DB_NAME ... WITH RECOVERY, REPLACE;

-- will restore your "DB"(overwrite) ALWAYS!

Do not listen for "DB_A & DB_B as well". "DB" will be your restored database name. Use RECOVERY for the state "ON-LINE" and REPLACE for overwriting existing database files. Use MOVE for the different file location (different from original one). No other case. No other options use! Any "SKIP, NOREWIND, NOUNLOAD" is no need. It's for tapes only. All your mistake was in the same old_physical_file_names for the "new_physical_files" which really belongs to another database (child mistake for admins to multiple DB cloning on the same resource). Make every time new SUBFOLDER for cloning DB and take a patience at all. Or use MOVE option.

Good luck.