Restoring SQL

Our recent SAN crash resulted in our needing to recover all 13 of our virtual servers from backup and has hampered my blog posting, but the blog must go on… so back on track….

One of or servers MIA was our SQL server.  We reinstalled a base SQL installation with the appropriate service packs and then began to restore our databases. 

As many of you know we use SonicWall’s CDP 4440i for our backup.  This is a disk to disk to disk solution that has worked really well.  It has a SQL agent , and we had the agent installed prior to our SAN failure which had been backing up a full backup daily, 3 hour differentials.    So we reinstalled the agent and planned to bring the databases back online. 

Since most were 3rd party databases, we elected to allow the applications to recreate the databases and then restore the data back over the empty databases.  This allowed the connectivity to the SQL server to be restored before we tried to restore the databases. 

SonicWall’s agent has a feature when you select the data set you want restored and you select restore to application. The database is taken offline, restored and then brought back online.

No problems, well no problems except for one database.  Our hourly staff use TimeClock Plus to document their hours worked and that was the DB that caused problems.  For some reason we couldn’t restore the DB to SQL with the SonicWall tool.  So we elected to use option 2, save the .bak file locally on the SQL server and use the SQL restore function.  This option didn’t work either, in-fact after calling Microsoft we still couldn’t restore the database.  When you would choose to restore the DB in SQL you couldn’t find the .bak file when you would browse for it.  The SQL manager console would just display a blank folder.  So we contacted SonicWall thinking the .bak was corrupt.  Engineering at SonicWall had me upload the .bak file and worked on our case for several days with no avail.  Finally they remotely connected to see if they could help that way.  It finally dawned on me to save the .bak file on the e: drive rather than the c: drive since that is where SQL is installed.  Suddenly SQL recognized the .bak file.  I took the database offline, ran a SQL command and we were in business. 

In case you want the SQL command:
Restore database <databasename> from disk=’<Disklocationof.bak>’ with replace, recovery

Some things we learned:

• SQL needs the .bak file on the same drive as SQL is installed. 
• The SQL DB has to be offline before you can run the recovery command.
• Microsoft SQL support sometimes overlooks the easy things. They didn’t think to tell us to move the .bak file to the e: drive.

So once again the CDP has paid for itself, and some experience has helped us for the future.