SQL Server restore fails if backups taken in rapid succession

How I (unfairly) think of SQL Server

I don’t spend an awful lot of time thinking about SQL Server. To me it’s a workhorse which I think is a compliment. * It’s steady, reliable and gets the job done. It’s not glamorous though and people don’t talk about it at dinner parties.

I did have cause to give the old SQL Server workhorse a bit more thought recently. I’m having to do a bit of database work and I’m taking lots of backups in rapid succession and restoring them. For some reason the backups didn’t restore the most recent version of the database. The restored DB was weirdly out of date. I got round this by going to options on the backup dialog and selecting ‘Overwrite existing backup sets’

Once I’d blatted out the existing sets then it worked fine. I don’t know why it wouldn’t work if I appended to backup sets and clearly this is a development environment workaround. I’m not advocating people merrily wiping out backup sets on production sets. A better workaround might have been to start using snapshots but once the workhorse was lashed back to the plough then I was happy. That said, I’d be very interested to know why it was doing that if anyone knows.

* On reflection, I wouldn’t like it if called me a workhorse so perhaps it’s not as much of a compliment as I imagine.

Leave a Reply

Your email address will not be published. Required fields are marked *