How to upgrade a test instance of SQL Server with production data without using full backups

Question

I have two MS SQL 2005 servers, one for production and one for testing, and both have a Full recovery model. I restore a backup of the production database on the test server, and then the user makes the changes.

I want to be able to:

• Discard all changes made to the test SQL server.
• Apply all transactions that occurred on the production SQL server since the test server was initially restored so that the two servers had the same data.

I do not want to do a full recovery of the database from the backup file, since it takes too much time with our + 200 GB database, especially when all the changed data is less than 1 GB.

EDIT

Based on the suggestions below, I tried to restore the database using NoRecovery, but you cannot create a snapshot of the database in this state.

I also tried restoring it in Standby Read only mode, which works, and I can take a snapshot of the database and then apply transactional logs to the original db, but I can not make the database writable again while there are snapshots against This.

Duration:

restore database TestDB with recovery 

Results with 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 

Answer 1

After you tried all the suggestions here, I did not find the means to accomplish what I presented in the question using SQL. If someone can find a way and post it or there is another suggestion, I would be happy to try something else, but at the moment, it seems that this cannot be.

Answer 2

First, as soon as you restore the backup and set the database to “restored”, this means that you can never apply another transaction log backup to it.

However, there are database snapshots. I have never used them, but I believe that you can use them for this purpose. I think you need to restore the database, leave it in "not restored" mode - definitely not in standby mode - and then create snapshots on it. (Or is it a mirror? I read about it many years ago, but I never had a reason to use it.)

Then, when you want to update the database, you delete the snapshot, restore the “next” set of transaction log backups, and create a new snapshot.

However, I do not think this will work very well. Higher and higher management and maintenance costs in this case, if testers / developers make many changes, your database snapshot can become very large, even larger than the original database, and the hard disk space used in addition to the "original" Database. For rarely modified databases, this might work, but for large OLTP systems, I have serious doubts.

Answer 3

So what you really want is a copy of Production to be made in Test. First, should you have a current backup of your product somewhere ?. Typically in a database, this full backup size is made on Sunday evening, and then differential backups are made every night for a week.

Take a backup of Sunday and restore it as another database name on your server, say TestRestore. You should be able to drop it at 17:00, and it will take about 10 hours. If this takes a long time, see Optimizing Backup and Restoring Performance in SQL Server.

When you arrive in the morning to restore the last differential backup from the previous night, it will not take much time.

Then remove the users from the Test database and rename Test to TestOld (someone will need something), and then rename the TestRestore database to the Test database. See How to Rename a SQL Server Database.

The decision about the long range is to do the delivery of logs from Production to TestRestore. In an instant, you can rename things and have a new test database.

Answer 4

For rollback, the easiest way is to use a virtual machine and not save changes when it is closed. To copy the changes between production and test, could you restore differential backups or transaction log backups from production to test db?

Answer 5

Storage providers (like netapp) provide the ability to have recordable snapshots. This gives you the ability to create a snapshot in a few seconds at the factory, run your tests and reduce / recreate the snapshot. This is a long-term solution, but ... It works

Answer 6

There is work on Server1 that compresses the last full backup. On Server2, there is a task that performs the following steps: Copy the compressed file to the local disk Decompresses the file to make the full backup available Kills all sessions in the database that must be restored Restores database Sets recovery model to Simple Gives db_owner privileges to developers

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