SQLTeam.com | Weblogs | Forums

SQL Server Backup job


I have come across a job and I need to determine if I have to recreate it on a new server. I already have scheduled maintenance plans to back up all of our databases. But this is additional job existed that has a single step that runs two lines of t-sql code with MASTER selected as the database and in the command box:

backup database A to disk = 'g:\backups\eBOBackup.bak' with init
backup database B to disk = 'g:\backups\eBOBackup.bak'

I am trying to determine if there is any difference between this and the backup that is being created from the maintenance job other than the fact that the second backup is being appended to the first.


... which is scary beyond belief!

Those backups will be breaking any DIFFerential chain. If you need to have differential backups, going forwards, then you definitely don't want those backups statements migrated! I can't see that that backup is doing anything especially useful, unless something else is expecting to find that-file in that-folder - e.g. to restore to a copy-database for DEVs or somesuch. (Looks like the database is NOT using Full Recovery Model - which would worry me if it is OLTP)

Looks to me like someone threw together a backup "just so that we have got something". The problem with it is that it always reuses the same filename. MAYBE your Tape Backup grabs it in the intervening time so you can get a backup back from "three days ago", but if not you definitely need that capability (you may never want to be able to restore back to three days ago, and repeat all the work inbetween, but you might well want to restore back to three days ago to see what the data looked like then - e.g. you think there has been some Fraud, or someone "accidentally" deleted 10,000 customers 3 days ago and has only just admitted / realised it). Going forwards I recommend that your backup files have unique names (e.g. include database name + date/time in the filename) and that you keep a decent amount online on the server if you have enough disk space. It is much easier to recover from a file which you still have, rather than having to restore from Tape first and then recover the file.

If you are migrating and/or sorting out the backup situation its a good time to reconsider how that functions. I was reading Brent Ozar's newsletter the other day and he posed the question "Is backing up ever minute too often?" ... turns out that it isn't, and given the choice why wouldn't you do that? Your maximum data loss (assuming an OLTP system) is then only 60 seconds ... I've changed from hourly to 10 minute backup intervals over the last few years and now thinking "Yeah, why not back up every minute." Hardware is a lot faster than it was, SQL does Compressed Backups even in the cheap & cheerful versions ... just need to be careful with the large number of files (e.g. a directory folder should not hold TOO many files) and make sure that the MSDB maintenance job is running to purge old, stale, backup data - retain 3 months or so, only.

If you have OLTP database that are NOT using Full Recovery model then I recommend that you reconsider that too - how much data can you afford to lose, how much can you re-enter after a breakdown? and how much downtime is tolerable to get everyone back Upright and Working again? Nowadays all data entry seems to be in response to Phone Calls or Emails, rather than "The Post", so the ability to recreate data from a paper-trail is often zilch. So I now assume that a zero-data-loss disaster recovery strategy is likely to be my clients' default position


Thank you for your response. I appreciate you taking the time.

I have reached out to another division to see if they are taking that backup for some reason. Clearly I am going to need to spend a lot more time to go over what we are doing here and see if it can be improved. My first impression was similar to yours in that it seemed unnecessary. And scary if someone is still retrieving this file and depending on it.



You could just bung a well respected backup solution on the server and start with that? My preference would be Minion Backup - choose a path for the backup folder, and maybe one or two other config settings, and run the install script and then backups will happen to a sensible, safe, schedule - which you can then tinker with if you need to. The Maintenance Wizards that MS provide have been an embarrassment since they were first introduced, and they still aren't well respected by professional DBAs.

The other candidate is Ola Hallengren's backup script, but personally I find that one more techie


Disable the job, remove the files, see if anyone screams ... would be my approach. :slight_smile:

If they do scream ask them to justify the String and Gum bolt-together that they are using.