December 5, 2013 at 2:17 pm
I have a question about backup and restore chain.
We do a full backup every sunday, and daily differential, and 3 hour transaction log backup on both Producaiton and test enviroment.
Now I have a script written to restore some databases from production to test server on every Wedenday night. The script is doing this: copies some production dbs backups from sunday backup to test server, then restore them to test server. then delete those copied over backup files.
My question is :
Do I need to backup the databases on test server after they are restored?
My current process is not doing that.
For I assume if I really want to restore the database on test server from test server backups, I can use test server full + differential + transactions? is that right?
Basically I assume the testing server backup chain is not broken after I restored the database from production.
December 5, 2013 at 2:56 pm
That is great that you are testing your backups by restoring them. I believe that your question is this: If I restore a backup (full + diff + tlogs) do I break the backup chain? The answer is no, but if you restore with recovery you won't be able to apply any additional backups. Is there an issue that you are trying to solve or just curious?
December 5, 2013 at 3:40 pm
Thanks, let me redescribe some of the info. Note I deleted the copied over backup files from production to test after restore.
We do backup on both test server and production.
After we restore a backup from prodution to test server, do I need to do a full backup right after the restore so that the backup chain on testing server can be continued?
or I don't need to do so, the backup chain will be continued, on differential or transaction log back up.
For example in the case if one day later there is something wrong on the testing database, and I need to do the restore from testing server backups. Is the original sunday full backup of testing server still can be used or not?
Thanks,
December 5, 2013 at 9:03 pm
No it cannot be used, so better take a backup once it is restored.
If you restore your old backup (without recovery) it will allow you to restore the diff and tlog of the same continuity only.
Regards
Durai Nagarajan
December 5, 2013 at 9:46 pm
sqlfriends (12/5/2013)
Thanks, let me redescribe some of the info. Note I deleted the copied over backup files from production to test after restore.We do backup on both test server and production.
After we restore a backup from prodution to test server, do I need to do a full backup right after the restore so that the backup chain on testing server can be continued?
or I don't need to do so, the backup chain will be continued, on differential or transaction log back up.
For example in the case if one day later there is something wrong on the testing database, and I need to do the restore from testing server backups. Is the original sunday full backup of testing server still can be used or not?
You don't need to take a full on the test server. If you wanted to restore your Sunday full from prod you could start to take tlog backups on the dev server after the restore without having to take a full. So, if something goes wrong you simply restore your full from Sunday (prod) and apply all tlogs (dev) to get it back to a point in time.
The gist: The restore doesn't break the backup chain and you can take tlog backups on your dev server without having to take a full first.
I hope that answers your question.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply