3

I have two MS SQL 2005 servers, one for production and one for test and both have a Recovery Model of Full. I restore a backup of the production database to the test server and then have users make changes.

I want to be able to:

  • Roll back all the changes made to the test SQL server
  • Apply all the transactions that have occurred on the production SQL server since the test server was originally restored so that the two servers have the same data

I do not want to do a full database restore from backup file as this takes far too long with our +200GB database especially when all the changed data is less than 1GB.

EDIT

Based on the suggestions below I have tried restoring a database with NoRecovery but you cannot create a snapshot of a database that is in that state.

I have also tried restoring it to Standby Read only mode which works and I can take a snapshot of the database then and still apply transaction logs to the original db but I cannot make the database writable again as long as there are snapshots against it.

Running:

restore database TestDB with recovery

Results in the following error:

Msg 5094, Level 16, State 2, Line 1 The operation cannot be performed on a database with database snapshots or active DBCC replicas
Chris Magnuson
  • 5,780
  • 7
  • 34
  • 37

6 Answers6

4

First off, once you've restored the backup and set the database to "recovered", that's it -- you will never be able to apply another transaction log backup to it.

However, there are database snapshots. I've never used them, but I believe you could use them for this purpose. I think you need to restore the database, leave it in "not restored" mode -- definitly not standby -- and then generate snapshots based on that. (Or was that mirroring? I read about this stuff years ago, but never had reason to use it.)

Then when you want to update the database, you drop the snapshot, restore the "next" set of transaction log backups, and create a fresh snapshot.

However, I don't think this would work very well. Above and beyond the management and maintenance overhead of doing this, if the testers/developers do a lot of modifications, your database snapshot could get very big, even bigger than the original database -- and that's hard drive space used in addition to the "original" database. For infrequently modified databases this could work, but for large OLTP systems, I have serious doubts.

Philip Kelley
  • 39,426
  • 11
  • 57
  • 92
  • I would be curious if someone can confirm that a strategy like this would actually work. Can you even create a snapshot before you set the database to recovered? Snapshots are also only available in the Enterprise version of MS SQL and currently we are using Standard edition though for the test server it's under MSDN so I could convert it if what your proposing works. Can you give me specific detail about what steps I would need to take to restore a database without putting it into recovered mode and what I need to then be able to apply additional transaction logs to it? – Chris Magnuson Aug 29 '09 at 11:30
  • One of the WITH clause settings of the RECOVER command is: Recovery, NoRecovery, and Standby. Recovery is the "set it online" switch; "NoRecover" leaves it offline, waiting for the next restore command (generally the next t-log file), and Standby is like NoRecovery but the DB is also in read-only mode. Check out http://msdn.microsoft.com/en-us/library/ms186858.aspx for the BOL entry, and http://articles.techrepublic.com.com/5100-10878_11-1041267.html for friendlier explanation. – Philip Kelley Aug 31 '09 at 13:52
  • As for figuring out if the norecovery/snapshot idea works, no amount of online explanation can match an hour or two of hands-on experimentation. Get a backup of (or create) a small database, and mess around with restores, snapshots, and so forth. The article at http://www.simple-talk.com/sql/database-administration/sql-server-2005-snapshots/ discusses a lot about snapshots, and towards the bottom mentions my idea (snapshot on a log-shipped NoRecovery database) - maybe this is where I first read it? – Philip Kelley Aug 31 '09 at 14:02
  • No, it won't work on Standard edition -- but it will work on Developer edition, which is the environment you're dealing with. The danger there is that developers might start using all the wifty Enterprise-only featrures, requiring an $$upgrade$$, just like MS planned. – Philip Kelley Aug 31 '09 at 14:04
  • And one last question. You get a copy of Production data, implement it in Development as you described... and ecah week, each month, the data gets older and older. At what point will the Production data no longer be "good enough" to develop against? How often will you have to get and restore a fresh copy of Production data? – Philip Kelley Aug 31 '09 at 14:05
  • Our data is typically too stale to use after about 2 weeks +/- 1 week depending on the project. If we could get a fast and reliable way to restore the database without having to do a full restore we would have it automatically done weekly with the ability for a developer to manually initiate it if he needs newer data for some reason. Just FYI this database is for Microsoft Dynamics NAV (Navision 4.0). – Chris Magnuson Aug 31 '09 at 14:34
  • I have tested your suggestions but still cannot accomplish the task. I restored a database with NoRecovery but I cannot make a snapshot of it in that state. If I restore it into Standby mode it is read only but allows me to apply transaction logs to it and I can even make a snapshot of the DB. The problem is I cannot take it out of read only mode (restore database TestDB with recovery) as it gives me the following error, Msg 5094, Level 16, State 2, Line 1 The operation cannot be performed on a database with database snapshots or active DBCC replicas. Am I missing something? – Chris Magnuson Sep 05 '09 at 21:24
  • Sounds like you have to drop/eradicate the snapshot before you can apply the next t-log restore. – Philip Kelley Sep 08 '09 at 13:38
  • Which defeats the purpose since then I have no way of reverting back to a state where I can apply transaction logs from a state where the database is usable for testing. Am I missing something? I am not seeing anything in what you have recommended that can be used as an answer for the question I asked. – Chris Magnuson Sep 09 '09 at 12:54
  • My thoughts were that something like this would work [not sure if spacing will show properly in the comment]: - Backup database - Restore database into Standby mode (call it database "X") - Create database snapshot "X1" - Develop against X1 - Drop snapshot X1 - Restore subsequent t-log backups to "X" - Create database snapshot "X2" - Develop against X2 - Drop snapshot X2 - Restore further t-log backups to "X" - Etc. I believe that this methodology will work, but I have not tested it. I thought it would apply to your situation, but it might not. – Philip Kelley Sep 09 '09 at 17:01
2

So what you really want is a copy of Production to be made in Test. First, you must have a current backup of production somewhere??. Usually on a database this size full backups are made Sunday nights and then differential backups are made each night during the week.

Take the Sunday backup copy and restore it as a different database name on your server, say TestRestore. You should be able to kick this off at 5:00 pm and it should take about 10 hours. If it takes a lot longer see Optimizing Backup and Restore Performance in SQL Server.

When you get in in the morning restore the last differential backup from the previous night, this shouldn't take long at all.

Then kick the users off the Test database and rename Test to TestOld (someone will need something), then rename your TestRestore database to be the Test database. See How to rename a SQL Server Database.

The long range solution is to do log shipping from Production to TestRestore. The at a moments notice you can rename things and have a fresh Test database.

JBrooks
  • 9,901
  • 2
  • 28
  • 32
  • I understand that your trying to answer the question you think I am asking instead of the one that I actually asked but you have not provided any way of accomplishing what I outlined in the question. The point is to avoid performing a full database restore more than once for a server that is going to have production data refreshed on it multiple times. Log shipping will also not solve this problem as that would require that you leave the database in either NORECOVERY or STANDBY which makes the database unusable for testing or development. http://msdn.microsoft.com/en-us/library/ms187103.aspx – Chris Magnuson Sep 09 '09 at 12:52
  • 1
    What you outlined is not possible in SQL Server. I was saying that your Test database would be like now - normal and usable. Sitting next to it would be a new database named TestRestore receiving the logs from prod. At a moments notice you could drop Test and rename TestRestore to Test, create a new TestRestore and continue the process. This gets around the time it takes to restore such a large database. – JBrooks Sep 09 '09 at 18:24
1

For the rollback, the easiest way is probably using a virtual machine and not saving changes when you close it. For copying changes across from the production to the test, could you restore the differential backups or transaction log backups from production to the test db?

MartW
  • 12,348
  • 3
  • 44
  • 68
0

After having tried all of the suggestions offered here I have not found any means of accomplishing what I outlined in the question through SQL. If someone can find a way and post it or has another suggestion I would be happy to try something else but at this point there appears to be no way to accomplish this.

Chris Magnuson
  • 5,780
  • 7
  • 34
  • 37
0

Storage vendors (as netapp) provide the ability to have writeable snapshots. It gives you the ability to create a snapshot within seconds on the production, do your tests, and drop/recreate the snapshot. It's a long term solution, but... It works

user3205737
  • 1
0

On Server1, a job exists that compresses the latest full backup On Server2, there's a job that performs the following steps: Copies the compressed file to a local drive Decompresses the file to make the full backup available Kills all sessions to the database that is about to be restored Restores the database Sets the recovery model to Simple Grants db_owner privileges to the developers

Ref:http://weblogs.sqlteam.com/tarad/archive/2009/02/25/How-to-refresh-a-SQL-Server-database-automatically.aspx

Zackyrosez
  • 1