interface supported: backup

Showing posts with label backup. Show all posts

Friday, March 30, 2012

not a valid undo file for database

Hi,
I've restored a database from backup, with STANDBY option.
Now, when I'm trying to apply further Transaction logs to
this database, the restore log command is failing with an
error 'File 'd:\standby.undo' is not a valid undo file for
database 'xyz', database ID 24.
Please let me know how to I avoid this error and restore
logs to this database.
Does the file mentioned in the error message actually exist?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ykchakri" <anonymous@.discussions.microsoft.com> wrote in message
news:21a001c4aa62$329a33a0$a501280a@.phx.gbl...
> Hi,
> I've restored a database from backup, with STANDBY option.
> Now, when I'm trying to apply further Transaction logs to
> this database, the restore log command is failing with an
> error 'File 'd:\standby.undo' is not a valid undo file for
> database 'xyz', database ID 24.
> Please let me know how to I avoid this error and restore
> logs to this database.
|||Yes, it does. And I've tried renaming this file and then
it gives a different error that the file does not exist.
SO, I'm sure that it is able to access this file, but
something is not right in this file.
What's the purpose of this file anyway ?

>--Original Message--
>Does the file mentioned in the error message actually
exist?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"ykchakri" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:21a001c4aa62$329a33a0$a501280a@.phx.gbl...
option.[vbcol=seagreen]
to[vbcol=seagreen]
an[vbcol=seagreen]
for
>
>.
>
|||The UNDO file is created when you perform RESTORE using the STANDBY option.
This is because SQL Server will actually perform recovery based on the transaction log when you are
using STANDBY, but as you say you want to be able to perform additional restores, SQL Server will
save the recovery work it performs in this undo file so it can undo the recovery work when you do
the next restore.
SQL Server will remember the name of the undo file so it will automatically find it when next
restore is performed. In this case, SQL Server doesn't recognize the undo file as a valid file.
Perhaps someone deleted the file and just created one through notepad, or picked some other UNDO
file and renamed it? Bottom-line is that SLQ Server *need* a valid UNDO file for the next restore.
You can always re-start all restores from the latest database backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ykchakri" <anonymous@.discussions.microsoft.com> wrote in message
news:2abc01c4ab08$347ecdb0$a501280a@.phx.gbl...[vbcol=seagreen]
> Yes, it does. And I've tried renaming this file and then
> it gives a different error that the file does not exist.
> SO, I'm sure that it is able to access this file, but
> something is not right in this file.
> What's the purpose of this file anyway ?
> exist?
> message
> option.
> to
> an
> for
sql

not a valid undo file for database

Hi,
I've restored a database from backup, with STANDBY option.
Now, when I'm trying to apply further Transaction logs to
this database, the restore log command is failing with an
error 'File 'd:\standby.undo' is not a valid undo file for
database 'xyz', database ID 24.
Please let me know how to I avoid this error and restore
logs to this database.Does the file mentioned in the error message actually exist?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ykchakri" <anonymous@.discussions.microsoft.com> wrote in message
news:21a001c4aa62$329a33a0$a501280a@.phx.gbl...
> Hi,
> I've restored a database from backup, with STANDBY option.
> Now, when I'm trying to apply further Transaction logs to
> this database, the restore log command is failing with an
> error 'File 'd:\standby.undo' is not a valid undo file for
> database 'xyz', database ID 24.
> Please let me know how to I avoid this error and restore
> logs to this database.|||Yes, it does. And I've tried renaming this file and then
it gives a different error that the file does not exist.
SO, I'm sure that it is able to access this file, but
something is not right in this file.
What's the purpose of this file anyway ?
>--Original Message--
>Does the file mentioned in the error message actually
exist?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"ykchakri" <anonymous@.discussions.microsoft.com> wrote in
message
>news:21a001c4aa62$329a33a0$a501280a@.phx.gbl...
>> Hi,
>> I've restored a database from backup, with STANDBY
option.
>> Now, when I'm trying to apply further Transaction logs
to
>> this database, the restore log command is failing with
an
>> error 'File 'd:\standby.undo' is not a valid undo file
for
>> database 'xyz', database ID 24.
>> Please let me know how to I avoid this error and restore
>> logs to this database.
>
>.
>|||The UNDO file is created when you perform RESTORE using the STANDBY option.
This is because SQL Server will actually perform recovery based on the transaction log when you are
using STANDBY, but as you say you want to be able to perform additional restores, SQL Server will
save the recovery work it performs in this undo file so it can undo the recovery work when you do
the next restore.
SQL Server will remember the name of the undo file so it will automatically find it when next
restore is performed. In this case, SQL Server doesn't recognize the undo file as a valid file.
Perhaps someone deleted the file and just created one through notepad, or picked some other UNDO
file and renamed it? Bottom-line is that SLQ Server *need* a valid UNDO file for the next restore.
You can always re-start all restores from the latest database backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ykchakri" <anonymous@.discussions.microsoft.com> wrote in message
news:2abc01c4ab08$347ecdb0$a501280a@.phx.gbl...
> Yes, it does. And I've tried renaming this file and then
> it gives a different error that the file does not exist.
> SO, I'm sure that it is able to access this file, but
> something is not right in this file.
> What's the purpose of this file anyway ?
>>--Original Message--
>>Does the file mentioned in the error message actually
> exist?
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>
>>"ykchakri" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:21a001c4aa62$329a33a0$a501280a@.phx.gbl...
>> Hi,
>> I've restored a database from backup, with STANDBY
> option.
>> Now, when I'm trying to apply further Transaction logs
> to
>> this database, the restore log command is failing with
> an
>> error 'File 'd:\standby.undo' is not a valid undo file
> for
>> database 'xyz', database ID 24.
>> Please let me know how to I avoid this error and restore
>> logs to this database.
>>
>>.

'not a valid Microsoft Tape Format backup set' error when trying to restore DB

Hello
I have a point in time Microsoft SQL Server 2000 .mdf data
file which was taken from a snapshot. Basically, it is the
database at a point in time, which I can attach to and
view data no problem in a sql server.
I also have hourly transaction logs which occured after
the above .mdf data file snapshot. I would like to apply
those transaction log files to the .mdf data file. I've
tried many many varieties, but seem to get farthest with
the following code:
ALTER DATABASE JDE_PRODUCTION SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
GO
RESTORE DATABASE JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\jde_PRODUCTIONdata_new.mdf '
WITH NORECOVERY
RESTORE LOG JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\07-22-
2004_7.00.00__JDE_PRODUCTION.trb'
WITH NORECOVERY
[...MORE RESTORE HOURLY LOG STATEMENTS...]
RESTORE LOG JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\07-22-
2004_13.00.01__JDE_PRODUCTION.trb'
WITH RECOVERY
/****end****/
My problem seems to be the following error:
Server: Msg 3242, Level 16, State 1, Line 2
The file on device 'F:\mssql7
\jdedata\jde_PRODUCTIONdata_new.mdf' is not a valid
Microsoft Tape Format backup set.
Can't seem to get around the fact that Microsoft is
looking for the restore database file as a Tape format,
which it isn't, it's a .mdf database file.
Any clues?
Thanks in advance...
Jeff
You can't RESTORE from an 'mdf' file because a RESTORE command expects a
file created with BACKUP. The backup file will be in MTF format regardless
of the underlying media type.
The proper way to accomplish your task is to start with a full backup.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff" <jblanding@.nwpasta.com> wrote in message
news:76fd01c47672$4443f470$a401280a@.phx.gbl...
> Hello
> I have a point in time Microsoft SQL Server 2000 .mdf data
> file which was taken from a snapshot. Basically, it is the
> database at a point in time, which I can attach to and
> view data no problem in a sql server.
> I also have hourly transaction logs which occured after
> the above .mdf data file snapshot. I would like to apply
> those transaction log files to the .mdf data file. I've
> tried many many varieties, but seem to get farthest with
> the following code:
> ALTER DATABASE JDE_PRODUCTION SET SINGLE_USER WITH
> ROLLBACK IMMEDIATE
> GO
> RESTORE DATABASE JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\jde_PRODUCTIONdata_new.mdf '
> WITH NORECOVERY
> RESTORE LOG JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\07-22-
> 2004_7.00.00__JDE_PRODUCTION.trb'
> WITH NORECOVERY
> [...MORE RESTORE HOURLY LOG STATEMENTS...]
> RESTORE LOG JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\07-22-
> 2004_13.00.01__JDE_PRODUCTION.trb'
> WITH RECOVERY
> /****end****/
> My problem seems to be the following error:
> Server: Msg 3242, Level 16, State 1, Line 2
> The file on device 'F:\mssql7
> \jdedata\jde_PRODUCTIONdata_new.mdf' is not a valid
> Microsoft Tape Format backup set.
> Can't seem to get around the fact that Microsoft is
> looking for the restore database file as a Tape format,
> which it isn't, it's a .mdf database file.
> Any clues?
> Thanks in advance...
> Jeff
sql

'not a valid Microsoft Tape Format backup set' error when trying to restore DB

Hello
I have a point in time Microsoft SQL Server 2000 .mdf data
file which was taken from a snapshot. Basically, it is the
database at a point in time, which I can attach to and
view data no problem in a sql server.
I also have hourly transaction logs which occured after
the above .mdf data file snapshot. I would like to apply
those transaction log files to the .mdf data file. I've
tried many many varieties, but seem to get farthest with
the following code:
ALTER DATABASE JDE_PRODUCTION SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
GO
RESTORE DATABASE JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\jde_PRODUCTIONda
ta_new.mdf'
WITH NORECOVERY
RESTORE LOG JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\07-22-
2004_7.00.00__JDE_PRODUCTION.trb'
WITH NORECOVERY
[...MORE RESTORE HOURLY LOG STATEMENTS...]
RESTORE LOG JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\07-22-
2004_13.00.01__JDE_PRODUCTION.trb'
WITH RECOVERY
/****end****/
My problem seems to be the following error:
Server: Msg 3242, Level 16, State 1, Line 2
The file on device 'F:\mssql7
\jdedata\jde_PRODUCTIONdata_new.mdf' is not a valid
Microsoft Tape Format backup set.
Can't seem to get around the fact that Microsoft is
looking for the restore database file as a Tape format,
which it isn't, it's a .mdf database file.
Any clues?
Thanks in advance...
JeffYou can't RESTORE from an 'mdf' file because a RESTORE command expects a
file created with BACKUP. The backup file will be in MTF format regardless
of the underlying media type.
The proper way to accomplish your task is to start with a full backup.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff" <jblanding@.nwpasta.com> wrote in message
news:76fd01c47672$4443f470$a401280a@.phx.gbl...
> Hello
> I have a point in time Microsoft SQL Server 2000 .mdf data
> file which was taken from a snapshot. Basically, it is the
> database at a point in time, which I can attach to and
> view data no problem in a sql server.
> I also have hourly transaction logs which occured after
> the above .mdf data file snapshot. I would like to apply
> those transaction log files to the .mdf data file. I've
> tried many many varieties, but seem to get farthest with
> the following code:
> ALTER DATABASE JDE_PRODUCTION SET SINGLE_USER WITH
> ROLLBACK IMMEDIATE
> GO
> RESTORE DATABASE JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\jde_PRODUCTIONda
ta_new.mdf'
> WITH NORECOVERY
> RESTORE LOG JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\07-22-
> 2004_7.00.00__JDE_PRODUCTION.trb'
> WITH NORECOVERY
> [...MORE RESTORE HOURLY LOG STATEMENTS...]
> RESTORE LOG JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\07-22-
> 2004_13.00.01__JDE_PRODUCTION.trb'
> WITH RECOVERY
> /****end****/
> My problem seems to be the following error:
> Server: Msg 3242, Level 16, State 1, Line 2
> The file on device 'F:\mssql7
> \jdedata\jde_PRODUCTIONdata_new.mdf' is not a valid
> Microsoft Tape Format backup set.
> Can't seem to get around the fact that Microsoft is
> looking for the restore database file as a Tape format,
> which it isn't, it's a .mdf database file.
> Any clues?
> Thanks in advance...
> Jeff

