Recovering All System Databases with Backups

Hi Team,
as part of my DR testing on a virtual machine, we are doing a force crash of the server. i will have all databases backups only. including system databases.

please could you tell me the steps how can I restore all the system databases with the available latest full backups

thanks in advance

And LOG backups? If you aren't using Full Recovery Model on DBs that you want to have a Disaster Recovery Restore on and if they contain changing data (e.g. OLTP) then ... IMHO ... You Should Be :sunglasses:

Personally I would do the following:

  1. Inform all users, disconnect all users from DBs, stop all Scheduled Tasks, prevent any external access from anything which does Remote Connections and/or Batch Updates through API/similar. It helps if there is a single "Button" that you can press that does this - i.e. something "Prepared in advance" :slight_smile:

  2. Is there a need to restore? Generally SQL will fail-safe (rollback all incomplete transactions WITHOUT any database corruption). For example, after a Power Failure. For your Disaster Recovery Test I am sure you need to do a Fire Drill Restore, but in real life that may not be required if the database passes a "Safe to use" test - e.g. running DBCC CHECKDB, so that would be my first step - but always proceed to Step 2 if doing a Fire Drill

  3. First make a TAIL TLog backup (if you can - i.e. server / disks have not been destroyed!)

  4. Restore the latest FULL backup for each database, followed by all the TLog backups (and finally the TAIL TLog backup, if you were able to make one). Only use RECOVERY after the last Restore has been made successfully.

  5. Probably then run DBCC CHECKDB to check that the database is OK, but if that will take a long time (because your database is large) you may let users back into the DB whilst the CHECKDB is running [with the previso that they may lose data if the CHECKDB fails and you THEN have to restore to an earlier date - but if your backups have been made from a "clean" database, better still: if your backups are test-restored onto a standby/DEV server, there is almost zero chance that they will fail to restore]

  6. re-enable Scheduled Tasks, Remote Access and User Access and inform users that the system is ready for use.

But ... the problem that then arises is that you have N x Databases to restore, you have to locate the most recent Full Backup for each of them, and you have M x TLog files to restore (for EACH database - in Full Recovery Model). Its a lot of files, and you really need a script that will do all that for you, rather than trying to do it manually (at a time when speed is of the essence, to allow users to resume working)

IF your MSDB database is intact you can use that to query what backups were made, when, and the location of their files. There are scripts available which will restore one/many DBs based on MSDB data.

If your MSDB is NOT available / or NOT current (you can only restore it from a backup several hours/days old perhaps) then you will need a script which looks at the physical backup Files available in a particular folder. If you always name your backups consistently (e.g. MyDatabaseName_21060522_090712.BAK) then a script can "parse" the database name, and the date/time parts to sequence the restores in the original order (but beware of daylight saving time changes and overlapping filenames ...)

But note that ALL your backup files must be available in that folder. IF you allow people to "just take a quick backup for Belt & Braces safety", and IF they are allowed to put that backup "anywhere" then all bets are off ...

... we have an SProc that makes a backup of a database, it requires three parameters - name of the DB, type (Full, Diff, TLog) and, optionally, a comment. We require that all backups are made using this SProc to ensure that they are in a specific folder etc. This is stipulated in any contracts with 3rd parties so that there are no "workarounds".

In my opinion, you should state out how is your DR setup. Personally, I don't think restoring all DB (including system DB) is the best way for your DR solution. You might want to consider log shipping, mirroring or always-on for you DR solution, which in turns means you do not require restore of your DBs as they should be sync up to a period of time (or fully sync). You mention your DR is on a VM, which means it should be online while Prod is on, hence by setting up a better DR solution might be easier (and better)!

Hope this helps.

I agree with DennisC above. You're talking about the "company jewels" here and just having a "restore" plan is grossly inadequate for a DR plan. To be honest, a full out system crash shouldn't cause any more than about a half second of down time while the system shifts to the fail-over node.

If you insist, though, here's one possibility to get you started. It is by no means as comprehensive as it should be.

Again, there's nothing trivial about DR. You have to have a plan and you have to test the plan (we test it ever year and we test fail-overs once a month). We also do nightly restores of our "Money Maker" databases because backups don't mean squat if you can't actually restore them.

My expectation would be that the cost of a "fail-over node" is way too high for a small enterprise. I also read this in terms of the O/P's "as part of my DR testing" meaning that this was a Fire Drill, and part of a wider policy.

We are probably close to being a Medium enterprise, our servers are most definitely critical to the business, but we don't have fail over servers. We would need multiple fail-over servers (Exchange, Document Management System, 2x SQL boxes, Terminal Services, and various other servers). The cost would be very significant (Hardware-$ plus all the Setup-$ and fail-over-testing-$)

Our DR is downtime of two days. All employees working from home (assuming office is destroyed) connecting to replacement, restored, servers (hardware/IT support supplier has an SLA for that) at a new site. All our backups are off-site (in real time, not a daily tape backup type scenario), and most backups are at least every 10 minutes. I'm confident that we would lose almost no data ... but we will suffer a major outage if DR were needed.

Our DR plan most definitely includes a "restore everything" approach ...

I would recommend you before restoring MSDB, make sure that your Agent Service is stopped for Exclusive access.

Surely that applies to all DB's? My restore script would force SINGLE USER (or OFFLINE) in order to ensure exclusive access.

Hi,

  1. Disconnect all users from the database.
  2. Stop all SQL jobs running behind.
  3. Stop all scheduled task.
  4. Make sure that your Agent Service is stopped for Exclusive access.
  5. Take a TLog back up.
  6. Execute Re-store process.
  7. Execute DBCC CHECKDB to check that database Is OK.
  8. Re-enable the scheduled tasks.