Disaster Recovery Process - Cannot restore Master

Hello All,

I have a production SQL 2005 sp1 server running on Windows 2003. The SQL server was upgraded from SQL 2000.

In SQL 2000 our disaster recovery plan consisted of making daily backups of all system databases and user databases.

If we did have a system failure, we would copy those databases to a different physical server, Open SQL in single user mode and restore master.We would then restart the SQL service in normal mode and continue restoring the system databases then the user databases.

Now with SQL 2005, I am testing this process by attempting to restore all of the databases into our rebuild DEV server.I have SQL 2005 installed and patched to the same level as the production SQL server (9.0.2047).I can start SQL 2005 in single user mode, open SQL Server Management Studio, browse to master, right click master, click tasks ->restore ->Database.I choose “From Device” and add the BAK file from last nights backups.I select Master as the restore to database, put a check in the backup set to restore and press ok.Below is the error that I am getting:

“Restore failed for Server ‘SERVERNAME’ – System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'master' database. (Microsoft.SqlServer.Smo)

Do I have a real error, or am I going about the process incorrectly for SQL 2005?

Here is the SQL Script that my actions are producing:

RESTORE DATABASE [master] FROMDISK = N'D:\ProductionSQLBackupforDEV\ \MSSQL\BACKUP\master\master_backup_200605110020.bak' WITHFILE = 1,NOUNLOAD,STATS = 10

GO

Also, all of our databases, system and user, are stored in a different directory than c:\program files\mssql……We have moved them to D:\directory.

If I need to re evaluate our “Backup” Procedures to be able to perform these actions please let me know.

Thank you for any assistance you can provide.

Keith

Does anyone have any ideas on this problem?

Can the master database from SQL2005 on server1 be restored to SQL2005 on server2?

If so, how do you do it?

If not, how do you recover from a disaster where you are forced to restore from backups?

Thank you,

Keith

|||

Did you try the WITH REPLACE option?

That should work.

|||

Thank you,

That was exactly what I was looking for.

Keith