'not a valid Microsoft Tape Format backup set' error when trying to restore DB

Hello
I have a point in time Microsoft SQL Server 2000 .mdf data
file which was taken from a snapshot. Basically, it is the
database at a point in time, which I can attach to and
view data no problem in a sql server.
I also have hourly transaction logs which occured after
the above .mdf data file snapshot. I would like to apply
those transaction log files to the .mdf data file. I've
tried many many varieties, but seem to get farthest with
the following code:
ALTER DATABASE JDE_PRODUCTION SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
GO
RESTORE DATABASE JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\jde_PRODUCTIONdata_new.mdf'
WITH NORECOVERY
RESTORE LOG JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\07-22-
2004_7.00.00__JDE_PRODUCTION.trb'
WITH NORECOVERY
[...MORE RESTORE HOURLY LOG STATEMENTS...]
RESTORE LOG JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\07-22-
2004_13.00.01__JDE_PRODUCTION.trb'
WITH RECOVERY
/****end****/
My problem seems to be the following error:
Server: Msg 3242, Level 16, State 1, Line 2
The file on device 'F:\mssql7
\jdedata\jde_PRODUCTIONdata_new.mdf' is not a valid
Microsoft Tape Format backup set.
Can't seem to get around the fact that Microsoft is
looking for the restore database file as a Tape format,
which it isn't, it's a .mdf database file.
Any clues?
Thanks in advance...
JeffYou can't RESTORE from an 'mdf' file because a RESTORE command expects a
file created with BACKUP. The backup file will be in MTF format regardless
of the underlying media type.
The proper way to accomplish your task is to start with a full backup.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff" <jblanding@.nwpasta.com> wrote in message
news:76fd01c47672$4443f470$a401280a@.phx.gbl...
> Hello
> I have a point in time Microsoft SQL Server 2000 .mdf data
> file which was taken from a snapshot. Basically, it is the
> database at a point in time, which I can attach to and
> view data no problem in a sql server.
> I also have hourly transaction logs which occured after
> the above .mdf data file snapshot. I would like to apply
> those transaction log files to the .mdf data file. I've
> tried many many varieties, but seem to get farthest with
> the following code:
> ALTER DATABASE JDE_PRODUCTION SET SINGLE_USER WITH
> ROLLBACK IMMEDIATE
> GO
> RESTORE DATABASE JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\jde_PRODUCTIONdata_new.mdf'
> WITH NORECOVERY
> RESTORE LOG JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\07-22-
> 2004_7.00.00__JDE_PRODUCTION.trb'
> WITH NORECOVERY
> [...MORE RESTORE HOURLY LOG STATEMENTS...]
> RESTORE LOG JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\07-22-
> 2004_13.00.01__JDE_PRODUCTION.trb'
> WITH RECOVERY
> /****end****/
> My problem seems to be the following error:
> Server: Msg 3242, Level 16, State 1, Line 2
> The file on device 'F:\mssql7
> \jdedata\jde_PRODUCTIONdata_new.mdf' is not a valid
> Microsoft Tape Format backup set.
> Can't seem to get around the fact that Microsoft is
> looking for the restore database file as a Tape format,
> which it isn't, it's a .mdf database file.
> Any clues?
> Thanks in advance...
> Jeff

NOSKIP & SKIP make no difference to the amount of time backup take

