Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Restore System DBs to Another SQL Instance Expand / Collapse
Author
Message
Posted Monday, August 16, 2010 4:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 19, 2014 6:03 AM
Points: 2,900, Visits: 3,301
One good practice for a DR is that any task should be able to be done by someone outside the team that normally looks after the service. This means the process for getting Exchange working should be planned and documented so that (say) a DBA can get Exchange working in a DR. Equally, is should be possible for (say) a SAN administrator to get SQL Server working. The whole point of an unplanned DR is that you should excpect reduced staff availability, either because they are a casualty or they cannot get to the DR site.

Personally, I would look on a DR process that recovers SQL Server by restoring system DBs as a higher risk solution than a process that keeps the system DBs at the DR site up to date. It may be a pain to apply configuration changes to a primary and a DR site, but when a real DR happens it should be far easier to get SQL Server working.


Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 18 October 2014: now over 31,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #969658
Posted Monday, August 16, 2010 12:28 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 22, 2011 2:04 PM
Points: 64, Visits: 164
Thanks Ed! Totally agree - when stuff hits the fan, the last thing you want is someone who isn't the DBA to be messing around with the system DBs in SQL Server LOL. Thanks again.
Post #969964
Posted Wednesday, August 18, 2010 9:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:39 PM
Points: 6,760, Visits: 14,412
dso808 (8/11/2010)

Now we've been working on restoring the system databases first (of course) to the new instance and came up across this problem. Our production server has 2 drives - k: for data files and l: for log files. Our test box for the restores is set up with only a c: drive.


all this is entirely possible, you do need to know the clustered system layout beforehand. This is something you should have documented already, detailing service accounts\groups, IP addresses, network names, disks, etc!

If you want to recreate the cluster you re deploy a new cluster using same names, IP, patch level, etc then restore matching master, model and msdb using the following;



1/ Ensure you have to hand or know the location of valid and matching SQL server backups for the following databases,

Master
Model
MSDB

Note: The TEMPDB is recreated from the Model database each time the SQL Server instance starts.

Do not attempt to restore disparate backups as this could cause at minimum, system collation issues. The backups must exist on a shared drive visible to the SQL Server instance.

2/ RESTORING MASTER DATABASE

Take offline all clustered SQL Server services for the instance you are maintaining, this is done via Windows “Cluster Administrator”.


3/ START SQL SERVER IN SINGLE USER MODE

The SQL Server instance needs to be started in single user mode to be able to restore the master database. This is done by executing SQLSERVR.EXE at the command prompt within the SQL Server instance Binn directory with a set of command line parameters. You may find the executable path by opening Windows services and viewing the properties of the SQL Server service you are maintaining.


Open a command prompt and execute SQLSERVR.EXE as shown below. (For a default instance, omit the instance name entry on the command line).

E.g.

sqlservr -c -m -sinst1

Pressing return will start the SQL Server instance,

4/ Now open a new command window leaving the existing one active (do not minimise). The new command window will be used to restore the master database via the SQLCMD prompt as shown below,

sqlcmd -SMyServer\inst1 -E

This is a combination of the virtual network name and the named instance. Had I created a default instance it would be named simply MyServer.

Remember the network name is simply a virtual computername!!

To restore the database, use the following syntax (where ? is the shared drive letter, !!!!! are the paths and ****** is the filename). It is important to specify the REPLACE parameter to overwrite the existing database!

RESTORE DATABASE MASTER FROM DISK = '?:\!!!!!\!!!!!!\*******.bak' WITH REPLACE

After pressing return you are required to enter your next command. To execute the SQL statement above, type “GO” followed by return.


Restoring the Master database should automatically exit the running SQL Server instance in the first command window. If it doesn’t, stop the server by clicking into the command window and hitting CTRL-C.

5/ BRINGING THE INSTANCE BACK ONLINE

