| Author |
Topic |
|
DeepSeed
Yak Posting Veteran
52 Posts |
 Posted - 2010-06-02 : 16:51:15
|
| Hello,In my testing environment I would like to set a sort of snapshot on the database and then half hour or 10 minutes later be able to roll back to that snapshot.With large databases backing up and restoring takes far too long. Anyone have any ideas? |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-06-02 : 16:59:17
|
| Create a database snapshot and restore from the snapshot.You have to have Enterprise or Developer Edition and SQL 2005 or 2008 to use database snapshots.CODO ERGO SUM |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
DeepSeed
Yak Posting Veteran
52 Posts |
Posted - 2010-06-02 : 17:35:51
|
| I am using standard edition. I have enterprise and dev but don't like to test on it because our customers use standard and there is huge differences in performance.I just want to erase all my changes as quickly as possible. Whatever the best method is. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-02 : 18:09:43
|
Copy the MDF / LDF any faster?Detach, copy, re-attach. To restore detach and re-attach the copy made earlier (perhaps with a RENAME back to the original name first).Probably, if the DB is large, then the COPY is going to be slow.Personally I treat QA as distinct from DEV, so I wouldn't be bothered to be using different [performance-wise] version of SQL in DEV, as QA would be using exact hardware / loading that Production would use. Your situation may be different though  |
|
|
|
DeepSeed
Yak Posting Veteran
52 Posts |
Posted - 2010-06-02 : 18:26:23
|
| It is an unknown amount of data from virtual users. I think copy would still be too long but might be faster.I am on an exact hardware / configuration that we use in production.The problem is when I need to test 50, 100, 200, 300, 500, 1000 users from the same set of 1000 users creating something on their account I need to erase that data after each test.I can run each test in around 5 - 10 minutes, so I can get all my data in around 1 - 2 hours. With database restores it can take all day depending on the size of the database.I could have a script prep 10 copies of my database overnight but then if I need to make any changes to the database before running the test I need to make it in every copy, so it becomes more of a hassle than it is worth. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-02 : 18:50:12
|
| If you will start from the same point for each test, and rollback to the same point, then I think COPY could work.DetachCopy detached files to "MASTER" filenamesRe-AttachRun a batch file (say) to copy MASTER filenames to TEMP filenameswhilst that copy is running make your testsWhen done the COPY will have finished too (hopefully!)Detach databaseDelete detached filesRename TEMP files to normal filenamesAttach databaseRun the batch file to copy MASTER to TEMP (again)and run the next testRepeat! |
|
|
|
DeepSeed
Yak Posting Veteran
52 Posts |
Posted - 2010-06-02 : 19:44:42
|
| That might work, then I am using up resources while I am running the load test though. Don't know what kind of impact it would have.SQL 2008 costs enough, shouldn't it come with the best backup and restore software :(I was just thinking if it only takes SQL about 3 minutes of CPU time to put all that new data in, why can't it erase it in 3 minutes or less? Similar to how you can perform a single transaction and then rollback, except with millions of transactions!! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-06-02 : 20:15:38
|
quote:
Originally posted by DeepSeed
I am using standard edition. I have enterprise and dev but don't like to test on it because our customers use standard and there is huge differences in performance.I just want to erase all my changes as quickly as possible. Whatever the best method is.
You need to decide what you really want to test, functionality or performance.If you want to quickly test functionality, database snapshots will be the fastest way to restore back to the start point. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-06-03 : 08:02:24
|
| lateral thinking.build multiple "spare" copies of your base database.use db1. run a test phase. review outputs.switch to next spare database - db2. run a test phase,etcin mean time, dump db1 or replace db2 with new copy offline.switch to db3 ,etc and repeat. switching DB's should be fastest option going for you. |
|
|
|
DeepSeed
Yak Posting Veteran
52 Posts |
Posted - 2010-06-03 : 11:53:01
|
| Yea the problem is I need to make quick changes, so then I have to make changes to all the temp databases which is more work than it is worth.My Functional test server is not the issue, it is all automated and all the users delete any information they add between each test. Easy to do with one user at a time.In Load Test I need to push the system until it breaks which leaves dirty data, I also need to test mass data generation without deletion to isolate it. I think I will just keep doing it the way I am right now. I really need a way to take snapshots and revert back more than anything, I don't have a base database that is static for much more than 4 hours, the prep changes for each new performance test. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-06-03 : 12:15:36
|
quote:
Yea the problem is I need to make quick changes, so then I have to make changes to all the temp databases which is more work than it is worth.
Not really, if all the changes are scripted out...which they should be if your tests must be consistent. And you'd really only have to run the script if you move to the next database. |
|
|
|
DeepSeed
Yak Posting Veteran
52 Posts |
Posted - 2010-06-03 : 12:25:49
|
| They are done through a web front end, like for example having 1000 users go in and set something, it takes a lot of time for them all to go in and finish, since I can't ask them all to do it at once without taking down the server. |
|
|
|
|
How to rollback the database faster than restoring