This is a multi-part message in MIME format.
--=_NextPart_000_0008_01C394D0.D84DC670
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I understand that backup up to a device is done for backward = compatibility, and backup to disk would be the preferred method for Sql = 2000.
If I backup to a device, I can easily view what is on the backup device = in EM. By clicking on the Backup device in EM, I can see the full = backup, followed by the log backups, therefore, easily obtaining the = file numbers I want for restoring the logs.
How can I see this information if the backup is to disk?
Thanx!
--=_NextPart_000_0008_01C394D0.D84DC670
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I understand that backup up to a device is done for backward = compatibility, and backup to disk would be the preferred method for Sql 2000.
If I backup to a device, I can easily view what is on the backup = device in EM. By clicking on the Backup device in EM, I can see the full = backup, followed by the log backups, therefore, easily obtaining the file = numbers I want for restoring the logs.
How can I see this information if the backup is to disk?
Thanx!
--=_NextPart_000_0008_01C394D0.D84DC670--<<I understand that backup up to a device is done for backward compatibility, and backup to disk
would be the preferred method for Sql 2000.>>
That is not true. Why do you say that? I always use pre-defined backup devices just for the
advantage of them being listed in EM. EM does not have a GUI to read info from the backup history
tables (which would be required for a GUI to show stuff from directly addressed backup
destinations), nor have I seen any such GUI (although I've thought of writing one myself, but
time...).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23uuq0HPlDHA.2232@.TK2MSFTNGP09.phx.gbl...
I understand that backup up to a device is done for backward compatibility, and backup to disk would
be the preferred method for Sql 2000.
If I backup to a device, I can easily view what is on the backup device in EM. By clicking on the
Backup device in EM, I can see the full backup, followed by the log backups, therefore, easily
obtaining the file numbers I want for restoring the logs.
How can I see this information if the backup is to disk?
Thanx!|||Also, you can always use RESTORE HEADERONLY and FILELISTONLY from query analyzer, of course.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23uuq0HPlDHA.2232@.TK2MSFTNGP09.phx.gbl...
I understand that backup up to a device is done for backward compatibility, and backup to disk would
be the preferred method for Sql 2000.
If I backup to a device, I can easily view what is on the backup device in EM. By clicking on the
Backup device in EM, I can see the full backup, followed by the log backups, therefore, easily
obtaining the file numbers I want for restoring the logs.
How can I see this information if the backup is to disk?
Thanx!
Showing posts with label format. Show all posts
Showing posts with label format. Show all posts
Tuesday, March 20, 2012
Backup Question
What effect does using the FORMAT command have when backing up to disk? BOL
only addresses tapes, so I'm not sure.It allow you to split up a media set, and also overwrite a device earlier
then what you specified in RETAINDAYS or EXPIREDATE.
--
Tibor Karaszi
"John Hamilton" <jhamil@.nowhere.com> wrote in message
news:utjmnvhoDHA.2312@.TK2MSFTNGP12.phx.gbl...
> What effect does using the FORMAT command have when backing up to disk?
BOL
> only addresses tapes, so I'm not sure.
>|||So, does that mean it does not wipe out the entire directory on a hard
drive?
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:kvupb.36249$dP1.120988@.newsc.telia.net...
> It allow you to split up a media set, and also overwrite a device earlier
> then what you specified in RETAINDAYS or EXPIREDATE.
> --
> Tibor Karaszi
>
> "John Hamilton" <jhamil@.nowhere.com> wrote in message
> news:utjmnvhoDHA.2312@.TK2MSFTNGP12.phx.gbl...
> > What effect does using the FORMAT command have when backing up to disk?
> BOL
> > only addresses tapes, so I'm not sure.
> >
> >
>|||No. it doesn't wipe out the whole directory. It has
nothing to do with the NT FORMAT command.
When backing up to disk, I use the FORMAT option primarily
for reusing a backup file that was part of a backup stripe
set or for reusing a file in a new backup stripe set.
Other than that, I don't use the FORMAT option at all when
backing up to disk.
Linchi
>--Original Message--
>So, does that mean it does not wipe out the entire
directory on a hard
>drive?
>"Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
>wrote in message news:kvupb.36249
$dP1.120988@.newsc.telia.net...
>> It allow you to split up a media set, and also
overwrite a device earlier
>> then what you specified in RETAINDAYS or EXPIREDATE.
>> --
>> Tibor Karaszi
>>
>> "John Hamilton" <jhamil@.nowhere.com> wrote in message
>> news:utjmnvhoDHA.2312@.TK2MSFTNGP12.phx.gbl...
>> > What effect does using the FORMAT command have when
backing up to disk?
>> BOL
>> > only addresses tapes, so I'm not sure.
>> >
>> >
>>
>
>.
>sql
only addresses tapes, so I'm not sure.It allow you to split up a media set, and also overwrite a device earlier
then what you specified in RETAINDAYS or EXPIREDATE.
--
Tibor Karaszi
"John Hamilton" <jhamil@.nowhere.com> wrote in message
news:utjmnvhoDHA.2312@.TK2MSFTNGP12.phx.gbl...
> What effect does using the FORMAT command have when backing up to disk?
BOL
> only addresses tapes, so I'm not sure.
>|||So, does that mean it does not wipe out the entire directory on a hard
drive?
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:kvupb.36249$dP1.120988@.newsc.telia.net...
> It allow you to split up a media set, and also overwrite a device earlier
> then what you specified in RETAINDAYS or EXPIREDATE.
> --
> Tibor Karaszi
>
> "John Hamilton" <jhamil@.nowhere.com> wrote in message
> news:utjmnvhoDHA.2312@.TK2MSFTNGP12.phx.gbl...
> > What effect does using the FORMAT command have when backing up to disk?
> BOL
> > only addresses tapes, so I'm not sure.
> >
> >
>|||No. it doesn't wipe out the whole directory. It has
nothing to do with the NT FORMAT command.
When backing up to disk, I use the FORMAT option primarily
for reusing a backup file that was part of a backup stripe
set or for reusing a file in a new backup stripe set.
Other than that, I don't use the FORMAT option at all when
backing up to disk.
Linchi
>--Original Message--
>So, does that mean it does not wipe out the entire
directory on a hard
>drive?
>"Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
>wrote in message news:kvupb.36249
$dP1.120988@.newsc.telia.net...
>> It allow you to split up a media set, and also
overwrite a device earlier
>> then what you specified in RETAINDAYS or EXPIREDATE.
>> --
>> Tibor Karaszi
>>
>> "John Hamilton" <jhamil@.nowhere.com> wrote in message
>> news:utjmnvhoDHA.2312@.TK2MSFTNGP12.phx.gbl...
>> > What effect does using the FORMAT command have when
backing up to disk?
>> BOL
>> > only addresses tapes, so I'm not sure.
>> >
>> >
>>
>
>.
>sql
Wednesday, March 7, 2012
Backup on Network Drive
This is a multi-part message in MIME format.
--=_NextPart_000_004E_01C3ECC2.CF1CAF40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi,
We want to take back up of databases on network drives. How should we do = this ?
- Ketan
--=_NextPart_000_004E_01C3ECC2.CF1CAF40
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi,
We want to take back up = of databases on network drives. How should we do this ?
- Ketan
--=_NextPart_000_004E_01C3ECC2.CF1CAF40--It is not generally recommended to backup directly to network drives. You
might be better off backup up to a local array, & then copying the file
across the network. There is a discussion on this here:
http://www.sql-server-performance.com/backup_restore_tuning.asp
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||Use an UNC path in the destination file name. Make sure that the SQL Server service account has
appropriate permissions on that share.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Ketan Patel" <ketanforever@.indiatimes.com> wrote in message
news:ua9$kQJ7DHA.2392@.TK2MSFTNGP11.phx.gbl...
Hi,
We want to take back up of databases on network drives. How should we do this ?
- Ketan|||Thanks for your reply.
I have verified the service account. It was System account. I have changed
this to other having appropriate access to network drives and this works.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OqqmdWJ7DHA.3360@.tk2msftngp13.phx.gbl...
> Use an UNC path in the destination file name. Make sure that the SQL
Server service account has
> appropriate permissions on that share.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Ketan Patel" <ketanforever@.indiatimes.com> wrote in message
> news:ua9$kQJ7DHA.2392@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We want to take back up of databases on network drives. How should we do
this ?
> - Ketan
>
>
>
>|||I will vouch for that from experience.
:-)
Greg Jackson
PDX, Oregon
--=_NextPart_000_004E_01C3ECC2.CF1CAF40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi,
We want to take back up of databases on network drives. How should we do = this ?
- Ketan
--=_NextPart_000_004E_01C3ECC2.CF1CAF40
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi,
We want to take back up = of databases on network drives. How should we do this ?
- Ketan
--=_NextPart_000_004E_01C3ECC2.CF1CAF40--It is not generally recommended to backup directly to network drives. You
might be better off backup up to a local array, & then copying the file
across the network. There is a discussion on this here:
http://www.sql-server-performance.com/backup_restore_tuning.asp
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||Use an UNC path in the destination file name. Make sure that the SQL Server service account has
appropriate permissions on that share.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Ketan Patel" <ketanforever@.indiatimes.com> wrote in message
news:ua9$kQJ7DHA.2392@.TK2MSFTNGP11.phx.gbl...
Hi,
We want to take back up of databases on network drives. How should we do this ?
- Ketan|||Thanks for your reply.
I have verified the service account. It was System account. I have changed
this to other having appropriate access to network drives and this works.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OqqmdWJ7DHA.3360@.tk2msftngp13.phx.gbl...
> Use an UNC path in the destination file name. Make sure that the SQL
Server service account has
> appropriate permissions on that share.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Ketan Patel" <ketanforever@.indiatimes.com> wrote in message
> news:ua9$kQJ7DHA.2392@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We want to take back up of databases on network drives. How should we do
this ?
> - Ketan
>
>
>
>|||I will vouch for that from experience.
:-)
Greg Jackson
PDX, Oregon
Sunday, February 19, 2012
Backup log, no automatic deletion of expired logs?
Hi there,
I use the following scenario-
Every day at 00:00 I backup the database:
BACKUP DATABASE c5win300sql TO c5win300sql_dat WITH FORMAT
Every half hour from 06:00 to 23:00 I backup the log:
declare @.expDate datetime
select @.expDate = dateadd(day,1,getdate())
BACKUP LOG c5win300sql TO c5win300sql_log WITH EXPIREDATE = @.expdate
From the above, you can see that I set a expiration date, RESTORE HEADERONLY
FROM c5win300sql_log confirms, that it works correctly.
However, the c5win300sql_log backup continues to grow and the expired logs does
not get "deleted" from the file. How can I rectify this issue "properly"?
--
I doubt, therefore I might be.All that EXPIREDATE and RETAINDAYS does it that they prohibit you from overwriting (using INIT) the
backup file before the desired date and time. It doesn't make any automatic deletion of old backups.
In fact, you cannot delete only some backups from a backup file. It is all or nothing.
Maint Wiz has functionality to create new backup files with names based on date and time (among
other). And Maint wiz can delete files other than x days. You can also write similar code yourself,
but that takes some coding to do.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kim Noer" <kn@.nospam.dk> wrote in message news:uiiTdKb%23FHA.1032@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> I use the following scenario-
> Every day at 00:00 I backup the database:
> BACKUP DATABASE c5win300sql TO c5win300sql_dat WITH FORMAT
> Every half hour from 06:00 to 23:00 I backup the log:
> declare @.expDate datetime
> select @.expDate = dateadd(day,1,getdate())
> BACKUP LOG c5win300sql TO c5win300sql_log WITH EXPIREDATE = @.expdate
> From the above, you can see that I set a expiration date, RESTORE HEADERONLY FROM c5win300sql_log
> confirms, that it works correctly.
> However, the c5win300sql_log backup continues to grow and the expired logs does not get "deleted"
> from the file. How can I rectify this issue "properly"?
> --
> I doubt, therefore I might be.|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote in message news:%23yIEs7b%23FHA.600@.tk2msftngp13.phx.gbl
> Maint Wiz has functionality to create new backup files with names
> based on date and time (among other). And Maint wiz can delete files
> other than x days. You can also write similar code yourself, but that
> takes some coding to do.
Thanks for your answer. However, can I with get away with a simple 'WITH FORMAT'
at 06:00 (when the first log backup is being done)?
I got a 5 day tape backup and 2x 1 day disk backup, so my butt should be covered
somewhat.
--
I doubt, therefore I might be.|||I never use FORMAT, unless I either need to overwrite a backup before EXPIREDATE (which never
happens as I don't use EXPIREDATE), or want to split up a media set. The normal way to overwrite is
to specify INIT. As to whether you can overwrite every time, only you can decide based on how
covered you are (if and where you have put your old backups).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kim Noer" <kn@.nospam.dk> wrote in message news:eCapq9n%23FHA.1676@.TK2MSFTNGP09.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> wrote in message news:%23yIEs7b%23FHA.600@.tk2msftngp13.phx.gbl
>> Maint Wiz has functionality to create new backup files with names
>> based on date and time (among other). And Maint wiz can delete files
>> other than x days. You can also write similar code yourself, but that
>> takes some coding to do.
> Thanks for your answer. However, can I with get away with a simple 'WITH FORMAT' at 06:00 (when
> the first log backup is being done)?
> I got a 5 day tape backup and 2x 1 day disk backup, so my butt should be covered somewhat.
> --
> I doubt, therefore I might be.
I use the following scenario-
Every day at 00:00 I backup the database:
BACKUP DATABASE c5win300sql TO c5win300sql_dat WITH FORMAT
Every half hour from 06:00 to 23:00 I backup the log:
declare @.expDate datetime
select @.expDate = dateadd(day,1,getdate())
BACKUP LOG c5win300sql TO c5win300sql_log WITH EXPIREDATE = @.expdate
From the above, you can see that I set a expiration date, RESTORE HEADERONLY
FROM c5win300sql_log confirms, that it works correctly.
However, the c5win300sql_log backup continues to grow and the expired logs does
not get "deleted" from the file. How can I rectify this issue "properly"?
--
I doubt, therefore I might be.All that EXPIREDATE and RETAINDAYS does it that they prohibit you from overwriting (using INIT) the
backup file before the desired date and time. It doesn't make any automatic deletion of old backups.
In fact, you cannot delete only some backups from a backup file. It is all or nothing.
Maint Wiz has functionality to create new backup files with names based on date and time (among
other). And Maint wiz can delete files other than x days. You can also write similar code yourself,
but that takes some coding to do.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kim Noer" <kn@.nospam.dk> wrote in message news:uiiTdKb%23FHA.1032@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> I use the following scenario-
> Every day at 00:00 I backup the database:
> BACKUP DATABASE c5win300sql TO c5win300sql_dat WITH FORMAT
> Every half hour from 06:00 to 23:00 I backup the log:
> declare @.expDate datetime
> select @.expDate = dateadd(day,1,getdate())
> BACKUP LOG c5win300sql TO c5win300sql_log WITH EXPIREDATE = @.expdate
> From the above, you can see that I set a expiration date, RESTORE HEADERONLY FROM c5win300sql_log
> confirms, that it works correctly.
> However, the c5win300sql_log backup continues to grow and the expired logs does not get "deleted"
> from the file. How can I rectify this issue "properly"?
> --
> I doubt, therefore I might be.|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote in message news:%23yIEs7b%23FHA.600@.tk2msftngp13.phx.gbl
> Maint Wiz has functionality to create new backup files with names
> based on date and time (among other). And Maint wiz can delete files
> other than x days. You can also write similar code yourself, but that
> takes some coding to do.
Thanks for your answer. However, can I with get away with a simple 'WITH FORMAT'
at 06:00 (when the first log backup is being done)?
I got a 5 day tape backup and 2x 1 day disk backup, so my butt should be covered
somewhat.
--
I doubt, therefore I might be.|||I never use FORMAT, unless I either need to overwrite a backup before EXPIREDATE (which never
happens as I don't use EXPIREDATE), or want to split up a media set. The normal way to overwrite is
to specify INIT. As to whether you can overwrite every time, only you can decide based on how
covered you are (if and where you have put your old backups).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kim Noer" <kn@.nospam.dk> wrote in message news:eCapq9n%23FHA.1676@.TK2MSFTNGP09.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> wrote in message news:%23yIEs7b%23FHA.600@.tk2msftngp13.phx.gbl
>> Maint Wiz has functionality to create new backup files with names
>> based on date and time (among other). And Maint wiz can delete files
>> other than x days. You can also write similar code yourself, but that
>> takes some coding to do.
> Thanks for your answer. However, can I with get away with a simple 'WITH FORMAT' at 06:00 (when
> the first log backup is being done)?
> I got a 5 day tape backup and 2x 1 day disk backup, so my butt should be covered somewhat.
> --
> I doubt, therefore I might be.
Friday, February 10, 2012
Backup format
Hi,
Does anyone know of any documentation/web page that details SQL SERVER 7
backup format? Thanks.
Jon.Jon <jonov@.iprimus.com.au> wrote in message news:<4008f085_1@.news.iprimus.com.au>...
> Hi,
> Does anyone know of any documentation/web page that details SQL SERVER 7
> backup format? Thanks.
> Jon.
As far as I'm aware, the backup format is not documented. There are
third party backup products that seem to be based on a knowledge of
the format (eg. restore a single object without restoring the whole
backup), but if so, I'd guess they were developed under NDA.
Simon
Subscribe to:
Posts (Atom)