When your backup is against the wall

Our flooded backyard on Christmas Day 2015

So, we had a developmental database which a developer wanted to clean up and put on a production server.  I did a backup, and cleaned up the database and had it moved to the new server.

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 IMMEDIATE

Then, after you're done, put it back in multiuser mode by doing a

 ALTER DATABASE OOPS SET MULTI_USER WITH ROLLBACK IMMEDIATE

 Also, you might need to use the REPLACE option on the full backup by doing a WITH REPLACE, NORECOVERY