Ola Hallengren’s DDBoost Backups

The already awesome Ola’s Maintenance solution for SQL Server became more awesome with his latest release. If you are not aware of Ola’s maintenance solution, you should definitely check it out. In the July 2018 release, quite a few enhancements were introduced. My personal favorites are ability to add percentage of modified rows to update stats, resumable index rebuilds support and support for EMC DDboost(Data Domain Boost). In fact I was badly waiting for DDboost support last year(we were using DDboost to backup our VLDBs). Unfortunately, DDboost was not something Ola’s solution suported at that time and what I ended up was writing up my own stored procs calling “dbo.emc_run_backup” and “ddbmexptool.exe” based on our Lockbox settings and scheduled the jobs as needed. Well, I got it worked without any issues but had to spend quite a bit of time to understand all the bells and whistles of DDboost and come up with my own procs and error logging.

If you happen to have any plans evaluating DDBoost for SQL backups now or in near future, well you have an awesome solution already available. All you need to do is pass ‘DATA_DOMAIN_BOOST’ as a parameter for third-part backup software in Ola’s backup job and specify below parameters.

Image source: https://ola.hallengren.com/sql-server-backup.html


A big shout-out to Ola! Cheers, Happy weekend.


How to Create a Suspect Database???….

Yes, You heard it right! How to Create a SUSPECT Database. You might be thinking, why would someone make a Database Suspect! I got you! Well, This post is not to teach you how to make your Production Database as “SUSPECT” 😉  I thought of sharing this Info, to fight a PANIC situation where you see a SUSPECT Database in your environment.  Create a SUSPECT Database in your play area and practice how to recover from that situation, get familiarize with a SUSPECT DB and play with it and try to recover from it far before a real Disaster hits your Prod Database(I hope not).

In this post, I’m not going to show how to recover from “SUSPECT DB”, Instead just to simulate a scenario where your DB goes into SUSPECT State. Let’s begin!!!…

Couple of months ago, I’ve written a small blog post(see here) on how to simulate 824 Error using Hex Editor. I’ll be using the same tool to achieve our goal here in this post.

Simple Method:

Step 1: Create a Dummy Database to play with.

Step 2 : Shutdown your SQL Server DB Engine Service.

Step 3: Open your LDF file with Hex Editor and edit it manually with few Bogus entries.

Step 4: Start your SQL Instance, you should be seeing your Database marked as Suspect by SQL Server.

Alternate, more practical method:

Step 1: I’ve created a dummy database to play with “Suspect_tst”.

Step2: I’ve started an explicit transaction(but didn’t committed or Rolled Back) and left it as an Open transaction and did a Checkpoint.

Step3: Force Shut down your SQL Server.

Step4: Open your ldf file in Hex Editor and choose a random row and fill them with some random bogus values same as in method 1.

Step5: Start your SQL Server, You will be seeing a Suspect Database 🙂

You can see the below Screenshot what i meant in Steps 1 and 2.

Now, as of Step 3, all you’ve to do is just open a new query and type “SHUTDOWN  WITH NOWAIT;“(Never ever run this on Production, Trust me, you’ll immediately loose your job:D)

for Step 5, as you can see in the below screenshot, I’ve opened ldf file and filled in 2nd Row with all 1’s.

Save and now Start your SQL Server Instance! Once SQL has been started, query your DB Status as shown below.

Tadaaaaa! We did it….:)

Now, you know how to simulate this most disastrous situation, play with this database for recovery, loosing as less amount data as you can. Cheers!

How to Rebuild Master Database?? AKA Rebuilding SQL Server 2008R2.

Have you ever been in a situation where you lost the complete Drive where your System Databases are residing and even SAN Admins were not able to bring the Drive back?? Fortunately I’ve never been in that situation:)  What would be our available options if that disaster strikes?? In this scenario restoring System Databases won’t work because SQL Server Service itself is completely down and your System Databases mdf and ldf files are gone!!..So what should we do?? REBUILD YOUR MASTER. Rebuilding Master will actually create brand new System databases from scratch as if you just Installed your SQL Server.  There’s a misconception that we need the Actual SQL Server media and it’s mandatory to rebuild your Master. Those days are gone..That was the case with SQL Server 2005 and earlier. Starting SQL Server 2008 it’ll rebuild based upon the TEMPLATES which it creates in your BINN Directory. (This is the one of the main Reasons,you should place your SQL Binaries and actual System Databases on completely separate Physical Drives.  Let me show you what I’m talking about…..You can see the templates folder which SQL Server created while initial Installation of this Instance on my machine.

These are not the actual system database files….I repeat, these are Just templates which SQL Server will create behind the scenes during Installation Process. My actual System Databases are located in DATA folder as shown below

Note: On My laptop I Installed every thing Just on C Drive. You should never ever do this in a real environment. There’s no single valid/good reason to do so…

Once rebuild process is complete, You’ve to Restore all the User Databases from your Backups and Restore Master and Master Databases in case if you want to get back all your Logins, Jobs, Server objects etc….Yes! I agree that sounds like a very painful process….But if you’ve all your recent healthy backups and few other dependent objects…you are the champion:) So..if you don’t have a proper Backup/Restore Strategy in place…you are gone!!..You might even loose your Job for this single most important valid reason. In this post, let’s try to rebuild our entire SQL Server/Master Database.

I’ll simulate scenario by Shutting down SQL Server Services on this Instance and physically deleting the mdf and ldf files of system databases and let’s see how it looks and what are the errors you’ll be getting in this case.

Step1: Now my Data folder looks as shown below

Step 2: I tried to Start SQL Server Service from our Configuration Manager and the error messages which i’m getting are (see below Screenshots).

In the Event Viewer I’m seeing the below

Okay…! In this case we know that the issue is not with Invalid Startup Option since we deliberately deleted our Master.mdf file.

Step 3: Let’s see how to rebuild using SETUP.EXE 

As i already mentioned above we no longer need installation media to rebuild SQL Server(you can use it as well if you want to). All you need to know is where did you choose the “Setup Bootstrap/program files” while Installing your SQL Server initially. ( standardizing all these Drives, locations and paths across the Servers in your organization greatly reduces the pain to maintaining all these important details individually at Server level). In my case it’s “C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release” as you can see below

Step 4: Open Command Prompt(Run as Admin) and navigate to this folder where you can find SETUP.EXE and we can do the “QUIET Installation” passing the required parameters. In My case the Syntax was


If you want to explore all other options and parameters available to us….you can issue “setup.exe/?”  for help from windows as shown below.

In the above syntax every switch is Space Seperated. Here we are using Quiet Mode and the action is Rebuild master Database, Instance name is DR(For Default Instance: INSTANCENAME will be “MSSQLSERVER”), adding myself as a Local Admin and providing sa password(since mine is a mixed mode).

Very Very Important: Actually It failed for me and got below error Messages!!!! The error Messages were “The following is an exception stack listing the exceptions in outermost to innermost order  Inner exceptions are being indented  Exception type: Microsoft.SqlServer.Setup.Chainer.Workflow.NoopWorkflowException Message: The state of your SQL Server installation was not changed after the setup execution. Please review the summary.txt logs for further details.”  I’ve no idea what it is trying to convey here…Looks like a Bug( may be may be not) to me in SQL Server 2008R2( I’m using 2008R2 for this demo).
Work Around: I’ve selected “C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\Setup.exe” instead of “C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release\setup.exe

Even if this doesn’t work for you, please try to make use of setup.exe from Installation media. This should work. I hope this strange behavior has been fixed in DENALI.

Sooo…..the actual Syntax and Path which worked for my case is as shown below

it took around 7-10 minutes in my case and got a command prompt without any errors as you can see in the above screenshot 🙂

FYI Failed path(in my case) as per Microsoft documentation  is as shown below

Please keep this Bug in your mind and don’t just PANIC if you encounter the same error message.

