November 12, 2008 at 4:44 am
Hello everyone,
I have the following scenario:
* A full backup from a production environment database was taken and restored to a test lab development environment.
* The database in the development environment has been changed with new columns added to the tables.
* Users continue to work with the production database and add transactions.
* The production database is being backed up nightly, and a transaction log backup is taken at noon.
* The development and production environments are physically isolated.
What I would like to do is to take the database from the development environment, restore it to the production environment, and then add the transactions that have happened since the development effort started.
The way I am doing this is as follows: I simply take a manual backup of the development database, and restore that. There is a maintenance plan in the production environment that automatically backs up the transaction logs, so I take the most recent backup of that and attempt to restore it. The following error message appears when I attempt to restore the transaction log:
The log in this backup set terminates at LSN 9524000000621400001, which is too early to apply to the database. A more recent log backup that includes LSN 9529000000735800001 can be restored.
The T-SQL query that I'm writing is as follows:
RESTORE DATABASE YSCPersonnel
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\db-11-11-2008.bak'
WITH NORECOVERY, REPLACE;
RESTORE LOG YSCPersonnel
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\db_backup_200811121200.trn'
WITH RECOVERY, REPLACE;
Any ideas what I might be doing wrong?
November 12, 2008 at 5:39 am
You cannot add transaction log backups to a full backup of another database!!!
Why can't you simply script the schema changes you've made in development and deploy those scripts to production. That's how almost everyone else is doing it.
[font="Verdana"]Markus Bohse[/font]
November 12, 2008 at 5:43 am
This cannot be done. the development version and production version of the database have had separate, different updates made to them. therefore their LSNs will be out of synch.
You are going to have to manually reapply the schema changes you made in development to the production database, rather than do this via a restore.
---------------------------------------------------------------------
November 12, 2008 at 6:29 am
Thanks for the tip. I'm not a DBA so I don't know exactly what I can and can't do with the databases, and I'm being asked to do these things in a hurry. You've potentially managed to save me even more grief.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply