April 9, 2009 at 6:19 am
Cluster Node Instance restore.
One of the clusters has registry edit settings and a rebuild of the instance is needed on Production
So following along i have the steps outlined.
1. install SQL Instance on Cluster Node
2. Turn off Node
3. Copy mssqlsystemresource.ldf and mssqlsystemresource.mdf files from backup to the directory
4. start sql in single mode in Dos
k:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>sqlservr -m
5. restore master backup bak file.
SQLCMD -Sservername -E -Q"restore database master from disk = 'directory\master.bak' with replace"
6. will shutdown sql log into sql normal way and use analyzer and do msdb
and model.
--in query analyzer
--MSDB DATABASE
RESTORE DATABASE MSDB
FROM DISK = 'T:\BACKUPS\msdb.bak'
--MODEL DATABASE
RESTORE DATABASE MODEL
FROM DISK = 'T:\BACKUPS\model.bak'
WITH REPLACE
7. Attach all other application databases mdf/ldf
Hows that look.
April 9, 2009 at 7:05 am
Tracey....
so, you are trashing an instance, having already saved away your system database files?
then rebuilding the instance with the same name to the exact sqme release level, followed by putting saved system database files back?
is that correct?
---------------------------------------------------------------------
April 9, 2009 at 7:42 am
Yes that is actually what we doing...same instance name.
April 9, 2009 at 9:22 am
then your plan is basically fine except you don't need to copy the mssqlsystemresource files back from your backup, the resource database put in place by the install will be fine.
when you restore your master database you will find all the user databases will be attached as the old master database knows about them and user database files are not touched by uninstalls.
couple of extra steps:
backup your new system databases before you restore over them
at the very end stop the instance again and take file copies of the system databases (inc resource database)
---------------------------------------------------------------------
April 9, 2009 at 9:53 am
Thanks for the clarification.
April 9, 2009 at 1:28 pm
larry Hennig (3/13/2009)
The problem I desribed is that you cannot recover them unless SQL Server is running and you need at least master.MDF & master.LDF to start SQL Server in minimal mode.
this should help you
http://qa.sqlservercentral.com/Forums/Topic688810-146-2.aspx
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 9, 2009 at 1:53 pm
This command
k:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>sqlservr -m
I was trying on a developer edition i assume this only works on enterprise as i tried sqlservr -m sqlservername and it keeps saying instance name wrong.
tried sqlservr -m computername\sqlinstancename
So i assume because of developer ...i will try on enterprise version the steps first before i do this on production.
April 9, 2009 at 2:23 pm
Try this in your command line...
NET START MSSQLSERVER /c /m
which will start SQL in single user mode
Note: For default instance its MSSQLSERVER, for named instance its MSSQL$instancename
April 9, 2009 at 3:13 pm
Thanks that worked ............
Restored the master.bak using SQLCMD
Brought back up SQL and restored model and msdb using query analyzer
That all worked.
Now to unistall SQL Server instance on test and retry it again.
Pretty easy when you know the steps.
April 9, 2009 at 3:14 pm
Perry Whittle (4/9/2009)
larry Hennig (3/13/2009)
The problem I desribed is that you cannot recover them unless SQL Server is running and you need at least master.MDF & master.LDF to start SQL Server in minimal mode.
this should help you
http://qa.sqlservercentral.com/Forums/Topic688810-146-2.aspx%5B/quote%5D
Perry, I presume when you copied master database files from a different instance, after restarting you ran sp_dropserver\sp_addserver?
It seems this process does not rename the windows groups that SQL creates on install and contain the server name, but they still function. MS have said this will be fixed in later editions
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=372048
---------------------------------------------------------------------
April 9, 2009 at 3:47 pm
Ok i just installed SQL 2005 then added a few databases testa, testb and sql logins testalogin testblogin
Then backup master.bak model.bak msdb.bak
Uninstalled the whole sql
Reinstalled the sql again.
Performed the restore of master.bak, model, msdb.
When i go back in SQL none of the databases testa, testb are there or in sys.sysdatabases or no logins.
Must have missed a step i thought i get the logins and jobs etc back and all user databases testa, testb? by restoring master.bak
(Sorry it did work the 2nd time and brought back all user databases and logins from master)....
Now for the real test come next Saturday for production....
Thanks all appreciate your help.
April 9, 2009 at 4:05 pm
No George, the whole point is this
your master database is corrupt, you cant start sql to restore the database. You dont want to rebuild or you cant cos you dont have the CD. So,
Restore the backup of the master database to another server as a different database name. Once the database has been restored then detach it and rename the disk files back to "master.mdf" and "mastlog.ldf" and then place them back onto the corrupt server (archive the original corrupted files just in case), the sql instance will then start. I tested this and it works fine. Obviously it would be best to restore the backup to a server that is the same version as the corrupted instance, but other wise it works
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 9, 2009 at 5:33 pm
Perry Whittle (4/9/2009)
No George, the whole point is thisyour master database is corrupt, you cant start sql to restore the database. You dont want to rebuild or you cant cos you dont have the CD. So,
Restore the backup of the master database to another server as a different database name. Once the database has been restored then detach it and rename the disk files back to "master.mdf" and "mastlog.ldf" and then place them back onto the corrupt server (archive the original corrupted files just in case), the sql instance will then start. I tested this and it works fine. Obviously it would be best to restore the backup to a server that is the same version as the corrupted instance, but other wise it works
Ah, I see, neat trick. I'll have to remember that.
sorry, I did not read the whole thread properly.
---------------------------------------------------------------------
April 9, 2009 at 5:57 pm
yes, it wasnt until i tested it and was able to confirm it works that i now plug it into my DR processes
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 9, 2009 at 6:18 pm
Perry, that was an excellent tip! Thank-you.
Viewing 15 posts - 31 through 45 (of 49 total)
You must be logged in to reply to this topic. Login to reply