Well, with all this hard work…what did we achieved?? Did SQL Server Instance “DR” got rebuilt??  Yesssssssssss as you can see below, all the System database files are back.

Also..I was able to Start SQL Services from the Configuration manager and as you can see below….everything appears to be brand new…. Tadaaaaaaaaaaaaaaaaaaaaaaa!! We did it guys 🙂 🙂


Now it’t time to (patch if applicable) and  restore your Databases from your healthy Backups accordingly. You can see how to restore System Databases here.

That’s it Guys!!! You should be aware of this process and these challenges and work arounds as a production DBA. I would recommend to test this in your lab at least once or twice before actual disaster strikes your Production Servers…(I hope you never need this….But you should not be surprised when it hits). Hope this helps. Cheers!

SQL Server System Databases

SQL Server System Databases are very simple and pretty small (msdb might be huge in some cases if we are not properly maintaining it) databases at a very high level, but the most(i mean it) important databases within SQL Server.  Why am i stressing the word “the most Important”??? Because SQL Server Stores it majority of the configuration information in these Databases besides storing some in Registry  and few configuration(ini/xml) files and SQL needs them in a healthy shape to be in running state. So what are the System Databases in SQL Server?

MasterMODELMSDBTEMPDBRESOURCE Database  and Distribution(I would call it as a pseudo System Database 😉 You will see this DB only if you’ve configured replication). Let me explain what each database does in brief…Let’s start with Master Database.

Master:  The Very first Database loaded by SQL Server on Service Start. If if finds any issue bringing up this database, entire SQL Server Instance will be down. Master Database stores very critical information regarding Instance configurations such as CPU info, Memory configurations, other sp_configure settings etc. It stores the Information(Meta Data) of all other databases on the respective instance. Stores Logins information and all other Instance scoped objects(Server Objects) such as Linked Servers, ENDPOINTS etc…

Model: This little guy acts as a TEMPLATE Database. whatever settings you configure on this database, they will be reflected on any other new database you create on this Instance(Classic Example – Setting Model Database Recovery Model to SIMPLE on your DEV Server, making sure that all the databases which you are going to create from now on will be in SIMPLE Rec mode on this Intance). If you create any object(let’s say a user table) inside Model database, all the new databases which you create from now on contains the same object! So…..Basically you can use this guy for your standardization!

MSDB: Well, this guy stores all the critical information any thing related to Automation. Soo….anything which you do with SQL Agent, it gets stored or it updates MSDB Database in one or the other form. All your Agent Jobs, Maintenance Plans information, Backup/Restore Events/Alerts/DB Mail information, even SSIS packages(if you are doing MSDB Deployments) and So on…..! Very Very Important and heavily used by Agent Service.

TEMPDB: The busiestttttttttttttt System Database out of all!  Ignoring TempDB configuration will heavily impact your SQL Server performance.  As the name implies…it’s a temporary Database where all the temporary objects will be created, Cursors use TempDB, Row Versioning is done in TEMPDB(SNAPSHOT ISOLATION), ONLINE INDEX REBUILDS(choosing Store/Sort Results in TEMPDB), Memory Spills occur a lot to TEMPDB with Hash Joins and Hash Recursions and even SORTS and many more things heavily uses TEMPDB.  Interesting thing to note regarding this Database is, It’ll be “Wiped Out” entirely when we shutdown SQL Server and Brand new TEMPDB will be created at Start up of SQL Server.  So….there’s no concept of  “CRASH RECOVERY ON TEMPDB” in SQL Server/You can’t take a Backup of TEMPDB.

RESOURCE DATABASE: This guy will always be in “STEALTH” Mode hiding from us. We can not see this database from our SSMS. We’ve to actually go to our File System to look at the Data and log files of this Database. It is a Read-Only Database containing “System Objects” of SQL Server. This guy is very very important while we are upgrading our SQL(applying Hotfixes/Service Packs/ CU’s etc)which many of the DBA’s ignore to take backup before applying Patches!!!

I believe that’s enough information to understand how critical these Databases are! For more information on these Databases check BOL….MSFT has tons and tons of very useful information on these Databases.

