April 25, 2008 at 3:00 pm
I have never done this in a DR scenario before. What I used to do in SQL Server 2000 was apply the logins to a new installation of SQL 2000 then recover the application databases. I'm using 2005 now and I'm in a different mode where I would like to be able to recover the system (master, model, msdb) to a different server with the same build 9.0.3054. I'm following the procedures from Microsoft where they state to place the instance in single user mode first then invoke SQLCMD then perform the restore. Sounds simple enough.
C:\> SQLCMD
> RESTORE DATABASE MASTER from DISK = 'Z:\MINIDR\master_backup_200804200315.bak'
The console is coming back with a message that the backup set holds a backup other than the existing master database. So I suppose that this cannot be done and is only intended for recovery on the same instance.
Any input/experience is appreciated.
Thanks.
April 25, 2008 at 5:39 pm
Are you sure they're the same build no? That should work.
April 25, 2008 at 8:31 pm
Both instances are showing 9.0.3054. The target instance is on a Virtual server environment that was setup by the Networks admin primarily for this exercise.
Can you point me to an article regarding this. I would really like to be able to get this working.
Thanks.
April 25, 2008 at 11:05 pm
[font="Courier New"]Refer the link below for restoration of master category.. I hope this will help you!!!
How to Rebuild System Databases in SQL 2005[/font]
Regards..Vidhya Sagar
SQL-Articles
April 26, 2008 at 7:14 pm
Thanks for the response. I was however interested in pursuing the recovery of a database server onto another server in case a disaster happened. That is why I backup the master, model and msdb databases religiously. There must be a way to do this. What would be the point in this if the current server was fried? All you have now are backups of the system databases and application databases. It would seem that installing a new instance of SQL Server on a new box is the only solution. It would be tragic though if I can recover the application databases but not the system databases. MSDB for instance, has all the info on batch jobs.
Has anyone gotten this type of recovery to work?
April 26, 2008 at 8:21 pm
ok this worked for me..... just in case anyone has to do this:
> RESTORE DATABASE master FROM DISK = 'C:\MINIDR\masterbackupfile.bak' WITH
RECOVERY, REPLACE;
2> GO
Problem solved.
April 27, 2008 at 5:08 pm
I've done this before. Here are the steps I used:
[font="Courier New"]
source server = server from which backups originated
target server = server onto which we wish to restore the system databases
Presuming that paths to system files are different (more difficult to do than if paths are the same)
1. Ensure target server is same build revision as source server. Patch accordingly.
2. Start target server in single user mode (sqlservr -c -m -f)
3. Connect to SQL Server using sqlcmd
4. Restore master database (Note - does not require WITH MOVE option).
When done, SQL Server stops automatically.
5. Start target server in single user mode (sqlservr -c -m -f -T3608)
6. Connect to SQL Server using sqlcmd
7. Use ALTER DATABASE command to point SQL Server to the mssqlsystemresource database:
ALTER DATABASE mssqlsystemresource
MODIFY FILE (name = data, filename = ' \mssqlsystemresource.mdf')
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (name = log, filename = ' \mssqlsystemresource.ldf')
GO
8. Stop SQL Server (Ctrl-C). Start in single user mode.
9. Use ALTER DATABASE command to point SQL Server to the model, msdb & tempdb databases:
modeldev = model.mdf
modellog = modellog.ldf
msdbdata = msdbdata.mdf
msdblog = msdblog.ldf
tempdev = tempdb.mdf
templog = templog.ldf
10. Stop SQL Server. Start SQL Server (either from cmd or as service).
11. Restore msdb, model if required.
[/font]
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
April 28, 2008 at 6:34 am
Scott,
Sounds good. Thanks a lot. I will give this a whirl.
April 28, 2008 at 8:48 am
Let us know. I'd missed the Resource database, which is new.
The KB article I found shows doing just what you do, which should work.
April 30, 2008 at 7:39 pm
Hey Guys,
I have something I'm trying that I'm not sure can work, but is kinda related to this topic.
I'm trying to simulate a copy I may need to do in the office, of my master DB from one sql 2k5 instance to another on a different machine. Currently, I'm simulating it on my local machine on which I have two instances installed.
What I did was (and pardon me if this is a stupid idea to begin with), copy my master db files (.mdf and .ldf) using SyncBack (an app that copies files even when they're being used), to a different directory. Then I stopped the sql server service on my second instance (the destination), and copied the files to the path used by the second instance (as specified in the start up paramters), therefore, overwriting the original master.mdf and mastlog.ldf files of the second instance.
The problem now I have is that I can no longer start the second instance. I'm able to start it in single user mode, and access the master, but that's about it; I'm pretty much lost as to what I should do next (hehe). Was this idea destined to fail from the getgo? Should I have used the backup and restore solution instead?
Sorry, I couldn't find the answer I need in other forums. Really hope you guys can show me a step by step solution.
Thanks!
Jafar
May 1, 2008 at 3:22 pm
Copying database files while they are in use is dodgy at best. I can't say I know of any time it has worked. You would be better off doing one of two things:
1. stop both instances, copy the files from instance1 to instance2, start both
2. backup instance1, restore to instance2
As mentioned above, remember the resource database as well. Your instances should be the same build too.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
July 14, 2008 at 11:29 am
In testing a DR recovery of SQL 2005 I have successfully restored all of the system and user databases.
My problem was in starting the SQL Agent service - it would not start
So far the solution has been to make the SQL Agent Service account a member of the sysadmin role. This worked and allowed the agent service to start.
As I thought more about this - I realized that the windows groups that the SQL 2005 install created are all local server groups. When I rebuilt the SQL server the SIDS created on the new server were different than the original server (as stored in the master database).
So - as part of a server rebuild - should I consider dropping the local group SQL logins from the SQL instance and add them back as member of the correct server rolls? - Partial list of groups would be: (additional information at http://msdn.microsoft.com/en-us/library/ms143504.aspx)
SQLServer2005MSSQLUser$InstanceName
SQLServer2005SQLAgentUser$InstanceName
SQLServer2005MSOLAPUser$InstanceName
SQLServer2005ReportServerUser$InstanceName
SQLServer2005MSFTESQLUser$InstanceName
Thanks
August 2, 2008 at 7:25 pm
[font="Arial"]I have a question: has anybody successfully moved the Resource database in a SQL 2005 Clustered instance? Attempting to put the database server in single-user mode will not work, since the cluster service is required to run and will lock the SQL instance.[/font]
August 3, 2008 at 2:11 am
I think you need to go through this:
Please let me know if you need anything more.
Thanks,
September 17, 2008 at 4:53 am
Scott Duncan,
Its really fine & worked for me, but I am little bit confused with Step 7.
For Eg: Master db restore
Alter Database master modify file (name = master, filename = 'd:\master_ddmmyy.bak')
GO
Alter Database master modify file (name = mastlog, filename = 'd:\master_ddmmyy.bak')
GO
The above said command used for what purpose, is it for changing the data filename and log filename?
I would appretiate you if you could explain little bit in this.
Regards,
Mohanraj Jayaraman
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply