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.