backup filename vs appending to device 
Author Message
 backup filename vs appending to device

I know that SQL Server 2000 can backup a database or TLOG to a unique
file or also you can backup to a file configured as a device.

In the latter case, I believe SQL internally appends your backups to
some monilith file structure and extracts the needed data when
requested to. My question is: which is better?

I am concerned that perhaps SQL stores the information about the
structure of backups appended to a device in the PRIMARY filegroup. If
this is true, then if my database becomes corrupt, I might not be able
to access the data needed to restore the transaction log backups.
Wouldn't it be better to backup to individual files, such as
"...\pubs20020318T210617.DIF" (date and time in ISO8601)

Can anyone confirm/dispell my speculation?

Thanks,

Max Hodges, MCP
Web Database Administrator
Online Technology
Dell Computer Japan



Fri, 03 Sep 2004 18:07:56 GMT
 backup filename vs appending to device

Max,

Your reading too much into this.  A backup Device is nothing more than a
logical pointer to a physical file.  So specifying the Device or just the
filename has the same end results.  What gets stored in either case is up to
how you configure the backup job or command.

--
Andrew J. Kelly   SQL MVP
Targitinteractive, Inc.


Quote:
> I know that SQL Server 2000 can backup a database or TLOG to a unique
> file or also you can backup to a file configured as a device.

> In the latter case, I believe SQL internally appends your backups to
> some monilith file structure and extracts the needed data when
> requested to. My question is: which is better?

> I am concerned that perhaps SQL stores the information about the
> structure of backups appended to a device in the PRIMARY filegroup. If
> this is true, then if my database becomes corrupt, I might not be able
> to access the data needed to restore the transaction log backups.
> Wouldn't it be better to backup to individual files, such as
> "...\pubs20020318T210617.DIF" (date and time in ISO 8601)

> Can anyone confirm/dispell my speculation?

> Thanks,

> Max Hodges, MCP
> Web Database Administrator
> Online Technology
> Dell Computer Japan



Fri, 03 Sep 2004 21:13:01 GMT
 backup filename vs appending to device
In addition to Andrew's post:

And, the backup history is written to the system tables regardless of if you do the backup
"through a pointer" or "directly".

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...



Quote:
> Max,

> Your reading too much into this.  A backup Device is nothing more than a
> logical pointer to a physical file.  So specifying the Device or just the
> filename has the same end results.  What gets stored in either case is up to
> how you configure the backup job or command.

> --
> Andrew J. Kelly   SQL MVP
> Targitinteractive, Inc.



> > I know that SQL Server 2000 can backup a database or TLOG to a unique
> > file or also you can backup to a file configured as a device.

> > In the latter case, I believe SQL internally appends your backups to
> > some monilith file structure and extracts the needed data when
> > requested to. My question is: which is better?

> > I am concerned that perhaps SQL stores the information about the
> > structure of backups appended to a device in the PRIMARY filegroup. If
> > this is true, then if my database becomes corrupt, I might not be able
> > to access the data needed to restore the transaction log backups.
> > Wouldn't it be better to backup to individual files, such as
> > "...\pubs20020318T210617.DIF" (date and time in ISO 8601)

> > Can anyone confirm/dispell my speculation?

> > Thanks,

> > Max Hodges, MCP
> > Web Database Administrator
> > Online Technology
> > Dell Computer Japan



Fri, 03 Sep 2004 22:41:06 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. DISK DEVICE vs TAPE DEVICE BACKUP

2. Backup using a filename or Device?

3. Backup Device vs Backup in Maint Plan

4. Backup device vs. backup to disk

5. Backup - Append To Device Flag

6. Unique Filename for backups - pruning old backups

7. After rs.open FILENAME, I cannot rs.fieldname.append FIELDNAME

8. Backup error - Can't open dump device or device off line

9. SQL 7 - Dumping Devices or creating backup device on a Network Drive

10. Understanding Backup Devices and Temporary Bkp devices

11. Restore from database device not backup device

12. backup permissions? device error or device offline


 
Powered by phpBB® Forum Software