I have my maintenance plan set up to backup to disk each day, including the
tran log.
I am getting an error on the Tran log backup, however.
It doesn't tell me why it failed, just that it failed.
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The Job
was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1 (Step
1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
The help center is no help.
The other 3 parts of the maintenance plan seem to go fine.
Also, there is no log in the Sql Logs, but there is in the Event log (where
I got this). There is plenty of disk space, so that is not the problem.
Am I missing something here?
Thanks,
Tom
Specify a report file for the maint plan and check that for specific error messages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ubIr1UV5FHA.3876@.TK2MSFTNGP09.phx.gbl...
>I have my maintenance plan set up to backup to disk each day, including the tran log.
> I am getting an error on the Tran log backup, however.
> It doesn't tell me why it failed, just that it failed.
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'DB Maintenance
> Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status: Failed - Invoked on: 2005-11-09 09:24:24 -
> Message: The job failed. The Job was invoked by User FTSOLUTIONS0\tfs. The last step to run was
> step 1 (Step 1).
> For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
> The help center is no help.
> The other 3 parts of the maintenance plan seem to go fine.
> Also, there is no log in the Sql Logs, but there is in the Event log (where I got this). There is
> plenty of disk space, so that is not the problem.
> Am I missing something here?
> Thanks,
> Tom
>
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:edxoSaV5FHA.1248@.TK2MSFTNGP14.phx.gbl...
> Specify a report file for the maint plan and check that for specific error
> messages.
I did that and got to following:
************************************************** ************************************************** *
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'VENUS' as 'FTSOLUTIONS0\servicesql' (trusted)
Starting maintenance plan 'All Databases Backup' on 11/9/2005 10:33:23 AM
Backup can not be performed on database 'FTS'. This sub task is ignored.
Backup can not be performed on database 'master'. This sub task is ignored.
[3] Database model: Transaction Log Backup...
Destination: [d:\Microsoft SQL
Server\MSSQL\BACKUP\model_tlog_200511091033.TRN]
** Execution Time: 0 hrs, 0 mins, 1 secs **
[4] Database model: Verifying Backup...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Backup can not be performed on database 'msdb'. This sub task is ignored.
End of maintenance plan 'All Databases Backup' on 11/9/2005 10:33:24 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
************************************************** ************************************************** *
So it works for some and not others.
And why can't it backup master or msdb - they have log files
Also, there are files that are unreadable there: ERRORLOG, ERRORLOG.1,
SQLAGENT.OUT, SQLAGENT.1. What are they and how do you read them?
Thanks,
Tom
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ubIr1UV5FHA.3876@.TK2MSFTNGP09.phx.gbl...
>
|||I think that master & msdb are simple recovery model, hence cannot have
tran. log backups. Just remove this portion of your maintenence plan.
|||Ok.
But why did FTS not backup?
Tom
"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131562250.817157.160290@.g43g2000cwa.googlegr oups.com...
>I think that master & msdb are simple recovery model, hence cannot have
> tran. log backups. Just remove this portion of your maintenence plan.
>
|||Most log backups fail because the database is in simple recovery mode. Check
that first. Second, verify the database has had a full backup. Finally, I
assume we're not talking about the msdb and master--but just to be safe,
it's not one of those two, correct?
"tshad" wrote:
> I have my maintenance plan set up to backup to disk each day, including the
> tran log.
> I am getting an error on the Tran log backup, however.
> It doesn't tell me why it failed, just that it failed.
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
> 'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
> Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The Job
> was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1 (Step
> 1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> The help center is no help.
> The other 3 parts of the maintenance plan seem to go fine.
> Also, there is no log in the Sql Logs, but there is in the Event log (where
> I got this). There is plenty of disk space, so that is not the problem.
> Am I missing something here?
> Thanks,
> Tom
>
>
|||>From his report, it looks like we are talking about master & msdb. As
well as one other called FTS. Like fnguy said - check to see if if FTS
is simple recovery model. If it is, you can't back up the tran. log.
Backup can not be performed on database 'FTS'. This sub task is
ignored.
Backup can not be performed on database 'master'. This sub task is
ignored.
Backup can not be performed on database 'msdb'. This sub task is
ignored.
|||"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:90AC17EA-75F3-45C0-BA2D-C5CB3600AF4C@.microsoft.com...
> Most log backups fail because the database is in simple recovery mode.
> Check
> that first. Second, verify the database has had a full backup. Finally,
> I
> assume we're not talking about the msdb and master--but just to be safe,
> it's not one of those two, correct?
No, it isn't. It's one of our normal databases.
But you mentioned, the database having a full backup.
Do you need to do the Tran backup first?
Thanks,
Tom[vbcol=seagreen]
>
> "tshad" wrote:
|||"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131567831.621737.163390@.g43g2000cwa.googlegr oups.com...
> well as one other called FTS. Like fnguy said - check to see if if FTS
> is simple recovery model. If it is, you can't back up the tran. log.
> Backup can not be performed on database 'FTS'. This sub task is
> ignored.
> Backup can not be performed on database 'master'. This sub task is
> ignored.
> Backup can not be performed on database 'msdb'. This sub task is
> ignored.
>
How do you check to see if it is a simple recovery model? I assume it
isn't, but I am not sure.
Tom
|||Check the recovery model of your database.
If the FTS database is in Simple recovery model, then log backups make no
sense.
> Also, there are files that are unreadable there: ERRORLOG, ERRORLOG.1,
> SQLAGENT.OUT, SQLAGENT.1. What are they and how do you read them?
>
ERRORLOG.n are the SQL error logs. They are simple text files. You should
be able to open them in notepad unless there is some strange protection
applied. As an administrator you should certainly be agle to read them.
Kevin Farlee
Showing posts with label tran. Show all posts
Showing posts with label tran. Show all posts
Thursday, March 8, 2012
Backups of tran log not working
I have my maintenance plan set up to backup to disk each day, including the
tran log.
I am getting an error on the Tran log backup, however.
It doesn't tell me why it failed, just that it failed.
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The Job
was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1 (Step
1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
The help center is no help.
The other 3 parts of the maintenance plan seem to go fine.
Also, there is no log in the Sql Logs, but there is in the Event log (where
I got this). There is plenty of disk space, so that is not the problem.
Am I missing something here?
Thanks,
TomSpecify a report file for the maint plan and check that for specific error m
essages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ubIr1UV5FHA.3876@.TK2MSFTNGP09.phx.gbl...
>I have my maintenance plan set up to backup to disk each day, including the
tran log.
> I am getting an error on the Tran log backup, however.
> It doesn't tell me why it failed, just that it failed.
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Pl
an 'DB Maintenance
> Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status: Failed - Invoked on
: 2005-11-09 09:24:24 -
> Message: The job failed. The Job was invoked by User FTSOLUTIONS0\tfs. T
he last step to run was
> step 1 (Step 1).
> For more information, see Help and Support Center at http://go.microsoft.com/fwl
ink/events.asp.
> The help center is no help.
> The other 3 parts of the maintenance plan seem to go fine.
> Also, there is no log in the Sql Logs, but there is in the Event log (wher
e I got this). There is
> plenty of disk space, so that is not the problem.
> Am I missing something here?
> Thanks,
> Tom
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:edxoSaV5FHA.1248@.TK2MSFTNGP14.phx.gbl...
> Specify a report file for the maint plan and check that for specific error
> messages.
I did that and got to following:
****************************************
************************************
*************************
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'VENUS' as 'FTSOLUTIONS0\servicesql' (trusted)
Starting maintenance plan 'All Databases Backup' on 11/9/2005 10:33:23 AM
Backup can not be performed on database 'FTS'. This sub task is ignored.
Backup can not be performed on database 'master'. This sub task is ignored.
[3] Database model: Transaction Log Backup...
Destination: [d:\Microsoft SQL
Server\MSSQL\BACKUP\model_tlog_200511091
033.TRN]
** Execution Time: 0 hrs, 0 mins, 1 secs **
[4] Database model: Verifying Backup...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Backup can not be performed on database 'msdb'. This sub task is ignored.
End of maintenance plan 'All Databases Backup' on 11/9/2005 10:33:24 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
****************************************
************************************
*************************
So it works for some and not others.
And why can't it backup master or msdb - they have log files
Also, there are files that are unreadable there: ERRORLOG, ERRORLOG.1,
SQLAGENT.OUT, SQLAGENT.1. What are they and how do you read them?
Thanks,
Tom
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ubIr1UV5FHA.3876@.TK2MSFTNGP09.phx.gbl...
>|||I think that master & msdb are simple recovery model, hence cannot have
tran. log backups. Just remove this portion of your maintenence plan.|||Ok.
But why did FTS not backup?
Tom
"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131562250.817157.160290@.g43g2000cwa.googlegroups.com...
>I think that master & msdb are simple recovery model, hence cannot have
> tran. log backups. Just remove this portion of your maintenence plan.
>|||Most log backups fail because the database is in simple recovery mode. Chec
k
that first. Second, verify the database has had a full backup. Finally, I
assume we're not talking about the msdb and master--but just to be safe,
it's not one of those two, correct?
"tshad" wrote:
> I have my maintenance plan set up to backup to disk each day, including th
e
> tran log.
> I am getting an error on the Tran log backup, however.
> It doesn't tell me why it failed, just that it failed.
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Pl
an
> 'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
> Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The J
ob
> was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1 (St
ep
> 1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> The help center is no help.
> The other 3 parts of the maintenance plan seem to go fine.
> Also, there is no log in the Sql Logs, but there is in the Event log (wher
e
> I got this). There is plenty of disk space, so that is not the problem.
> Am I missing something here?
> Thanks,
> Tom
>
>|||>From his report, it looks like we are talking about master & msdb. As
well as one other called FTS. Like fnguy said - check to see if if FTS
is simple recovery model. If it is, you can't back up the tran. log.
Backup can not be performed on database 'FTS'. This sub task is
ignored.
Backup can not be performed on database 'master'. This sub task is
ignored.
Backup can not be performed on database 'msdb'. This sub task is
ignored.|||"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:90AC17EA-75F3-45C0-BA2D-C5CB3600AF4C@.microsoft.com...
> Most log backups fail because the database is in simple recovery mode.
> Check
> that first. Second, verify the database has had a full backup. Finally,
> I
> assume we're not talking about the msdb and master--but just to be safe,
> it's not one of those two, correct?
No, it isn't. It's one of our normal databases.
But you mentioned, the database having a full backup.
Do you need to do the Tran backup first?
Thanks,
Tom[vbcol=seagreen]
>
> "tshad" wrote:
>|||"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
> well as one other called FTS. Like fnguy said - check to see if if FTS
> is simple recovery model. If it is, you can't back up the tran. log.
> Backup can not be performed on database 'FTS'. This sub task is
> ignored.
> Backup can not be performed on database 'master'. This sub task is
> ignored.
> Backup can not be performed on database 'msdb'. This sub task is
> ignored.
>
How do you check to see if it is a simple recovery model? I assume it
isn't, but I am not sure.
Tom|||Check the recovery model of your database.
If the FTS database is in Simple recovery model, then log backups make no
sense.
> Also, there are files that are unreadable there: ERRORLOG, ERRORLOG.1,
> SQLAGENT.OUT, SQLAGENT.1. What are they and how do you read them?
>
ERRORLOG.n are the SQL error logs. They are simple text files. You should
be able to open them in notepad unless there is some strange protection
applied. As an administrator you should certainly be agle to read them.
Kevin Farlee
tran log.
I am getting an error on the Tran log backup, however.
It doesn't tell me why it failed, just that it failed.
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The Job
was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1 (Step
1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
The help center is no help.
The other 3 parts of the maintenance plan seem to go fine.
Also, there is no log in the Sql Logs, but there is in the Event log (where
I got this). There is plenty of disk space, so that is not the problem.
Am I missing something here?
Thanks,
TomSpecify a report file for the maint plan and check that for specific error m
essages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ubIr1UV5FHA.3876@.TK2MSFTNGP09.phx.gbl...
>I have my maintenance plan set up to backup to disk each day, including the
tran log.
> I am getting an error on the Tran log backup, however.
> It doesn't tell me why it failed, just that it failed.
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Pl
an 'DB Maintenance
> Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status: Failed - Invoked on
: 2005-11-09 09:24:24 -
> Message: The job failed. The Job was invoked by User FTSOLUTIONS0\tfs. T
he last step to run was
> step 1 (Step 1).
> For more information, see Help and Support Center at http://go.microsoft.com/fwl
ink/events.asp.
> The help center is no help.
> The other 3 parts of the maintenance plan seem to go fine.
> Also, there is no log in the Sql Logs, but there is in the Event log (wher
e I got this). There is
> plenty of disk space, so that is not the problem.
> Am I missing something here?
> Thanks,
> Tom
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:edxoSaV5FHA.1248@.TK2MSFTNGP14.phx.gbl...
> Specify a report file for the maint plan and check that for specific error
> messages.
I did that and got to following:
****************************************
************************************
*************************
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'VENUS' as 'FTSOLUTIONS0\servicesql' (trusted)
Starting maintenance plan 'All Databases Backup' on 11/9/2005 10:33:23 AM
Backup can not be performed on database 'FTS'. This sub task is ignored.
Backup can not be performed on database 'master'. This sub task is ignored.
[3] Database model: Transaction Log Backup...
Destination: [d:\Microsoft SQL
Server\MSSQL\BACKUP\model_tlog_200511091
033.TRN]
** Execution Time: 0 hrs, 0 mins, 1 secs **
[4] Database model: Verifying Backup...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Backup can not be performed on database 'msdb'. This sub task is ignored.
End of maintenance plan 'All Databases Backup' on 11/9/2005 10:33:24 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
****************************************
************************************
*************************
So it works for some and not others.
And why can't it backup master or msdb - they have log files
Also, there are files that are unreadable there: ERRORLOG, ERRORLOG.1,
SQLAGENT.OUT, SQLAGENT.1. What are they and how do you read them?
Thanks,
Tom
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ubIr1UV5FHA.3876@.TK2MSFTNGP09.phx.gbl...
>|||I think that master & msdb are simple recovery model, hence cannot have
tran. log backups. Just remove this portion of your maintenence plan.|||Ok.
But why did FTS not backup?
Tom
"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131562250.817157.160290@.g43g2000cwa.googlegroups.com...
>I think that master & msdb are simple recovery model, hence cannot have
> tran. log backups. Just remove this portion of your maintenence plan.
>|||Most log backups fail because the database is in simple recovery mode. Chec
k
that first. Second, verify the database has had a full backup. Finally, I
assume we're not talking about the msdb and master--but just to be safe,
it's not one of those two, correct?
"tshad" wrote:
> I have my maintenance plan set up to backup to disk each day, including th
e
> tran log.
> I am getting an error on the Tran log backup, however.
> It doesn't tell me why it failed, just that it failed.
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Pl
an
> 'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
> Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The J
ob
> was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1 (St
ep
> 1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> The help center is no help.
> The other 3 parts of the maintenance plan seem to go fine.
> Also, there is no log in the Sql Logs, but there is in the Event log (wher
e
> I got this). There is plenty of disk space, so that is not the problem.
> Am I missing something here?
> Thanks,
> Tom
>
>|||>From his report, it looks like we are talking about master & msdb. As
well as one other called FTS. Like fnguy said - check to see if if FTS
is simple recovery model. If it is, you can't back up the tran. log.
Backup can not be performed on database 'FTS'. This sub task is
ignored.
Backup can not be performed on database 'master'. This sub task is
ignored.
Backup can not be performed on database 'msdb'. This sub task is
ignored.|||"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:90AC17EA-75F3-45C0-BA2D-C5CB3600AF4C@.microsoft.com...
> Most log backups fail because the database is in simple recovery mode.
> Check
> that first. Second, verify the database has had a full backup. Finally,
> I
> assume we're not talking about the msdb and master--but just to be safe,
> it's not one of those two, correct?
No, it isn't. It's one of our normal databases.
But you mentioned, the database having a full backup.
Do you need to do the Tran backup first?
Thanks,
Tom[vbcol=seagreen]
>
> "tshad" wrote:
>|||"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
> well as one other called FTS. Like fnguy said - check to see if if FTS
> is simple recovery model. If it is, you can't back up the tran. log.
> Backup can not be performed on database 'FTS'. This sub task is
> ignored.
> Backup can not be performed on database 'master'. This sub task is
> ignored.
> Backup can not be performed on database 'msdb'. This sub task is
> ignored.
>
How do you check to see if it is a simple recovery model? I assume it
isn't, but I am not sure.
Tom|||Check the recovery model of your database.
If the FTS database is in Simple recovery model, then log backups make no
sense.
> Also, there are files that are unreadable there: ERRORLOG, ERRORLOG.1,
> SQLAGENT.OUT, SQLAGENT.1. What are they and how do you read them?
>
ERRORLOG.n are the SQL error logs. They are simple text files. You should
be able to open them in notepad unless there is some strange protection
applied. As an administrator you should certainly be agle to read them.
Kevin Farlee
Backups of tran log not working
I have my maintenance plan set up to backup to disk each day, including the
tran log.
I am getting an error on the Tran log backup, however.
It doesn't tell me why it failed, just that it failed.
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The Job
was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1 (Step
1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
The help center is no help.
The other 3 parts of the maintenance plan seem to go fine.
Also, there is no log in the Sql Logs, but there is in the Event log (where
I got this). There is plenty of disk space, so that is not the problem.
Am I missing something here?
Thanks,
TomSpecify a report file for the maint plan and check that for specific error messages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ubIr1UV5FHA.3876@.TK2MSFTNGP09.phx.gbl...
>I have my maintenance plan set up to backup to disk each day, including the tran log.
> I am getting an error on the Tran log backup, however.
> It doesn't tell me why it failed, just that it failed.
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'DB Maintenance
> Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status: Failed - Invoked on: 2005-11-09 09:24:24 -
> Message: The job failed. The Job was invoked by User FTSOLUTIONS0\tfs. The last step to run was
> step 1 (Step 1).
> For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
> The help center is no help.
> The other 3 parts of the maintenance plan seem to go fine.
> Also, there is no log in the Sql Logs, but there is in the Event log (where I got this). There is
> plenty of disk space, so that is not the problem.
> Am I missing something here?
> Thanks,
> Tom
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:edxoSaV5FHA.1248@.TK2MSFTNGP14.phx.gbl...
> Specify a report file for the maint plan and check that for specific error
> messages.
I did that and got to following:
*****************************************************************************************************
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'VENUS' as 'FTSOLUTIONS0\servicesql' (trusted)
Starting maintenance plan 'All Databases Backup' on 11/9/2005 10:33:23 AM
Backup can not be performed on database 'FTS'. This sub task is ignored.
Backup can not be performed on database 'master'. This sub task is ignored.
[3] Database model: Transaction Log Backup...
Destination: [d:\Microsoft SQL
Server\MSSQL\BACKUP\model_tlog_200511091033.TRN]
** Execution Time: 0 hrs, 0 mins, 1 secs **
[4] Database model: Verifying Backup...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Backup can not be performed on database 'msdb'. This sub task is ignored.
End of maintenance plan 'All Databases Backup' on 11/9/2005 10:33:24 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
*****************************************************************************************************
So it works for some and not others.
And why can't it backup master or msdb - they have log files
Also, there are files that are unreadable there: ERRORLOG, ERRORLOG.1,
SQLAGENT.OUT, SQLAGENT.1. What are they and how do you read them?
Thanks,
Tom
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ubIr1UV5FHA.3876@.TK2MSFTNGP09.phx.gbl...
>>I have my maintenance plan set up to backup to disk each day, including
>>the tran log.
>> I am getting an error on the Tran log backup, however.
>> It doesn't tell me why it failed, just that it failed.
>> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
>> Plan 'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) -
>> Status: Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job
>> failed. The Job was invoked by User FTSOLUTIONS0\tfs. The last step to
>> run was step 1 (Step 1).
>> For more information, see Help and Support Center at
>> http://go.microsoft.com/fwlink/events.asp.
>> The help center is no help.
>> The other 3 parts of the maintenance plan seem to go fine.
>> Also, there is no log in the Sql Logs, but there is in the Event log
>> (where I got this). There is plenty of disk space, so that is not the
>> problem.
>> Am I missing something here?
>> Thanks,
>> Tom
>|||I think that master & msdb are simple recovery model, hence cannot have
tran. log backups. Just remove this portion of your maintenence plan.|||Ok.
But why did FTS not backup?
Tom
"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131562250.817157.160290@.g43g2000cwa.googlegroups.com...
>I think that master & msdb are simple recovery model, hence cannot have
> tran. log backups. Just remove this portion of your maintenence plan.
>|||Most log backups fail because the database is in simple recovery mode. Check
that first. Second, verify the database has had a full backup. Finally, I
assume we're not talking about the msdb and master--but just to be safe,
it's not one of those two, correct?
"tshad" wrote:
> I have my maintenance plan set up to backup to disk each day, including the
> tran log.
> I am getting an error on the Tran log backup, however.
> It doesn't tell me why it failed, just that it failed.
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
> 'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
> Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The Job
> was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1 (Step
> 1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> The help center is no help.
> The other 3 parts of the maintenance plan seem to go fine.
> Also, there is no log in the Sql Logs, but there is in the Event log (where
> I got this). There is plenty of disk space, so that is not the problem.
> Am I missing something here?
> Thanks,
> Tom
>
>|||>From his report, it looks like we are talking about master & msdb. As
well as one other called FTS. Like fnguy said - check to see if if FTS
is simple recovery model. If it is, you can't back up the tran. log.
Backup can not be performed on database 'FTS'. This sub task is
ignored.
Backup can not be performed on database 'master'. This sub task is
ignored.
Backup can not be performed on database 'msdb'. This sub task is
ignored.|||"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:90AC17EA-75F3-45C0-BA2D-C5CB3600AF4C@.microsoft.com...
> Most log backups fail because the database is in simple recovery mode.
> Check
> that first. Second, verify the database has had a full backup. Finally,
> I
> assume we're not talking about the msdb and master--but just to be safe,
> it's not one of those two, correct?
No, it isn't. It's one of our normal databases.
But you mentioned, the database having a full backup.
Do you need to do the Tran backup first?
Thanks,
Tom
>
> "tshad" wrote:
>> I have my maintenance plan set up to backup to disk each day, including
>> the
>> tran log.
>> I am getting an error on the Tran log backup, however.
>> It doesn't tell me why it failed, just that it failed.
>> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
>> Plan
>> 'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
>> Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The
>> Job
>> was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1
>> (Step
>> 1).
>> For more information, see Help and Support Center at
>> http://go.microsoft.com/fwlink/events.asp.
>> The help center is no help.
>> The other 3 parts of the maintenance plan seem to go fine.
>> Also, there is no log in the Sql Logs, but there is in the Event log
>> (where
>> I got this). There is plenty of disk space, so that is not the problem.
>> Am I missing something here?
>> Thanks,
>> Tom
>>|||"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
> >From his report, it looks like we are talking about master & msdb. As
> well as one other called FTS. Like fnguy said - check to see if if FTS
> is simple recovery model. If it is, you can't back up the tran. log.
> Backup can not be performed on database 'FTS'. This sub task is
> ignored.
> Backup can not be performed on database 'master'. This sub task is
> ignored.
> Backup can not be performed on database 'msdb'. This sub task is
> ignored.
>
How do you check to see if it is a simple recovery model? I assume it
isn't, but I am not sure.
Tom|||Check the recovery model of your database.
If the FTS database is in Simple recovery model, then log backups make no
sense.
> Also, there are files that are unreadable there: ERRORLOG, ERRORLOG.1,
> SQLAGENT.OUT, SQLAGENT.1. What are they and how do you read them?
>
ERRORLOG.n are the SQL error logs. They are simple text files. You should
be able to open them in notepad unless there is some strange protection
applied. As an administrator you should certainly be agle to read them.
Kevin Farlee|||As Kevin suggested, it is probably in simple recovery mode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23LFHRAW5FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Ok.
> But why did FTS not backup?
> Tom
> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
> news:1131562250.817157.160290@.g43g2000cwa.googlegroups.com...
>>I think that master & msdb are simple recovery model, hence cannot have
>> tran. log backups. Just remove this portion of your maintenence plan.
>|||> How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
sp_helpdb, for instance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
> news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >From his report, it looks like we are talking about master & msdb. As
>> well as one other called FTS. Like fnguy said - check to see if if FTS
>> is simple recovery model. If it is, you can't back up the tran. log.
>> Backup can not be performed on database 'FTS'. This sub task is
>> ignored.
>> Backup can not be performed on database 'master'. This sub task is
>> ignored.
>> Backup can not be performed on database 'msdb'. This sub task is
>> ignored.
> How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
> Tom
>|||Pls execute following SP from Query Analyser and post it's output:
sp_helpdb 'fts'
"tshad" wrote:
> "fnguy" <fnguy@.discussions.microsoft.com> wrote in message
> news:90AC17EA-75F3-45C0-BA2D-C5CB3600AF4C@.microsoft.com...
> > Most log backups fail because the database is in simple recovery mode.
> > Check
> > that first. Second, verify the database has had a full backup. Finally,
> > I
> > assume we're not talking about the msdb and master--but just to be safe,
> > it's not one of those two, correct?
> No, it isn't. It's one of our normal databases.
> But you mentioned, the database having a full backup.
> Do you need to do the Tran backup first?
> Thanks,
> Tom|||How do you check to see if it is a simple recovery model? I assume it
isn't, but I am not sure.
--
Also - open enterprise manager, right click the database in question,
and choose properties. Go to options tab. See what exists under the
Recovery model section. If it's simple, then all you can do is full
backups. If it's full, then you can do logs.
Tibor Karaszi wrote:
> > How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
> sp_helpdb, for instance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
> >
> > "Corey Bunch" <unc27932@.yahoo.com> wrote in message
> > news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
> >> >From his report, it looks like we are talking about master & msdb. As
> >> well as one other called FTS. Like fnguy said - check to see if if FTS
> >> is simple recovery model. If it is, you can't back up the tran. log.
> >>
> >> Backup can not be performed on database 'FTS'. This sub task is
> >> ignored.
> >> Backup can not be performed on database 'master'. This sub task is
> >> ignored.
> >> Backup can not be performed on database 'msdb'. This sub task is
> >> ignored.
> >>
> >
> > How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
> >
> > Tom
> >|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ejxz3mc5FHA.2364@.TK2MSFTNGP12.phx.gbl...
>> How do you check to see if it is a simple recovery model? I assume it
>> isn't, but I am not sure.
> sp_helpdb, for instance.
That does show it as
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
>> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
>> news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >From his report, it looks like we are talking about master & msdb. As
>> well as one other called FTS. Like fnguy said - check to see if if FTS
>> is simple recovery model. If it is, you can't back up the tran. log.
>> Backup can not be performed on database 'FTS'. This sub task is
>> ignored.
>> Backup can not be performed on database 'master'. This sub task is
>> ignored.
>> Backup can not be performed on database 'msdb'. This sub task is
>> ignored.
>>
>> How do you check to see if it is a simple recovery model? I assume it
>> isn't, but I am not sure.
>> Tom
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ejxz3mc5FHA.2364@.TK2MSFTNGP12.phx.gbl...
>> How do you check to see if it is a simple recovery model? I assume it
>> isn't, but I am not sure.
> sp_helpdb, for instance.
That was it.
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
Recovery=SIMPLE, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
IsAutoUpdateStatistics, IsFullTextEnabled
But how did it get that way? What did I do to make it that way? My other
databases aren't that way.
Can I change that?
Thanks,
Tom
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
>> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
>> news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >From his report, it looks like we are talking about master & msdb. As
>> well as one other called FTS. Like fnguy said - check to see if if FTS
>> is simple recovery model. If it is, you can't back up the tran. log.
>> Backup can not be performed on database 'FTS'. This sub task is
>> ignored.
>> Backup can not be performed on database 'master'. This sub task is
>> ignored.
>> Backup can not be performed on database 'msdb'. This sub task is
>> ignored.
>>
>> How do you check to see if it is a simple recovery model? I assume it
>> isn't, but I am not sure.
>> Tom
>|||Yes, you can change it using the ALTER DATABASE command.
How it got set to simple in the first place? I can't answer that, except saying that someone or some
program did it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message news:uP89yUh5FHA.140@.TK2MSFTNGP10.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:ejxz3mc5FHA.2364@.TK2MSFTNGP12.phx.gbl...
>> How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
>> sp_helpdb, for instance.
> That was it.
> Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539,
> Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsTornPageDetectionEnabled,
> IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled
> But how did it get that way? What did I do to make it that way? My other databases aren't that
> way.
> Can I change that?
> Thanks,
> Tom
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
>> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
>> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
>> news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >From his report, it looks like we are talking about master & msdb. As
>> well as one other called FTS. Like fnguy said - check to see if if FTS
>> is simple recovery model. If it is, you can't back up the tran. log.
>> Backup can not be performed on database 'FTS'. This sub task is
>> ignored.
>> Backup can not be performed on database 'master'. This sub task is
>> ignored.
>> Backup can not be performed on database 'msdb'. This sub task is
>> ignored.
>>
>> How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
>> Tom
>>
>|||"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131630158.667899.30870@.g44g2000cwa.googlegroups.com...
> How do you check to see if it is a simple recovery model? I assume it
> isn't, but I am not sure.
> --
> Also - open enterprise manager, right click the database in question,
> and choose properties. Go to options tab. See what exists under the
> Recovery model section. If it's simple, then all you can do is full
> backups. If it's full, then you can do logs.
>
Can I just change that there?
Why would you want a simple model?
It does have a tran log defined for it and it is the only one of 5 databases
I have that isn't full. So what causes it to be simple I just created
another database, it has no question about it and it automatically made it
simple. I originally created FTS in this way - so what would cause it to be
simple - I know I didn't do it manually.
Thanks,
Tom
>
> Tibor Karaszi wrote:
>> > How do you check to see if it is a simple recovery model? I assume it
>> > isn't, but I am not sure.
>> sp_helpdb, for instance.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
>> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
>> >
>> > "Corey Bunch" <unc27932@.yahoo.com> wrote in message
>> > news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >> >From his report, it looks like we are talking about master & msdb.
>> >> >As
>> >> well as one other called FTS. Like fnguy said - check to see if if
>> >> FTS
>> >> is simple recovery model. If it is, you can't back up the tran. log.
>> >>
>> >> Backup can not be performed on database 'FTS'. This sub task is
>> >> ignored.
>> >> Backup can not be performed on database 'master'. This sub task is
>> >> ignored.
>> >> Backup can not be performed on database 'msdb'. This sub task is
>> >> ignored.
>> >>
>> >
>> > How do you check to see if it is a simple recovery model? I assume it
>> > isn't, but I am not sure.
>> >
>> > Tom
>> >
>|||Yes - you can just change that there using the drop down. Not sure why
it's full instead of simple. Simple model = less maintenance, but also
= less recoverability. See below links.
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1069109,00.html
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_4l83.asp
>From microsoft...
Simple Recovery Model
This recovery model facilitates the maintenance of a database by making
the transaction log virtually maintenance free. There are limitations
placed on the recoverability of a database if this recovery model is
used.
tran log.
I am getting an error on the Tran log backup, however.
It doesn't tell me why it failed, just that it failed.
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The Job
was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1 (Step
1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
The help center is no help.
The other 3 parts of the maintenance plan seem to go fine.
Also, there is no log in the Sql Logs, but there is in the Event log (where
I got this). There is plenty of disk space, so that is not the problem.
Am I missing something here?
Thanks,
TomSpecify a report file for the maint plan and check that for specific error messages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ubIr1UV5FHA.3876@.TK2MSFTNGP09.phx.gbl...
>I have my maintenance plan set up to backup to disk each day, including the tran log.
> I am getting an error on the Tran log backup, however.
> It doesn't tell me why it failed, just that it failed.
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'DB Maintenance
> Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status: Failed - Invoked on: 2005-11-09 09:24:24 -
> Message: The job failed. The Job was invoked by User FTSOLUTIONS0\tfs. The last step to run was
> step 1 (Step 1).
> For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
> The help center is no help.
> The other 3 parts of the maintenance plan seem to go fine.
> Also, there is no log in the Sql Logs, but there is in the Event log (where I got this). There is
> plenty of disk space, so that is not the problem.
> Am I missing something here?
> Thanks,
> Tom
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:edxoSaV5FHA.1248@.TK2MSFTNGP14.phx.gbl...
> Specify a report file for the maint plan and check that for specific error
> messages.
I did that and got to following:
*****************************************************************************************************
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'VENUS' as 'FTSOLUTIONS0\servicesql' (trusted)
Starting maintenance plan 'All Databases Backup' on 11/9/2005 10:33:23 AM
Backup can not be performed on database 'FTS'. This sub task is ignored.
Backup can not be performed on database 'master'. This sub task is ignored.
[3] Database model: Transaction Log Backup...
Destination: [d:\Microsoft SQL
Server\MSSQL\BACKUP\model_tlog_200511091033.TRN]
** Execution Time: 0 hrs, 0 mins, 1 secs **
[4] Database model: Verifying Backup...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Backup can not be performed on database 'msdb'. This sub task is ignored.
End of maintenance plan 'All Databases Backup' on 11/9/2005 10:33:24 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
*****************************************************************************************************
So it works for some and not others.
And why can't it backup master or msdb - they have log files
Also, there are files that are unreadable there: ERRORLOG, ERRORLOG.1,
SQLAGENT.OUT, SQLAGENT.1. What are they and how do you read them?
Thanks,
Tom
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ubIr1UV5FHA.3876@.TK2MSFTNGP09.phx.gbl...
>>I have my maintenance plan set up to backup to disk each day, including
>>the tran log.
>> I am getting an error on the Tran log backup, however.
>> It doesn't tell me why it failed, just that it failed.
>> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
>> Plan 'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) -
>> Status: Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job
>> failed. The Job was invoked by User FTSOLUTIONS0\tfs. The last step to
>> run was step 1 (Step 1).
>> For more information, see Help and Support Center at
>> http://go.microsoft.com/fwlink/events.asp.
>> The help center is no help.
>> The other 3 parts of the maintenance plan seem to go fine.
>> Also, there is no log in the Sql Logs, but there is in the Event log
>> (where I got this). There is plenty of disk space, so that is not the
>> problem.
>> Am I missing something here?
>> Thanks,
>> Tom
>|||I think that master & msdb are simple recovery model, hence cannot have
tran. log backups. Just remove this portion of your maintenence plan.|||Ok.
But why did FTS not backup?
Tom
"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131562250.817157.160290@.g43g2000cwa.googlegroups.com...
>I think that master & msdb are simple recovery model, hence cannot have
> tran. log backups. Just remove this portion of your maintenence plan.
>|||Most log backups fail because the database is in simple recovery mode. Check
that first. Second, verify the database has had a full backup. Finally, I
assume we're not talking about the msdb and master--but just to be safe,
it's not one of those two, correct?
"tshad" wrote:
> I have my maintenance plan set up to backup to disk each day, including the
> tran log.
> I am getting an error on the Tran log backup, however.
> It doesn't tell me why it failed, just that it failed.
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
> 'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
> Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The Job
> was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1 (Step
> 1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> The help center is no help.
> The other 3 parts of the maintenance plan seem to go fine.
> Also, there is no log in the Sql Logs, but there is in the Event log (where
> I got this). There is plenty of disk space, so that is not the problem.
> Am I missing something here?
> Thanks,
> Tom
>
>|||>From his report, it looks like we are talking about master & msdb. As
well as one other called FTS. Like fnguy said - check to see if if FTS
is simple recovery model. If it is, you can't back up the tran. log.
Backup can not be performed on database 'FTS'. This sub task is
ignored.
Backup can not be performed on database 'master'. This sub task is
ignored.
Backup can not be performed on database 'msdb'. This sub task is
ignored.|||"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:90AC17EA-75F3-45C0-BA2D-C5CB3600AF4C@.microsoft.com...
> Most log backups fail because the database is in simple recovery mode.
> Check
> that first. Second, verify the database has had a full backup. Finally,
> I
> assume we're not talking about the msdb and master--but just to be safe,
> it's not one of those two, correct?
No, it isn't. It's one of our normal databases.
But you mentioned, the database having a full backup.
Do you need to do the Tran backup first?
Thanks,
Tom
>
> "tshad" wrote:
>> I have my maintenance plan set up to backup to disk each day, including
>> the
>> tran log.
>> I am getting an error on the Tran log backup, however.
>> It doesn't tell me why it failed, just that it failed.
>> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
>> Plan
>> 'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
>> Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The
>> Job
>> was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1
>> (Step
>> 1).
>> For more information, see Help and Support Center at
>> http://go.microsoft.com/fwlink/events.asp.
>> The help center is no help.
>> The other 3 parts of the maintenance plan seem to go fine.
>> Also, there is no log in the Sql Logs, but there is in the Event log
>> (where
>> I got this). There is plenty of disk space, so that is not the problem.
>> Am I missing something here?
>> Thanks,
>> Tom
>>|||"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
> >From his report, it looks like we are talking about master & msdb. As
> well as one other called FTS. Like fnguy said - check to see if if FTS
> is simple recovery model. If it is, you can't back up the tran. log.
> Backup can not be performed on database 'FTS'. This sub task is
> ignored.
> Backup can not be performed on database 'master'. This sub task is
> ignored.
> Backup can not be performed on database 'msdb'. This sub task is
> ignored.
>
How do you check to see if it is a simple recovery model? I assume it
isn't, but I am not sure.
Tom|||Check the recovery model of your database.
If the FTS database is in Simple recovery model, then log backups make no
sense.
> Also, there are files that are unreadable there: ERRORLOG, ERRORLOG.1,
> SQLAGENT.OUT, SQLAGENT.1. What are they and how do you read them?
>
ERRORLOG.n are the SQL error logs. They are simple text files. You should
be able to open them in notepad unless there is some strange protection
applied. As an administrator you should certainly be agle to read them.
Kevin Farlee|||As Kevin suggested, it is probably in simple recovery mode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23LFHRAW5FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Ok.
> But why did FTS not backup?
> Tom
> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
> news:1131562250.817157.160290@.g43g2000cwa.googlegroups.com...
>>I think that master & msdb are simple recovery model, hence cannot have
>> tran. log backups. Just remove this portion of your maintenence plan.
>|||> How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
sp_helpdb, for instance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
> news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >From his report, it looks like we are talking about master & msdb. As
>> well as one other called FTS. Like fnguy said - check to see if if FTS
>> is simple recovery model. If it is, you can't back up the tran. log.
>> Backup can not be performed on database 'FTS'. This sub task is
>> ignored.
>> Backup can not be performed on database 'master'. This sub task is
>> ignored.
>> Backup can not be performed on database 'msdb'. This sub task is
>> ignored.
> How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
> Tom
>|||Pls execute following SP from Query Analyser and post it's output:
sp_helpdb 'fts'
"tshad" wrote:
> "fnguy" <fnguy@.discussions.microsoft.com> wrote in message
> news:90AC17EA-75F3-45C0-BA2D-C5CB3600AF4C@.microsoft.com...
> > Most log backups fail because the database is in simple recovery mode.
> > Check
> > that first. Second, verify the database has had a full backup. Finally,
> > I
> > assume we're not talking about the msdb and master--but just to be safe,
> > it's not one of those two, correct?
> No, it isn't. It's one of our normal databases.
> But you mentioned, the database having a full backup.
> Do you need to do the Tran backup first?
> Thanks,
> Tom|||How do you check to see if it is a simple recovery model? I assume it
isn't, but I am not sure.
--
Also - open enterprise manager, right click the database in question,
and choose properties. Go to options tab. See what exists under the
Recovery model section. If it's simple, then all you can do is full
backups. If it's full, then you can do logs.
Tibor Karaszi wrote:
> > How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
> sp_helpdb, for instance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
> >
> > "Corey Bunch" <unc27932@.yahoo.com> wrote in message
> > news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
> >> >From his report, it looks like we are talking about master & msdb. As
> >> well as one other called FTS. Like fnguy said - check to see if if FTS
> >> is simple recovery model. If it is, you can't back up the tran. log.
> >>
> >> Backup can not be performed on database 'FTS'. This sub task is
> >> ignored.
> >> Backup can not be performed on database 'master'. This sub task is
> >> ignored.
> >> Backup can not be performed on database 'msdb'. This sub task is
> >> ignored.
> >>
> >
> > How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
> >
> > Tom
> >|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ejxz3mc5FHA.2364@.TK2MSFTNGP12.phx.gbl...
>> How do you check to see if it is a simple recovery model? I assume it
>> isn't, but I am not sure.
> sp_helpdb, for instance.
That does show it as
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
>> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
>> news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >From his report, it looks like we are talking about master & msdb. As
>> well as one other called FTS. Like fnguy said - check to see if if FTS
>> is simple recovery model. If it is, you can't back up the tran. log.
>> Backup can not be performed on database 'FTS'. This sub task is
>> ignored.
>> Backup can not be performed on database 'master'. This sub task is
>> ignored.
>> Backup can not be performed on database 'msdb'. This sub task is
>> ignored.
>>
>> How do you check to see if it is a simple recovery model? I assume it
>> isn't, but I am not sure.
>> Tom
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ejxz3mc5FHA.2364@.TK2MSFTNGP12.phx.gbl...
>> How do you check to see if it is a simple recovery model? I assume it
>> isn't, but I am not sure.
> sp_helpdb, for instance.
That was it.
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
Recovery=SIMPLE, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
IsAutoUpdateStatistics, IsFullTextEnabled
But how did it get that way? What did I do to make it that way? My other
databases aren't that way.
Can I change that?
Thanks,
Tom
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
>> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
>> news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >From his report, it looks like we are talking about master & msdb. As
>> well as one other called FTS. Like fnguy said - check to see if if FTS
>> is simple recovery model. If it is, you can't back up the tran. log.
>> Backup can not be performed on database 'FTS'. This sub task is
>> ignored.
>> Backup can not be performed on database 'master'. This sub task is
>> ignored.
>> Backup can not be performed on database 'msdb'. This sub task is
>> ignored.
>>
>> How do you check to see if it is a simple recovery model? I assume it
>> isn't, but I am not sure.
>> Tom
>|||Yes, you can change it using the ALTER DATABASE command.
How it got set to simple in the first place? I can't answer that, except saying that someone or some
program did it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message news:uP89yUh5FHA.140@.TK2MSFTNGP10.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:ejxz3mc5FHA.2364@.TK2MSFTNGP12.phx.gbl...
>> How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
>> sp_helpdb, for instance.
> That was it.
> Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539,
> Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsTornPageDetectionEnabled,
> IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled
> But how did it get that way? What did I do to make it that way? My other databases aren't that
> way.
> Can I change that?
> Thanks,
> Tom
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
>> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
>> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
>> news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >From his report, it looks like we are talking about master & msdb. As
>> well as one other called FTS. Like fnguy said - check to see if if FTS
>> is simple recovery model. If it is, you can't back up the tran. log.
>> Backup can not be performed on database 'FTS'. This sub task is
>> ignored.
>> Backup can not be performed on database 'master'. This sub task is
>> ignored.
>> Backup can not be performed on database 'msdb'. This sub task is
>> ignored.
>>
>> How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
>> Tom
>>
>|||"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131630158.667899.30870@.g44g2000cwa.googlegroups.com...
> How do you check to see if it is a simple recovery model? I assume it
> isn't, but I am not sure.
> --
> Also - open enterprise manager, right click the database in question,
> and choose properties. Go to options tab. See what exists under the
> Recovery model section. If it's simple, then all you can do is full
> backups. If it's full, then you can do logs.
>
Can I just change that there?
Why would you want a simple model?
It does have a tran log defined for it and it is the only one of 5 databases
I have that isn't full. So what causes it to be simple I just created
another database, it has no question about it and it automatically made it
simple. I originally created FTS in this way - so what would cause it to be
simple - I know I didn't do it manually.
Thanks,
Tom
>
> Tibor Karaszi wrote:
>> > How do you check to see if it is a simple recovery model? I assume it
>> > isn't, but I am not sure.
>> sp_helpdb, for instance.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
>> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
>> >
>> > "Corey Bunch" <unc27932@.yahoo.com> wrote in message
>> > news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >> >From his report, it looks like we are talking about master & msdb.
>> >> >As
>> >> well as one other called FTS. Like fnguy said - check to see if if
>> >> FTS
>> >> is simple recovery model. If it is, you can't back up the tran. log.
>> >>
>> >> Backup can not be performed on database 'FTS'. This sub task is
>> >> ignored.
>> >> Backup can not be performed on database 'master'. This sub task is
>> >> ignored.
>> >> Backup can not be performed on database 'msdb'. This sub task is
>> >> ignored.
>> >>
>> >
>> > How do you check to see if it is a simple recovery model? I assume it
>> > isn't, but I am not sure.
>> >
>> > Tom
>> >
>|||Yes - you can just change that there using the drop down. Not sure why
it's full instead of simple. Simple model = less maintenance, but also
= less recoverability. See below links.
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1069109,00.html
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_4l83.asp
>From microsoft...
Simple Recovery Model
This recovery model facilitates the maintenance of a database by making
the transaction log virtually maintenance free. There are limitations
placed on the recoverability of a database if this recovery model is
used.
Friday, February 24, 2012
Backup/Restore Tran Log
Hello Experts,
This is my first time that I work with the logic of
Transaction Log Backups/Restores seriously.
--First I made a full backup, then I backup my log in a
file:
BACKUP LOG [TEST_3]
TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH INIT, NAME = N'TEST 3 backup', NOSKIP
--then I wanted to restore this log:
declare @.FilePosition as int
SELECT @.FilePosition = position
FROM msdb..backupset
WHERE database_name = 'TEST_3'
AND type != 'F'
AND backup_set_id = (SELECT MAX(backup_set_id)
FROM msdb..backupset
WHERE database_name
= 'TEST_3')
RESTORE LOG TEST_3
FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH FILE = @.FilePosition
Even though I don't have any other connection in my DB
the error message I get is:
Server: Msg 3101, Level 16, State 1, Line 11
Exclusive access could not be obtained because the
database is in use.
Server: Msg 3013, Level 16, State 1, Line 11
RESTORE LOG is terminating abnormally.
Can you please post a transaction backup/restore example?
Thanks in advance!
Hi,
It seems there is some one connected to your database. Execute sp_who and
check the user connected to the database, either issue KILL SPID from the
query analyzer to kill the user connected or execute the below command just
before the restore:-
Alter database TEST_3 set single_user with rollback immediate
go
Restore command -- Execute
go
Alter database TEST_3 set multi_user
Thanks
Hari
MCDBA
"Konstantinos Michas" <anonymous@.discussions.microsoft.com> wrote in message
news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
> Hello Experts,
> This is my first time that I work with the logic of
> Transaction Log Backups/Restores seriously.
> --First I made a full backup, then I backup my log in a
> file:
> BACKUP LOG [TEST_3]
> TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH INIT, NAME = N'TEST 3 backup', NOSKIP
> --then I wanted to restore this log:
> declare @.FilePosition as int
> SELECT @.FilePosition = position
> FROM msdb..backupset
> WHERE database_name = 'TEST_3'
> AND type != 'F'
> AND backup_set_id = (SELECT MAX(backup_set_id)
> FROM msdb..backupset
> WHERE database_name
> = 'TEST_3')
> RESTORE LOG TEST_3
> FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH FILE = @.FilePosition
> Even though I don't have any other connection in my DB
> the error message I get is:
> Server: Msg 3101, Level 16, State 1, Line 11
> Exclusive access could not be obtained because the
> database is in use.
> Server: Msg 3013, Level 16, State 1, Line 11
> RESTORE LOG is terminating abnormally.
> Can you please post a transaction backup/restore example?
> Thanks in advance!
|||Thanks for the reply, the error insists to appear
I'll try to KILL SPIDs
>--Original Message--
>Hi,
>It seems there is some one connected to your database.
Execute sp_who and
>check the user connected to the database, either issue
KILL SPID from the
>query analyzer to kill the user connected or execute
the below command just
>before the restore:-
>Alter database TEST_3 set single_user with rollback
immediate
>go
>Restore command -- Execute
>go
>Alter database TEST_3 set multi_user
>Thanks
>Hari
>MCDBA
>
>
>"Konstantinos Michas"
<anonymous@.discussions.microsoft.com> wrote in message[vbcol=seagreen]
>news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
Server\MSSQL\BACKUP\BACKUP[vbcol=seagreen]
example?
>
>.
>
This is my first time that I work with the logic of
Transaction Log Backups/Restores seriously.
--First I made a full backup, then I backup my log in a
file:
BACKUP LOG [TEST_3]
TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH INIT, NAME = N'TEST 3 backup', NOSKIP
--then I wanted to restore this log:
declare @.FilePosition as int
SELECT @.FilePosition = position
FROM msdb..backupset
WHERE database_name = 'TEST_3'
AND type != 'F'
AND backup_set_id = (SELECT MAX(backup_set_id)
FROM msdb..backupset
WHERE database_name
= 'TEST_3')
RESTORE LOG TEST_3
FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH FILE = @.FilePosition
Even though I don't have any other connection in my DB
the error message I get is:
Server: Msg 3101, Level 16, State 1, Line 11
Exclusive access could not be obtained because the
database is in use.
Server: Msg 3013, Level 16, State 1, Line 11
RESTORE LOG is terminating abnormally.
Can you please post a transaction backup/restore example?
Thanks in advance!
Hi,
It seems there is some one connected to your database. Execute sp_who and
check the user connected to the database, either issue KILL SPID from the
query analyzer to kill the user connected or execute the below command just
before the restore:-
Alter database TEST_3 set single_user with rollback immediate
go
Restore command -- Execute
go
Alter database TEST_3 set multi_user
Thanks
Hari
MCDBA
"Konstantinos Michas" <anonymous@.discussions.microsoft.com> wrote in message
news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
> Hello Experts,
> This is my first time that I work with the logic of
> Transaction Log Backups/Restores seriously.
> --First I made a full backup, then I backup my log in a
> file:
> BACKUP LOG [TEST_3]
> TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH INIT, NAME = N'TEST 3 backup', NOSKIP
> --then I wanted to restore this log:
> declare @.FilePosition as int
> SELECT @.FilePosition = position
> FROM msdb..backupset
> WHERE database_name = 'TEST_3'
> AND type != 'F'
> AND backup_set_id = (SELECT MAX(backup_set_id)
> FROM msdb..backupset
> WHERE database_name
> = 'TEST_3')
> RESTORE LOG TEST_3
> FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH FILE = @.FilePosition
> Even though I don't have any other connection in my DB
> the error message I get is:
> Server: Msg 3101, Level 16, State 1, Line 11
> Exclusive access could not be obtained because the
> database is in use.
> Server: Msg 3013, Level 16, State 1, Line 11
> RESTORE LOG is terminating abnormally.
> Can you please post a transaction backup/restore example?
> Thanks in advance!
|||Thanks for the reply, the error insists to appear
I'll try to KILL SPIDs
>--Original Message--
>Hi,
>It seems there is some one connected to your database.
Execute sp_who and
>check the user connected to the database, either issue
KILL SPID from the
>query analyzer to kill the user connected or execute
the below command just
>before the restore:-
>Alter database TEST_3 set single_user with rollback
immediate
>go
>Restore command -- Execute
>go
>Alter database TEST_3 set multi_user
>Thanks
>Hari
>MCDBA
>
>
>"Konstantinos Michas"
<anonymous@.discussions.microsoft.com> wrote in message[vbcol=seagreen]
>news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
Server\MSSQL\BACKUP\BACKUP[vbcol=seagreen]
example?
>
>.
>
Backup/Restore Tran Log
Hello Experts,
This is my first time that I work with the logic of
Transaction Log Backups/Restores seriously.
--First I made a full backup, then I backup my log in a
file:
BACKUP LOG [TEST_3]
TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH INIT, NAME = N'TEST 3 backup', NOSKIP
--then I wanted to restore this log:
declare @.FilePosition as int
SELECT @.FilePosition = position
FROM msdb..backupset
WHERE database_name = 'TEST_3'
AND type != 'F'
AND backup_set_id = (SELECT MAX(backup_set_id)
FROM msdb..backupset
WHERE database_name
= 'TEST_3')
RESTORE LOG TEST_3
FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH FILE = @.FilePosition
Even though I don't have any other connection in my DB
the error message I get is:
Server: Msg 3101, Level 16, State 1, Line 11
Exclusive access could not be obtained because the
database is in use.
Server: Msg 3013, Level 16, State 1, Line 11
RESTORE LOG is terminating abnormally.
Can you please post a transaction backup/restore example?
Thanks in advance!Hi,
It seems there is some one connected to your database. Execute sp_who and
check the user connected to the database, either issue KILL SPID from the
query analyzer to kill the user connected or execute the below command just
before the restore:-
Alter database TEST_3 set single_user with rollback immediate
go
Restore command -- Execute
go
Alter database TEST_3 set multi_user
Thanks
Hari
MCDBA
"Konstantinos Michas" <anonymous@.discussions.microsoft.com> wrote in message
news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
> Hello Experts,
> This is my first time that I work with the logic of
> Transaction Log Backups/Restores seriously.
> --First I made a full backup, then I backup my log in a
> file:
> BACKUP LOG [TEST_3]
> TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH INIT, NAME = N'TEST 3 backup', NOSKIP
> --then I wanted to restore this log:
> declare @.FilePosition as int
> SELECT @.FilePosition = position
> FROM msdb..backupset
> WHERE database_name = 'TEST_3'
> AND type != 'F'
> AND backup_set_id = (SELECT MAX(backup_set_id)
> FROM msdb..backupset
> WHERE database_name
> = 'TEST_3')
> RESTORE LOG TEST_3
> FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH FILE = @.FilePosition
> Even though I don't have any other connection in my DB
> the error message I get is:
> Server: Msg 3101, Level 16, State 1, Line 11
> Exclusive access could not be obtained because the
> database is in use.
> Server: Msg 3013, Level 16, State 1, Line 11
> RESTORE LOG is terminating abnormally.
> Can you please post a transaction backup/restore example?
> Thanks in advance!|||Thanks for the reply, the error insists to appear
I'll try to KILL SPIDs
>--Original Message--
>Hi,
>It seems there is some one connected to your database.
Execute sp_who and
>check the user connected to the database, either issue
KILL SPID from the
>query analyzer to kill the user connected or execute
the below command just
>before the restore:-
>Alter database TEST_3 set single_user with rollback
immediate
>go
>Restore command -- Execute
>go
>Alter database TEST_3 set multi_user
>Thanks
>Hari
>MCDBA
>
>
>"Konstantinos Michas"
<anonymous@.discussions.microsoft.com> wrote in message
>news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
Server\MSSQL\BACKUP\BACKUP[vbcol=seagree
n]
example?[vbcol=seagreen]
>
>.
>
This is my first time that I work with the logic of
Transaction Log Backups/Restores seriously.
--First I made a full backup, then I backup my log in a
file:
BACKUP LOG [TEST_3]
TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH INIT, NAME = N'TEST 3 backup', NOSKIP
--then I wanted to restore this log:
declare @.FilePosition as int
SELECT @.FilePosition = position
FROM msdb..backupset
WHERE database_name = 'TEST_3'
AND type != 'F'
AND backup_set_id = (SELECT MAX(backup_set_id)
FROM msdb..backupset
WHERE database_name
= 'TEST_3')
RESTORE LOG TEST_3
FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH FILE = @.FilePosition
Even though I don't have any other connection in my DB
the error message I get is:
Server: Msg 3101, Level 16, State 1, Line 11
Exclusive access could not be obtained because the
database is in use.
Server: Msg 3013, Level 16, State 1, Line 11
RESTORE LOG is terminating abnormally.
Can you please post a transaction backup/restore example?
Thanks in advance!Hi,
It seems there is some one connected to your database. Execute sp_who and
check the user connected to the database, either issue KILL SPID from the
query analyzer to kill the user connected or execute the below command just
before the restore:-
Alter database TEST_3 set single_user with rollback immediate
go
Restore command -- Execute
go
Alter database TEST_3 set multi_user
Thanks
Hari
MCDBA
"Konstantinos Michas" <anonymous@.discussions.microsoft.com> wrote in message
news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
> Hello Experts,
> This is my first time that I work with the logic of
> Transaction Log Backups/Restores seriously.
> --First I made a full backup, then I backup my log in a
> file:
> BACKUP LOG [TEST_3]
> TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH INIT, NAME = N'TEST 3 backup', NOSKIP
> --then I wanted to restore this log:
> declare @.FilePosition as int
> SELECT @.FilePosition = position
> FROM msdb..backupset
> WHERE database_name = 'TEST_3'
> AND type != 'F'
> AND backup_set_id = (SELECT MAX(backup_set_id)
> FROM msdb..backupset
> WHERE database_name
> = 'TEST_3')
> RESTORE LOG TEST_3
> FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH FILE = @.FilePosition
> Even though I don't have any other connection in my DB
> the error message I get is:
> Server: Msg 3101, Level 16, State 1, Line 11
> Exclusive access could not be obtained because the
> database is in use.
> Server: Msg 3013, Level 16, State 1, Line 11
> RESTORE LOG is terminating abnormally.
> Can you please post a transaction backup/restore example?
> Thanks in advance!|||Thanks for the reply, the error insists to appear
I'll try to KILL SPIDs
>--Original Message--
>Hi,
>It seems there is some one connected to your database.
Execute sp_who and
>check the user connected to the database, either issue
KILL SPID from the
>query analyzer to kill the user connected or execute
the below command just
>before the restore:-
>Alter database TEST_3 set single_user with rollback
immediate
>go
>Restore command -- Execute
>go
>Alter database TEST_3 set multi_user
>Thanks
>Hari
>MCDBA
>
>
>"Konstantinos Michas"
<anonymous@.discussions.microsoft.com> wrote in message
>news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
Server\MSSQL\BACKUP\BACKUP[vbcol=seagree
n]
example?[vbcol=seagreen]
>
>.
>
Backup/Restore Tran Log
Hello Experts,
This is my first time that I work with the logic of
Transaction Log Backups/Restores seriously.
--First I made a full backup, then I backup my log in a
file:
BACKUP LOG [TEST_3]
TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH INIT, NAME = N'TEST 3 backup', NOSKIP
--then I wanted to restore this log:
declare @.FilePosition as int
SELECT @.FilePosition = position
FROM msdb..backupset
WHERE database_name = 'TEST_3'
AND type != 'F'
AND backup_set_id = (SELECT MAX(backup_set_id)
FROM msdb..backupset
WHERE database_name
= 'TEST_3')
RESTORE LOG TEST_3
FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH FILE = @.FilePosition
Even though I don't have any other connection in my DB
the error message I get is:
Server: Msg 3101, Level 16, State 1, Line 11
Exclusive access could not be obtained because the
database is in use.
Server: Msg 3013, Level 16, State 1, Line 11
RESTORE LOG is terminating abnormally.
Can you please post a transaction backup/restore example?
Thanks in advance!Hi,
It seems there is some one connected to your database. Execute sp_who and
check the user connected to the database, either issue KILL SPID from the
query analyzer to kill the user connected or execute the below command just
before the restore:-
Alter database TEST_3 set single_user with rollback immediate
go
Restore command -- Execute
go
Alter database TEST_3 set multi_user
Thanks
Hari
MCDBA
"Konstantinos Michas" <anonymous@.discussions.microsoft.com> wrote in message
news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
> Hello Experts,
> This is my first time that I work with the logic of
> Transaction Log Backups/Restores seriously.
> --First I made a full backup, then I backup my log in a
> file:
> BACKUP LOG [TEST_3]
> TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH INIT, NAME = N'TEST 3 backup', NOSKIP
> --then I wanted to restore this log:
> declare @.FilePosition as int
> SELECT @.FilePosition = position
> FROM msdb..backupset
> WHERE database_name = 'TEST_3'
> AND type != 'F'
> AND backup_set_id = (SELECT MAX(backup_set_id)
> FROM msdb..backupset
> WHERE database_name
> = 'TEST_3')
> RESTORE LOG TEST_3
> FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH FILE = @.FilePosition
> Even though I don't have any other connection in my DB
> the error message I get is:
> Server: Msg 3101, Level 16, State 1, Line 11
> Exclusive access could not be obtained because the
> database is in use.
> Server: Msg 3013, Level 16, State 1, Line 11
> RESTORE LOG is terminating abnormally.
> Can you please post a transaction backup/restore example?
> Thanks in advance!|||Thanks for the reply, the error insists to appear :)
I'll try to KILL SPIDs
>--Original Message--
>Hi,
>It seems there is some one connected to your database.
Execute sp_who and
>check the user connected to the database, either issue
KILL SPID from the
>query analyzer to kill the user connected or execute
the below command just
>before the restore:-
>Alter database TEST_3 set single_user with rollback
immediate
>go
>Restore command -- Execute
>go
>Alter database TEST_3 set multi_user
>Thanks
>Hari
>MCDBA
>
>
>"Konstantinos Michas"
<anonymous@.discussions.microsoft.com> wrote in message
>news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
>> Hello Experts,
>> This is my first time that I work with the logic of
>> Transaction Log Backups/Restores seriously.
>> --First I made a full backup, then I backup my log in a
>> file:
>> BACKUP LOG [TEST_3]
>> TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
>> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
>> WITH INIT, NAME = N'TEST 3 backup', NOSKIP
>> --then I wanted to restore this log:
>> declare @.FilePosition as int
>> SELECT @.FilePosition = position
>> FROM msdb..backupset
>> WHERE database_name = 'TEST_3'
>> AND type != 'F'
>> AND backup_set_id = (SELECT MAX(backup_set_id)
>> FROM msdb..backupset
>> WHERE database_name
>> = 'TEST_3')
>> RESTORE LOG TEST_3
>> FROM DISK = N'D:\Microsoft SQL
Server\MSSQL\BACKUP\BACKUP
>> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
>> WITH FILE = @.FilePosition
>> Even though I don't have any other connection in my DB
>> the error message I get is:
>> Server: Msg 3101, Level 16, State 1, Line 11
>> Exclusive access could not be obtained because the
>> database is in use.
>> Server: Msg 3013, Level 16, State 1, Line 11
>> RESTORE LOG is terminating abnormally.
>> Can you please post a transaction backup/restore
example?
>> Thanks in advance!
>
>.
>
This is my first time that I work with the logic of
Transaction Log Backups/Restores seriously.
--First I made a full backup, then I backup my log in a
file:
BACKUP LOG [TEST_3]
TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH INIT, NAME = N'TEST 3 backup', NOSKIP
--then I wanted to restore this log:
declare @.FilePosition as int
SELECT @.FilePosition = position
FROM msdb..backupset
WHERE database_name = 'TEST_3'
AND type != 'F'
AND backup_set_id = (SELECT MAX(backup_set_id)
FROM msdb..backupset
WHERE database_name
= 'TEST_3')
RESTORE LOG TEST_3
FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH FILE = @.FilePosition
Even though I don't have any other connection in my DB
the error message I get is:
Server: Msg 3101, Level 16, State 1, Line 11
Exclusive access could not be obtained because the
database is in use.
Server: Msg 3013, Level 16, State 1, Line 11
RESTORE LOG is terminating abnormally.
Can you please post a transaction backup/restore example?
Thanks in advance!Hi,
It seems there is some one connected to your database. Execute sp_who and
check the user connected to the database, either issue KILL SPID from the
query analyzer to kill the user connected or execute the below command just
before the restore:-
Alter database TEST_3 set single_user with rollback immediate
go
Restore command -- Execute
go
Alter database TEST_3 set multi_user
Thanks
Hari
MCDBA
"Konstantinos Michas" <anonymous@.discussions.microsoft.com> wrote in message
news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
> Hello Experts,
> This is my first time that I work with the logic of
> Transaction Log Backups/Restores seriously.
> --First I made a full backup, then I backup my log in a
> file:
> BACKUP LOG [TEST_3]
> TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH INIT, NAME = N'TEST 3 backup', NOSKIP
> --then I wanted to restore this log:
> declare @.FilePosition as int
> SELECT @.FilePosition = position
> FROM msdb..backupset
> WHERE database_name = 'TEST_3'
> AND type != 'F'
> AND backup_set_id = (SELECT MAX(backup_set_id)
> FROM msdb..backupset
> WHERE database_name
> = 'TEST_3')
> RESTORE LOG TEST_3
> FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH FILE = @.FilePosition
> Even though I don't have any other connection in my DB
> the error message I get is:
> Server: Msg 3101, Level 16, State 1, Line 11
> Exclusive access could not be obtained because the
> database is in use.
> Server: Msg 3013, Level 16, State 1, Line 11
> RESTORE LOG is terminating abnormally.
> Can you please post a transaction backup/restore example?
> Thanks in advance!|||Thanks for the reply, the error insists to appear :)
I'll try to KILL SPIDs
>--Original Message--
>Hi,
>It seems there is some one connected to your database.
Execute sp_who and
>check the user connected to the database, either issue
KILL SPID from the
>query analyzer to kill the user connected or execute
the below command just
>before the restore:-
>Alter database TEST_3 set single_user with rollback
immediate
>go
>Restore command -- Execute
>go
>Alter database TEST_3 set multi_user
>Thanks
>Hari
>MCDBA
>
>
>"Konstantinos Michas"
<anonymous@.discussions.microsoft.com> wrote in message
>news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
>> Hello Experts,
>> This is my first time that I work with the logic of
>> Transaction Log Backups/Restores seriously.
>> --First I made a full backup, then I backup my log in a
>> file:
>> BACKUP LOG [TEST_3]
>> TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
>> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
>> WITH INIT, NAME = N'TEST 3 backup', NOSKIP
>> --then I wanted to restore this log:
>> declare @.FilePosition as int
>> SELECT @.FilePosition = position
>> FROM msdb..backupset
>> WHERE database_name = 'TEST_3'
>> AND type != 'F'
>> AND backup_set_id = (SELECT MAX(backup_set_id)
>> FROM msdb..backupset
>> WHERE database_name
>> = 'TEST_3')
>> RESTORE LOG TEST_3
>> FROM DISK = N'D:\Microsoft SQL
Server\MSSQL\BACKUP\BACKUP
>> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
>> WITH FILE = @.FilePosition
>> Even though I don't have any other connection in my DB
>> the error message I get is:
>> Server: Msg 3101, Level 16, State 1, Line 11
>> Exclusive access could not be obtained because the
>> database is in use.
>> Server: Msg 3013, Level 16, State 1, Line 11
>> RESTORE LOG is terminating abnormally.
>> Can you please post a transaction backup/restore
example?
>> Thanks in advance!
>
>.
>
Subscribe to:
Posts (Atom)