Backups on System Databases:

Well, now let’s see how can we secure these Databases. In other Words how to design a Disaster Recovery strategy on these System Databases. I’ve seen few(TBH Many) cases where a DBA ignores Backing up the System Databases….Very Very Bad!! Trust me guys…you’ll pay the penalty someday or the other if you ignore backing up these databases!

Before proceeding any further, let me tell you something…You Should never Ignore backing up and performing frequent Consistency Checks on your System Databases. In a worst case scenario where you have to rebuild your entire server, you would need them to be restored without any issues. ( Imagine a case where you have 100+ Jobs and 300+ Logins and 20-30 Linked Servers and you don’t have your System Database Backups….It’s scary enough just to imagine right?? ) FYI I’ve shown how to Restore Master and MSDB Database here.

Note: You’ve to know your SQL Server Instance Collation Information to rebuild your SQL Server(Rebuilding Master DB). I’m saying…rebuilding master, not Restoring your master database in this collation context. I’ll show you how to rebuild entire SQL Server in later posts.

Master Database  – Take Full backup as often as you do for your User Databases. You can’t take Log backup for Master Database. Only Full backups are allowed on Master Database.

Model Database – Well, it’s your call! if you are using it, you can take backups..if not you can ignore. I personally feel taking Model backups is just a safe bet and the size of the Model Database backup will be usually in few Kilo Bytes, which is almost negligible!

MSDB Database – You’ve to take regular Backups. You can perform all types of Backups on MSDB(Full/Diff/Log). Plan accordingly!

TEMPDB – You can’t Backup TEMPDB Database. As i mentioned above, SQL will recreate tempdb on every service restart.

Resource Database –  This should be our part of Backup Strategy(whenever possible). At least once a week or bi-weekly! You should not ignore backing up this especially when you are patching/upgrading your SQL Server. So..where should we locate this hidden Database?? The Answer is in your SQL Server Program Files(location where you installed your SQL Server Binaries) starting SQL Server 2008, not in the Data file(s) Directory anymore”.  You can see in the Below Screenshot for the default location of this Database files on one of my Instance

So, in my case the path is “C:\Program Files\Microsoft SQL Server\MSSQL10_50.PROD\MSSQL\Binn”.
For backing up this Database, all we need to do is, Just copy these two files and place them in your secure backups location. So…We can’t do a SQL Backup on this Database, the trick is to copy the files at File System Level.

That’s pretty much it Guys!! Hope this is useful and informational…

Restoring Master and MSDB Database(s) – System Databases

Guys, today was a very looooong day for us. We had to restore Master Database and MSDB Database on our Production Server…Yessssssssss, you heard it rightL

