August 11, 2010 at 2:13 pm
Hi and thanks for reading my post. Here's my scenario...
We're working on a DR scenario where we would take ALL our nightly backup files (system and user) from our production sql server 2005 database and then restore them onto a brand new (no user databases) instance of sql server 2005. We have a cluster so this is just worst-case scenario where we would have to completely rebuild the production database.
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.
So you guessed it - it's been a nightmare trying to restore the master database. Restored it, but then I got errors that the system couldn't find the data and log files for mssqlsystemresource (because of course they were pointed to k: and l: drives that didn't exist). OK, so I corrected that with an alter database command. Then I got the same message for model - and then for tempdb and so on...
Suffice to say, we still can't get the test instance to start up. Now I'm getting an error message about the service broker being down and the need to bring msdb online. I can start the sql service - but NOT the sql agent service.
After restoring and fixing (I thought) all the system DBs, when I again tried to start up the instance in single user mode, I got a TON of error messages stating that the system could not open up all our user database files (that don't exist yet because we haven't restored them yet) because again, the system was looking for a k: and l: drive that didn't exist.
I'd love to hear from everyone their take on what's the best approach to tackle this. Is it worth going through all this and if it is, how do I get out of this mud-pit? Also, if we were to simply get a test box with the exact hardware configuration as our production box (with k: and l: drives), would this eliminate all these issues and make the process easier?
A big thank you in advance to all who reply. I'm new to the DBA role so any tips are MUCH appreciated.
August 11, 2010 at 5:23 pm
You are going through a very rough way of doing this.
I'd not worry about the system databases. Instead, I'd worry about the data in them that is needed to bring your DR system up.
I'd suggest establishing a migration policy where:
1. Any changes to the production server model database also needs to be done on the DR server model database.
2. All SSIS pkgs and jobs deployed to the production server need to be deployed to the DR server, but disabled. (IMO, the SSIS pkgs should use configuration files to be able to run on either server without issue.)
3. Any custom user messages added to the system need to be added to both servers.
4. Any server objects (linked servers, etc.) need to be added to both servers.
I would also utilize a SSIS transfer user task to frequently transfer all users from the production server to the DR server. If I remember correctly, there might even be a task to transfer system messages. These could be on either server; if on the DR server, then these should be about the only enabled jobs you have.
Oh... it would be wise to make your DR server identical to your production server, if at all possible. Ideally (IMO), same model, same configuration, serial numbers differ by only 1.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 11, 2010 at 5:35 pm
HI and thanks so much for your reply. Yes, this is a very rough way to go about this and it's been a very unpleasant experience. I guess we have no choice but to configure the DR server as a cluster as well? Anyone else have any other suggestions? Thanks again!
August 12, 2010 at 1:26 am
dso808 (8/11/2010)
HI and thanks so much for your reply. Yes, this is a very rough way to go about this and it's been a very unpleasant experience. I guess we have no choice but to configure the DR server as a cluster as well? Anyone else have any other suggestions? Thanks again!
The DR server does not need to be a cluster.
However, you should create the same objects(Linked Servers, Users, Job Mail Profiles, Jobs(Disabled), SSIS packages) in both the places. In case you loose your main server, you will have most of the content already in the DR Server.
this will help you and would not really need the system databases to be restored.
For the user databases, you can use the backups to restore them.
August 12, 2010 at 1:00 pm
Hi and thanks so much for your help. Your suggestion sounds like the most feasible solution for our situation. We've also thought of perhaps setting up the DR server to mirror our user databases, recreate all logins, jobs, maintenance plans etc onto the DR server, and then make procedures so that going forward, all changes to our production server (the cluster) are also made to the DR server.
But lesson learned. System database backups from a cluster should only be restored onto that cluster - NOT to another instance. Especially into an instance in a non-clustered environment.
Any one else have input? Anyone else have a cluster with a non-clustered DR server backing it up? Whats the best way to set those 2 pieces up to play music together? Thanks again in advance to all who reply!
August 12, 2010 at 6:43 pm
dso808 (8/12/2010)
Hi and thanks so much for your help. Your suggestion sounds like the most feasible solution for our situation. We've also thought of perhaps setting up the DR server to mirror our user databases, recreate all logins, jobs, maintenance plans etc onto the DR server, and then make procedures so that going forward, all changes to our production server (the cluster) are also made to the DR server.But lesson learned. System database backups from a cluster should only be restored onto that cluster - NOT to another instance. Especially into an instance in a non-clustered environment.
Any one else have input? Anyone else have a cluster with a non-clustered DR server backing it up? Whats the best way to set those 2 pieces up to play music together? Thanks again in advance to all who reply!
you can restore the system databases on other instance, however the productversion(serverproperty('ProductVersion')) of other system should be the same, or otherwise it would not let you restore it.
August 13, 2010 at 3:50 am
See when you already have your prod server on a cluster and this exercise you're doing for the worst case scenario, then I don't think you need a more spare box sitting idle for this situation to happen... secondly you can restore system db's of a cluster to a non cluster server and to avoid these errors better install the same version of sql server on DR box, with same default setting for data & log files (no need to be identical in terms of hardware but at least the number of drives shud be same in number and in size also if you'r planning to restore all user db's)
this will eliminate all the errors you were facing after restoring master.....
I've one more addition for your DR strategy, if you're data & log drives are on san then in this scenario when your server is down and your SAN is up then you can directly attach those drives to this new DR machine.
Rohit
August 13, 2010 at 10:16 am
I just went through an off-site DR recovery Exercise (Scottsdale AZ, in June - no matter how dry it is, 110 is still HOT; and for all you people in AZ, 6% humidity is NOT humid, contrary to what a local weather forecaster stated). This is the document I authored and used as the step-by-step guide, and it worked really well. You may not want all of this, and I removed anything specific to my company. This is for SQL 2008, and is written for a separate SSAS Instance.
1.Install .Net Framework 3.5, SP1
If needed, install .Net Framework 3.5, SP1.
2.Configure SQL Service Account with the ‘Perform Volume Maintenance’ permission (Only do this if your standards require it and / or permissions / standards allow it)
The ‘Perform Volume Maintenance Tasks’ allows SQL Server to allocate Data files (.mdf and .ndf) without zero-filling the file space, this will reduce database restore times by approximately 50%.
To grant ‘Perform Volume Maintenance Tasks’, (this requires Local Administrator rights):
i.On the Start menu, click Run. In the Open box, type secpol.msc on the server. The Security Policy editor opens.
ii.Under Security Settings on the left, go to Local Policies and under that to User Rights Assignment.
iii.Under Policy on the right side, go to "Perform volume maintenance tasks" and double click on it
iv.On the Local Security Setting tab click on the "Add User or Group" button
v.Click the ‘Object Types’ button, and check ‘Groups’ in the Object Types dialog. Click ‘OK’.
vi.In "Select Users, Computers, or Group", "Enter the object names to select" enter the SQL Server service account / service account group in Domain\Account format.
vii.Click [OK].
3.Install SQL Server
Instance Name[Your Server name here]
Components to Install
•RDBMS (SQL Engine)
•SSIS
•SSRS, in Native Mode
Service Accounts – use standard Service Accounts
•SQL Server [Service Account ???]
•SQL Agent [Service Account ???]
•SSRS [Service Account ???]
•Full Text [Service Account ???]
•SSIS [Service Account ???]
•Browser [Service Account ???]
4.Configure SQL Server
5.Install SSAS
Instance Name[Your Server name here]
Components to Install
•SSAS
Service Accounts – use standard Service Account
•SSAS [Service Account ???]
6.Configure SSAS
7.Recreate Users – OLAP
Add following Users to Server Role on SSAS:
•[Domain\User]
•[Domain\User]
(etc)
8.Patch SQL Server
Install SQL Server SP1 – apply to all installed components.
9.Restore master Database - SQL
1.Stop all SQL Server components in SQL Server Configuration Manager, and set everything to Manual Startup.
2.Start SQL Server in single-user mode:
In SQL Server Configuration Manager:
a.In the left pane, select SQL Server Services.
b.In the right pane, right-click SQL Server, and then click Properties.
c.On the Advanced tab, in the Startup Parameters box: insert -m in front of the existing startup options.
d.Click OK.
e.Start SQL Server.
3.Using SQLCmd, restore the master database:
a.Execute RESTORE DATABASE master from <device> WITH REPLACE
4.SQL server should have automatically stopped once the master database was restored, if not Stop SQL Server.
10.Modify the system database locations to match the DR Installation
1.Restart SQL Server with the –T3608 Trace Flag (NoRecovery)
In SQL Server Configuration Manager:
a.In the left pane, select SQL Server Services.
b.In the right pane, right-click SQL Server, and then click Properties.
c.On the Advanced tab, in the Startup Parameters box: insert –T3608 after the –m startup option.
d.Click OK.
e.Start SQL Server.
2.Using SQLCmd, modify the files for msdb, model and TempDB.
At a SQLCmd prompt:
a.Execute SELECT name, physical_name FROM master.sys.master_files WHERE Database_ID < 5
b.For each file listed, execute ALTER DATABASE {db} MODIFY FILE (NAME = {name}, FILENAME = '{filename}') where {db} is the database, {name} is the logical name from the list and {filename} is the path to the installed data folder with the file name from the list.
11.Restore msdb Database - SQL
1.Restore the msdb database:
a.In SQLCmd execute RESTORE DATABASE msdb from <device> WITH REPLACE
2.Restart SQL Server in No Recovery, and multi-user mode:
In SQL Server Configuration Manager:
a.Stop SQL Server.
b.Click SQL Server Services.
c.In the right pane, right-click SQL Server, and then click Properties.
d.On the Advanced tab, in the Startup Parameters box: remove -m from the startup options.
e.Click OK.
f.Start SQL Server.
12.Drop User Databases from restored master DB - SQL
1.Drop existing user databases, including ReportServer databases.
2.For each database:
a.Set the database to OFFLINE
b.DROP the database.
13.Restore User Databases from restored backups from tape - SQL
1.Restore user databases to default locations for SQL
ReportServer databases must be restored to the same name they were backed up from.
14.Restore User Databases - SSAS
1.Restore databases to default locations for all OLAP Server databases in the backups recovered from Tape
15.Recover Reporting Services
In Reporting Services Configuration Manager,
1.Point SSRS to the restored Report server Databases:
a.In the ‘Database’ tab, click the [Change Database] button,
b.Select ‘Choose and Existing Report Server Database’, click [Next],
c.Make sure the local SQL Server is selected, click [Next],
d.Select the restored Report Server database, click [Next],
e.Complete the dialog with [Next] until finished.
2.Restore the .snk encryption key file from the production SSRS Instance:
a.In the ‘Encryption Keys’ tab, select the [Restore] button,
b.Navigate to the File Location of the .snk file restored from tape,
c.Enter the Password, click [OK],
August 13, 2010 at 12:15 pm
Thanks Ramji - our cluster does use a SAN so your tip is definately useful!
August 13, 2010 at 12:20 pm
Thanks Simon! Wow, this document will definately be a useful template for creating our own DR procedures. Looks great. Thanks again.
August 16, 2010 at 4:06 am
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: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 16, 2010 at 12:28 pm
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.
August 18, 2010 at 9:59 am
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" 😉
August 18, 2010 at 12:43 pm
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!
August 18, 2010 at 1:27 pm
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" 😉
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply