SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by VM / backup database / Latest Posts

RE: backup database

hakan.winther — Fri, 12 Aug 2011 00:35:08 GMT

I thought that the backup would complete successfully, regardless of the transaction. It could have been a nice feature to be able to :- start a transaction- do some changes to the database that you want to test later without interfering other users- take a backup (it shouldn't participate in the transaction)- rollback the transaction to remove your changes (not the backup)- restore the backup with a new name (or a virtual restore with Red gate Virtual Restore)- do your testing in your copy of the databaseBut the uncommitted changes would't be in the backup anyway, so the feature wouldn't work anyway.

RE: backup database

COOL_ICE — Thu, 23 Sep 2010 01:29:02 GMT

Nice question, I got it right :)

RE: backup database

Raunak Jhawar — Wed, 22 Sep 2010 22:14:04 GMT

Great question!!!

RE: backup database

Steve Jones - SSC Editor — Wed, 22 Sep 2010 11:46:34 GMT

It doesn't make sense, but I can see people trying to do this and thinking they can somehow capture all changes with a backup and a verify, or a reindexing, or something.I missed it, thinking that the transaction would be ignored.

RE: backup database

SQLRNNR — Wed, 22 Sep 2010 11:15:04 GMT

Thanks for the question

RE: backup database

rlwilde — Wed, 22 Sep 2010 10:22:08 GMT

Nice question. Although I can't see why anyone would try to do this. :hehe:

RE: backup database

Cliff Jones — Wed, 22 Sep 2010 09:02:37 GMT

[quote][b]sknox (9/22/2010)[/b][hr][quote][b]Dave62 (9/22/2010)[/b][hr][quote][b]sknox (9/22/2010)[/b][hr]... --other data changes (e.g, by other users) are not rolled back. In this case, the items related to a backup are the backup file itself, and the metadata stored about the backup by SQL server. So "rolling back" a backup would be deleting the backup file and the metadata but leaving any changes to the database intact.[/quote]Agreed. This is the best way to deal with the live scenario. But would utterly fail in the maintenance scenario. If you actually want to get back to the state prior to the backup operation deleting the backup file is not going to work out too well.[/quote]Agreed agreed. And at this point, we've taken what was essentially a cute joke on your part and utterly destroyed it. Apologies for our pedantry. [/quote]Thanks, I did learn something useful today. The definition of pedantry.

RE: backup database

sknox — Wed, 22 Sep 2010 08:42:43 GMT

[quote][b]Dave62 (9/22/2010)[/b][hr][quote][b]sknox (9/22/2010)[/b][hr]... --other data changes (e.g, by other users) are not rolled back. In this case, the items related to a backup are the backup file itself, and the metadata stored about the backup by SQL server. So "rolling back" a backup would be deleting the backup file and the metadata but leaving any changes to the database intact.[/quote]Agreed. This is the best way to deal with the live scenario. But would utterly fail in the maintenance scenario. If you actually want to get back to the state prior to the backup operation deleting the backup file is not going to work out too well.[/quote]Agreed agreed. And at this point, we've taken what was essentially a cute joke on your part and utterly destroyed it. Apologies for our pedantry.

RE: backup database

Dave62 — Wed, 22 Sep 2010 08:38:15 GMT

[quote][b]sknox (9/22/2010)[/b][hr]... --other data changes (e.g, by other users) are not rolled back. In this case, the items related to a backup are the backup file itself, and the metadata stored about the backup by SQL server. So "rolling back" a backup would be deleting the backup file and the metadata but leaving any changes to the database intact.[/quote]Agreed. This is the best way to deal with the live scenario. But would utterly fail in the maintenance scenario. If you actually want to get back to the state prior to the backup operation deleting the backup file is not going to work out too well.

RE: backup database

JF1081 — Wed, 22 Sep 2010 08:36:04 GMT

[quote][b]Carlo Romagnano (9/22/2010)[/b][hr]Some operations have no sense inside a transaction. I surprise to see so many wrong answers.[/quote]For me, it came down to a guess between would it just do the backup anyway or would it tell you that what you are doing does not make sense?

RE: backup database

sknox — Wed, 22 Sep 2010 08:29:51 GMT

[quote][b]Dave62 (9/22/2010)[/b][hr][quote][b]cengland0 (9/22/2010)[/b][hr][quote][b]Dave62 (9/22/2010)[/b][hr]I think the proper way to "rollback" a backup is to do a restore. ;-)[/quote]No, because that will bring the database back to a previous state. The way to rollback the backup would be to delete the file that was created.[/quote]This may be one of those "it depends" things. In my way of thinking, when you rollback a transaction, returning it to the previous state (before the transaction began) is the desired outcome. So restoring the database after a backup is like rolling back a transaction. They both return the database to the state prior to the respective action.[/quote]Not quite. Rolling back a transaction does NOT restore a database to the state it was in before the transaction began. It ONLY restores those items affected by the transaction --other data changes (e.g, by other users) are not rolled back. In this case, the items related to a backup are the backup file itself, and the metadata stored about the backup by SQL server. So "rolling back" a backup would be deleting the backup file and the metadata but leaving any changes to the database intact.

RE: backup database

Dave62 — Wed, 22 Sep 2010 08:21:32 GMT

[quote][b]cengland0 (9/22/2010)[/b][hr][quote][b]Dave62 (9/22/2010)[/b][hr]I think the proper way to "rollback" a backup is to do a restore. ;-)[/quote]No, because that will bring the database back to a previous state. The way to rollback the backup would be to delete the file that was created.[/quote]This may be one of those "it depends" things. In my way of thinking, when you rollback a transaction, returning it to the previous state (before the transaction began) is the desired outcome. So restoring the database after a backup is like rolling back a transaction. They both return the database to the state prior to the respective action.I see what you are saying though because if the database is live a restore would lose all transactions from all users that occurred after the backup.But if the database was in single-user mode during a maintenance window or something than the effect of a restore is the same as rolling back a transaction. And this is one situation I can think of where a DBA may want to "rollback" a backup. Something went wrong during maintenance and he or she wants to start over. But since you can't rollback a backup the next best option is to restore. In this case, trying to rollback by deleting the backup file would be disastrous for the DBA.

RE: backup database

cengland0 — Wed, 22 Sep 2010 07:56:32 GMT

[quote][b]Dave62 (9/22/2010)[/b][hr]I think the proper way to "rollback" a backup is to do a restore. ;-)[/quote]No, because that will bring the database back to a previous state. The way to rollback the backup would be to delete the file that was created.

RE: backup database

sknox — Wed, 22 Sep 2010 07:20:41 GMT

[quote][b]Toreador (9/22/2010)[/b][hr]...I'm not sure what the question was supposed to teach us though - would anybody really try doing a backup inside a transaction?![/quote]Perhaps not intentionally, but indirectly: how many of you know what all of the stored procs on your systems actually do?I can think of no logical reason* for someone to include a backup within a stored proc that does something else, but if they did, and you didn't know it, and called the proc from within a transaction...*And since there's no logical reason to do it, it almost certainly has been done.

RE: backup database

Dave62 — Wed, 22 Sep 2010 07:05:23 GMT

I think the proper way to "rollback" a backup is to do a restore. ;-)

RE: backup database

Daniel Bowlin — Wed, 22 Sep 2010 06:59:53 GMT

RE: backup database

William Vach — Wed, 22 Sep 2010 06:27:46 GMT

I learned something. I thought that the backup would be OK, but the there would be no history for the backup in msdb.Good question.

RE: backup database

cengland0 — Wed, 22 Sep 2010 04:46:06 GMT

[quote][b]Toreador (9/22/2010)[/b][hr]I guessed wrongly that because it makes no sense to do a backup within a transactiobn, then the transaction would be ignored and it would work.I'm not sure what the question was supposed to teach us though - would anybody really try doing a backup inside a transaction?![/quote]Woo hoo, I finally got one right.Believe it or not, a simple Google search reveals people did try to run a backup within a transaction:http://www.sqlnewsgroups.net/sqlserver/t10622-commit-rollback.aspxWhat were they thinking?

RE: backup database

Open Minded — Wed, 22 Sep 2010 04:26:59 GMT

I thought this was on error then rollback. I remember you could test a backup without anything really happening, just a test to see if the wirings, pipes and screws are correct.

RE: backup database

paul.knibbs — Wed, 22 Sep 2010 03:07:51 GMT

Had to read through the BACKUP item in Books Online to be sure what would happen here, because it certainly makes no sense to put a backup inside a transaction and I had no idea what SQL would actually do if you tried!

RE: backup database

Toreador — Wed, 22 Sep 2010 02:33:19 GMT

I guessed wrongly that because it makes no sense to do a backup within a transactiobn, then the transaction would be ignored and it would work.I'm not sure what the question was supposed to teach us though - would anybody really try doing a backup inside a transaction?!

RE: backup database

Carlo Romagnano — Wed, 22 Sep 2010 00:58:01 GMT

Some operations have no sense inside a transaction. I surprise to see so many wrong answers.

RE: backup database

UMG Developer — Tue, 21 Sep 2010 22:30:08 GMT

Nice question thanks!

backup database

VM-723206 — Tue, 21 Sep 2010 22:29:37 GMT

Comments posted to this topic are about the item [B]backup database[/B]