July 16, 2007 at 11:35 am
I am trying to test my recovery/backup method.
So I have my backups running nightly (full) and logs every 30 minutes on the prod server.
Now I am trying to restore EVERYTHING to the DEV server.
So I have DEV with Windows 2003 and a fresh install of MSSQL 2005.
I then applied all the updates to both Windows and SQL Server and now DEV and PROD have the same software versions.
So I took my backup tape from a few days ago from PROD and placed it in DEV then stopped all the SQL services, except sql server which I set to -m for single user and then opened sqlcmd and executed:
RESTORE DATABASE master FROM TAPE = '\\.\tape0' WITH RECOVERY;
GO
This appeared to work and I received the message saying that SQL Server service was now stopping.
I went to restart the service and received:
Configuration block version 0 is not a valid version number. SQL Server is exiting. Restore the master database or reinstall.
..ouch.
So, I'm not sure why this happened.
The SQL Server versions were the same.... I don't understand.
I'm currently rebuilding the master database on my DEV server and then I intend to try again.
On a related tangent, I can't use SMSS to restore databases because when I select TAPE for the data source to restore from, it always times out... Does anyone know how to increase the time out window for that by chance (it times out when scanning the tape in SSMS)
July 16, 2007 at 6:59 pm
just insert from tape into disk and give a try.......and start sql in single user mode....
net start msssqlserver \c \m and check if its working.....
[font="Verdana"]- Deepak[/font]
July 16, 2007 at 11:55 pm
Restoring master database can't be done as how you do for a user db. You have to have the server in single user mode before you do a restore of master database. include the -c -m switch in startup parameters and then start sql services then restore the backup file.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 17, 2007 at 9:01 am
No to be rude, but I don't think anyone addressed my original issues.
I set the server to single user mode.
I then restored from tape with the TSQL code I listed.
Once I restarted after this process I received the error code I mentioned.
What I can't figure out is why this happened. Both versions of SQL Server were the same on the prod server I made the tape on, and the DEV server I restored the tape to.
July 18, 2007 at 1:53 am
Where they same editions and build numbers?
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 18, 2007 at 7:07 am
I finally ended up doing the entire thing through sqlcmd using the restore options indicating the file location on the tape.
So I can restore full backups at least, but I'm working on getting the hang of the log backups.
Do you HAVE to specify a log file stop date, or can you just restore the most recent log?
The server version is Microsoft SQL Server 2005 - 9.00.3042.00 (Build 3790: Service Pack 2)
Still not sure why I can't restore through the GUI due to the time out errors when it is reading the header, but oh well.
July 19, 2007 at 4:28 am
You will not be able to restore log backup for master database btw you would not be able to back them up too. how did you take a log backup then of master database.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 19, 2007 at 7:01 am
Sorry, I should have been more clear.
Log backups for the other databases, user, etc...
I don't bother with log backups for any of the system databases (granted I can't with Master but I think you can with either model or msdb).
Eitherway, I do a full backup nightly and the only logs I care about are the user databases which I do every 30 minutes.
July 19, 2007 at 8:02 am
In this case I usually:
1. start SQL server in single user mode
2. Restore system dbs from command line
3. Restart SQL Server in multi user mode
4. Open SSMS
5. Restore Full backup for the nth user database (leaving the db ready for T-log backups)
6. Restore the oldest T-log backup to the nth user database
7. Restore the next oldest T-log backup to the nth user database (repeat as necessary)
8. Restore the newest T-log backup to the nth user database
9. Repeat steps 5-8 for all user databases.
That's one way; however from reading posts of the really really good SQL server DBA's on this forum, it seems most have scripts just waiting to do the complete restore from start to finish, and do it all from the command line. (I think this should be every DBA's ultimate goal.)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply