Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Backups vs System Backups Expand / Collapse
Author
Message
Posted Tuesday, November 27, 2012 3:58 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 10:24 AM
Points: 16, Visits: 98
Being sorta new to DBA work ...

If / what are the benefits of keeping sql backups in addition to system backups ?
Post #1389449
Posted Tuesday, November 27, 2012 4:21 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 11:09 AM
Points: 901, Visits: 7,159
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
Post #1389459
Posted Tuesday, November 27, 2012 4:24 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 10:24 AM
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
Post #1389461
Posted Tuesday, November 27, 2012 4:33 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 11:09 AM
Points: 901, Visits: 7,159
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
Post #1389462
Posted Wednesday, November 28, 2012 8:01 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:15 AM
Points: 15,622, Visits: 28,006
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1389806
Posted Wednesday, November 28, 2012 8:14 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 978, Visits: 5,086
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
Post #1389820
Posted Wednesday, November 28, 2012 7:48 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 3,893, Visits: 7,138
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"
Post #1390233
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse