November 27, 2012 at 3:58 pm
Being sorta new to DBA work ...
If / what are the benefits of keeping sql backups in addition to system backups ?
November 27, 2012 at 4:21 pm
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.
November 27, 2012 at 4:24 pm
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
November 27, 2012 at 4:33 pm
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?
November 28, 2012 at 8:01 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 28, 2012 at 8:14 am
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
November 28, 2012 at 7:48 pm
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; Theyll drag you down to their level and beat you with experience
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply