February 3, 2011 at 8:53 am
nick.welham (2/3/2011)
Two questions, if someone could help me please!1. Does the Master DB contain the server Master encryption key data or should I add this separately?
2. I know the Master db contains the code for the scheduled agents, but if I follow the rebuild sequence outlined will they appear in the SQL Server Agent Jobs automatically, or do I need to dig the code out for each step and recreate it?
Thank you!
job info is actually in the msdb database
--
Thiago Dantas
@DantHimself
February 3, 2011 at 8:55 am
nick.welham (2/3/2011)
Two questions, if someone could help me please!1. Does the Master DB contain the server Master encryption key data or should I add this separately?
2. I know the Master db contains the code for the scheduled agents, but if I follow the rebuild sequence outlined will they appear in the SQL Server Agent Jobs automatically, or do I need to dig the code out for each step and recreate it?
Thank you!
Welham,
Encryption key data should always be backed up on their own regardless of where it lives.
Also the jobs are contained in the MSDB database.
Rudy
February 3, 2011 at 9:01 am
Thanks! Will a replacement of the the MSDB database populate the SQL Server Agent jobs?
February 3, 2011 at 9:13 am
Yes. Restoring MSDB database will get you all of your SQLAgent jobs. Keep in mind if you attempt to restore MSDB the SQL ServerAgent service has to be stopped first.
If you are restoring a MSDB database from one server to another and both servers have different names there is some tweaking to change the servername in one of the tables you will have to do.
February 3, 2011 at 10:28 am
Rudy Panigas (2/3/2011)
mohammed moinudheen (2/3/2011)
Rudy,The title of this article is really catchy.
Regarding this section: It's All Proactive
You are referring to a SQL Server instance which is running fine right?
I am assuming you are not copying from the instance which has db corruption.
Yes Mohammed, only IF the instance (default or named) is running fine. Do not run this on a bad/corrupted master database as this will not help you.
Rudy,
Thanks for clarifying.
M&M
February 3, 2011 at 10:29 am
Thanks for the article, it makes a good point in that in some cases a SQL Server backup isn't enough, and that having an actual copy of the DB files can speed up the recovery of some problems.
I like the suggestion that Ian gave in that you restore the backup of your system databases with different names and detach them, that way no down-time is necessary get an up-to-date DR copy of them.
February 3, 2011 at 12:27 pm
I don't think I am going to take my prod down to save 15-30 mins on recovery. If you do it right the rebuild master shouldn't take more than that anyway.
February 3, 2011 at 4:40 pm
Yup, a useful tip. As an aside, I'm bummed they discontinued rebuildm.exe. I only used it once, but it worked fine. Nobody mentioned, but (I believe since SQL 2005), you can't "rebuild master" anymore. You have to uninstall/reinstall SQL.
February 3, 2011 at 9:57 pm
I use the following command when I stuff up the collation:
start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=Password01 SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS
There are various other switches etc - refer BOL. Yes it does rebuild the master database, this is for when you realise early enough that you have stuffed up. Which I obviously have done enough times to make it worthwhile hanging on to this command 🙂
pcd
February 4, 2011 at 11:24 am
We do it every year once, we have very huge prod DB in log shipping. The client wants to get copy of then live prod db for their testing and reports. We copy our master files and logshipping undo tuf file soemwhere else on DR. Restore the LS db in recovery mode and get the backup to restore later with diff name.
Then remove just restored DB, and restore the copied files in similar fashion. I thought we were cheating:unsure: but happy to know we are not alone now;-)
It is much less time comsuming than getting backup from prod server.
February 4, 2011 at 1:20 pm
Hey PCD that's a cool tip, thanks. 🙂
February 8, 2011 at 3:30 pm
Thanks for the article.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 4, 2011 at 7:31 pm
HOW TO ELIMINATE THE NEED TO REBUILD THE MASTER DATABASE
You can create copies of the system DB files while SQL Server is running. 😎
Enhance your daily backup job such that, after the master database is backed up, a script does this:
1. Obtains the name of the backup file or device (from the system tables).
2. Restores the backup as "master2", using WITH MOVE to specify the default physical filenames, but in a different location, such as a subfolder to your backups.
3. Detaches the master2 database.
Those steps will create a ready-to-use copy of the master MDF and LDF files without interrupting the availability of applications (because there is no need to stop SQL Server).
The files will have the same "recovery point" date and time as the BAK file.
When replacement system DB files are needed, there is no need to use special startup modes, no need to reinstall or REBUILD, no need for a ghost installation of SQL Server, no concerns about having exact documentation of the instance configuration or patch level.
NOTE: When SQL Server detaches a database, the file permissions are reduced such that only the run-time identify of SQL Server will have access to them. If you need to use the copies, you will have to take ownership of the files first. You should COPY them back into place (instead of MOVING them) to ensure that they inherit the permissions of the folder they belong in.
You can also do this to the msdb and model databases, to allow you to very quickly restore all three system databases and get SQL Server running again.
A fuller version of this solution would rename the prior copy of the files to *.old until the new copies have been generated, and would start by checking for evidence of a prior failure by checking whether any *.old files exist.
With "the backup folder" being a special location for the copies of the Sysdb files, here is the algorithm I am using:
1. In the backup folder , check for files named *.mdf.old. If found, raise an alert that the prior run did not end cleanly (include a directory listing) and then continue.
2. In the backup folder, delete any files that are named *.mdf.old or *.ldf.old.
3. In the backup folder,
rename *.mdf to *.mdf.old
rename *.ldf to *.ldf.old
4. Check whether any *.old files are missing. if so, raise an alert and then continue.
5. For master, msdb and model ...
5.1 Obtain the name of the current backup files (from the system tables).
5.2 Restore the backup as "<dbname>2", using WITH MOVE to allocate it with the default physical filenames, located in the backup folder.
5.3 Detach the <dbname>2 database.
6. Check whether any MDF or LDF files are missing. if so, raise an alert and then STOP.
7. In the backup folder, delete *.old
8. Check whether any *.old files exit. if so, raise an alert and continue.
The steps that check for files or the lack of files may seem redundant, but they serve an important role: they ensure that you are alerted to problems even if those problems did not raise an error that caused the job to fail. (Ensuring the ability to restore data is a DBAs primary responsibility, so I like to have a high level of confidence that the file copies were created successfully).
This algorithm is also robust enough to handle the situation of a prior run being interrupted.
The text of the alerts can be accumulated until the end (or until a STOP is encountered). That would allow all information to be sent in one alert.
These copies of the system database file are backed up to tape along with the server, so if the server is restored from an older backup, the copies will also be restored as of that date, so they will match the state of the instance's binaries.
I would share my code, but alas, I do not own it. I might write a version of my own to share - perhaps in PowerShell.
March 4, 2011 at 7:35 pm
(ignore this)
March 7, 2011 at 10:24 am
larry Hennig (3/4/2011)
HOW TO ELIMINATE THE NEED TO REBUILD THE MASTER DATABASEI have not seen this idea offered by anyone else, so I am claiming credit for the algorithm. :smooooth: If you describe this method anywhere else, please be up front about giving me (Larry Hennig :-)) credit for coming up with the algorithm. Likewise, please put a suitable credit in an in-line comment if you implement this algorithm in code.
You will notice on page 2 that IAN suggested essentially the same thing of backing up the DB, restoring it, and then detaching it. However, he didn't go into as much detail about how to make the process more reliable/resilient. (see post: here)
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply