1

I am currently working administering a SQL Server 2014 Standard Edition instance for Sharepoint 2013. We started 5 month ago with ~25 DB's and now we have 60 in total. So the databases in use are growing. In case of a recovery I would have to restore all my collected backups quickly.

Until now I would copy the name and put it into a single script. But in a real disaster situation I would love to be prepared better. How do you manage this? Do you have a tool or anything which registers the newly created DB's?

Improve this question

3 Answers 3

Reset to default
3

There are couple of things that you need to make clear

But in a real disaster situation I would love to be prepared better. How do you manage this?

Dont wait for the disaster to happen. Taking backups is not just enough. You should be able to confirm that the backups you take are able to restore as well. A backup is good ONLY if you are able to restore it (and DBCC CHECKDB returns no corruption errors).

I tend to take my backups with CHECKSUM. To quote Paul Randal

using the WITH CHECKSUM option and having the backup complete successfully tells you that those pages with page checksums had not been corrupted by the I/O subsystem at the time the backup was taken.

Do you have a tool or anything which registers the newly created DB's?

Technically, you dont need a tool. All the new databases are in master.sys.databases and msdb - backup tables record the backup history. The script from Paul Brewer - restore Gene takes care of generating the restore commands for you. (I have used it and its an excellent tool/script !)

If you really need to log what databases are created, then you can use server level trigger CREATE_DATABASE and log it in a DBA database.

Improve this answer
2

Here's what I personally prefer to do:

  • All my SQL Instances that I build have Ola Hallengren's MaintenanceSolution installed. And I'm using Ola's 'DatabaseBackup' stored procedure to perform backups as required (FULLs, DIFFs, LOGs)
  • One amazing feature of Ola's MaintenanceSolution is the 'CommandLog' table. This table stores a historical record of all actions performed by the MaintenanceSolution stored procedures.
  • Now the issue is that Ola's scripts don't include a feature to automatically create the 'RESTORE' commands that are required to restore your databases.
  • To the rescue comes Jared Zagelbaum who created exactly this missing feature and I'm now using the combination of these 2 solutions successfully on many SQL instances.

This ZIP file of the presentation from Jared at SQL Saturday Minnesota has all the gory details: http://www.sqlpass.org/EventDownload.aspx?suid=11023

Improve this answer
1

Paul Brewer has a nice script that will generate restore scripts.

https://paulbrewer.wordpress.com/2014/08/15/restore-gene-version-3-free-public-framework-technical-article/

There is a T-SQL version and a PoSH version. Run it after you do your backups and then store the scripts with them. I'd test it out a few times ahead of time to make sure you are comfortable with it but I've used it on more than one occasion and it works great.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.