Hi
I run a full backup once a month and a transaction log backup every 10 mins
through working hours for the remainder of the month until the backup is
re-initialised at th start of the following month.
I backup to virtual sql disk object devices.
As the month progresses the backup takes longer and longer whilst the amount
of data being backed up every 10 minutes cycle is always roughly the same.
its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
month.
I noticed that NOSKIP was being used in the command - i changed this to
SKIP, but it made no difference in the amount of time the backup takes. Isnt
this suppoed to stop some sort of integrity scan on all other backup sets in
the archive?
full syntax of the backup command that runs now is:
BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
but as I say, the SKIP command instead of NOSKIP makes no difference.
whats going on here?
any help appreciated.
Thanks
Alastair.On Aug 1, 3:44 pm, Methodology <Methodol...@.discussions.microsoft.com>
wrote:
> Hi
> I run a full backup once a month and a transaction log backup every 10 mins
> through working hours for the remainder of the month until the backup is
> re-initialised at th start of the following month.
> I backup to virtual sql disk object devices.
> As the month progresses the backup takes longer and longer whilst the amount
> of data being backed up every 10 minutes cycle is always roughly the same.
> its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
> month.
> I noticed that NOSKIP was being used in the command - i changed this to
> SKIP, but it made no difference in the amount of time the backup takes. Isnt
> this suppoed to stop some sort of integrity scan on all other backup sets in
> the archive?
> full syntax of the backup command that runs now is:
> BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
> NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
> but as I say, the SKIP command instead of NOSKIP makes no difference.
> whats going on here?
> any help appreciated.
> Thanks
> Alastair.
Noskip/skip does not make any difference on Backup time. It just
checks expiration date of backup sets. I just guess that the reason
may be that your disk gets fragmented after number of backups and that
may cause delay.|||yes but if theres a LOT of backup sets surely this will add a delay?
"amish" wrote:
> On Aug 1, 3:44 pm, Methodology <Methodol...@.discussions.microsoft.com>
> wrote:
> > Hi
> >
> > I run a full backup once a month and a transaction log backup every 10 mins
> > through working hours for the remainder of the month until the backup is
> > re-initialised at th start of the following month.
> >
> > I backup to virtual sql disk object devices.
> >
> > As the month progresses the backup takes longer and longer whilst the amount
> > of data being backed up every 10 minutes cycle is always roughly the same.
> > its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
> > month.
> >
> > I noticed that NOSKIP was being used in the command - i changed this to
> > SKIP, but it made no difference in the amount of time the backup takes. Isnt
> > this suppoed to stop some sort of integrity scan on all other backup sets in
> > the archive?
> >
> > full syntax of the backup command that runs now is:
> >
> > BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
> > NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
> >
> > but as I say, the SKIP command instead of NOSKIP makes no difference.
> >
> > whats going on here?
> >
> > any help appreciated.
> >
> > Thanks
> >
> > Alastair.
> Noskip/skip does not make any difference on Backup time. It just
> checks expiration date of backup sets. I just guess that the reason
> may be that your disk gets fragmented after number of backups and that
> may cause delay.
>|||Looking at the BACKUP commands you posted, which includes REWIND and
NOFORMAT, it looks like the backup goes to tape. When you referred to
"virtual sql disk object devices" I was not expecting that. Tapes are
not virtual disks, tapes are tapes.
I have seen that behavior when backing up directly to tape. The
problem in that case was reading the tape from the start to the point
where the prior backup ended. Lets consider that by the end of the
month there have been around 1000 log backups assuming a 5 day work
week and an 8 hour work day.
I suggest changing the REWIND option to NOREWIND. From the
documentation: "NOREWIND -- Specifies that SQL Server will keep the
tape open after the backup operation. You can use this option to help
improve performance when performing multiple backup operations to a
tape."
Roy Harvey
Beacon Falls, CT
On Wed, 1 Aug 2007 03:44:00 -0700, Methodology
<Methodology@.discussions.microsoft.com> wrote:
>Hi
>I run a full backup once a month and a transaction log backup every 10 mins
>through working hours for the remainder of the month until the backup is
>re-initialised at th start of the following month.
>I backup to virtual sql disk object devices.
>As the month progresses the backup takes longer and longer whilst the amount
>of data being backed up every 10 minutes cycle is always roughly the same.
>its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
>month.
>I noticed that NOSKIP was being used in the command - i changed this to
>SKIP, but it made no difference in the amount of time the backup takes. Isnt
>this suppoed to stop some sort of integrity scan on all other backup sets in
>the archive?
>full syntax of the backup command that runs now is:
>BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
>NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
>but as I say, the SKIP command instead of NOSKIP makes no difference.
>whats going on here?
>any help appreciated.
>Thanks
>Alastair.|||thanks for your response Roy.
if you go to server objects >backup devices you can configure a virtual
'tape' that is in effect a file on the physical disk. this is what im backing
up to. as far as I can tell the only differnece between using this method and
backing up direct to disk is that you get to use a shorter name in the backup
job command rather than a full file path.
anyhoo
may be this ISNT the only differnce. REWIND is default right? so maybe SQL
is 'rewinding' the virtual tape? ive changed the job to norewind and ill see
what happens in 2 backups time (20 mins.)
cheers for the pointer
"Roy Harvey" wrote:
> Looking at the BACKUP commands you posted, which includes REWIND and
> NOFORMAT, it looks like the backup goes to tape. When you referred to
> "virtual sql disk object devices" I was not expecting that. Tapes are
> not virtual disks, tapes are tapes.
> I have seen that behavior when backing up directly to tape. The
> problem in that case was reading the tape from the start to the point
> where the prior backup ended. Lets consider that by the end of the
> month there have been around 1000 log backups assuming a 5 day work
> week and an 8 hour work day.
> I suggest changing the REWIND option to NOREWIND. From the
> documentation: "NOREWIND -- Specifies that SQL Server will keep the
> tape open after the backup operation. You can use this option to help
> improve performance when performing multiple backup operations to a
> tape."
> Roy Harvey
> Beacon Falls, CT
> On Wed, 1 Aug 2007 03:44:00 -0700, Methodology
> <Methodology@.discussions.microsoft.com> wrote:
> >Hi
> >
> >I run a full backup once a month and a transaction log backup every 10 mins
> >through working hours for the remainder of the month until the backup is
> >re-initialised at th start of the following month.
> >
> >I backup to virtual sql disk object devices.
> >
> >As the month progresses the backup takes longer and longer whilst the amount
> >of data being backed up every 10 minutes cycle is always roughly the same.
> >its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
> >month.
> >
> >I noticed that NOSKIP was being used in the command - i changed this to
> >SKIP, but it made no difference in the amount of time the backup takes. Isnt
> >this suppoed to stop some sort of integrity scan on all other backup sets in
> >the archive?
> >
> >full syntax of the backup command that runs now is:
> >
> >BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
> >NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
> >
> >but as I say, the SKIP command instead of NOSKIP makes no difference.
> >
> >whats going on here?
> >
> >any help appreciated.
> >
> >Thanks
> >
> >Alastair.
>|||On Aug 1, 4:37 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> Looking at the BACKUP commands you posted, which includes REWIND and
> NOFORMAT, it looks like the backup goes to tape. When you referred to
> "virtual sql disk object devices" I was not expecting that. Tapes are
> not virtual disks, tapes are tapes.
> I have seen that behavior when backing up directly to tape. The
> problem in that case was reading the tape from the start to the point
> where the prior backup ended. Lets consider that by the end of the
> month there have been around 1000 log backups assuming a 5 day work
> week and an 8 hour work day.
> I suggest changing the REWIND option to NOREWIND. From the
> documentation: "NOREWIND -- Specifies that SQL Server will keep the
> tape open after the backup operation. You can use this option to help
> improve performance when performing multiple backup operations to a
> tape."
> Roy Harvey
> Beacon Falls, CT
> On Wed, 1 Aug 2007 03:44:00 -0700, Methodology
>
> <Methodol...@.discussions.microsoft.com> wrote:
> >Hi
> >I run a full backup once a month and a transaction log backup every 10 mins
> >through working hours for the remainder of the month until the backup is
> >re-initialised at th start of the following month.
> >I backup to virtual sql disk object devices.
> >As the month progresses the backup takes longer and longer whilst the amount
> >of data being backed up every 10 minutes cycle is always roughly the same.
> >its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
> >month.
> >I noticed that NOSKIP was being used in the command - i changed this to
> >SKIP, but it made no difference in the amount of time the backup takes. Isnt
> >this suppoed to stop some sort of integrity scan on all other backup sets in
> >the archive?
> >full syntax of the backup command that runs now is:
> >BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
> >NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
> >but as I say, the SKIP command instead of NOSKIP makes no difference.
> >whats going on here?
> >any help appreciated.
> >Thanks
> >Alastair.- Hide quoted text -
> - Show quoted text -
I dont have much idea about tapes but in BOL it says that
NOREWIND implies NOUNLOAD, and these options are incompatible within a
single BACKUP statement
and Methodology has specified also NOUNLOAD in the script.|||fair point - ive taken out NOUNLOAD and changedto NOREWIND as the latter
implies the former
thanks
"amish" wrote:
> On Aug 1, 4:37 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> > Looking at the BACKUP commands you posted, which includes REWIND and
> > NOFORMAT, it looks like the backup goes to tape. When you referred to
> > "virtual sql disk object devices" I was not expecting that. Tapes are
> > not virtual disks, tapes are tapes.
> >
> > I have seen that behavior when backing up directly to tape. The
> > problem in that case was reading the tape from the start to the point
> > where the prior backup ended. Lets consider that by the end of the
> > month there have been around 1000 log backups assuming a 5 day work
> > week and an 8 hour work day.
> >
> > I suggest changing the REWIND option to NOREWIND. From the
> > documentation: "NOREWIND -- Specifies that SQL Server will keep the
> > tape open after the backup operation. You can use this option to help
> > improve performance when performing multiple backup operations to a
> > tape."
> >
> > Roy Harvey
> > Beacon Falls, CT
> >
> > On Wed, 1 Aug 2007 03:44:00 -0700, Methodology
> >
> >
> >
> > <Methodol...@.discussions.microsoft.com> wrote:
> > >Hi
> >
> > >I run a full backup once a month and a transaction log backup every 10 mins
> > >through working hours for the remainder of the month until the backup is
> > >re-initialised at th start of the following month.
> >
> > >I backup to virtual sql disk object devices.
> >
> > >As the month progresses the backup takes longer and longer whilst the amount
> > >of data being backed up every 10 minutes cycle is always roughly the same.
> > >its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
> > >month.
> >
> > >I noticed that NOSKIP was being used in the command - i changed this to
> > >SKIP, but it made no difference in the amount of time the backup takes. Isnt
> > >this suppoed to stop some sort of integrity scan on all other backup sets in
> > >the archive?
> >
> > >full syntax of the backup command that runs now is:
> >
> > >BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
> > >NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
> >
> > >but as I say, the SKIP command instead of NOSKIP makes no difference.
> >
> > >whats going on here?
> >
> > >any help appreciated.
> >
> > >Thanks
> >
> > >Alastair.- Hide quoted text -
> >
> > - Show quoted text -
> I dont have much idea about tapes but in BOL it says that
> NOREWIND implies NOUNLOAD, and these options are incompatible within a
> single BACKUP statement
> and Methodology has specified also NOUNLOAD in the script.
>|||NOUNLOAD/UNLOAD and REWIND/NOREWIND are tape-specific commands. If
you are not using tape they should not be in the BACKUP command at
all.
However, I am paranoid when it comes to dealing with backups. I don't
know that anyone has ever confirmed that there are no side effects to
using tape-specific features with a disk backup. So while I would
suggest removing the tape-specific parameters if you are not using
tape, I would combine that with starting a fresh backup file. It
would not hurt to backup to another device or file for the rest of the
month.
Roy Harvey
Beacon Falls, CT
On Wed, 1 Aug 2007 05:02:02 -0700, Methodology
<Methodology@.discussions.microsoft.com> wrote:
>thanks for your response Roy.
>if you go to server objects >backup devices you can configure a virtual
>'tape' that is in effect a file on the physical disk. this is what im backing
>up to. as far as I can tell the only differnece between using this method and
>backing up direct to disk is that you get to use a shorter name in the backup
>job command rather than a full file path.
>anyhoo
>may be this ISNT the only differnce. REWIND is default right? so maybe SQL
>is 'rewinding' the virtual tape? ive changed the job to norewind and ill see
>what happens in 2 backups time (20 mins.)
>cheers for the pointer
>"Roy Harvey" wrote:
>> Looking at the BACKUP commands you posted, which includes REWIND and
>> NOFORMAT, it looks like the backup goes to tape. When you referred to
>> "virtual sql disk object devices" I was not expecting that. Tapes are
>> not virtual disks, tapes are tapes.
>> I have seen that behavior when backing up directly to tape. The
>> problem in that case was reading the tape from the start to the point
>> where the prior backup ended. Lets consider that by the end of the
>> month there have been around 1000 log backups assuming a 5 day work
>> week and an 8 hour work day.
>> I suggest changing the REWIND option to NOREWIND. From the
>> documentation: "NOREWIND -- Specifies that SQL Server will keep the
>> tape open after the backup operation. You can use this option to help
>> improve performance when performing multiple backup operations to a
>> tape."
>> Roy Harvey
>> Beacon Falls, CT
>> On Wed, 1 Aug 2007 03:44:00 -0700, Methodology
>> <Methodology@.discussions.microsoft.com> wrote:
>> >Hi
>> >
>> >I run a full backup once a month and a transaction log backup every 10 mins
>> >through working hours for the remainder of the month until the backup is
>> >re-initialised at th start of the following month.
>> >
>> >I backup to virtual sql disk object devices.
>> >
>> >As the month progresses the backup takes longer and longer whilst the amount
>> >of data being backed up every 10 minutes cycle is always roughly the same.
>> >its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
>> >month.
>> >
>> >I noticed that NOSKIP was being used in the command - i changed this to
>> >SKIP, but it made no difference in the amount of time the backup takes. Isnt
>> >this suppoed to stop some sort of integrity scan on all other backup sets in
>> >the archive?
>> >
>> >full syntax of the backup command that runs now is:
>> >
>> >BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
>> >NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
>> >
>> >but as I say, the SKIP command instead of NOSKIP makes no difference.
>> >
>> >whats going on here?
>> >
>> >any help appreciated.
>> >
>> >Thanks
>> >
>> >Alastair.|||no difference - still taking 5 minutes...
heeeelllpppppp
"Methodology" wrote:
> fair point - ive taken out NOUNLOAD and changedto NOREWIND as the latter
> implies the former
> thanks
>
> "amish" wrote:
> > On Aug 1, 4:37 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> > > Looking at the BACKUP commands you posted, which includes REWIND and
> > > NOFORMAT, it looks like the backup goes to tape. When you referred to
> > > "virtual sql disk object devices" I was not expecting that. Tapes are
> > > not virtual disks, tapes are tapes.
> > >
> > > I have seen that behavior when backing up directly to tape. The
> > > problem in that case was reading the tape from the start to the point
> > > where the prior backup ended. Lets consider that by the end of the
> > > month there have been around 1000 log backups assuming a 5 day work
> > > week and an 8 hour work day.
> > >
> > > I suggest changing the REWIND option to NOREWIND. From the
> > > documentation: "NOREWIND -- Specifies that SQL Server will keep the
> > > tape open after the backup operation. You can use this option to help
> > > improve performance when performing multiple backup operations to a
> > > tape."
> > >
> > > Roy Harvey
> > > Beacon Falls, CT
> > >
> > > On Wed, 1 Aug 2007 03:44:00 -0700, Methodology
> > >
> > >
> > >
> > > <Methodol...@.discussions.microsoft.com> wrote:
> > > >Hi
> > >
> > > >I run a full backup once a month and a transaction log backup every 10 mins
> > > >through working hours for the remainder of the month until the backup is
> > > >re-initialised at th start of the following month.
> > >
> > > >I backup to virtual sql disk object devices.
> > >
> > > >As the month progresses the backup takes longer and longer whilst the amount
> > > >of data being backed up every 10 minutes cycle is always roughly the same.
> > > >its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
> > > >month.
> > >
> > > >I noticed that NOSKIP was being used in the command - i changed this to
> > > >SKIP, but it made no difference in the amount of time the backup takes. Isnt
> > > >this suppoed to stop some sort of integrity scan on all other backup sets in
> > > >the archive?
> > >
> > > >full syntax of the backup command that runs now is:
> > >
> > > >BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
> > > >NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
> > >
> > > >but as I say, the SKIP command instead of NOSKIP makes no difference.
> > >
> > > >whats going on here?
> > >
> > > >any help appreciated.
> > >
> > > >Thanks
> > >
> > > >Alastair.- Hide quoted text -
> > >
> > > - Show quoted text -
> >
> > I dont have much idea about tapes but in BOL it says that
> >
> > NOREWIND implies NOUNLOAD, and these options are incompatible within a
> > single BACKUP statement
> >
> > and Methodology has specified also NOUNLOAD in the script.
> >
> >|||I guess the answer is not to write so many backups to a single file.
Personally I don't think I would set up a backup scheme with one
monthly database backup and log backups every ten minutes. If a
database is active enough to justify a ten-minute interval for the
logs, I would backup the enitre database at least weekly, but more
likely daily. Of course I have no way of knowing your environment and
specific factors that went into the design.
Roy Harvey
Beacon Falls, CT
On Wed, 1 Aug 2007 05:40:02 -0700, Methodology
<Methodology@.discussions.microsoft.com> wrote:
>no difference - still taking 5 minutes...
>
>heeeelllpppppp
>
>
>"Methodology" wrote:
>> fair point - ive taken out NOUNLOAD and changedto NOREWIND as the latter
>> implies the former
>> thanks
>>
>> "amish" wrote:
>> > On Aug 1, 4:37 pm, Roy Harvey <roy_har...@.snet.net> wrote:
>> > > Looking at the BACKUP commands you posted, which includes REWIND and
>> > > NOFORMAT, it looks like the backup goes to tape. When you referred to
>> > > "virtual sql disk object devices" I was not expecting that. Tapes are
>> > > not virtual disks, tapes are tapes.
>> > >
>> > > I have seen that behavior when backing up directly to tape. The
>> > > problem in that case was reading the tape from the start to the point
>> > > where the prior backup ended. Lets consider that by the end of the
>> > > month there have been around 1000 log backups assuming a 5 day work
>> > > week and an 8 hour work day.
>> > >
>> > > I suggest changing the REWIND option to NOREWIND. From the
>> > > documentation: "NOREWIND -- Specifies that SQL Server will keep the
>> > > tape open after the backup operation. You can use this option to help
>> > > improve performance when performing multiple backup operations to a
>> > > tape."
>> > >
>> > > Roy Harvey
>> > > Beacon Falls, CT
>> > >
>> > > On Wed, 1 Aug 2007 03:44:00 -0700, Methodology
>> > >
>> > >
>> > >
>> > > <Methodol...@.discussions.microsoft.com> wrote:
>> > > >Hi
>> > >
>> > > >I run a full backup once a month and a transaction log backup every 10 mins
>> > > >through working hours for the remainder of the month until the backup is
>> > > >re-initialised at th start of the following month.
>> > >
>> > > >I backup to virtual sql disk object devices.
>> > >
>> > > >As the month progresses the backup takes longer and longer whilst the amount
>> > > >of data being backed up every 10 minutes cycle is always roughly the same.
>> > > >its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
>> > > >month.
>> > >
>> > > >I noticed that NOSKIP was being used in the command - i changed this to
>> > > >SKIP, but it made no difference in the amount of time the backup takes. Isnt
>> > > >this suppoed to stop some sort of integrity scan on all other backup sets in
>> > > >the archive?
>> > >
>> > > >full syntax of the backup command that runs now is:
>> > >
>> > > >BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
>> > > >NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
>> > >
>> > > >but as I say, the SKIP command instead of NOSKIP makes no difference.
>> > >
>> > > >whats going on here?
>> > >
>> > > >any help appreciated.
>> > >
>> > > >Thanks
>> > >
>> > > >Alastair.- Hide quoted text -
>> > >
>> > > - Show quoted text -
>> >
>> > I dont have much idea about tapes but in BOL it says that
>> >
>> > NOREWIND implies NOUNLOAD, and these options are incompatible within a
>> > single BACKUP statement
>> >
>> > and Methodology has specified also NOUNLOAD in the script.
>> >
>> >

NOSKIP & SKIP make no difference to the amount of time backup take

Monday, March 26, 2012

norewind on SQL 2k backup

I could use some help on this one.
I have an SQL 2k server set up to do a scheduled backup to a tape drive.
The backup is part of a maintenance plan if it matters. It is backing up
around 50 databases, and between each backup, the server dismounts and
remounts the tape. This ends up creating a huge delay, and a backup can
easily run for 10 to 15 hours. I also think that it rewinds the tape during
each dismount/mount and overwrites the previous database. I found some
information about using the "norewind" option to fix this, but I have no
idea how to use it. The maintenance plan creates a job with one step, and
it runs the command:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
B02C357F-6496-4B99-B899-4F4D57EE789B -Rpt "E:\BACKUP_LOG\ALL CNS
DATA4.txt" -DelTxtRpt 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia
TAPE -BkUpDB "\\.\Tape0" '
Can I simply specify '-NOREWIND' as an option here? If not, how or where
would I specify this setting?
I'd really appreciate any information. Thanks in advance for any help.
JasonI never do a SQL backup to tape. Why does the server dismount and remount
the tape? Why don't we make it simple: use SQL to backup DB to disk; backup
these backup files to tape using NT backup or BackupExec (not the SQL module
of BackupExec). Depend on the amount of data you need to backup to tape,
you will a DLT tape, a SDLT tape, or a tape library. We have an HP
StorageWorks MSL5026. It backs up SQL backup files with the rate =335MB/minute.
"j" <jason1@.thecolossal.com> wrote in message
news:AdW_a.3486$S_.2867@.fed1read01...
> I could use some help on this one.
> I have an SQL 2k server set up to do a scheduled backup to a tape drive.
> The backup is part of a maintenance plan if it matters. It is backing up
> around 50 databases, and between each backup, the server dismounts and
> remounts the tape. This ends up creating a huge delay, and a backup can
> easily run for 10 to 15 hours. I also think that it rewinds the tape
during
> each dismount/mount and overwrites the previous database. I found some
> information about using the "norewind" option to fix this, but I have no
> idea how to use it. The maintenance plan creates a job with one step, and
> it runs the command:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> B02C357F-6496-4B99-B899-4F4D57EE789B -Rpt "E:\BACKUP_LOG\ALL CNS
> DATA4.txt" -DelTxtRpt 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia
> TAPE -BkUpDB "\\.\Tape0" '
> Can I simply specify '-NOREWIND' as an option here? If not, how or where
> would I specify this setting?
>
> I'd really appreciate any information. Thanks in advance for any help.
> Jason
>|||That suggestion has come up at work. I think that's what we'll end up
doing. It should make life a lot easier.
Thanks,
Jason
"Flicker" <hthan@.superioraccess.com> wrote in message
news:OqLvOV1YDHA.2308@.TK2MSFTNGP12.phx.gbl...
> I never do a SQL backup to tape. Why does the server dismount and remount
> the tape? Why don't we make it simple: use SQL to backup DB to disk;
backup
> these backup files to tape using NT backup or BackupExec (not the SQL
module
> of BackupExec). Depend on the amount of data you need to backup to tape,
> you will a DLT tape, a SDLT tape, or a tape library. We have an HP
> StorageWorks MSL5026. It backs up SQL backup files with the rate => 335MB/minute.
>
> "j" <jason1@.thecolossal.com> wrote in message
> news:AdW_a.3486$S_.2867@.fed1read01...
> > I could use some help on this one.
> >
> > I have an SQL 2k server set up to do a scheduled backup to a tape drive.
> > The backup is part of a maintenance plan if it matters. It is backing
up
> > around 50 databases, and between each backup, the server dismounts and
> > remounts the tape. This ends up creating a huge delay, and a backup can
> > easily run for 10 to 15 hours. I also think that it rewinds the tape
> during
> > each dismount/mount and overwrites the previous database. I found some
> > information about using the "norewind" option to fix this, but I have no
> > idea how to use it. The maintenance plan creates a job with one step,
and
> > it runs the command:
> >
> > EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> > B02C357F-6496-4B99-B899-4F4D57EE789B -Rpt "E:\BACKUP_LOG\ALL CNS
> > DATA4.txt" -DelTxtRpt 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia
> > TAPE -BkUpDB "\\.\Tape0" '
> >
> > Can I simply specify '-NOREWIND' as an option here? If not, how or
where
> > would I specify this setting?
> >
> >
> > I'd really appreciate any information. Thanks in advance for any help.
> >
> > Jason
> >
> >
>

Friday, March 23, 2012

Noob here, dumb, but quick question

