Database Backup and Transaction Log backup

Hello everyone, i am new to this forum, so please bear with me if i make any mistake in my first post :slight_smile:

I have a client that uses our ERP Software, they are preparing for deployment into production so we want to make sure the proper backup/recovery plan is in place.

Currently all the databases in the server are being backup by an external SQL backup utility, which i have no access to and don't even know where the backup files are being stored. but i know from the SQL logs that our ERP database (along with all other user databases in the server) is being backed up every morning at 3am.

However, they put all databases on SIMPLE recovery model, and whatever external tool they use does not do Transaction Log backup.

I was told by management to switch the ERP db to FULL recovery model, then i set up Transaction Log Backup Job in SQL Server Agent. However, the Transaction Log Backup did not work until i do a FULL database backup first (although there was already a FULL database backup done by this other utility at 3am on the night when the db was still in SIMPLE....i am guessing another FULL backup has to be done after the switch from SIMPLE to FULL model ? Anyhoo, the Transaction Log backup works after i took a FULL backup at 9am)

So my questions:

  1. Do the two backup (DB and Log) have to be done by a single utility for it to work? (Currently , its done by 2 different utilities, DB backup done by their other Utility and Log backup done by SQL Server Agent)

Ideally, I would want to turn off the 3am db backup job done by this other external utility, and consolidate it into SQL Server Agent but i have no control over that other backup utility , and their IT guy is not replying me.

Which brings to the next question

  1. If i schedule both DB and Log backup in SQL Server Agent at 1am, will the 3am SQL DB backup job done by other utility mess up the Log backup?

Would appreciate any inputs you can provide!


No. You can restore a Full Backup (any Full Backup) and then restore the Log Backup taken after that time, and then all subsequent Log Backups, in chronological order.

However, you do need to test a restore to prove that point! It WILL be critical that the restore can be done WITH NORECOVERY otherwise it will not be possible to apply Log Backups.

Me too. Those sorts of SQL Agent backups terrify me; the tape they are being backed up to has to be recoverable, so in the event of a hardware failure on the server, in several years time, a suitable/compatible tape drive has to be found and the software installed with the right version [to be able to read THAT tape] and configured to be able to restore the backup file. It may be critical that the server has the same version AND Patch Level even!, of MS SQL installed before the restore will work.

Also, if you want to do a restore, following a disaster, when time is critical, you have the delay of recovering from tape - when the tape drive might be busy with other backups / restores, and if the restore fails you then have to wait for an earlier tape to be restored and so on.

I would also have concern as to whether the database can be restored, from backup, to a Newly Named database. For example, someone deletes some data by accident and you need to restore just that (not the whole DB); you can restore the backup to a New, Temporary, database and then INSERT the missing rows into the production database to copy then across. Or you want to do a fraud investigation. Or you want to do a test restore, to make sure that you can ... certainly don't want to do that over the top of the original DB!

That said, if the SQL Agent they are using is a well known, and trusted, brand then it all should be fine (and there are advantages with such tools - such as being able to restore a single table direct out of the backup files).

No (unless it, too, is taking Log Backups - you need ALL the Log Backups, undamaged, in order to do a restore to point-in-time)

Just in case the other utility is taking differential backups you could take a COPY Full Backup (which won't break their backup chain).

Either way, I would insist that your client make a trial-restore, including Log File restore to point-in-time, to prove that they can and you should take no responsibility for their 3rd party backup product. Clearly their IT guy is happy with it, that's fine, just its not in your skill set so the client needs to take responsibility for backups

Here's my 2p-worth:

Backups to disk files means that there are some/many backup files available online, before they are purged. The files can then be copied to Tape/Cloud "at some future point", e.g. overnight (we also copy them, immediately, to an offsite stand-by server). We keep Log backups for 3 days, differential daily backups for a week and Sunday full backups for 4 weeks. I can restore from any of those without having to ask the IT guys to restore from tape for me (actually in our case it would be "restore from Cloud", but same difference, I can't do that myself, I have to ask a colleague)

The backups are made using standard MS SQL software, supported on future versions (typically MS supports restore from two earlier major version), it is independent of Patch Version.

I can easily copy a backup file to a different server and restore it (for testing, or DEV work, or to do a Corruption Check on the DB itself). I can easily restore to a different named database. I can restore log files, in additional to restoring from a full backup, to any point-in-time.

Clearly the Client's IT Guy is a twit. He is happy with a full-backup-only approach and doesn't understand the benefit of being able to restore from a Log Backup to point-in-time. The Business Manager will definitely understand the benefit when you explain that he can either have a restore from Last Night at 3am, or "any point in time with a maximum of 10 minutes data loss" (or whatever backup frequency your log files are ... we used to do hourly, then 10 minutes became fashionable, and now lots of places are doing log backups every minute - same total disk space used each day,. just lots more individual, smaller, files :slight_smile: (there is some "overhead" in each of the files, so not entirely "same disk space"). If you don't already have a very frequent backup interval for your log files I suggest you consider that; it makes no sense to take Log backups ay more than 10 minutes apart (and don't get "clever" postponing them overnight . you will probably be doing Index Rebuilds then which will generate more log data than users during the day!)

It might well have been that your Log Backup would have worked after the next 3am backup was taken.

1 Like

I think you need to use a third party tool to recover a corrupted SQL database. Few days back, I already faced similar problem and I used SQL Recovery Tool to recover my data. I would recommend you to use this tools as it is highly trustable with your data which can easily retrieve data without loss of any information.

but this isn't about a corrupted database (yet!), surely?

Hi Kristen & Jason
Thank you both for your replies, i have been away for a while so didnt follow up on this, sorry for the late reply.
I finally did get a confirmation from them that their external backup tool only back up database with SIMPLE recovery model, so for the specific database i was working on, i did a SQL FULL, DIFF and Transaction Log backup for them using SQL Job Agent.

1 Like

Personally, this looks like a lawsuit waiting in the wings. As Kristen so aptly stated, their IT people seem to be twits when it comes to databases. I wouldn't setup backups for them unless you get written sign off in contract form and give them written instructions on how to not screw the Point-in-Time backups up. If there is any data corruption or they can't do a restore for what ever reason there is to do such a thing in the future, they will hold you and the company you work for responsible.

Make sure that everything is in writing and nothing get's done unless it's in writing and signed off by the receiving company... especially when twits are involved. :wink: