| Author |
Topic |
|
dakins
Starting Member
4 Posts |
 Posted - 2011-11-01 : 13:25:24
|
| I am a developer and have been working with SQL for a while as far as programming (creating dbs, t-sql, etc). I've created db backups but never a maintenance plan. The guy that used to do the backups left, and I'm looking to refine the process and add some best practices.For most of our databases we're going to go with log backups throughout the day, differentials at night and a weekly full backup.My question is the best way to group the files. The guy before me threw everything into one bak file. We recently had to restore one of these - the backup file was over 130G and it gave us a heck of a time.Should each backup be in it's own file (no appending to the set), or should full, differential and transaction backups be grouped together in a respective file? Or should we have one backup per day with all the transactional and differential backups for the day in it? |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-01 : 13:32:31
|
| I always create backups as seperate files and include the timestamp.<DBNAME>_FULL_yyymmdd_hhmmss.bak<DBNAME>_DIFF_yyymmdd_hhmmss.bak<DBNAME>_LOG_yyymmdd_hhmmss.bakBut then I tend not to use maintenance plans.The tr logs are independant from the other backups in that they are not affected by them - pick any full + one diff and you can restore all the following tr logs.Full and diff are different - the diffs need to be matched to a full.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
dakins
Starting Member
4 Posts |
Posted - 2011-11-01 : 13:40:28
|
| When I say "maintenance plan" I was referring to the built in "maintenance plan" in SSMS. That's what is currently being used now. I'm looking to script it out and run it with jobs.I just wasn't sure what the best/recommended practice was for file naming conventions & appending. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-11-01 : 14:16:22
|
| Why would you wanna run them as jobs ? The maintenance plan itself do run as jobs.Also what I would recommend is have a daily full backup,2 diff backup's each day and log backup's recurring say every 15 or 30 mins and put them in folder named as the current day and delete the those backup folders which are like 2 days old.PBUH |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-01 : 14:42:29
|
quote:
Originally posted by dakins
When I say "maintenance plan" I was referring to the built in "maintenance plan" in SSMS. That's what is currently being used now. I'm looking to script it out and run it with jobs.I just wasn't sure what the best/recommended practice was for file naming conventions & appending.
Are you talking about the ssis package that can be created and scheduled as a job in the agent? That's all it probably is.As I said - I don't tend to use them. My naming conventions as posted and I don't append.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-01 : 14:47:10
|
| Separate files, one-per-backup where the filename includes Date/Time or somesuch, makes it much easier to purge "stale" files. If one physical file contains multiple backups you can;t do that so easily, and you are more at risk from some corruption of the file.We retain full, weekly, backups online for 4 weeks, Daily DIFF backups for a week and 15-minute-ly TLog backups for 2 days. The whole lot are on tape too, but getting our Tape-guys to restore from tape takes several hours and mostly we need to restore from within the last hour or two, for some major cock-up, or to restore a recent backup to a TEMP database to check how something was at that time, or to restore a specific record(s) that was deleted accidentally. Its annoying (to both the Admin folk AND the Users) to have to wait a couple of hours to get that back from tape, and quite often having recovered one, and reviewed it, we then immediately need to try a different one (e.g. to find out "when" the accident/event happened)In a total disaster we will need the tape backup from offsite ... |
|
|
|
dakins
Starting Member
4 Posts |
Posted - 2011-11-01 : 14:52:33
|
quote:
Are you talking about the ssis package that can be created and scheduled as a job in the agent? That's all it probably is.
Yes, that's what's being used now that I'm trying to get away from. We have a couple of servers that have 20+ databases on them, and they all get backed up into one single file - every single database. I can't imaging trying to restore from that.quote:
Why would you wanna run them as jobs ? The maintenance plan itself do run as jobs.Also what I would recommend is have a daily full backup,2 diff backup's each day and log backup's recurring say every 15 or 30 mins and put them in folder named as the current day and delete the those backup folders which are like 2 days old.
I do want to run them as jobs, I'll just either create the jobs as t-sql, or my own ssis packages I create that use t-sql rather than the built in tasks they give you for backing up, etc. |
|
|
|
dakins
Starting Member
4 Posts |
Posted - 2011-11-01 : 14:55:26
|
quote:
Originally posted by Kristen
We retain full, weekly, backups online for 4 weeks, Daily DIFF backups...
Those DIFF backups start to get large because they are cumulative correct? So if I do my full backup on Sunday, Saturday's DIFF is going to included all the extents from Mon - Sat, even if I'm doing DIFFs every day - right? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-02 : 05:39:13
|
| Yes, that's correct. If they were INCREMENTAL (not supported by SQL) then they would only contain the additional changes since the previous Incremental backup.But Differential backups get larger each day until the next FULL is done (in our case by the end of the week they are approaching the size of the FULL backup). However, overall it saves disk space for us compared to taking a FULL backup every day, and enables us to relatively quickly restore to any specific day during the last week.Our aim is to be able to restore back a decent length of time (e.g. to investigate something that went wrong which was not detected at the time), but with increasing granularity with time - so we can restore to point-in-time using TLog backups for the last couple of days, to a specific day for the last week, and to a specific Sunday for the past 4 weeks - if we need better than that then we have to get the backups off tape (which usually takes a few hours to have scheduled by our data centre) |
|
|
|
|
New to backups