patternsqlMinor
Standard ways to return SQL Server database to a specific state
Viewed 0 times
returnsqlwaysstandarddatabasestatespecificserver
Problem
What are the most efficient ways to save the exact state of a SQL server database and then be able to bring it back to that state at will?
I am testing code performance against a database and each test alters it so I would like to be able to (as quickly and easily as possible) return it back to its beginning state each time I want to test performance again. I expect to be doing a lot of this.
I am testing code performance against a database and each test alters it so I would like to be able to (as quickly and easily as possible) return it back to its beginning state each time I want to test performance again. I expect to be doing a lot of this.
Solution
To be honest, a full backup will be the easiest and quickest way to do that. Simply backup your database (a full backup) to the point you want to revert to, and then when you need to get back to that point, do a full restore.
Also, depending on your version and edition of SQL Server (currently not specified in your question), you could work with Database Snapshots, and use a snapshot to revert back to.
The above two solutions are quick and easy if you're wanting to revert the entire database back to a certain point (either at the full backup, or the snapshot). But if you're only looking to revert a subset of your database back to a certain point in time (think: certain types of development, benchmarking, or demo'ing) then the easiest thing would probably be a reversion script to target your specified affected objects.
With a tiny database that you want to revert the entire thing back, the first two options would be quickest. But if you are dealing with a relatively large database, where a restore is just not working for you timewise, then you could also consider a script to do the revert. It all depends on what you're trying to do, how big the database is, and what you're willing to handle based on the clock vs. trouble of reverting.
Also, depending on your version and edition of SQL Server (currently not specified in your question), you could work with Database Snapshots, and use a snapshot to revert back to.
The above two solutions are quick and easy if you're wanting to revert the entire database back to a certain point (either at the full backup, or the snapshot). But if you're only looking to revert a subset of your database back to a certain point in time (think: certain types of development, benchmarking, or demo'ing) then the easiest thing would probably be a reversion script to target your specified affected objects.
With a tiny database that you want to revert the entire thing back, the first two options would be quickest. But if you are dealing with a relatively large database, where a restore is just not working for you timewise, then you could also consider a script to do the revert. It all depends on what you're trying to do, how big the database is, and what you're willing to handle based on the clock vs. trouble of reverting.
Context
StackExchange Database Administrators Q#56089, answer score: 4
Revisions (0)
No revisions yet.