differential backup 2005 advice

Morning.
Im running a FULL BACKUP in overwrite mode daily at 3am.
Im running a DIFFERENTAL BACKUP in overwrite mode hourly.
I cannot lose more than an hours data.
SQL 2005 Standard.
Backup Timeline:
3am Full
4am Differential
5am Overwrites 4am Differental.
etc ..
So if i restore the 5am Differential have i lost the 3am to 4am data ? (does
it let me do this ?)
If this is the case should I create a Differential overwrite as a separeate
file every hour ? (cannot append as data too large).
Example Timeline:
3am full
4am Differental4am.bak
5am Differental5am.bak
So i need to restore to 5am i need to
1. restore 3am full
2. restore Differential4am.bak
3. restore Differantal5am.bak
Is this correct ?
Thanks for any advice
Scott
Scott
BOL says
"A differential database backup records only the data that has changed since
the last database backup. You can make more frequent backups because
differential database backups are smaller and faster than database backups.
Making frequent backups decreases your risk of losing data."
Test your DDR
create database test
GO
create table test..test(id int identity)
insert test..test default values
backup database test to disk = 'c:\db.bak' WITH INIT
insert test..test default values
backup database test to disk = 'c:\db_diff1.bak' WITH DIFFERENTIAL
insert test..test default values
backup database test to disk = 'c:\db_diff2.bak' WITH DIFFERENTIAL
GO
RESTORE DATABASE test FROM disk = 'C:\db.bak' WITH FILE = 1, norecovery
--RESTORE DATABASE test FROM disk = 'c:\db_diff1.bak' WITH FILE = 1,
recovery
RESTORE DATABASE test FROM disk = 'c:\db_diff2.bak' WITH FILE = 1, recovery
select * from test..test
DROP Database test
"Scott" <scott_lotus@.yahoo.co.uk> wrote in message
news:extosaChIHA.748@.TK2MSFTNGP04.phx.gbl...
> Morning.
> Im running a FULL BACKUP in overwrite mode daily at 3am.
> Im running a DIFFERENTAL BACKUP in overwrite mode hourly.
> I cannot lose more than an hours data.
> SQL 2005 Standard.
>
> Backup Timeline:
> 3am Full
> 4am Differential
> 5am Overwrites 4am Differental.
> etc ..
> So if i restore the 5am Differential have i lost the 3am to 4am data ?
> (does it let me do this ?)
> If this is the case should I create a Differential overwrite as a
> separeate file every hour ? (cannot append as data too large).
> Example Timeline:
> 3am full
> 4am Differental4am.bak
> 5am Differental5am.bak
> So i need to restore to 5am i need to
> 1. restore 3am full
> 2. restore Differential4am.bak
> 3. restore Differantal5am.bak
> Is this correct ?
> Thanks for any advice
> Scott
>
|||ah i see ! : )
many thanks for the reply and the rename tip.
so thats the key, differential is changes since last full ... didnt know
that. All makes sense now.
Whats "BOL" ?
all the best
scott
|||: ) brillant , thank you very much everyone.
Scott
|||I just thought that you might be interested in a stored procedure for doing
backups that supports creation of backup files with date and time in the file
name, verification of backups as well as deletion of old backup files. It is
available on http://ola.hallengren.com.
Ola Hallengren
"Scott" wrote:

> : ) brillant , thank you very much everyone.
> Scott
>
>