| Author |
 Topic  |
|
|
jat421
Starting Member
2 Posts |
 Posted - 02/04/2013 : 09:16:54
|
Hi, we have a one DB MSSQL 2005 server that does a full backup every night and then I have a script that copies that backup file over to our reporting server MSSQL 2008 and do a restore.
It takes about 7 hours to do the restore. Now my question is from the full .bak file can I do a differential restore? If yes, any links would be appreciated!.
Our policy does not allow us to do a differential backup so that it out of the question. |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 02/04/2013 : 10:52:07
|
You cannot do a differential restore from a full backup.
You cannot do a differential restore to any database that has already been brought online.
CODO ERGO SUM |
 |
|
|
jat421
Starting Member
2 Posts |
Posted - 02/05/2013 : 08:00:13
|
| Thanks would you have any other suggestions to make this process faster? as of now it takes about 7 hours for the whole process. We tried replication but that put too much load on the DB server and slowed down everyone. |
|
|
|
Hommer
Aged Yak Warrior
693 Posts |
Posted - 02/05/2013 : 10:12:09
|
That policy doesn't make sense. They let you run full but not diff,
because diff will discriminating changed data from unchanged?
Seriously, you just made your case that you need to have a diff.
Other possibility I can think of is asynchronous database mirroring. I haven’t seen one between 2008 and 2005. You may need to do some research, or someone here can give you a better answer.
quote:
Originally posted by jat421
...
Our policy does not allow us to do a differential backup so that it out of the question.
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
jeffw8713
Aged Yak Warrior
USA
694 Posts |
Posted - 02/06/2013 : 13:59:27
|
As soon as you bring the database online on the reporting server - your only option to refresh is to restore a full backup again. Because differential backups are tied to a specific full backup - you would need to use that full backup for the restore, then use the differential and any transaction log backups to bring the reporting database up to date.
There are several ways you can approach this process to make it faster...
1) Database Mirroring - Database Snapshots
If you can setup database mirroring and build database snapshots (Enterprise Edition only - I believe), then all you have to worry about is how often you create the snapshot to provide current up-to-date data for reporting.
2) SAN mirroring of backup drive
If your SAN guys can setup a mirrored backup drive, you can present a snapshot of that backup drive to the reporting database server and restore from that drive. This will save you the time of actually copying the backup files across the network. I use this technique currently on one of my systems.
3) Cross-over cables
If you can create a secondary network between the 2 servers using a cross-over cable, you can then restore directly from the other server over that network. This will reduce the time it takes to copy the files and be almost as fast as restoring from local drives. I use this technique on a couple of other systems I support.
4) Other Options
There are definitely other options and approaches available. Backing up across the network and using a private backup network also works - although that will be slower on the restores it may be faster overall.
Good luck,
Jeff
|
|
|
|
jackv
Flowing Fount of Yak Knowledge
United Kingdom
1770 Posts |
|
| |
Topic |
|
Restore a Backup
