|
Home > Archive > MS SQL Server > July 2005 > Transaction Log Backups
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Transaction Log Backups
|
|
| Mark Bella 2005-07-18, 1:23 pm |
| Hello Everyone,
I'm trying to backup the transaction logs in SQL, but the jobs keep failing.
This is the command we are using:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID (Insert ID) -Rpt "D:\Program
Files\Microsoft SQL Server\MSSQL\LOG\DB Maintenance Plan16.txt" -DelTxtRpt
4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog "D:\Program
Files\Microsoft SQL Server\MSSQL\BACKUP" -DelBkUps 4WEEKS -CrBkSubDir -BkExt
"TRN"'
Thanks in advance,
Mark
| |
| Neil MacMurchy 2005-07-18, 1:23 pm |
| did you hear "Mark Bella" <bella_mark@hotmail.com> say in
news:#f1Rez7iFHA.476@TK2MSFTNGP14.phx.gbl:
> Hello Everyone,
>
> I'm trying to backup the transaction logs in SQL, but the jobs keep
> failing.
>
> This is the command we are using:
>
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID (Insert ID) -Rpt "D:\Program
> Files\Microsoft SQL Server\MSSQL\LOG\DB Maintenance Plan16.txt"
> -DelTxtRpt 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog
> "D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP" -DelBkUps 4WEEKS
> -CrBkSubDir -BkExt "TRN"'
>
>
> Thanks in advance,
> Mark
>
>
>
do you have enough space on the target directory? does the account
running the job (SQL Agent account) have permissions to the target
directory?
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
| |
| Alejandro Mesa 2005-07-18, 1:23 pm |
| Any info about the error?
AMB
"Mark Bella" wrote:
> Hello Everyone,
>
> I'm trying to backup the transaction logs in SQL, but the jobs keep failing.
>
> This is the command we are using:
>
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID (Insert ID) -Rpt "D:\Program
> Files\Microsoft SQL Server\MSSQL\LOG\DB Maintenance Plan16.txt" -DelTxtRpt
> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog "D:\Program
> Files\Microsoft SQL Server\MSSQL\BACKUP" -DelBkUps 4WEEKS -CrBkSubDir -BkExt
> "TRN"'
>
>
> Thanks in advance,
> Mark
>
>
>
| |
| Andrew J. Kelly 2005-07-18, 1:23 pm |
| Is the database in Simple Mode by any chance?
--
Andrew J. Kelly SQL MVP
"Mark Bella" <bella_mark@hotmail.com> wrote in message
news:%23f1Rez7iFHA.476@TK2MSFTNGP14.phx.gbl...
> Hello Everyone,
>
> I'm trying to backup the transaction logs in SQL, but the jobs keep
> failing.
>
> This is the command we are using:
>
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID (Insert ID) -Rpt "D:\Program
> Files\Microsoft SQL Server\MSSQL\LOG\DB Maintenance Plan16.txt" -DelTxtRpt
> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog "D:\Program
> Files\Microsoft SQL Server\MSSQL\BACKUP" -DelBkUps
> 4WEEKS -CrBkSubDir -BkExt "TRN"'
>
>
> Thanks in advance,
> Mark
>
| |
| Mark Bella 2005-07-18, 8:23 pm |
| Thank you for your quick responses.
There is approximatly 30 GB free on the drive and we are using the SA
account to run this job.
I did take a look at the database, it is in simple mode.
Mark Bella
"Mark Bella" <bella_mark@hotmail.com> wrote in message
news:%23f1Rez7iFHA.476@TK2MSFTNGP14.phx.gbl...
> Hello Everyone,
>
> I'm trying to backup the transaction logs in SQL, but the jobs keep
> failing.
>
> This is the command we are using:
>
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID (Insert ID) -Rpt "D:\Program
> Files\Microsoft SQL Server\MSSQL\LOG\DB Maintenance Plan16.txt" -DelTxtRpt
> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog "D:\Program
> Files\Microsoft SQL Server\MSSQL\BACKUP" -DelBkUps
> 4WEEKS -CrBkSubDir -BkExt "TRN"'
>
>
> Thanks in advance,
> Mark
>
| |
| Neil MacMurchy 2005-07-18, 8:23 pm |
| did you hear "Mark Bella" <bella_mark@hotmail.com> say in news:
#dLMy08iFHA.3336@tk2msftngp13.phx.gbl:
>
> I did take a look at the database, it is in simple mode.
>
that's the issue. no point in backing up a transaction log that
automagically truncates itself each time a checkpoint is reached.
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
| |
| Hari Prasad 2005-07-18, 8:23 pm |
| Hi Mark,
You can not backup the Transaction log in SIMPLE recovery model. Recovery
model should be eigher FULL or BULK_LOGGED recovery model
to a Transaction log backup. In simple recovery the transaction log will be
cleared after each recovery/ checkpoint. So you will not sufficiant
information in LDF to do a backup.
If it is a production database change the recovery model to FULL and do:-
1. Perform a FULL database backup of the database
2. Schedule a Transaction log backup. Interval and Archival can be set based
on the requirement.
Thanks
Hari
SQL Server MVP
"Mark Bella" <bella_mark@hotmail.com> wrote in message
news:%23dLMy08iFHA.3336@tk2msftngp13.phx.gbl...
> Thank you for your quick responses.
>
> There is approximatly 30 GB free on the drive and we are using the SA
> account to run this job.
>
> I did take a look at the database, it is in simple mode.
>
> Mark Bella
>
>
> "Mark Bella" <bella_mark@hotmail.com> wrote in message
> news:%23f1Rez7iFHA.476@TK2MSFTNGP14.phx.gbl...
>
>
| |
| Alejandro Mesa 2005-07-18, 8:23 pm |
| You can not backup the transaction log of a db that has its recovery model in
simple mode.
Example:
use northwind
go
backup log northwind
to disk = 'c:\temp\northwind_l
og.bak'
go
Result:
Server: Msg 4208, Level 16, State 1, Line 2
The statement BACKUP LOG is not allowed while the recovery model is SIMPLE.
Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
Server: Msg 3013, Level 16, State 1, Line 2
BACKUP LOG is terminating abnormally.
AMB
"Mark Bella" wrote:
> Thank you for your quick responses.
>
> There is approximatly 30 GB free on the drive and we are using the SA
> account to run this job.
>
> I did take a look at the database, it is in simple mode.
>
> Mark Bella
>
>
> "Mark Bella" <bella_mark@hotmail.com> wrote in message
> news:%23f1Rez7iFHA.476@TK2MSFTNGP14.phx.gbl...
>
>
>
| |
| Mark Bella 2005-07-18, 8:23 pm |
| Hello Everyone,
I forgot the Simple model does not support transaction logs. Thanks again
for your help/guidance!
Due to some requirements of an application, I switched the recovery model to
FULL.
Thanks again for your assistance.
Mark Bella
"Mark Bella" <bella_mark@hotmail.com> wrote in message
news:%23dLMy08iFHA.3336@tk2msftngp13.phx.gbl...
> Thank you for your quick responses.
>
> There is approximatly 30 GB free on the drive and we are using the SA
> account to run this job.
>
> I did take a look at the database, it is in simple mode.
>
> Mark Bella
>
>
> "Mark Bella" <bella_mark@hotmail.com> wrote in message
> news:%23f1Rez7iFHA.476@TK2MSFTNGP14.phx.gbl...
>
>
|
|
|
|
|