Sql-server – Restore Database from Database with Different Name

restoresql server

I have 2 databases, DB1 and DB2. DB1 has production/live data. A script runs daily (takes about 3 hours) to fill DB2. The goal is to restore DB1 with DB2 so that DB1 is down for as little time as possible. I've restored databases before but never from a differently named one. How can I do this?

Best Answer

Are you looking to do something like a re-name swap? Something like this:

  1. Load DB2
  2. Rename DB1 to DB_temp
  3. Rename DB2 to DB1
  4. Rename DB_temp to DB2

Then you re-load DB2 again the next time?

If so you can simply alter the name of the DB rather than deal with the time a restore takes (the file names will remain the same but that really doesn't matter in this case.)

ALTER DATABASE CurrentDBName MODIFY NAME = NewDBName;

Now on the other hand if you actually want to keep DB2 in place and just restore over DB1 it would look something like this:

RESTORE DB1 FROM DISK = 'DB2 backup file' WITH REPLACE

There are several methods you can use to improve your speed. Probably the easiest is to stripe your backup.

BACKUP DATABASE DB2 TO DISK = 'loc1', DISK = 'Loc2', DISK = 'Loc3' WITH FORMAT
RESTORE DB1 FROM DISK = 'loc1', DISK = 'Loc2', DISK = 'Loc3' WITH REPLACE

Warning, you will need to have all of the files you stripe to in order to do your restore. If you lose one the whole backup is useless.

Related Question