PS Rebuild Master is different from Restoring Master Database.
Let me tell you the story….what exactly forced us to make this dangerous move.
This weekend our Windows folks had a planned Windows Patching activity and they were doing some Net backup Upgrades on our Production Database Servers. Everything appeared to be normal, till they rebooted the machine. Once they initiated Reboot, Server was not coming up! OMG…you can imagine all kinds of screams on the call! They did all sorts of attempts to bring the server UP via HP ILO without any success:( They stepped into our Data Center and the result was the same…At last HP Engineer was Onsite, but considering the time factor (Business starts on Monday Morning), they decided to rebuild the Entire Windows Server! So… we, the SQL Server DBA’s decided to use our DR Server meanwhile. Unfortunately the Application Folks were not happy running on the DR Server for more than 48 Hours (Since they (we) identified few missing critical SSIS packages and Linked Servers. The Bad DBA who’s supporting this Server missed it for some reason. I’ll come up with a short Blog post very soon what needs to be considered besides Just the Database in your DR Strategy – Stay Tuned)
FYI we had the below-
SQL Server 2005SP3. (Pls. don’t ask me why are we still running on 2005 SP3 on this box;-p )
DR Strategy – DB Mirroring.
Well, So the Quick/Short Plan was:
1 – Windows Folks Reimage the Entire Windows Server.
2 – Storage team takes care of SAN and Drive Allocations etc.
3 – Once notified, We’ll Install SQL Server and recover the databases from our DR (the current Production) Server.
At last we got notified that Windows is all set and the Server is ready for SQL Installation.  At this point interestingly one of the LUN (F$) was not visible to us. SAN Folks mi(e)ssed it. Again we pulled them and they had to do their magic. Well, it’s time to Install SQL and LiteSpeed and do the rest of Our Magic as DBA’sJ
1.      We Installed SQL Server, Making sure that the DR and Prod Servers match the Build numbers.
2.     Before migrating databases from DR to PROD Server, our plan was to restore MSDB and MASTER (We had >100 Agent Jobs and several linked Servers, Endpoints, tons and tons of logins). Our Poor DBA didn’t notice the importance of securing all that info in a secure place till date on prod Server (A very good lesson learnt). So we thought of Restoring Master and MSDB Databases to restore all that critical pieces of puzzle within short time and with less pain in our Butt!

Glad that We’ve a very reliable Backup/Restore Strategy. Soo….Always note that Database Backups   (Healthy and restorable backups) are DBA’s Best of Best FriendsJ (I mean it).

Let’s Get Started with Restoring MSDB Database:

Let’s simulate a simple scenario here! Assume, the current Server Snapshot looks as below

Please notice tst, tst1 logins and DENALI linked Server and tst,tst1 Agent Jobs. Let’s assume the below snapshot is from a brand new Installation (you can see all the above mentioned pieces are missing here).

Before Restoring MSDB, make sure that your Agent Service is stopped for Exclusive access. Let me tell you something, Restoring MSDB and MODEL Databases is no different from restoring a simple User Database.

RESTORE DATABASE [msdb] FROM DISK = N’D:\msdb1.bak’
11 percent processed.
23 percent processed.
35 percent processed.
41 percent processed.
53 percent processed.
65 percent processed.
71 percent processed.
83 percent processed.
95 percent processed.
100 percent processed.
Processed 2136 pages for database ‘msdb’, file ‘MSDBData’ on file 1.
Processed 2 pages for database ‘msdb’, file ‘MSDBLog’ on file 1.
RESTORE DATABASE successfully processed 2138 pages in 1.407 seconds (11.871 MB/sec).

Now, let’s Start SQL Agent and look at the jobs.

Ha ha…our Jobs are Back. That’s all we need to do for Restoring MSDB!!!

Let’s Restore Master Database now.(Go and grab your coffee!)
First Step you’ve to remember to restore Master Database is you’ve to start your SQL Server in Single User Mode.
How to start SQL in Single User Mode?
Go to SQL Configuration manager and add -m in startup parameters as shown below.

Points to Remember: Semi Colon is mandatory between switches and there should be no space in between as shown above.

When SQL is started in single-user mode, first stop SQL Server Agent. Otherwise, Agent might connect first and prevent you to connect from!! 1st Sysadmin connection will be allowed to be connected at this time and rest of the connections will be thrown an error as per below screenshot

Now, open your sqlcmd (Microsoft recommends us to use SQLCMD while restoring Master Database) and please see the syntax as shown below. FYI My SQL Instance name is PROD.

Now, go to your Config manager, your SQL Server will be in Stopped State.Not a problem at all! (Restore Master Database will shut Down the SQL Server Service).
Remove the –m switch added in startup parameters and start the SQL Server and Agent Services.
Now if you see the below snapshot, Server came up exactly as it was earlier in the first Screenshot with all the logins, Jobs and the Linked Server.Tadaaaaaaaaaaaa:) 🙂 🙂

Also, Once Master has been restored successfully, our Mirroring Database went into Synchronizing/and then
into Synchronized state after a while from Disconnected State.Hurrrray………!! This happened because all the Mirroring ENDPOINTS info was secured in our Master Database Backup which knows the Mirror Database Server/Database information to communicate with:)

That’s all Folks! Hope this post helps you. Happy Tuesday!