September 22, 2010 at 8:38 am
sknox (9/22/2010)
... --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.
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.
September 22, 2010 at 8:42 am
Dave62 (9/22/2010)
sknox (9/22/2010)
... --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.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.
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.
September 22, 2010 at 9:02 am
sknox (9/22/2010)
Dave62 (9/22/2010)
sknox (9/22/2010)
... --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.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.
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.
Thanks, I did learn something useful today. The definition of pedantry.
September 22, 2010 at 10:22 am
Nice question. Although I can't see why anyone would try to do this. :hehe:
September 22, 2010 at 11:15 am
Thanks for the question
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 22, 2010 at 11:46 am
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.
September 22, 2010 at 10:14 pm
Great question!!!
Raunak J
September 23, 2010 at 1:29 am
Nice question, I got it right 🙂
- SAMJI
If you marry one they will fight with you, If you marry 2 they will fight for you 🙂
August 12, 2011 at 12:35 am
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 database
But the uncommitted changes would't be in the backup anyway, so the feature wouldn't work anyway.
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply