2

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.

Improve this question

2 Answers 2

Reset to default
1

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:

restore database [yourDB] from disk = 'backup_file.bak' with norecovery, file=1
restore database [yourDB] from disk = 'backup_file.bak' with recovery, file=2

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.

Improve this answer
0

Differential backups are cummulative. So, it's last full backup, plus last diff, and you're set. The problem is that if you have diff, but don't have the full backup on which the diff depends, you can't restore it. Anyone going to the datacenter with an external disk taking backup will break your story (unless he takes the backup with copy_only).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.