backing up SQL databases but not underlying system drive

Posted on 2014-07-30
Medium Priority
Last Modified: 2014-08-11
Apologies for the basic level of the question but I wanted some input into the following:

Our DBA backups the user and system databases - and in their opinion that is all they need to backup on the windows database server

They claim that if they needed to rebuild the server hey could do it from scratch with minimal effort.

Question: What is your view on this? What outside of the SQL backups would be included in a backup of the system drive and would be required when rebuilding the DB server from scratch? Why are these so important? Is rebuilding from scratch a lot more effort than restoring the backup? What files in the system backup would need configuring from scratch if building from fresh.

Please keep answers pretty basic and management / risk freindly.
Question by:pma111
LVL 75

Accepted Solution

Aneesh Retnakaran earned 336 total points
ID: 40229755
As far as the database server is concerned,  backup of the databases should be enough and that's very much need to rebuild the system from scratch.
If you are in a virtual environment,  taking the backup if the entire server  would also be helpful.
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 40229759
One more thing I would  add to the above settings is storing the database /systems settings somewhere with your backups
LVL 40

Assisted Solution

lcohan earned 336 total points
ID: 40229763
They are correct in my opinion assuming the backup files are kept safe outside the system where the failure occurs - in other words they are not lost with the SQL Server box and can be restored over a newly built server. I would also save SQL Server configurations - sp_configure would show them - and the system drives and folders to be identical in case of a disaster recovery needed.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 332 total points
ID: 40230075
>> What is your view on this? <<

It's probably do-able that way, but it's not nearly as easy as they are stating.  Several other things definitely need to be included/considered.

>> What outside of the SQL backups would be included in a backup of the system drive and would be required when rebuilding the DB server from scratch? <<

Just off the top of my head; iow, this is NOT a complete list:
1) Windows and other local security settings for SQL-related logins;
2) Full-text index catalogs (if any);
3) Format files used by/for SQL Server;
4) SQL script files used by/for SQL Server;
5) SSIS package files (packages not stored in SQL itself);
6) 3rd party files, if you have RedGate or other 3rd party vendor code;
7) Optional: SQL Server log files -- these can be valuable to check for errors right before the system went down, but are not strictly required to restart SQL

>> Is rebuilding from scratch a lot more effort than restoring the backup? <<
Probably not for Windows, assuming that the recovery team has built a slip-streamed version of the OS to be used for a re-install.  I can't directly speak to other software -- decompression, Java versions, etc. -- but those need to be considered.
LVL 31

Assisted Solution

by:Rich Weissler
Rich Weissler earned 332 total points
ID: 40230112
I concur with the other experts, but would add one addition element to backup, if you're using them... any certificates or keys or other encryption pieces needed.

One thing to keep in mind, no one really cares about backups... they only care about restores.  With that in mind, look at the DBA's restore plan, and have them practice it once into a test/lab area.  Make certain they can get everything back with what they already backup.  If they have to go back and get ANYTHING from the existing server, make certain it is documented, they add that to their backup routine AND to the restore plan.

Author Comment

ID: 40230136
Presume windows patches is another thing you lose when building from scratch which may impact recovery times to get it up to date and secure?
LVL 31

Expert Comment

by:Rich Weissler
ID: 40230205
When I build a new server, patches to the current version is part of the new server build.  But, yes... building a suitable replacement server could well impact recovery time.

That said... SQL patches are important.  When the version of SQL increments from a patch, you can't restore backwards.  (I can't think of exceptions off the top of my head.)  As an example, if you production SQL is at 11.00.5058 (SQL 2012 SP2), and the new server build is installed with 11.00.3000 (SQL 2012 SP1)... I wouldn't expect the restores to load on the new server until the later patch is installed.  (And if production is 11.00.3000 and the new server is built with SQL 11.00.5058... the database upgrade will occur when the database is restored on the new server.  In most cases, that doesn't take long, but there isn't a option to not have that upgrade happen.  Sometimes that can be important for change control, etc.)
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 40230212
That's true, but if you are very concerned abt the recovery time, and you think your application is critical, you should set up some High Availabity methods like clustering ,mirroring , log shipping etc ..
LVL 42

Assisted Solution

by:Paul Solovyovsky
Paul Solovyovsky earned 332 total points
ID: 40230440
For better RPO/RTO we image the OS so that we can restore the OS with patches under an hour or so versus building/patching a new OS, SQL, etc.. from scratch.  When time counts you don't want to spend 1/2 day getting the hardware/OS and base SQL installed before you start restoring databases.
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 332 total points
ID: 40230724
When the version of SQL increments from a patch, you can't restore backwards
And it should be clarified that additionally the master database cannot be restored on a SQL Server with a newer service pack.  In other words, SQL Server has to have the exact same version and service pack as the original in order to restore the master database.

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question