October 19, 2006 at 10:30 am
I have a SQL Server 2000 SP3 Enterprise Edition on a Windows 2000 SP4 Cluster server. I am using Named Instances on Virtual Window Servers so that auto-failover will work. I have data on S:\ drive and logs on L:\. I backup up my system and user databases. For Disaster Recovery planning, I need to restore the databases onto a non-clustered Windows server, which does have SQL Server 2000 SP3 Enterprise Edition. The other thing to note is the DR server only has D:\ drive (no S:\ or L:\). Typically, one would start SQL Server in single-user mode, restore master, and then start up in normal mode. The startup in single-user mode and restore master works but the startup normal does not. Event Viewer has errors about cluster and about not finding S:\ and L:\ for User databases.
In short, backing up master from a named instance on a cluster server and trying to restore it in a named instance on a single (non-cluster) server. Is this possible? If yes, how?
October 19, 2006 at 11:41 am
as for the s:\ and l:\ drives...
you're going to have to specify the file path and names in your syntax...
for instance, something like the following:
@filename1 = N'D:\Program Files\Microsoft SQL Server\MSSQL\Data\staging_snap_Data.mdf',
@filename2 = N'D:\Program Files\Microsoft SQL Server\MSSQL\Data\staging_snap_Log.ldf'
<where the D:\ drive was formerly the S:\ or L:\ drive)>
It is not clear to me why you would restore master on a server that is already running... guess that is the complete way; but I think I would just put my database back together in an already running instance.
Okay, now I will get shot down on that comment... bring it on!!!! (and thanks for the education).
David
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982
October 19, 2006 at 12:21 pm
1) I did issue the file move from S:\ and L:\ to D:\ for the master files (.mdf and .ldf) during the restore process. SQL Server restore of master indicates master was successfully restored. However, it won't start up and the error messages indicate it cannot find S:\ and L:\ for all the User databases. I don't know why it just doesn't startup and label the User databases as suspect.
2) Our DR plan is to have SQL Server running at my company's aother location. This will allow for faster up time in the event of a disaster, meaning the first location is lost due to some misfortune like a fire.
3) I just read in Microsoft TechNet (http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx) the way to recover a cluster server is to backup the entire server, which my Network Admins don't do. I am hoping someone out there knows of another way. My preference is to restore master and then the User databases but I am going to test restoring only the User databases (no master). If this works, then I just need to determine a way to keep the logins insync between the primary SQL Server and the DR server. Ultimately, I could invoke replication (log shipping, snapshot, or transactional).
October 20, 2006 at 9:52 am
It may be easier to not bother trying to restore master.
Just script out your user databases on your Cluster, then create the databases on your DR Instance (make changes to the script to use the D: drive).
When you restore your user databases on the DR Instance , use the WITH MOVE command to get your databases to the D: drive.
Tim
October 20, 2006 at 10:12 am
That is what I ended up doing. However, I am interested if someone out there knows of a way to restore master from a cluster server backup.
October 20, 2006 at 10:43 am
Okay, so I will ask point blank... Why do you need to restore master?
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982
November 9, 2006 at 2:02 pm
For logins and passwords. My company does not like the idea of having to maintain the logins and passwords in two sites.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply