Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

backing up SQL databases but not underlying system drive

Posted on 2014-07-30
10
Medium Priority
?
238 Views
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.
0
Comment
Question by:pma111
[X]
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
10 Comments
 
LVL 75

Accepted Solution

by:
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.
0
 
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
0
 
LVL 40

Assisted Solution

by:lcohan
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.
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
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.
0
 
LVL 30

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.
0
 
LVL 3

Author Comment

by:pma111
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?
0
 
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.)
0
 
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 ..
0
 
LVL 42

Assisted Solution

by:paulsolov
paulsolov 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.
0
 
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.
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

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…
What we learned in Webroot's webinar on multi-vector protection.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

715 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