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
> >
> >
>

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

No transaction logs

can you manually backup the transaction log from EM?
Keene
>--Original Message--
>I have a maintenance plan set up on a database to take
transaction log backups every 2 hours. The job runs
successfully everyday, but I do not see any .trn files
generated (and the task pad description shows that no
transaction log backups ever took place).
>Any idea why? I've never seen this problem on any of my
other servers.
>Thank you!
>.
>It's SQL Server 7.0 with a database option of Truncate Log on Checkpoint...d
oes that make a difference?|||Gina,
With truncate log on checkpoint set the log file backups will be =
failing. You cannot do a backup log when this option is set as the =
contents of thye log are effectively destroyed at every checkpoint (ie =
every few minutes).
It sounds like you want this option OFF if you require point-in-time =
recovery.
Mike John
"Gina" <anonymous@.discussions.microsoft.com> wrote in message =
news:A4AC132C-1726-494E-AA0C-952A18EBED8E@.microsoft.com...
> It's SQL Server 7.0 with a database option of Truncate Log on =
Checkpoint...does that make a difference?|||Thank you very much!

No transaction logs

I have a maintenance plan set up on a database to take transaction log backu
ps every 2 hours. The job runs successfully everyday, but I do not see any
.trn files generated (and the task pad description shows that no transaction
log backups ever took plac
e).
Any idea why? I've never seen this problem on any of my other servers.
Thank you!I'd start by double checking the job / maint plan definition, the recovery
model for the databases and then if needed run a profiler trace to see
whether the BACKUP LOG command is submitted. Also, make sure you define a
report file for the maint plan and check that report file for error
messages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Gina" <anonymous@.discussions.microsoft.com> wrote in message
news:01060BCF-B18D-427F-AFB7-86ECE6A8A9DC@.microsoft.com...
> I have a maintenance plan set up on a database to take transaction log
backups every 2 hours. The job runs successfully everyday, but I do not see
any .trn files generated (and the task pad description shows that no
transaction log backups ever took place).
> Any idea why? I've never seen this problem on any of my other servers.
> Thank you!

No sync - No Initialize & scripts

If you restore the db you plan to replicate to another server, and then setup Trans. Repl. with immediate update, without doing a Snapshot Initialization (Taking the option that says data & schema are already there)
how do the scripts get created?
The scripts that will do the insert/update/delete (sp_msdel_tbl1, sp_msdel_tbl2, etc.)?
Thanx!
JLS
In SQL2000, you need to call sp_scriptpublicationcustomprocs to generate a script for creating the sp_MSins|del|upd procedures at the subscriber and then call sp_MSaddsynctrigger for each article at the subscriber to create the immediate updating triggers. The following kb has more details on this:
http://support.microsoft.com/default...b;en-us;320499
In SQL2005 (publisher), we create the replication system objects for you automatically if you use 'replication support only' or 'initialize with backup' as the @.sync_method in sp_addsubscription (SQL2005 Management Studio will use "replication support only" if you choose not to initialize data\schema)
-Raymond
"JLS" <jlshoop@.hotmail.com> wrote in message news:eTe%23PyJ5FHA.3276@.TK2MSFTNGP10.phx.gbl...
If you restore the db you plan to replicate to another server, and then setup Trans. Repl. with immediate update, without doing a Snapshot Initialization (Taking the option that says data & schema are already there)
how do the scripts get created?
The scripts that will do the insert/update/delete (sp_msdel_tbl1, sp_msdel_tbl2, etc.)?
Thanx!
JLS
|||It is Sql 2000, Thanx for the link to the kb! You're a great help!!!
"Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message news:uSamV6J5FHA.2524@.TK2MSFTNGP10.phx.gbl...
In SQL2000, you need to call sp_scriptpublicationcustomprocs to generate a script for creating the sp_MSins|del|upd procedures at the subscriber and then call sp_MSaddsynctrigger for each article at the subscriber to create the immediate updating triggers. The following kb has more details on this:
http://support.microsoft.com/default...b;en-us;320499
In SQL2005 (publisher), we create the replication system objects for you automatically if you use 'replication support only' or 'initialize with backup' as the @.sync_method in sp_addsubscription (SQL2005 Management Studio will use "replication support only" if you choose not to initialize data\schema)
-Raymond
"JLS" <jlshoop@.hotmail.com> wrote in message news:eTe%23PyJ5FHA.3276@.TK2MSFTNGP10.phx.gbl...
If you restore the db you plan to replicate to another server, and then setup Trans. Repl. with immediate update, without doing a Snapshot Initialization (Taking the option that says data & schema are already there)
how do the scripts get created?
The scripts that will do the insert/update/delete (sp_msdel_tbl1, sp_msdel_tbl2, etc.)?
Thanx!
JLS