December 6, 2016 at 10:00 am
Hello,
I need to be able to test something (which will change some data on the big databases) and after revert it back to the normal state.
What is better option ?
Backup/restore
Snapshot
Thank you,
Pedro Ribeiro
December 6, 2016 at 2:30 pm
river1 (12/6/2016)
Hello,I need to be able to test something (which will change some data on the big databases) and after revert it back to the normal state.
What is better option ?
Backup/restore
Snapshot
Thank you,
Pedro Ribeiro
Just how much change are we talking about? Millions of rows? More? Less? If we're talking about a relatively small amount of data, you could make copies of the tables involved prior to the change, and then update your change process to OUTPUT a list of the primary key values for each changed table (into a separate table for each changed table), and then use those keys to do an UPDATE back to the original tables from the copies, using the key values. Judging just how much data you can handle this way is going to be a judgment call that only you and/or your co-workers can make.
However, a couple of things to keep in mind. If this database is constantly being updated with other updates, and within the tables involved, backup and restore are probably out of consideration, as then you'd lose other updates by virtue of the restore, and the only way to avoid that is to keep any application that uses the database down for the duration. Also, if you don't test your updates and restoration processes, you could get yourself in deep doo-doo, so a good time to perform this activity would be immediately following a good backup.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 9, 2016 at 1:08 am
Hello I cannot develop new things. I need to use SQL Server funcionalities.
This is why it is interesting to understand if I should use snapshot or backups to revert back very quick and if possible whitout the need of the SQL Server DBA
Can you help me?
December 9, 2016 at 1:34 am
If you're not the DBA then you shouldn't even have access to create snapshots and backups. Is this a test server? It can't hurt to take a backup before you start (provided nobody else is using differential backups, in which case it might hurt) even if you don't end up restoring from it. If, as Steve mentioned, there will be other updates happening while you're doing your work, then restoring from a backup or reverting to snapshot will mean all those updates will be lost. Why not just take a backup and restore it to another server (subject to whatever constraints you have about using possibly live data for test purposes) and then you can play about with it to your heart's content?
John
December 9, 2016 at 2:16 am
This is a very valid point. If I will have or not someone using the environemnt to test. Probably yes. I am not sure. Let me try to clarify and we will proceed after I get this clear.
December 9, 2016 at 9:34 am
Hello. The answer to the question is: this is a test server and what I would like to accomplish is:
1) make a backup of the database before test starts
2) Start testing (can take 2 or 3 days)
3) Revert back to point 1 (state before test as started)
Because the database is big. probably it makes sense to create a snapshot of the database before they start using and at the end request the snapshop to revert the database to previous state.
What do you think about it?
Also
1) People will be testing while we are doing this tests on the same database but data can be lost with no problem if they whish to revert back to previous state
December 12, 2016 at 2:19 am
I would do both, in that case. Take a backup, take a snapshot. Start testing. Revert to snapshot when finished. If anything goes wrong, you still have the backup to fall back on. Bear in mind that if you make a lot of changes to the database, the snapshot could end up being the same size as the original database.
John
December 13, 2016 at 9:43 am
OK. If I understood well your suggestion is:
1) Keep the regular backup jobs ongoing (one per week the full, once per day the dif. and every hour the logs)
2) Before we start the test, create the snaphot
3) Start testing
4) At the end of the testing, revert back to the original database state using the snapshot (if we wish) or apply the changes using the snapshot (if we prefer).
So backup is just for a security reason, correct? goal is to work with the snapshot, correct?
December 13, 2016 at 9:50 am
In this case, yes. I'd take an extra differential just before you start (ie before creating the snapshot).
You don't apply the changes using the snapshot. If you're happy with the changes and want to keep them, just drop the snapshot.
You do have Enterprise Edition, don't you?
John
December 13, 2016 at 12:14 pm
Hello, thank you for the feedback. Yes it is enterprise edition.
Can I create the snapshot using t-sql instead of using GUI?
What about to re-apply or delete the snapshot? can I also use t-sql?
Thanks,
Pedro
December 13, 2016 at 12:25 pm
It is possible to revert a source database from a database snapshot, but any changes made to the source database in the interim will be lost. Perhaps the best solution would be a DML rollback script; meaning a T-SQL script which reverses the DML modifications made by the first script.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 14, 2016 at 2:13 am
river1 (12/13/2016)
Can I create the snapshot using t-sql instead of using GUI?What about to re-apply or delete the snapshot? can I also use t-sql?
Pedro
Sure you can. Check out the documentation.
John
December 14, 2016 at 3:14 am
Thank you.
I just spoke with a team here and I think we may have a problem....
They say that if I do a snapshop (involves two databases) and then revert back (apply it to the database) the replications will be broken...
December 14, 2016 at 3:20 am
You have replication in your test environment? Please don't tell me it's from a live server? OK, we're back to my original suggestion of taking a backup of the database and restoring it elsewhere.
John
December 14, 2016 at 4:21 am
It is a test server. Yes we have replications. It is not a prod server
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply