Restoring Databases in a DRP situation
ChrisAVWood
Posts: 361
Hi,
We have just completed a DRP exercise that involved restoring from backups taken by SQLBackup. In out DRP we are not backing up the actual database but the backups of the database. So after we have recovered the master and msdb databases our system will need the application databases restored as master is expecting them. Their definitions existed in master but we had problems in restoring them as the actual databases did not actually exist.
Any help would be welcomed on how to perform this sort of restore? We were using 5.2.0.2825
Thanks
Chris
We have just completed a DRP exercise that involved restoring from backups taken by SQLBackup. In out DRP we are not backing up the actual database but the backups of the database. So after we have recovered the master and msdb databases our system will need the application databases restored as master is expecting them. Their definitions existed in master but we had problems in restoring them as the actual databases did not actually exist.
Any help would be welcomed on how to perform this sort of restore? We were using 5.2.0.2825
Thanks
Chris
English DBA living in CANADA
Comments
Because the database was defined in master it could not be added as new or replace using the GUI. We deleted the database from master and then added it as a new database. This was the only way to restore from the RedGate backup.
Chris
Thanks for your post. If you are in a Disaster Recovery scenario where you have to restore a SQL Server's configuration and all of its' databases, then it should suffice to restore master. Provided the master database backup is current, it will list all of the databases on the system, although some may be in 'suspect' mode, but should be able to restore over.
There are some particular things to pay attention to when restoring the master database using SQL Backup. These are covered in the help file and in a bit more detail here. This describes how to bring the server up in single-user mode and restore the master database using SQL Backup.
We don't use SQLBackup for our system databases only our applications ones. This is why the master knows about the application DB's bu they come up suspect as the database files don't exist.
Thanks
Chris
The same logic still applies, though, in that you have to bring the server up in single-user mode and then restore the master database. The only difference is that you would enter your RESTORE DATABASE command into Query Analyzer or Management Studio.
Our experience was this.
We started SQL in single user mode and restored master.
We then restored msdb.
When we started SQL it had the application databases all marked as suspect because master knew about them but the database files did not exist.
We tried to use the SQLBackup GUI to restore them from the backup saying Existing and it errored with file in accessible
We tried to use the SQLBackup GUI to restore them from the backup saying New and it errored with database already exists.
This means that SQLBackup read master so it thought the actual database existed but the files were not there.
We eventually had to delete the database so master had no information on it and then restore saying New.
Chris
OK, I see now. SQL Server does not let you restore a database that has no files. If the MDF file exists, but is corrupt, it's possible, but if there are no files you must drop the database and recreate it. Since this is a restriction of SQL Server, there certainly wouldn't be any way to code around it in SQL Backup. Maybe putting your question to www.sqlservercentral.com forums may yield some doable workarounds.
I think you could also script around this in your DR script. Basically set up a cursor for select [name] from sysdatabases and drop each database and recreate it, then run your restore job after the database has been newly-created.
I hope this helps.
We would do this one database at a time because we would want the dbid to stay the same as it was before we restore.
You confirmed why the restore problem so we will create scripts to do the restore after a delete from master.
Thanks
Chris
Where do we get a copy of SQLbackupc? Need to download from Redgate or should we keep a copy it laying around?
Thanks
Colby
SqlBackupC.exe is part of the server components installation, so you should find it in the SQL Backup program folder on the SQL Server already.
This SqlBackupC.exe is a Redgate tool not a Microsoft SQL tool. So when I run the Redgate SQL backup monitoring tool for a new server in a disater recovery mode , do I then install this Redgate server components and then get on the box and run your command line version?
Thoughts?
Colby
I need to create a complete disaster recovery plan using Redgate software. The product really does a great job in backup up all databases.
Now I need to plan for a complete restore of the server in a disaster recovery mode. I need to understand all aspects of the disaster recovery and need to know what steps to make sure all users and there passwords are also restored. Sometimes if they are not done correctly, you get orphaned userid and lost passwords. I now know about restoring Master, MSdb and model databases backuped with Redgate based on the articles in this thread.
Thanks
Colby
Yes, in order to restore these databases you will need to install SQL Backup server components on the server, or use the free converter (sqb2mtf.exe) to convert the backups to native format. It's probably better to use SQL Backup in terms of space saving, but you will need to licence to use it (or make sure it's been installed on the DR server within two weeks of the disaster in order to be able to use the trial version).
In order to reassociate SQL users with their security accounts, you can use the sp_change_users_login stored procedure from SQL Server.
I really think it would be a good idea to visit a SQL Server community site to discuss DR scenarios as the information is provided by those that have 'been there' and 'done that'. www.sqlservercentral.com is the best source of this type of information that I know of.