A couple days ago one of our production databases suddenly imploded, and the only warning it gave us wasthe status of the database in Management Studio Express said "Suspect" after the database name.
We couldn't write more data, update data, but my team found a couple articles that came in handy:
Code Project: How to restore a suspect database
and
SQL-Articles
The first was most useful. But because altering the state of the database and repairing it causes just a bit of ice water to run through my veins, first we did a test restore of the previous night's backup to SQL Express running on my laptop.
Now, if you have a full backup, a differential backup, and a few transaction logs to apply, here's what you'll want to do in the GUI. There are others who swear by TSQL but I'm not fluent in that language so here's my way.
In SQL Management Studio, right click on Databases, select New Database
Create a new database with the identical name to the one you're restoring
I left all options at defaults
Then, with your .bak file handy, restore over the empty database you just created.
Right click on the empty database just created, select Tasks > Restore > Database
In the dialog box, select "source for restore" *from device, and locate the full backup file to restore first. Then select Options in the top left.
Change the path for "Restore As" to a valid path. For the full backup, you'll need to check "Overwrite the existing database"
If, IF you have a differential backup and/or transaction logs to apply after the full backup, then you'll need to select:
Leave the database non-operational and do not roll back uncommitted transactions ... (RESTORE WITH NORECOVERY)
if you don't choose this, you won't be able to restore the differential or txn logs.
If you're only restoring the full backup and nothing else, keep the default first option "Leave the database ready to use by rolling back uncommitted transactions ... (RESTORE WITH RECOVERY)
Got that?
Click OK when ready and wait patiently for the first increment of 10% to be passed. Progress is measured in 10% increments for reasons known only to the MS SQL team.
After this one is up, if you chose the RESTORE WITH NORECOVERY option, the database will say "Restoring"
Now do about the same thing as above to restore a differential if you have one. If you have transaction logs, choose the NORECOVERY option, if this is it, chose the RECOVERY option and you'll have a functioning database when you're done.
If you need to restore a transaction log,
Right click on the database name > Tasks > Restore > Transaction log
choose the file, and select the RESTORE WITH RECOVERY option if you want the DB to be functional after this txn log is applied.
Possible errors you may see:
System.Data.SqlClient.SqlError: The log or differential backup cannot be restored because no files are ready to rollforward. (Microsoft.SqlServer.Express.Smo)
For me this meant I chose RESTORE WITH RECOVERY when I still had differential or transaction logs to apply.
System.Data.SqlClient.SqlError: This differential backup cannot be restored because the database has not been restored to the correct earlier state.
For me, this meant I had restored a full backup but didn't have the next in line differential to apply. I found a newer full backup and then was able to restore the diff. Don't ask, I had two different apps making full backups of the database.
HTHSomeone ...
