SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Backups vs System Backups


SQL Backups vs System Backups

Author
Message
cjbaum
cjbaum
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 98
Being sorta new to DBA work ...

If / what are the benefits of keeping sql backups in addition to system backups ?
David Webb-CDS
David Webb-CDS
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4216 Visits: 8586
You need both. A normal system backup, if it can copy the .mdf and .ldf database components at all, will not result in a database that is restorable. (Check with the vendor of your backup software to see if they have special tools for database backups). The native SQL Server backups copy out the data and logs in such a way as to allow a restore operation to put the data back and adjust any in-flight transactions that may or may not have completed. An OS copy operation can't do that. If all you have so far is OS utility copies of your .mdf and .ldf files you need to take backups IMMEDIATELY using the SQL Server backup utilities and then take a look at the BOL topics of backup and restore. There are also several good articles on this site on database and log backup management. I'd site the references, but the site search seems to be non-functional.

Bottom line. You need SQL Server backups.



And then again, I might be wrong ...
David Webb
cjbaum
cjbaum
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 98
Thanks. Im looking for as much ammo as possible

My sysadmin guys keep arguing that the "System Center" backups are enough, which seem ok if they want to restore the whole server
David Webb-CDS
David Webb-CDS
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4216 Visits: 8586
Some backup software is SQL Server aware and will actually use the native functionality under the covers. I don't know enough about the System Center product set to know. It may be covered under Data Protection Manager, so your admin may be gettting a good set of backups because DPM is SQL Server aware.

Maybe someone who is using it can weigh in with more authority?



And then again, I might be wrong ...
David Webb
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100827 Visits: 33014
The real question is not what can be backed up. The real question is, what can be recovered. If you take a system backup once a day, then you can only ever recover up to the point of that backup. Or can you. Have the system administrator demonstrate a restore (obviously, do this test on a test/dev system, not production). If that works, great. Now, ask the business, can we lose an entire day's worth of data? If not, then you also need to do log backups or differentials... but, there's the problem, those won't work with your system backup. So, assuming your business is like most of the businesses I've worked with, you're going to need to do a regular, SQL Server, backup and then logs/differentials to allow for the ability to recover to a point in time short of once a day.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
jasona.work
jasona.work
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10427 Visits: 12611
Presuming your SysAdmin guys are using Data Protection Manager (full name from MS: System Center Data Protection Manager 20XX) to back up the server, there's a few "gotchas."

First, DPM doesn't give you quite the "fine-grained" control of a point-in-time recovery. You can ONLY recover a DB to the point DPM backed it up. So if DPM is doing hourly backups, and the DB gets hosed at 59 minutes after the hour, that's 59 minutes of data gone.

Second, DPM (at least in my situation) when backing up SQL DBs directly, broke my backup log chain (See this thread) Not fun.

Third, DPM won't keep your transaction logs under control, so you'll still need to perform SQL backups, or put the DBs into Simple recovery mode.

My suggestion would be to work with the Sysadmins, perhaps by setting up regular backups in SQL to a folder either on the SQL server, or on the network, then have them back that up. They could also still run complete backups of the server, as I believe DPM can do "bare metal restores" of protected servers. So if the server caught fire, the Sysadmins could restore everything, then you go in and restore your SQL backups (making sure to drop the "pre-attached" SQL DBs that DPM would still have backed up)

And, of course, before doing any of this against production, test, test, test, test.

Jason
MyDoggieJessie
MyDoggieJessie
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12666 Visits: 7444
Agree with what every one is saying here...you need and want SQL backups.

The "benefits" have been well mentioned here already but wanted to point out that for DBs in simple mode or relatively small DB's that live on VM's, if your Tech department is taking an image/backup/clone of the VM itself...say after any ETLs/jobs have loaded the SQL Server with data for the day, then that would also be sufficient.

Of course, in attempts of becoming a DBA when I grow up I'd be the first to tell the Tech guys that I need my FULL, DIFF, and TLOG backups for at least a week in a safe, reliable, and remote location so i could do what needed to be done myself in the event of a DR scenario

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search