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