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 scheduler 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 scheduler 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.
hi,
usually you will not be able to restore system databases to a server that is not the one you took the dumps of...
you can restore on it users databases with little efforts (usually related to database users/logins mapping), but system databases should not be restored from dumps of different servers..
if this is not the case, perhaps the files related HP restore did not a good job.. I did not find lot of info about problems like that..
you can perhaps try, in order to verify the problem is not "Network" related, modifying the connection string to "(Local)\InstanceName", in order to activate a shared memory connection and not a connection under a different network protocol..
you can even check all the required newtowork protocols are enabled via the SQL Server Configuration Manager and SQL Server Surface Area Configuration...
but both these are related to
>The configuration of the AdminConnection\TCP protocol in the SQL instance SQLEXPRESS is not valid.
and
>Named Pipes Provider: Could not open a connection to SQL Server [2].
and not with the very first problem,
>Configuration block version 0 is not a valid version number...
this seems to me more .Net assembly related versus SQL related..
waiting for Mike to jump in 
regards
|||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?
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.
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. I can do it with the operating system and everything else.
Do i need to re attach the databases or something additional like that?
|||hi,
I usually have seen total disaster recovery performed via traditional CDs..
I mean, you have all your installation media from which install the OS, all software and the like... (actually new machines as well 
).. then you go on with SQL Server databases.. but "user's databases" and not system dbs as it's usually a mess to mix inter-instances system dbs..
taking a "live backup" of the folders SQL Server is running from and it's related subdirs, including data folder, is usually not safe for dbs, as this, if available to the backup sw at all, includes unclean data from the physical files.. tapes and backup sw vendors should use the appropriate apis to interface to and interact with SQL Server live databases or the result will just be a mess...
if something goes wrong, say, master.mdf gets corrupted, you just restore a corrupted master database.. and usually that means SQL Server will not be able to start at all, so you are no longer able to restore your last valid (SQL) backups.. but you are required to "rebuild" the master db.. from the installation media.. http://msdn2.microsoft.com/en-us/library/ms143269.aspx ...
(this is very hand in SQLExpress scenario as well, as, in MSDE 2000 time frame, you are out of luck as the feature was not available in the setup options, and no rebuildm.exe was shipped along with MSDE package..)
when you are done, you can then restore your user's databases as well..
but I'm still waiting for Mike to jump in for further info about you initial exception 
...
reagards
|||(cross-posting from my reply to this question in the SQL Disaster Recovery forum http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=744&SiteID=1 )
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:
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.
sql