Restore SQL Server Express Databases after a Disaster Recovery

Hello,

I would like to restore SQL Server Express and its databases from a tape backup to the same server. This is a disaster recovery senario.

I backed up the Master, Model, MSDB and my own test database using SQLCMD scripts. I have no problem restoring these using task manager on the server before the disaster recovery.

However, in my real disaster recovery testing, When the server is restored by tape drive (HP one button disaster recovery), I try to run my SQLCMD restore scripts in task manager and I cannot connect to the sql server. Also I cannot connect with Management studio. I have recieved the following error in event viewer.

-

Event Type: Error
Event Source: MSSQL$SQLEXPRESS
Event Category: (2)
Event ID: 3411
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
Configuration block version 0 is not a valid version number. SQL Server is exiting. Restore the master database or reinstall.
Data:
0000: 53 0d 00 00 15 00 00 00 S.......
0008: 16 00 00 00 43 00 4f 00 ....C.O.
0010: 50 00 4c 00 45 00 59 00 P.L.E.Y.
0018: 4e 00 45 00 57 00 53 00 N.E.W.S.
0020: 5c 00 53 00 51 00 4c 00 \.S.Q.L.
0028: 45 00 58 00 50 00 52 00 E.X.P.R.
0030: 45 00 53 00 53 00 00 00 E.S.S...
0038: 00 00 00 00 ....

Event Type: Warning
Event Source: SQLBrowser
Event Category: None
Event ID: 3
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
The configuration of the AdminConnection\TCP protocol in the SQL instance SQLEXPRESS is not valid.

Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7024
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
The SQL Server (SQLEXPRESS) service terminated with service-specific error 3411.
C:\Program Files\Microsoft SQL Server\90\Tools\Binn>sqlcmd -S.\SQLExpr
COPLEYNEWSDATABASEscripts\MASTERFULLRESTORE.sql"
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred whi
shing a connection to the server. When connecting to SQL Server 2005,
re may be caused by the fact that under the default settings SQL Serve
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

-

My question is, what is the correct procedure to follow when I want to do a disaster recovery and restore SQL Server Express from tape backup using the Simple Backup method and scripts.

Is it always required to reinstall sql server express from the original program file or is it possible to reinstall from back up tape.

I know my backup and restore scripts work because I tested them on the server before I do the disaster recovery and rebuild that server from tape.

This is some kind of issue with SQL Server Express being restored by tape backup.

Any suggestions, thanks.

The error message, "Configuration block version 0 is not a valid version number" tells us that the configuration page of the master database, which is one of the first pages read when we start up, is invalid. It appears that for some reason, the master database is not being correctly restored by your DR process.

If you can tell us more about your process, what specific tools and scripts make up your backup/restore process, we can search for the root cause.

|||

Thank you for your reply.

I guess I am wondering what other people do for disaster recovery. Is there something wrong with my disaster recovery plan if I am only looking at the Simple Backup as an option?

I do a daily backup the Master, MSDB, Model, MyDatabase using simple backup and running a SQLCMD that backs up the database files Master.bak, MSDB.bak, Model.bak, and MyDatabase.bak to a different directory. This way I can have my full tape backup of my server run afterwards and back up everything (Operating System, SQLExpress, Master.bak, MSDB.bak, Model.bak, MyDatabase.bak etc.) . Then when disaster hits, I can restore my same server from last nights tape backup and then run my SQLCMD restore scripts to restore the Master.bak, MSDB.bak, Model.bak, MyDatabase.bak.

I have done many disaster recoveries from tape backup with no problems with the Operating System or any applications, except when it comes to SQL Express. I have also run my restore scripts on the server with no problem restoring.

For some reason, after I follow the steps above, if I then go to open the SQL Express management studio express, I cannot connect to the server and its databases. This does'nt seem to be a settings issue unless settings are being changed behind the scenes. It does'nt make any sense to me that I would not be able restore SQL Express and its databases by tape backup.

Is it possible to restore the SQL Server Express from tape backup and then run restore scripts generated from the same tape backup up of the Master, Model, MSDB, etc. databases?

|||My SQLCMD scripts for backup and restore are all generated from the Sql Express Management Studio Express, simply by allowing it to write the code and then I mannually paste them into *.sql files that are later run by SQLCMD. I've tested all of them and they all work. I only have problems when I do a disaster recovery senario on the same server by restoring all the OS and data back to the server. After restoring the server, then I restore the databases with the scripts and .bak files that were previously backed up. They all restore with no errors. However, when I try to connect to SQL Server Express, it can't connect.|||

The issue is that the backup app is not properly restoring master. Most likely it failed to back up the file because it was locked by SQL at the time. Backup apps using the VSS infrastructure do not have this issue.

So, you have a few options:

1) shut down SQL Express during the backup so that the database files are properly saved

2) after bare-metal restore, uninstall SQL Express (this will not remove any databases you've created), reinstall it, and then restore your master database from backup (along with all other databases). This should restore all customizations that you have made.

3) Books Online documents a procedure for rebuild the system databases (master, model, msdb) using setup.exe. I'm having trouble getting this to work. I'll post when I get this resolved.

|||

OK. Got it.

If you extract the setup files to a directory by downloading and saving sqlexpr.exe (if you haven't already) using the command sqlexpr.exe /x, you'll have all the setup files.

Then, use the command:

start /wait <CD or DVD Drive>\setup.exe /qn INSTANCENAME=SQLExpress REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<NewStrongPassword> REINSTALLMODE=vomus

The setup will recreate all of the system databases without disturbing anything else. You can then restore master and the other databases from your backups and be up and running.

Can you please let me know what DR utility this is? I'd like to work with them to make it work better with SQL.

|||Thanks for your help Kevin, I am using HP one button disaster recovery with Tapeware backup software. It seems to work very well for me. The only thing it does'nt back up are log files and *.MDF files. Basically if its an open file then it might have trouble backing it up. Anyways here was my solution, which I feel is the least time consuming.

After a week of trying to figure out a working disaster recovery solution for sql express, I have finally succeeded.

I wanted to do a disaster recovery to the same server with a tape backup and then restore the Master.bak, Model.bak, MSDB.bak, and MyDatabase.bak, back to the SQLExpress DBMS. Let me start out by saying that you cannot do that unless you have a working copy of the Master.mdf on the server you are trying to restore to. The catch here is that the tape backup software will not back up the Master.mdf because its always being used by SQLExpress.

Now, I suppose I could have dettached the Master.mdf before I backed it up, but I decided to go a different route.

I found out that there is a directory called "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Template Data" and in it is a copy of the same contents (except I think that they are the original files created automatically by the original install, in other words, not uptodate) found in the "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" directory where the files need to reside in order to do a restore to the database, which happened to be missing after I did my tape back up restore. I simply draged and dropped a copy of the missing *mdf, *ldf files and then I was able to launch the SQLExpress Studio Management Express and connect to the SQL Server. The only thing different was that the Model database was set to (Read Only) but that didn't concern me because I was going to restore all the Databases (Master, Model, MSDB, MyDatabase) anyway.

After that was solved, I could now run any *.sql restore scripts and have them work.

Next step is to restore the Master.mdf but you can only do that in single user mode.
So, I went into SQLServer Express, single user mode with the command prompt:
C:cd "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn"
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn: Sqlservr.exe -s SQLEXPRESS -m
Press<enter>
After it does its thing you need to open an additional command prompt window because it will say that its ready for connections in single user mode, but you will not be given a new cursor prompt.

So I opened a new comand prompt window:
C:\cd "C:\Program Files\Microsoft SQL Server\90\Tools\Binn"
C:\Program Files\Microsoft SQL Server\90\Tools\Binn sqlcmd -S.\SQLExpress -i"E:\scripts\MASTERFULLRESTORE.sql"

This is what my MASTERFULLRESTORE.sql restore script file looks like (I created it previously using the GUI in sql express and then saved it to a script file instead of clicking on OK):
RESTORE DATABASE [Master] FROM DISK = N'E:\DATABASEBACKUP\Master.bak' WITH FILE= 1, NOUNLOAD, REPLACE, STATS = 10
GO

The SQL restore script will work this time becuase I have a working instance SQLExpress thanks to the previous drag and drop restore of the Master.mdf from the Template Data directory.

After that you need to go into Services and start the 'SQLServer Service'.
Then simply go into SQLExpress like normal and restore the Model, MSDB, from the GUI interface by right clicking on each one and selecting restore and then point to your *bak files.

When it came time to restore 'MyDatabase', I could see it in SQLExpress Management Studio Express but it was just a shell of it with no contents inside. This shell of my database is from the Master.mdf restore. Unfortunately, even though I could see it in the SQLExpress listing, when I right clicked on it, hoping for the restore option, It was grayed out. So I closed SQL Express and ran my restore.sql script from the command prompt using SQLCMD.exe and it succeeded.
So I opened a new comand prompt window:
C:\cd "C:\Program Files\Microsoft SQL Server\90\Tools\Binn"
C:\Program Files\Microsoft SQL Server\90\Tools\Binn sqlcmd -S.\SQLExpress -i"E:\scripts\MYDATABASEFULLRESTORE.sql"

I then went back into SQLExpress and ran some queries to test and everything seems to be working now.

Thanks to everyone for helping to point me in the right direction to solve this problem.