• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

backing up SQL databases but not underlying system drive

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.
0
pma111
Asked:
pma111
6 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
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.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
One more thing I would  add to the above settings is storing the database /systems settings somewhere with your backups
0
 
lcohanDatabase AnalystCommented:
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.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Scott PletcherSenior DBACommented:
>> 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.
0
 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
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.
0
 
pma111Author Commented:
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?
0
 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
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.)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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 ..
0
 
Paul SolovyovskySenior IT AdvisorCommented:
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.
0
 
Anthony PerkinsCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now