After restoring the Master database and closing the SQLSERVR.EXE command window you must now go into Cluster Administrator and bring the SQL Server service back online. Do not bring the SQL Server Agent or the SQL Backup Agent services online if you intend to restore the Model and MSDB databases.

6/ RESTORING THE MODEL AND THE MSDB DATABASES

Restore these databases as usual via SQL Server Management Studio. Ensure the SQL Server Agent and the SQLBackup Agent services are offline in “Cluster Administrator” first.

After restoring the Model and MSDB databases you must restart the SQL Server instance using Cluster Administrator.


7/ WHAT ABOUT TEMPDB?

The TEMPDB is re created from the Model database each time the SQL Server instance is started. You do not backup or restore this database!


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #971254
Posted Wednesday, August 18, 2010 12:43 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 22, 2011 2:04 PM
Points: 64, Visits: 164
Thanks very much Perry! Actually, we were trying to restore the system databases from a clustered server to a NON-clustered server thus all the problems and the creation of this post. But again thanks very much for the procedures - they will definately come in handy if we ever get create another cluster!
Post #971379
Posted Wednesday, August 18, 2010 1:27 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:39 PM
Points: 6,760, Visits: 14,412
dso808 (8/18/2010)
trying to restore the system databases from a clustered server to a NON-clustered server


I have done this before, its entirely possible. As I said you need to have extensive documentation on the cluster set up so any part of it may be re produced. This IMHO is where you fell short as you had no knowledge of the drive layouts


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #971414
Posted Thursday, August 19, 2010 6:52 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, December 14, 2014 1:27 PM
Points: 604, Visits: 1,727
Great info! We're planning an upgrade to our Sql 2005 Enterprise 32bit active / passive cluster to Server 2008 64bit with Sql 2005 Enterprise 64 bit. I don't plan to restore the master database as there shouldn't be much custom stuff in there and it's a pain to do. The few stored procedures will be scripted out and applied on the new server.

Custom code like procs and functions in the user databases will be carried over by re-attaching the databases on the new environment. (all database files are on a Netapp disk device ). I will have to deal with several mount points as our main client database consists of about 20 data files spread out on multiple luns/mount points on the netapp. I wish there was a way of scripting out the mount points

I do plan to restore MSDB as a way of bringing all of my jobs/maintenance plans over -- I've tested this and you just have to stop the sql agent first and make sure the new box has the same patch level as when backed up on the old server.

As far as model/ tempdb I knew our tempdb has ten files but hadn't thought about restoring the old model to the new server as a way of bringing our custom tempdb over. Hopefully that works, rather than having to manually create the new tempdb with multiple files.



Post #971829
Posted Monday, August 23, 2010 3:40 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 19, 2014 6:03 AM
Points: 2,900, Visits: 3,301
If you are upgrading from SQL 2005 to SQL 2008 then don't even thing of restoring MSDB as a way to get your data into SQL 2008, unless you want a shedload of stuff to not work as designed. You will also take yourself outside of Microsoft Support if you have a problem.

The only safe way to deal with MSDB contents across a SQL 2005 to SQL 2008 upgrade is to script out everything you need from SQL 2005 and script it back into SQL 2008.


Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 18 October 2014: now over 31,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #973299
Posted Monday, August 23, 2010 5:30 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, December 14, 2014 1:27 PM
Points: 604, Visits: 1,727
We're upgrading but sticking with sql 2005. Server 2008 refers to the OS.

"We're planning an upgrade to our Sql 2005 Enterprise 32bit active / passive cluster to Server 2008 64bit with Sql 2005 Enterprise 64 bit."



Post #973357
Posted Monday, August 23, 2010 8:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:39 PM
Points: 6,760, Visits: 14,412
Indianrock (8/19/2010)
Great info!


the info provided is designed to help re create an environment not upgrade it!


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #973503
Posted Thursday, October 31, 2013 6:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 23, 2014 6:40 AM
Points: 1, Visits: 101
Great post Simon. Thank you for sharing this.


Post #1510466
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse