Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jat421
Starting Member
2 Posts |
 Posted - 2013-02-04 : 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)
7020 Posts |
Posted - 2013-02-04 : 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 - 2013-02-05 : 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
808 Posts |
Posted - 2013-02-05 : 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
38200 Posts |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2013-02-06 : 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 SnapshotsIf 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 driveIf 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 cablesIf 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 OptionsThere 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
Master Smack Fu Yak Hacker
2179 Posts |
|
|
|
|
|
|
|
Restore a Backup