SQL Server incremental backup

18 May 2005 6:02 AM

John @ X

Hi there,
I need to take a backup with the following scanerio in mind:
At T1 I take the FULL BACK UP of the database called BACK_1_FULL.BACK in c:\
Then at T2 I would like to take only the differential BACKUP of the
Transaction log called BACK_1_TRANSLOG.BACK and at T3 I would like to take
only the differential BACKUP of the Transaction log called
BACK_2_TRANSLOG.BACK. At T4 my system fails and I need to bring the database
to a point where my T3 state is in. I tried taking the backups in DEVICE but
I always get an error from SQL Server Enterprise manager such as
Volume on Device 'Device2' is not part of a family media set. BACKUP WITH NO
FORMAT can be used to form a new media set. BACKUP LOG is terminating
abnormally.

If someone can give suggestions on how to backup and restore in this
situation it will be really helpful.
Thanks
John

18 May 2005 8:52 AM

Wayne Snyder

First some language stuff..

When you take a log backup it is an Incremental ( not differential
backup).... The only reason I mention this is that there is a real
Differential backup in SQL Server, and others will likely get confused at
the different terms..

When you restore you must first restore the DB Backup, THEN do the Log
backup 1, then restore the log backup 2.. It looks like you are selecting
BOTH log backups at the same time in the Device window.... And that will not
work with your scenarou...Doing multiples in the device window is for a
striped backup, and works only with FULL DB backups, and not log backups.

Do one at a time and see if that fixes your problem...

--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)

I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org

Show quoteHide quote

"John @ X" <Jo***@discussions.microsoft.com> wrote in message
news:F1419D05-90D0-4E64-938B-0B8220AE517F@microsoft.com...
> Hi there,
> I need to take a backup with the following scanerio in mind:
> At T1 I take the FULL BACK UP of the database called BACK_1_FULL.BACK in
> c:\
> Then at T2 I would like to take only the differential BACKUP of the
> Transaction log called BACK_1_TRANSLOG.BACK and at T3 I would like to take
> only the differential BACKUP of the Transaction log called
> BACK_2_TRANSLOG.BACK. At T4 my system fails and I need to bring the
> database
> to a point where my T3 state is in. I tried taking the backups in DEVICE
> but
> I always get an error from SQL Server Enterprise manager such as
> Volume on Device 'Device2' is not part of a family media set. BACKUP WITH
> NO
> FORMAT can be used to form a new media set. BACKUP LOG is terminating
> abnormally.
>
> If someone can give suggestions on how to backup and restore in this
> situation it will be really helpful.
> Thanks
> John
>

18 May 2005 8:55 AM

Tibor Karaszi

First you are confusing us a bit. You say that you would like to "take only the differential backup
.... of the transaction log". What does this mean? BACKUP LOG or BACKUP DATABASE ... WITH
DIFFERENTIAL. Make sure you read up on the backup types in Books Online. I assume you mean BACKUP
LOG.

It seems that you did a backup for the selected backup file earlier where you striped the backup.
Think of it as a RAID. You have two disks (backup to two files, striping) and then try to copy a
file to only one of those disks. No can do, of course. Same thing if it was the other way around.
See the FORMAT option for the backup command to split up a media set.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Show quoteHide quote

"John @ X" <Jo***@discussions.microsoft.com> wrote in message
news:F1419D05-90D0-4E64-938B-0B8220AE517F@microsoft.com...
> Hi there,
> I need to take a backup with the following scanerio in mind:
> At T1 I take the FULL BACK UP of the database called BACK_1_FULL.BACK in c:\
> Then at T2 I would like to take only the differential BACKUP of the
> Transaction log called BACK_1_TRANSLOG.BACK and at T3 I would like to take
> only the differential BACKUP of the Transaction log called
> BACK_2_TRANSLOG.BACK. At T4 my system fails and I need to bring the database
> to a point where my T3 state is in. I tried taking the backups in DEVICE but
> I always get an error from SQL Server Enterprise manager such as
> Volume on Device 'Device2' is not part of a family media set. BACKUP WITH NO
> FORMAT can be used to form a new media set. BACKUP LOG is terminating
> abnormally.
>
> If someone can give suggestions on how to backup and restore in this
> situation it will be really helpful.
> Thanks
> John
>