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/in...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.co
m...
> Techhead wrote:
> You don't. You'll have to run the BACKUP command directly, not from a mai
ntenance 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:[vbcol=seagreen]
> Also, why would you (Techhead) want to do a log backup using NO_TRUNCATE o
n a regular basis? This
> option is only there in order for you to do the last log backup on a corru
pt database. See:
> http://www.karaszi.com/SQLServer/in...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.603
0307@.realsqlguy.com...
What is the backup command to use?[vbcol=seagreen]|||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 se
e 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:
>
> What is the backup command to use?
>|||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:[vbcol=seagreen]
> 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...|||Lets see if I get this right. I might have missed what you are doing. You wa
nt 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. Se
e
http://www.karaszi.com/SQLServer/in...no_truncate.asp for more detai
ls. 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:
>|||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:[vbcol=seagreen]
> 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/in...no_truncate.asp for more det
ails. 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...|||So you will only have one transaction log backup between each database backu
p?
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:
>