backing up SQL databases but not underlying system drive

Posted on 2014-07-30
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 75

Accepted Solution

Aneesh Retnakaran earned 84 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 84 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 is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 83 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 30

Assisted Solution

by:Rich Weissler
Rich Weissler earned 83 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 30

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

paulsolov earned 83 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 83 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

735 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