Solved

backing up SQL databases but not underlying system drive

Posted on 2014-07-30
10
227 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
10 Comments
 
LVL 75

Accepted Solution

by:
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.
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 39

Assisted Solution

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

Assisted Solution

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

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.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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 29

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now