I am wondering if anyone can provide information on taking a differential backup and then restoring that differential backup to another SQL Server database?
I have a production environment that is connected to a SQLVault backup system that takes nightly backups. I also have a development environment that is not connected to the SQLVault backup system.
The production environment is in a remote datacenter. The development environment is hosted locally. The WAN linked between the datacenter and the local office is slow, when I try to copy over the 400 GB of backup to my development server.
Currently I need to take a laptop with external HDD to the datacenter and execute the backup process. Then pickup the laptop and external HDD and bring them back to the office. Followed by executing the restore of the backup to the development server.
I would like to be able to perform a nightly differential backup and ship that across the WAN and then restore it on top of the previous full backup. So, when I want to bring my development environment into sync with my production environment…I execute the full restore with differentials from the previous several days/weeks.
I tried taking a full backup yesterday. I then took a differential backup today (appending it to the backup I took yesterday). I tried to restore both, but get an invalid state error message when it moved on the second backup file.
I have also looked into log shipping, but that looks like it is configured more for a standby server and not another environment.
Thanks
Jlimited.
Best Answer
Whenever you restore a backup file with the intention to restore more backup files, you need to include
with norecovery. In your scenario, it would look something like:Note, once you run a restore
with recovery, no further restores are possible. So, in your scenario where you want to restore from production every day, you'd need to restore the full backup every time. Not that that's a bad thing; I do it in my shop and it seems to work out alright.