Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Restore a Backup
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

2 Posts

Posted - 02/04/2013 :  09:16:54  Show Profile  Reply with Quote
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 - 02/04/2013 :  10:52:07  Show Profile  Reply with Quote
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.

Go to Top of Page

Starting Member

2 Posts

Posted - 02/05/2013 :  08:00:13  Show Profile  Reply with Quote
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.
Go to Top of Page

Aged Yak Warrior

808 Posts

Posted - 02/05/2013 :  10:12:09  Show Profile  Reply with Quote
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.

Originally posted by jat421

Our policy does not allow us to do a differential backup so that it out of the question.

Go to Top of Page

Almighty SQL Goddess

38200 Posts

Posted - 02/05/2013 :  16:28:11  Show Profile  Visit tkizer's Homepage  Reply with Quote
Can you purchase a third party backup compression tool? We used Litespeed in the past but switched over to Red Gate for cost reasons. We are using native compression for 2008+ though.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server

Subscribe to my blog
Go to Top of Page

Aged Yak Warrior

819 Posts

Posted - 02/06/2013 :  13:59:27  Show Profile  Reply with Quote
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,

Go to Top of Page

Flowing Fount of Yak Knowledge

United Kingdom
2179 Posts

Posted - 02/07/2013 :  01:49:58  Show Profile  Visit jackv's Homepage  Reply with Quote
the suggestions outlined above are all worth pursuing. Have you analysed if there is a contention issues on the Restore itself. For example, how are you restoring ? Do you have instant file initialization set up - read more on . Under certain circumstances this can speed up the Restore

Jack Vamvas
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000