Ok, I know a LITTLE about SQL 2000. I am just starting to get my feet wet in the area. I know how to install SQL, backup DB's and Restore them. Can even do them over the network now. YAY ME! Anyway, here is my question. I have a production server with 4 RAID arrays. I have one for my OS, one for the Main SQL DB's (heavy transaction DB's), one for my log files, and one that is supposed to be for my temp DB. Well when I installed SQL it asked me for the default data path, which would be where my main SQL db's go. I dont remember it asking me where I want the temp DB to go. How do I change the location of the tempdb.mdf and tempdb.ldf to the drive arrays I want them to go to, even though I have already installed SQL.

Thanks. And sorry ahead of time for the noobiness of the question. I did do a search first, but didnt really see anything that would help me.To change where the data files are located for a given database, just right-click the database and change the Data Files and Transaction Log 'Properties'.

With that said, I don't remember if you can change the data file location for 'system' databases. So you might want to check into that...even so, I wouldn't expect any speed benefit from move the tempdb to a different location.

BTW, good question with some solid background for someone claiming to be noob.

Late,

Alexander|||We use this script for DR to relocate the tempdb to an array large enough to allow it to grow as needed. Change the paths and size to fit your situation, and don't forget to stop and restart the sever after altering the database.

use tempdb
alter database tempdb modify file (name = tempdev,
filename = 'T:\Program Files\Microsoft SQL Server\MSSQL\Data\tempdb.mdf', size = 10000 MB)
alter database tempdb modify file (name = templog,
filename = 'T:\Program Files\Microsoft SQL Server\MSSQL\Data\templog.ldf')|||http://support.microsoft.com/default.aspx?scid=kb;en-us;224071

As for me .. this link is permanently in my IE favourites folder ...

Wednesday, March 21, 2012

non-logged operations

SQL 7.0 SP1 on NT 4 SP6a Cluster.
We have a DB with the option "select into/bulk copy" set to true.
When non-logged operations happens, the backup of the transaction log fail, so we do a differential backup instead and everything is ok.
My questions are:

UPGRADING to SQL 2K,

1 Do we have to choose the "Bulk-Logged" recovery model?
and if so,
2 Do we still need to run differential backup because backup of t-log will fail as on SQL 7.0?
Can somebody help me with this?
TIA.
Franco
:cool:In BULK_LOGGED recovery mode certain bulk operations are only minimally logged increasing performance and decreasing log size. Performing builk operations do not require you to process backup, since they are actually logged thoughout BCM page. For every datafile there is one BCM page where one bit correspond to extent modification by minimally logged operation. When you perform tran log backup, sql scans BCM and backups these extents. As a result you are getting larger tranlog backups then in FULL mode.

HTH,
OBRP

Saturday, February 25, 2012

No truncating.

Hello eveybody !
I have a small problem with my logs on sql server .
Even after a backup (lod and/or db), server seems to not truncate his
logs ... so they're growing each days, and need to be deleted
manually.
Any ideas ?Hi,
After backup the physical LDF file will not get reduced automatcally. But
the logical space will be reduced by looking into
dbcc sqlperf(logspace)
To shrink the physical ldf file after the log backup use dbcc shrinkfile
command. See the dbcc shrinkfile command in boks online.
Thanks
Hari
MCDBA
"Frater" <None@.legioobscurantis.com> wrote in message
news:mb2kg059uld5st2p9tfvsoo34evdfcl3gg@.
4ax.com...
> Hello eveybody !
> I have a small problem with my logs on sql server .
> Even after a backup (lod and/or db), server seems to not truncate his
> logs ... so they're growing each days, and need to be deleted
> manually.
> Any ideas ?|||Can't you just set the database to autoshrink with:
sp_dboption database_name, 'autoshrink' TRUE
In article <#xXRVRhdEHA.3988@.tk2msftngp13.phx.gbl>,
hari_prasad_k@.hotmail.com says...[vbcol=seagreen]
> After backup the physical LDF file will not get reduced automatcally. But
> the logical space will be reduced by looking into
> dbcc sqlperf(logspace)
> To shrink the physical ldf file after the log backup use dbcc shrinkfile
> command. See the dbcc shrinkfile command in boks online.
> "Frater" <None@.legioobscurantis.com> wrote in message
> news:mb2kg059uld5st2p9tfvsoo34evdfcl3gg@.
4ax.com...|||Frater,
Truncating and shrinking are two different things.
Truncating the log (logically) cleans up space which was previously used in
the log so that it can be re-used. The log is a circular file which tries to
re-use the same space over and over. Truncating the log does NOT change the
physical size of the log... If the log is NOT truncated, then space can not
be re-used, so the log will grow to acquire the necessary space. Truncating
the log occurs automatically if the database is in SIMPLE recovery mode. The
log is truncated during a transaction log backup as well..
Shrinking the log can be done AFTER the log has been truncated. Use DBCC
Shrinkdatabase or DBCC Shrinkfile to physical reduce the log file size...
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
"Frater" <None@.legioobscurantis.com> wrote in message
news:mb2kg059uld5st2p9tfvsoo34evdfcl3gg@.
4ax.com...
> Hello eveybody !
> I have a small problem with my logs on sql server .
> Even after a backup (lod and/or db), server seems to not truncate his
> logs ... so they're growing each days, and need to be deleted
> manually.
> Any ideas ?|||Hi Brad
Shrinking the datasbase will shrink ALL the files, not just the log files,
and the autoshrink option will do this every 30 minutes.
It is incredibly resource intensive, as it tries to move all data in the
files to other places in the files, and all kinds of adjustments to indexes
might need to be done as a result.
Autoshrink is definitely NOT recommended for a production system.
The log file must be managed separately, as per the other suggestions in
this thread.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Brad Murray" <brad@.seesigifthere.com> wrote in message
news:MPG.1b73f47b2134b1ff989682@.news...[vbcol=seagreen]
> Can't you just set the database to autoshrink with:
> sp_dboption database_name, 'autoshrink' TRUE
> In article <#xXRVRhdEHA.3988@.tk2msftngp13.phx.gbl>,
> hari_prasad_k@.hotmail.com says...
But[vbcol=seagreen]

No truncating.

Hello eveybody !
I have a small problem with my logs on sql server .
Even after a backup (lod and/or db), server seems to not truncate his
logs ... so they're growing each days, and need to be deleted
manually.
Any ideas ?Hi,
After backup the physical LDF file will not get reduced automatcally. But
the logical space will be reduced by looking into
dbcc sqlperf(logspace)
To shrink the physical ldf file after the log backup use dbcc shrinkfile
command. See the dbcc shrinkfile command in boks online.
Thanks
Hari
MCDBA
"Frater" <None@.legioobscurantis.com> wrote in message
news:mb2kg059uld5st2p9tfvsoo34evdfcl3gg@.4ax.com...
> Hello eveybody !
> I have a small problem with my logs on sql server .
> Even after a backup (lod and/or db), server seems to not truncate his
> logs ... so they're growing each days, and need to be deleted
> manually.
> Any ideas ?|||Can't you just set the database to autoshrink with:
sp_dboption database_name, 'autoshrink' TRUE
In article <#xXRVRhdEHA.3988@.tk2msftngp13.phx.gbl>,
hari_prasad_k@.hotmail.com says...
> After backup the physical LDF file will not get reduced automatcally. But
> the logical space will be reduced by looking into
> dbcc sqlperf(logspace)
> To shrink the physical ldf file after the log backup use dbcc shrinkfile
> command. See the dbcc shrinkfile command in boks online.
> "Frater" <None@.legioobscurantis.com> wrote in message
> news:mb2kg059uld5st2p9tfvsoo34evdfcl3gg@.4ax.com...
> > Hello eveybody !
> >
> > I have a small problem with my logs on sql server .
> >
> > Even after a backup (lod and/or db), server seems to not truncate his
> > logs ... so they're growing each days, and need to be deleted
> > manually.
> >
> > Any ideas ?|||Frater,
Truncating and shrinking are two different things.
Truncating the log (logically) cleans up space which was previously used in
the log so that it can be re-used. The log is a circular file which tries to
re-use the same space over and over. Truncating the log does NOT change the
physical size of the log... If the log is NOT truncated, then space can not
be re-used, so the log will grow to acquire the necessary space. Truncating
the log occurs automatically if the database is in SIMPLE recovery mode. The
log is truncated during a transaction log backup as well..
Shrinking the log can be done AFTER the log has been truncated. Use DBCC
Shrinkdatabase or DBCC Shrinkfile to physical reduce the log file size...
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
"Frater" <None@.legioobscurantis.com> wrote in message
news:mb2kg059uld5st2p9tfvsoo34evdfcl3gg@.4ax.com...
> Hello eveybody !
> I have a small problem with my logs on sql server .
> Even after a backup (lod and/or db), server seems to not truncate his
> logs ... so they're growing each days, and need to be deleted
> manually.
> Any ideas ?|||Hi Brad
Shrinking the datasbase will shrink ALL the files, not just the log files,
and the autoshrink option will do this every 30 minutes.
It is incredibly resource intensive, as it tries to move all data in the
files to other places in the files, and all kinds of adjustments to indexes
might need to be done as a result.
Autoshrink is definitely NOT recommended for a production system.
The log file must be managed separately, as per the other suggestions in
this thread.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Brad Murray" <brad@.seesigifthere.com> wrote in message
news:MPG.1b73f47b2134b1ff989682@.news...
> Can't you just set the database to autoshrink with:
> sp_dboption database_name, 'autoshrink' TRUE
> In article <#xXRVRhdEHA.3988@.tk2msftngp13.phx.gbl>,
> hari_prasad_k@.hotmail.com says...
> >
> > After backup the physical LDF file will not get reduced automatcally.
But
> > the logical space will be reduced by looking into
> >
> > dbcc sqlperf(logspace)
> >
> > To shrink the physical ldf file after the log backup use dbcc shrinkfile
> > command. See the dbcc shrinkfile command in boks online.
> >
> > "Frater" <None@.legioobscurantis.com> wrote in message
> > news:mb2kg059uld5st2p9tfvsoo34evdfcl3gg@.4ax.com...
> > > Hello eveybody !
> > >
> > > I have a small problem with my logs on sql server .
> > >
> > > Even after a backup (lod and/or db), server seems to not truncate his
> > > logs ... so they're growing each days, and need to be deleted
> > > manually.
> > >
> > > Any ideas ?

No truncating.

Hello eveybody !
I have a small problem with my logs on sql server .
Even after a backup (lod and/or db), server seems to not truncate his
logs ... so they're growing each days, and need to be deleted
manually.
Any ideas ?
Hi,
After backup the physical LDF file will not get reduced automatcally. But
the logical space will be reduced by looking into
dbcc sqlperf(logspace)
To shrink the physical ldf file after the log backup use dbcc shrinkfile
command. See the dbcc shrinkfile command in boks online.
Thanks
Hari
MCDBA
"Frater" <None@.legioobscurantis.com> wrote in message
news:mb2kg059uld5st2p9tfvsoo34evdfcl3gg@.4ax.com...
> Hello eveybody !
> I have a small problem with my logs on sql server .
> Even after a backup (lod and/or db), server seems to not truncate his
> logs ... so they're growing each days, and need to be deleted
> manually.
> Any ideas ?
|||Can't you just set the database to autoshrink with:
sp_dboption database_name, 'autoshrink' TRUE
In article <#xXRVRhdEHA.3988@.tk2msftngp13.phx.gbl>,
hari_prasad_k@.hotmail.com says...[vbcol=seagreen]
> After backup the physical LDF file will not get reduced automatcally. But
> the logical space will be reduced by looking into
> dbcc sqlperf(logspace)
> To shrink the physical ldf file after the log backup use dbcc shrinkfile
> command. See the dbcc shrinkfile command in boks online.
> "Frater" <None@.legioobscurantis.com> wrote in message
> news:mb2kg059uld5st2p9tfvsoo34evdfcl3gg@.4ax.com...
|||Frater,
Truncating and shrinking are two different things.
Truncating the log (logically) cleans up space which was previously used in
the log so that it can be re-used. The log is a circular file which tries to
re-use the same space over and over. Truncating the log does NOT change the
physical size of the log... If the log is NOT truncated, then space can not
be re-used, so the log will grow to acquire the necessary space. Truncating
the log occurs automatically if the database is in SIMPLE recovery mode. The
log is truncated during a transaction log backup as well..
Shrinking the log can be done AFTER the log has been truncated. Use DBCC
Shrinkdatabase or DBCC Shrinkfile to physical reduce the log file size...
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
"Frater" <None@.legioobscurantis.com> wrote in message
news:mb2kg059uld5st2p9tfvsoo34evdfcl3gg@.4ax.com...
> Hello eveybody !
> I have a small problem with my logs on sql server .
> Even after a backup (lod and/or db), server seems to not truncate his
> logs ... so they're growing each days, and need to be deleted
> manually.
> Any ideas ?
|||Hi Brad
Shrinking the datasbase will shrink ALL the files, not just the log files,
and the autoshrink option will do this every 30 minutes.
It is incredibly resource intensive, as it tries to move all data in the
files to other places in the files, and all kinds of adjustments to indexes
might need to be done as a result.
Autoshrink is definitely NOT recommended for a production system.
The log file must be managed separately, as per the other suggestions in
this thread.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Brad Murray" <brad@.seesigifthere.com> wrote in message
news:MPG.1b73f47b2134b1ff989682@.news...[vbcol=seagreen]
> Can't you just set the database to autoshrink with:
> sp_dboption database_name, 'autoshrink' TRUE
> In article <#xXRVRhdEHA.3988@.tk2msftngp13.phx.gbl>,
> hari_prasad_k@.hotmail.com says...
But[vbcol=seagreen]

No Truncate Option in Log Backup

I need to peform a log backup with no truncate. My regular maintenance
plan runs like this:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
-DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
Where in here do I add the no truncate option?Techhead wrote:
> I need to peform a log backup with no truncate. My regular maintenance
> plan runs like this:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
>
> Where in here do I add the no truncate option?
>
You don't. You'll have to run the BACKUP command directly, not from a
maintenance plan.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Also, why would you (Techhead) want to do a log backup using NO_TRUNCATE on a regular basis? This
option is only there in order for you to do the last log backup on a corrupt database. See:
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:44E33CF4.6030307@.realsqlguy.com...
> Techhead wrote:
>> I need to peform a log backup with no truncate. My regular maintenance
>> plan runs like this:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
>> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
>> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
>> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
>>
>> Where in here do I add the no truncate option?
> You don't. You'll have to run the BACKUP command directly, not from a maintenance plan.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Why would I not want to truncate? Because I have a third party backup
solution doing it for me and it will not work if my maintenance plan
and 3rd party backup truncate at the same time. I still want to do a
full backup in SQL but not truncate... maybe a copy of the tran logs
will work, but no truncate.
Tibor Karaszi wrote:
> Also, why would you (Techhead) want to do a log backup using NO_TRUNCATE on a regular basis? This
> option is only there in order for you to do the last log backup on a corrupt database. See:
> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:44E33CF4.6030307@.realsqlguy.com...
> > Techhead wrote:
> >> I need to peform a log backup with no truncate. My regular maintenance
> >> plan runs like this:
> >>
> >> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> >> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
> >> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
> >> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
> >> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
> >>
> >>
> >> Where in here do I add the no truncate option?
> >>
> >
> > You don't. You'll have to run the BACKUP command directly, not from a maintenance plan.
What is the backup command to use?
> >
> >
> > --
> > Tracy McKibben
> > MCDBA
> > http://www.realsqlguy.com|||Techhead wrote:
> Why would I not want to truncate? Because I have a third party backup
> solution doing it for me and it will not work if my maintenance plan
> and 3rd party backup truncate at the same time. I still want to do a
> full backup in SQL but not truncate... maybe a copy of the tran logs
> will work, but no truncate.
>
Just curious, have you attempted to RESTORE a complete database,
including these transaction log backups? I don't fully understand why
you have a third-party solution in place, AND you're using the SQL
BACKUP command.
Most folks here will tell you to avoid third-party backup agents for
your databases, and instead use the native BACKUP and RESTORE commands
to dump the database to disk files, and then backup those files to tape.
The third-party tools can be unreliable and difficult to restore from,
particularly a point-in-time restore.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||> Why would I not want to truncate? Because I have a third party backup
> solution doing it for me and it will not work if my maintenance plan
> and 3rd party backup truncate at the same time.
I strongly encourage you to read the article I posted a link to. You will see that even with
NO_TRUNCATE, you cannot "skip" restoring one of the log backup "in between". The article was written
for SQL Server 2000, I haven't tried it on 2005 yet.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Techhead" <jorgenson.b@.gmail.com> wrote in message
news:1156284873.311342.152930@.p79g2000cwp.googlegroups.com...
> Why would I not want to truncate? Because I have a third party backup
> solution doing it for me and it will not work if my maintenance plan
> and 3rd party backup truncate at the same time. I still want to do a
> full backup in SQL but not truncate... maybe a copy of the tran logs
> will work, but no truncate.
>
> Tibor Karaszi wrote:
>> Also, why would you (Techhead) want to do a log backup using NO_TRUNCATE on a regular basis? This
>> option is only there in order for you to do the last log backup on a corrupt database. See:
>> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:44E33CF4.6030307@.realsqlguy.com...
>> > Techhead wrote:
>> >> I need to peform a log backup with no truncate. My regular maintenance
>> >> plan runs like this:
>> >>
>> >> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> >> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
>> >> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
>> >> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
>> >> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
>> >>
>> >>
>> >> Where in here do I add the no truncate option?
>> >>
>> >
>> > You don't. You'll have to run the BACKUP command directly, not from a maintenance plan.
>
> What is the backup command to use?
>> >
>> >
>> > --
>> > Tracy McKibben
>> > MCDBA
>> > http://www.realsqlguy.com
>|||But I don't want to restore using SQL, I want to use my 3rd party
backup solution to do the restore. THis solution can restore SQL DB's
to point-in-time using truncated logs that the software backed up. i
just don't want SQL truncating when my backup software does.
Tibor Karaszi wrote:
> > Why would I not want to truncate? Because I have a third party backup
> > solution doing it for me and it will not work if my maintenance plan
> > and 3rd party backup truncate at the same time.
> I strongly encourage you to read the article I posted a link to. You will see that even with
> NO_TRUNCATE, you cannot "skip" restoring one of the log backup "in between". The article was written
> for SQL Server 2000, I haven't tried it on 2005 yet.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Techhead" <jorgenson.b@.gmail.com> wrote in message
> news:1156284873.311342.152930@.p79g2000cwp.googlegroups.com...
> > Why would I not want to truncate? Because I have a third party backup
> > solution doing it for me and it will not work if my maintenance plan
> > and 3rd party backup truncate at the same time. I still want to do a
> > full backup in SQL but not truncate... maybe a copy of the tran logs
> > will work, but no truncate.
> >
> >
> > Tibor Karaszi wrote:
> >> Also, why would you (Techhead) want to do a log backup using NO_TRUNCATE on a regular basis? This
> >> option is only there in order for you to do the last log backup on a corrupt database. See:
> >> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:44E33CF4.6030307@.realsqlguy.com...
> >> > Techhead wrote:
> >> >> I need to peform a log backup with no truncate. My regular maintenance
> >> >> plan runs like this:
> >> >>
> >> >> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> >> >> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
> >> >> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
> >> >> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
> >> >> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
> >> >>
> >> >>
> >> >> Where in here do I add the no truncate option?
> >> >>
> >> >
> >> > You don't. You'll have to run the BACKUP command directly, not from a maintenance plan.
> >
> >
> > What is the backup command to use?
> >> >
> >> >
> >> > --
> >> > Tracy McKibben
> >> > MCDBA
> >> > http://www.realsqlguy.com
> >|||Lets see if I get this right. I might have missed what you are doing. You want to do something like:
1 Backup database (disk or tape)
2 Tape backup log
3 Disk backup log with NO_TRUNCATE
4 Tape backup log
5 Disk backup log with NO_TRUNCATE
For above scenario, you will be able to restore 1, 2, 3, but not further. See
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp for more details. Can you please
elaborate if above is not your scenario.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Techhead" <jorgenson.b@.gmail.com> wrote in message
news:1157648034.276585.67180@.b28g2000cwb.googlegroups.com...
> But I don't want to restore using SQL, I want to use my 3rd party
> backup solution to do the restore. THis solution can restore SQL DB's
> to point-in-time using truncated logs that the software backed up. i
> just don't want SQL truncating when my backup software does.
> Tibor Karaszi wrote:
>> > Why would I not want to truncate? Because I have a third party backup
>> > solution doing it for me and it will not work if my maintenance plan
>> > and 3rd party backup truncate at the same time.
>> I strongly encourage you to read the article I posted a link to. You will see that even with
>> NO_TRUNCATE, you cannot "skip" restoring one of the log backup "in between". The article was
>> written
>> for SQL Server 2000, I haven't tried it on 2005 yet.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Techhead" <jorgenson.b@.gmail.com> wrote in message
>> news:1156284873.311342.152930@.p79g2000cwp.googlegroups.com...
>> > Why would I not want to truncate? Because I have a third party backup
>> > solution doing it for me and it will not work if my maintenance plan
>> > and 3rd party backup truncate at the same time. I still want to do a
>> > full backup in SQL but not truncate... maybe a copy of the tran logs
>> > will work, but no truncate.
>> >
>> >
>> > Tibor Karaszi wrote:
>> >> Also, why would you (Techhead) want to do a log backup using NO_TRUNCATE on a regular basis?
>> >> This
>> >> option is only there in order for you to do the last log backup on a corrupt database. See:
>> >> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> >> news:44E33CF4.6030307@.realsqlguy.com...
>> >> > Techhead wrote:
>> >> >> I need to peform a log backup with no truncate. My regular maintenance
>> >> >> plan runs like this:
>> >> >>
>> >> >> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> >> >> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
>> >> >> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
>> >> >> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
>> >> >> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
>> >> >>
>> >> >>
>> >> >> Where in here do I add the no truncate option?
>> >> >>
>> >> >
>> >> > You don't. You'll have to run the BACKUP command directly, not from a maintenance plan.
>> >
>> >
>> > What is the backup command to use?
>> >> >
>> >> >
>> >> > --
>> >> > Tracy McKibben
>> >> > MCDBA
>> >> > http://www.realsqlguy.com
>> >
>|||Actually this is what I want:
1. Backup Database to Disk
2. Backup Log to Disk NO_TRUNCATE
3. Backup Database to Tape
4. Backup Log to Tape TRUNCATE
Tibor Karaszi wrote:
> Lets see if I get this right. I might have missed what you are doing. You want to do something like:
> 1 Backup database (disk or tape)
> 2 Tape backup log
> 3 Disk backup log with NO_TRUNCATE
> 4 Tape backup log
> 5 Disk backup log with NO_TRUNCATE
> For above scenario, you will be able to restore 1, 2, 3, but not further. See
> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp for more details. Can you please
> elaborate if above is not your scenario.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Techhead" <jorgenson.b@.gmail.com> wrote in message
> news:1157648034.276585.67180@.b28g2000cwb.googlegroups.com...
> > But I don't want to restore using SQL, I want to use my 3rd party
> > backup solution to do the restore. THis solution can restore SQL DB's
> > to point-in-time using truncated logs that the software backed up. i
> > just don't want SQL truncating when my backup software does.
> >
> > Tibor Karaszi wrote:
> >> > Why would I not want to truncate? Because I have a third party backup
> >> > solution doing it for me and it will not work if my maintenance plan
> >> > and 3rd party backup truncate at the same time.
> >>
> >> I strongly encourage you to read the article I posted a link to. You will see that even with
> >> NO_TRUNCATE, you cannot "skip" restoring one of the log backup "in between". The article was
> >> written
> >> for SQL Server 2000, I haven't tried it on 2005 yet.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Techhead" <jorgenson.b@.gmail.com> wrote in message
> >> news:1156284873.311342.152930@.p79g2000cwp.googlegroups.com...
> >> > Why would I not want to truncate? Because I have a third party backup
> >> > solution doing it for me and it will not work if my maintenance plan
> >> > and 3rd party backup truncate at the same time. I still want to do a
> >> > full backup in SQL but not truncate... maybe a copy of the tran logs
> >> > will work, but no truncate.
> >> >
> >> >
> >> > Tibor Karaszi wrote:
> >> >> Also, why would you (Techhead) want to do a log backup using NO_TRUNCATE on a regular basis?
> >> >> This
> >> >> option is only there in order for you to do the last log backup on a corrupt database. See:
> >> >> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> >> >> news:44E33CF4.6030307@.realsqlguy.com...
> >> >> > Techhead wrote:
> >> >> >> I need to peform a log backup with no truncate. My regular maintenance
> >> >> >> plan runs like this:
> >> >> >>
> >> >> >> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> >> >> >> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
> >> >> >> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
> >> >> >> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
> >> >> >> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
> >> >> >>
> >> >> >>
> >> >> >> Where in here do I add the no truncate option?
> >> >> >>
> >> >> >
> >> >> > You don't. You'll have to run the BACKUP command directly, not from a maintenance plan.
> >> >
> >> >
> >> > What is the backup command to use?
> >> >> >
> >> >> >
> >> >> > --
> >> >> > Tracy McKibben
> >> >> > MCDBA
> >> >> > http://www.realsqlguy.com
> >> >
> >|||So you will only have one transaction log backup between each database backup?
Why do you want to use NO_TRUNCATE for the log backup to disk?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Techhead" <jorgenson.b@.gmail.com> wrote in message
news:1158096627.443342.147600@.b28g2000cwb.googlegroups.com...
> Actually this is what I want:
> 1. Backup Database to Disk
> 2. Backup Log to Disk NO_TRUNCATE
> 3. Backup Database to Tape
> 4. Backup Log to Tape TRUNCATE
>
> Tibor Karaszi wrote:
>> Lets see if I get this right. I might have missed what you are doing. You want to do something
>> like:
>> 1 Backup database (disk or tape)
>> 2 Tape backup log
>> 3 Disk backup log with NO_TRUNCATE
>> 4 Tape backup log
>> 5 Disk backup log with NO_TRUNCATE
>> For above scenario, you will be able to restore 1, 2, 3, but not further. See
>> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp for more details. Can you please
>> elaborate if above is not your scenario.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Techhead" <jorgenson.b@.gmail.com> wrote in message
>> news:1157648034.276585.67180@.b28g2000cwb.googlegroups.com...
>> > But I don't want to restore using SQL, I want to use my 3rd party
>> > backup solution to do the restore. THis solution can restore SQL DB's
>> > to point-in-time using truncated logs that the software backed up. i
>> > just don't want SQL truncating when my backup software does.
>> >
>> > Tibor Karaszi wrote:
>> >> > Why would I not want to truncate? Because I have a third party backup
>> >> > solution doing it for me and it will not work if my maintenance plan
>> >> > and 3rd party backup truncate at the same time.
>> >>
>> >> I strongly encourage you to read the article I posted a link to. You will see that even with
>> >> NO_TRUNCATE, you cannot "skip" restoring one of the log backup "in between". The article was
>> >> written
>> >> for SQL Server 2000, I haven't tried it on 2005 yet.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "Techhead" <jorgenson.b@.gmail.com> wrote in message
>> >> news:1156284873.311342.152930@.p79g2000cwp.googlegroups.com...
>> >> > Why would I not want to truncate? Because I have a third party backup
>> >> > solution doing it for me and it will not work if my maintenance plan
>> >> > and 3rd party backup truncate at the same time. I still want to do a
>> >> > full backup in SQL but not truncate... maybe a copy of the tran logs
>> >> > will work, but no truncate.
>> >> >
>> >> >
>> >> > Tibor Karaszi wrote:
>> >> >> Also, why would you (Techhead) want to do a log backup using NO_TRUNCATE on a regular
>> >> >> basis?
>> >> >> This
>> >> >> option is only there in order for you to do the last log backup on a corrupt database. See:
>> >> >> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://www.solidqualitylearning.com/
>> >> >>
>> >> >>
>> >> >> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> >> >> news:44E33CF4.6030307@.realsqlguy.com...
>> >> >> > Techhead wrote:
>> >> >> >> I need to peform a log backup with no truncate. My regular maintenance
>> >> >> >> plan runs like this:
>> >> >> >>
>> >> >> >> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> >> >> >> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
>> >> >> >> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
>> >> >> >> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
>> >> >> >> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
>> >> >> >>
>> >> >> >>
>> >> >> >> Where in here do I add the no truncate option?
>> >> >> >>
>> >> >> >
>> >> >> > You don't. You'll have to run the BACKUP command directly, not from a maintenance plan.
>> >> >
>> >> >
>> >> > What is the backup command to use?
>> >> >> >
>> >> >> >
>> >> >> > --
>> >> >> > Tracy McKibben
>> >> >> > MCDBA
>> >> >> > http://www.realsqlguy.com
>> >> >
>> >
>|||Tibor Karaszi wrote:
> So you will only have one transaction log backup between each database backup?
Actually, after a disk backup, my tape backup will back up the log and
truncate every 4 hours.
> Why do you want to use NO_TRUNCATE for the log backup to disk?
Because, as I stated earlier, the tape backup does the truncation. Why
would I want my disk backup and tape backup both truncating? You would
have to restore from both disk and tape in order to do a complete log
restore to point-in-time.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Techhead" <jorgenson.b@.gmail.com> wrote in message
> news:1158096627.443342.147600@.b28g2000cwb.googlegroups.com...
> > Actually this is what I want:
> >
> > 1. Backup Database to Disk
> > 2. Backup Log to Disk NO_TRUNCATE
> > 3. Backup Database to Tape
> > 4. Backup Log to Tape TRUNCATE
> >
> >
> > Tibor Karaszi wrote:
> >> Lets see if I get this right. I might have missed what you are doing. You want to do something
> >> like:
> >>
> >> 1 Backup database (disk or tape)
> >> 2 Tape backup log
> >> 3 Disk backup log with NO_TRUNCATE
> >> 4 Tape backup log
> >> 5 Disk backup log with NO_TRUNCATE
> >>
> >> For above scenario, you will be able to restore 1, 2, 3, but not further. See
> >> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp for more details. Can you please
> >> elaborate if above is not your scenario.
> >>
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Techhead" <jorgenson.b@.gmail.com> wrote in message
> >> news:1157648034.276585.67180@.b28g2000cwb.googlegroups.com...
> >> > But I don't want to restore using SQL, I want to use my 3rd party
> >> > backup solution to do the restore. THis solution can restore SQL DB's
> >> > to point-in-time using truncated logs that the software backed up. i
> >> > just don't want SQL truncating when my backup software does.
> >> >
> >> > Tibor Karaszi wrote:
> >> >> > Why would I not want to truncate? Because I have a third party backup
> >> >> > solution doing it for me and it will not work if my maintenance plan
> >> >> > and 3rd party backup truncate at the same time.
> >> >>
> >> >> I strongly encourage you to read the article I posted a link to. You will see that even with
> >> >> NO_TRUNCATE, you cannot "skip" restoring one of the log backup "in between". The article was
> >> >> written
> >> >> for SQL Server 2000, I haven't tried it on 2005 yet.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "Techhead" <jorgenson.b@.gmail.com> wrote in message
> >> >> news:1156284873.311342.152930@.p79g2000cwp.googlegroups.com...
> >> >> > Why would I not want to truncate? Because I have a third party backup
> >> >> > solution doing it for me and it will not work if my maintenance plan
> >> >> > and 3rd party backup truncate at the same time. I still want to do a
> >> >> > full backup in SQL but not truncate... maybe a copy of the tran logs
> >> >> > will work, but no truncate.
> >> >> >
> >> >> >
> >> >> > Tibor Karaszi wrote:
> >> >> >> Also, why would you (Techhead) want to do a log backup using NO_TRUNCATE on a regular
> >> >> >> basis?
> >> >> >> This
> >> >> >> option is only there in order for you to do the last log backup on a corrupt database. See:
> >> >> >> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
> >> >> >>
> >> >> >> --
> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> http://www.solidqualitylearning.com/
> >> >> >>
> >> >> >>
> >> >> >> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> >> >> >> news:44E33CF4.6030307@.realsqlguy.com...
> >> >> >> > Techhead wrote:
> >> >> >> >> I need to peform a log backup with no truncate. My regular maintenance
> >> >> >> >> plan runs like this:
> >> >> >> >>
> >> >> >> >> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> >> >> >> >> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
> >> >> >> >> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
> >> >> >> >> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
> >> >> >> >> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> Where in here do I add the no truncate option?
> >> >> >> >>
> >> >> >> >
> >> >> >> > You don't. You'll have to run the BACKUP command directly, not from a maintenance plan.
> >> >> >
> >> >> >
> >> >> > What is the backup command to use?
> >> >> >> >
> >> >> >> >
> >> >> >> > --
> >> >> >> > Tracy McKibben
> >> >> >> > MCDBA
> >> >> >> > http://www.realsqlguy.com
> >> >> >
> >> >
> >|||Techhead wrote:
> Tibor Karaszi wrote:
>> So you will only have one transaction log backup between each database backup?
> Actually, after a disk backup, my tape backup will back up the log and
> truncate every 4 hours.
>> Why do you want to use NO_TRUNCATE for the log backup to disk?
> Because, as I stated earlier, the tape backup does the truncation. Why
> would I want my disk backup and tape backup both truncating? You would
> have to restore from both disk and tape in order to do a complete log
> restore to point-in-time.
You could really save yourself some trouble, and avoid a potential
restore failure, by eliminating this third-party log backup. Use the
native SQL backup command to do your backups to disk, and then let the
tape software backup those disk files. That's what any experienced DBA
does, because those third-party gizmos are unreliable, slow, and awkward
to restore from.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||> You would
> have to restore from both disk and tape in order to do a complete log
> restore to point-in-time.
Did you read below article I posted earlier?
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
If not, please do. You cannot skip restoring a log backup, even if it was taken using NO_TRUNCATE.
Period. If you chose to not believe me, you are welcome, even though I have proof in above article.
It will be your head when you need to do a production restore.
In 2005, we have a new option for the backup command named COPY_ONLY. It will do what you want.
I really cannot say more on this. Again, read the article, and then it is up to you whether you want
to be able to restore from your log backups or not.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Techhead" <jorgenson.b@.gmail.com> wrote in message
news:1158255181.850317.47190@.h48g2000cwc.googlegroups.com...
> Tibor Karaszi wrote:
>> So you will only have one transaction log backup between each database backup?
> Actually, after a disk backup, my tape backup will back up the log and
> truncate every 4 hours.
>> Why do you want to use NO_TRUNCATE for the log backup to disk?
> Because, as I stated earlier, the tape backup does the truncation. Why
> would I want my disk backup and tape backup both truncating? You would
> have to restore from both disk and tape in order to do a complete log
> restore to point-in-time.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Techhead" <jorgenson.b@.gmail.com> wrote in message
>> news:1158096627.443342.147600@.b28g2000cwb.googlegroups.com...
>> > Actually this is what I want:
>> >
>> > 1. Backup Database to Disk
>> > 2. Backup Log to Disk NO_TRUNCATE
>> > 3. Backup Database to Tape
>> > 4. Backup Log to Tape TRUNCATE
>> >
>> >
>> > Tibor Karaszi wrote:
>> >> Lets see if I get this right. I might have missed what you are doing. You want to do something
>> >> like:
>> >>
>> >> 1 Backup database (disk or tape)
>> >> 2 Tape backup log
>> >> 3 Disk backup log with NO_TRUNCATE
>> >> 4 Tape backup log
>> >> 5 Disk backup log with NO_TRUNCATE
>> >>
>> >> For above scenario, you will be able to restore 1, 2, 3, but not further. See
>> >> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp for more details. Can you please
>> >> elaborate if above is not your scenario.
>> >>
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "Techhead" <jorgenson.b@.gmail.com> wrote in message
>> >> news:1157648034.276585.67180@.b28g2000cwb.googlegroups.com...
>> >> > But I don't want to restore using SQL, I want to use my 3rd party
>> >> > backup solution to do the restore. THis solution can restore SQL DB's
>> >> > to point-in-time using truncated logs that the software backed up. i
>> >> > just don't want SQL truncating when my backup software does.
>> >> >
>> >> > Tibor Karaszi wrote:
>> >> >> > Why would I not want to truncate? Because I have a third party backup
>> >> >> > solution doing it for me and it will not work if my maintenance plan
>> >> >> > and 3rd party backup truncate at the same time.
>> >> >>
>> >> >> I strongly encourage you to read the article I posted a link to. You will see that even
>> >> >> with
>> >> >> NO_TRUNCATE, you cannot "skip" restoring one of the log backup "in between". The article
>> >> >> was
>> >> >> written
>> >> >> for SQL Server 2000, I haven't tried it on 2005 yet.
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://www.solidqualitylearning.com/
>> >> >>
>> >> >>
>> >> >> "Techhead" <jorgenson.b@.gmail.com> wrote in message
>> >> >> news:1156284873.311342.152930@.p79g2000cwp.googlegroups.com...
>> >> >> > Why would I not want to truncate? Because I have a third party backup
>> >> >> > solution doing it for me and it will not work if my maintenance plan
>> >> >> > and 3rd party backup truncate at the same time. I still want to do a
>> >> >> > full backup in SQL but not truncate... maybe a copy of the tran logs
>> >> >> > will work, but no truncate.
>> >> >> >
>> >> >> >
>> >> >> > Tibor Karaszi wrote:
>> >> >> >> Also, why would you (Techhead) want to do a log backup using NO_TRUNCATE on a regular
>> >> >> >> basis?
>> >> >> >> This
>> >> >> >> option is only there in order for you to do the last log backup on a corrupt database.
>> >> >> >> See:
>> >> >> >> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
>> >> >> >>
>> >> >> >> --
>> >> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> >> http://www.solidqualitylearning.com/
>> >> >> >>
>> >> >> >>
>> >> >> >> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> >> >> >> news:44E33CF4.6030307@.realsqlguy.com...
>> >> >> >> > Techhead wrote:
>> >> >> >> >> I need to peform a log backup with no truncate. My regular maintenance
>> >> >> >> >> plan runs like this:
>> >> >> >> >>
>> >> >> >> >> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> >> >> >> >> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
>> >> >> >> >> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
>> >> >> >> >> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
>> >> >> >> >> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >> Where in here do I add the no truncate option?
>> >> >> >> >>
>> >> >> >> >
>> >> >> >> > You don't. You'll have to run the BACKUP command directly, not from a maintenance
>> >> >> >> > plan.
>> >> >> >
>> >> >> >
>> >> >> > What is the backup command to use?
>> >> >> >> >
>> >> >> >> >
>> >> >> >> > --
>> >> >> >> > Tracy McKibben
>> >> >> >> > MCDBA
>> >> >> >> > http://www.realsqlguy.com
>> >> >> >
>> >> >
>> >
>|||Tibor Karaszi wrote:
>> You would
>> have to restore from both disk and tape in order to do a complete log
>> restore to point-in-time.
> Did you read below article I posted earlier?
> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
> If not, please do. You cannot skip restoring a log backup, even if it
> was taken using NO_TRUNCATE. Period. If you chose to not believe me, you
> are welcome, even though I have proof in above article. It will be your
> head when you need to do a production restore.
> In 2005, we have a new option for the backup command named COPY_ONLY. It
> will do what you want.
> I really cannot say more on this. Again, read the article, and then it
> is up to you whether you want to be able to restore from your log
> backups or not.
Seems to be a theme around here this week, folks insist on using square
wheels on their car, in spite of all the suggestions given to use the
proper round ones... My guess is the third-party backup software has a
nice shiny GUI, or he convinced his boss that he MUST spend $$,$$$$ to
buy it, and now can't make it work...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I am an experienced DBA and 99% of all my other SQL backups are within
SQL and backed up to disk. Then tape comes around and backs those up. I
100% agree that this is the preferred method and this is what I do for
about 15 SQL servers EXCEPT for 1 particular instance. My point in this
posting is not to argue about what method works best. Don't worry about
what I want to do in this special circumstance. What I do is nobody's
business. How I do it is the whole point in this posting. Please stick
to the objective and leave the opinionated comments for another group
or time.
Tracy McKibben wrote:
> Techhead wrote:
> > Tibor Karaszi wrote:
> >> So you will only have one transaction log backup between each database backup?
> > Actually, after a disk backup, my tape backup will back up the log and
> > truncate every 4 hours.
> >> Why do you want to use NO_TRUNCATE for the log backup to disk?
> >
> > Because, as I stated earlier, the tape backup does the truncation. Why
> > would I want my disk backup and tape backup both truncating? You would
> > have to restore from both disk and tape in order to do a complete log
> > restore to point-in-time.
> You could really save yourself some trouble, and avoid a potential
> restore failure, by eliminating this third-party log backup. Use the
> native SQL backup command to do your backups to disk, and then let the
> tape software backup those disk files. That's what any experienced DBA
> does, because those third-party gizmos are unreliable, slow, and awkward
> to restore from.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Tibor Karaszi wrote:
> > You would
> > have to restore from both disk and tape in order to do a complete log
> > restore to point-in-time.
> Did you read below article I posted earlier?
> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
Yes I did and it has nothing to do with what I am doing.
> If not, please do.
You cannot skip restoring a log backup
"I am not skipping a log backup" *remember, my tape backup does the log
backup and truncates.
even if it was taken using NO_TRUNCATE.
> Period. If you chose to not believe me, you are welcome, even though I have proof in above article.
> It will be your head when you need to do a production restore.
> In 2005, we have a new option for the backup command named COPY_ONLY. It will do what you want.
There we go. COPY_ONLY. Just what I wanted to hear! This will solve my
issue once and for all.
SQL does a DB backup to disk and a COPY of the log to disk.
Tape does a DB backup to tape and a log backup to tape with TRUNCATION.
> I really cannot say more on this. Again, read the article, and then it is up to you whether you want
> to be able to restore from your log backups or not.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Techhead" <jorgenson.b@.gmail.com> wrote in message
> news:1158255181.850317.47190@.h48g2000cwc.googlegroups.com...
> > Tibor Karaszi wrote:
> >> So you will only have one transaction log backup between each database backup?
> > Actually, after a disk backup, my tape backup will back up the log and
> > truncate every 4 hours.
> >>
> >> Why do you want to use NO_TRUNCATE for the log backup to disk?
> >
> > Because, as I stated earlier, the tape backup does the truncation. Why
> > would I want my disk backup and tape backup both truncating? You would
> > have to restore from both disk and tape in order to do a complete log
> > restore to point-in-time.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Techhead" <jorgenson.b@.gmail.com> wrote in message
> >> news:1158096627.443342.147600@.b28g2000cwb.googlegroups.com...
> >> > Actually this is what I want:
> >> >
> >> > 1. Backup Database to Disk
> >> > 2. Backup Log to Disk NO_TRUNCATE
> >> > 3. Backup Database to Tape
> >> > 4. Backup Log to Tape TRUNCATE
> >> >
> >> >
> >> > Tibor Karaszi wrote:
> >> >> Lets see if I get this right. I might have missed what you are doing. You want to do something
> >> >> like:
> >> >>
> >> >> 1 Backup database (disk or tape)
> >> >> 2 Tape backup log
> >> >> 3 Disk backup log with NO_TRUNCATE
> >> >> 4 Tape backup log
> >> >> 5 Disk backup log with NO_TRUNCATE
> >> >>
> >> >> For above scenario, you will be able to restore 1, 2, 3, but not further. See
> >> >> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp for more details. Can you please
> >> >> elaborate if above is not your scenario.
> >> >>
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "Techhead" <jorgenson.b@.gmail.com> wrote in message
> >> >> news:1157648034.276585.67180@.b28g2000cwb.googlegroups.com...
> >> >> > But I don't want to restore using SQL, I want to use my 3rd party
> >> >> > backup solution to do the restore. THis solution can restore SQL DB's
> >> >> > to point-in-time using truncated logs that the software backed up. i
> >> >> > just don't want SQL truncating when my backup software does.
> >> >> >
> >> >> > Tibor Karaszi wrote:
> >> >> >> > Why would I not want to truncate? Because I have a third party backup
> >> >> >> > solution doing it for me and it will not work if my maintenance plan
> >> >> >> > and 3rd party backup truncate at the same time.
> >> >> >>
> >> >> >> I strongly encourage you to read the article I posted a link to. You will see that even
> >> >> >> with
> >> >> >> NO_TRUNCATE, you cannot "skip" restoring one of the log backup "in between". The article
> >> >> >> was
> >> >> >> written
> >> >> >> for SQL Server 2000, I haven't tried it on 2005 yet.
> >> >> >>
> >> >> >> --
> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> http://www.solidqualitylearning.com/
> >> >> >>
> >> >> >>
> >> >> >> "Techhead" <jorgenson.b@.gmail.com> wrote in message
> >> >> >> news:1156284873.311342.152930@.p79g2000cwp.googlegroups.com...
> >> >> >> > Why would I not want to truncate? Because I have a third party backup
> >> >> >> > solution doing it for me and it will not work if my maintenance plan
> >> >> >> > and 3rd party backup truncate at the same time. I still want to do a
> >> >> >> > full backup in SQL but not truncate... maybe a copy of the tran logs
> >> >> >> > will work, but no truncate.
> >> >> >> >
> >> >> >> >
> >> >> >> > Tibor Karaszi wrote:
> >> >> >> >> Also, why would you (Techhead) want to do a log backup using NO_TRUNCATE on a regular
> >> >> >> >> basis?
> >> >> >> >> This
> >> >> >> >> option is only there in order for you to do the last log backup on a corrupt database.
> >> >> >> >> See:
> >> >> >> >> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
> >> >> >> >>
> >> >> >> >> --
> >> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> >> http://www.solidqualitylearning.com/
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> >> >> >> >> news:44E33CF4.6030307@.realsqlguy.com...
> >> >> >> >> > Techhead wrote:
> >> >> >> >> >> I need to peform a log backup with no truncate. My regular maintenance
> >> >> >> >> >> plan runs like this:
> >> >> >> >> >>
> >> >> >> >> >> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> >> >> >> >> >> FB8A5BE6-65F9-498C-8F76-AA324BB6139D -Rpt "G:\Program Files\Microsoft
> >> >> >> >> >> SQL Server\MSSQL\LOG\DATABASE_NAMEDB Maintenance Plan6.txt" -DelTxtRpt
> >> >> >> >> >> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir
> >> >> >> >> >> -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"'
> >> >> >> >> >>
> >> >> >> >> >>
> >> >> >> >> >> Where in here do I add the no truncate option?
> >> >> >> >> >>
> >> >> >> >> >
> >> >> >> >> > You don't. You'll have to run the BACKUP command directly, not from a maintenance
> >> >> >> >> > plan.
> >> >> >> >
> >> >> >> >
> >> >> >> > What is the backup command to use?
> >> >> >> >> >
> >> >> >> >> >
> >> >> >> >> > --
> >> >> >> >> > Tracy McKibben
> >> >> >> >> > MCDBA
> >> >> >> >> > http://www.realsqlguy.com
> >> >> >> >
> >> >> >
> >> >
> >|||Tracy McKibben wrote:
> Tibor Karaszi wrote:
> >> You would
> >> have to restore from both disk and tape in order to do a complete log
> >> restore to point-in-time.
> >
> > Did you read below article I posted earlier?
> > http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
> >
> > If not, please do. You cannot skip restoring a log backup, even if it
> > was taken using NO_TRUNCATE. Period. If you chose to not believe me, you
> > are welcome, even though I have proof in above article. It will be your
> > head when you need to do a production restore.
> >
> > In 2005, we have a new option for the backup command named COPY_ONLY. It
> > will do what you want.
> >
> > I really cannot say more on this. Again, read the article, and then it
> > is up to you whether you want to be able to restore from your log
> > backups or not.
> Seems to be a theme around here this week, folks insist on using square
> wheels on their car, in spite of all the suggestions given to use the
> proper round ones... My guess is the third-party backup software has a
> nice shiny GUI, or he convinced his boss that he MUST spend $$,$$$$ to
> buy it, and now can't make it work...
Unlike some people in this world, I can take a square wheel and turn it
into a round one.
Anyways, without Tracy's "in-the-box" suggestions, I've been able to
make it work and I can restore either way. How did I do it? We'll if
you can take your square MCDBA exam books and turn them into round ones
then I'll tell you.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Friday, March 30, 2012

not a valid undo file for database

not a valid undo file for database

'not a valid Microsoft Tape Format backup set' error when trying to restore DB

'not a valid Microsoft Tape Format backup set' error when trying to restore DB

'not a valid Microsoft Tape Format backup set' error when trying to restore DB

NOSKIP & SKIP make no difference to the amount of time backup take

NOSKIP & SKIP make no difference to the amount of time backup take

NOSKIP & SKIP make no difference to the amount of time backup take

Monday, March 26, 2012

norewind on SQL 2k backup

Friday, March 23, 2012

Noob here, dumb, but quick question

Wednesday, March 21, 2012

non-logged operations

Saturday, February 25, 2012

No truncating.

No truncating.

No truncating.

No Truncate Option in Log Backup

interface supported

Blog Archive

About Me