|Our flooded backyard on Christmas Day 2015|
But, communication is difficult. He wanted to clean up the database once it landed on the new server, not while it was on the old server since there was software using it.
No problem, I'll restore the database. Except, I made a boo-boo. I used the GUI to do the backup, and wound up doing a transaction log backup.
If you're not familar, SQL Server (and most other enterprise database platforms), SQL can make a full backup of its data in a handy file. It also records all changes made in what is called transaction log backups. Transaction log backups are much faster, so you can do them much more often. On this database, it was every 30 minutes.
In order to restore the database, I had to restore the full backup and every log file after. So the key to this is the WITHNORECOVERY keyword. Do the restore as a single script, with the "WithRecovery" tag on only the last entry
restore database OOPS from disk ='d:\OOPS_backup_2016_06_09.bak' WITH NORECOVERY;
Restore log OOPS FROM DISK = 'd:\OOPS_backup_2016_06_10_080020_0584990.trn' WITH NORECOVERY;
Restore log OOPS FROM DISK = 'd:\OOPS_backup_backup_2016_06_10_083023_4130230.trn' WITH NORECOVERY;
Restore log OOPS FROM DISK = 'd:\OOPS_backup_backup_2016_06_10_090017_4780062.trn' WITH NORECOVERY;
Restore log OOPS FROM DISK = 'd:\OOPS_backup_backup_2016_06_10_093017_3391104.trn' WITH NORECOVERY;
Restore log OOPS FROM DISK = 'd:\OOPS_backup_backup_2016_06_10_100019_1231400.trn' WITH NORECOVERY;
Restore log OOPS FROM DISK = 'd:\OOPS_backup_backup_2016_06_10_103017_7468920.trn' WITH RECOVERY
NOTE: If you are getting an exclusive access error, you'll have to put the database into single user mode by doing a
ALTER DATABASE OOPS SET SINGLE_USER WITH ROLLBACK IMMEDIATEThen, after you're done, put it back in multiuser mode by doing a
ALTER DATABASE OOPS SET MULTI_USER WITH ROLLBACK IMMEDIATEAlso, you might need to use the REPLACE option on the full backup by doing a WITH REPLACE, NORECOVERY