SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

jat421
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)

USA
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.



CODO ERGO SUM
Go to Top of Page

jat421
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

Hommer
Aged Yak Warrior

786 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.

quote:
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

tkizer
Almighty SQL Goddess

USA
36845 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
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
794 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,

Jeff
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2046 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 http://www.sqlserver-dba.com/2011/01/sql-server-faster-restores-with-instant-file-initialisation-.html . Under certain circumstances this can speed up the Restore

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.06 seconds. Powered By: Snitz Forums 2000