Backup Error

I saw the following logged in the event log:
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 9/14/2005
Time: 12:20:00 AM
User: N/A
Computer: BRADFORDDC01
Description:
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'Master Maintenance Plan'' (0x6FF812A8ED86514CBDF51CE01D6147ED) - Status:
Failed - Invoked on: 2005-09-14 00:20:00 - Message: The job failed. The Job
was invoked by Schedule 9 (Schedule 1). The last step to run was step 1
(Step 1).
The error appears to occur when backing up the transaction log of the
"master" database. I have verified that all of the databases are set to a
recovery mode of "Full," and also added the "-SupportComputedColumn" flag to
the job (as per MS KB). I created a new maintenance plan for the master DB
to isolate the
failing job, and I am still receiving this error, can anyone offer any
insight?
Thanks,
RobRobert,
The transaction log of the Master database cannot be backed up. Use a full
database backup for the Master system database.
HTH
Jerry
"Robert Zahm" <robzahm@.hotmail.com> wrote in message
news:eBgoxEVuFHA.1032@.TK2MSFTNGP12.phx.gbl...
>I saw the following logged in the event log:
> Event Type: Warning
> Event Source: SQLSERVERAGENT
> Event Category: Job Engine
> Event ID: 208
> Date: 9/14/2005
> Time: 12:20:00 AM
> User: N/A
> Computer: BRADFORDDC01
> Description:
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
> Plan 'Master Maintenance Plan'' (0x6FF812A8ED86514CBDF51CE01D6147ED) -
> Status: Failed - Invoked on: 2005-09-14 00:20:00 - Message: The job
> failed. The Job was invoked by Schedule 9 (Schedule 1). The last step to
> run was step 1 (Step 1).
>
> The error appears to occur when backing up the transaction log of the
> "master" database. I have verified that all of the databases are set to a
> recovery mode of "Full," and also added the "-SupportComputedColumn" flag
> to
> the job (as per MS KB). I created a new maintenance plan for the master
> DB to isolate the
> failing job, and I am still receiving this error, can anyone offer any
> insight?
> Thanks,
> Rob
>
>|||Hi,
You can not backup the trsnaction log backup of Master database. Since you
are not doing any transaction inside Master it is not required
to do a log backup in Master database. It is always if you do a Full
database backup for MASTER database daily once.
It is not good practice to crete tables/ objects in Master database.
Thanks
Hari
SQL Server MVP
"Robert Zahm" <robzahm@.hotmail.com> wrote in message
news:eBgoxEVuFHA.1032@.TK2MSFTNGP12.phx.gbl...
>I saw the following logged in the event log:
> Event Type: Warning
> Event Source: SQLSERVERAGENT
> Event Category: Job Engine
> Event ID: 208
> Date: 9/14/2005
> Time: 12:20:00 AM
> User: N/A
> Computer: BRADFORDDC01
> Description:
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
> Plan 'Master Maintenance Plan'' (0x6FF812A8ED86514CBDF51CE01D6147ED) -
> Status: Failed - Invoked on: 2005-09-14 00:20:00 - Message: The job
> failed. The Job was invoked by Schedule 9 (Schedule 1). The last step to
> run was step 1 (Step 1).
>
> The error appears to occur when backing up the transaction log of the
> "master" database. I have verified that all of the databases are set to a
> recovery mode of "Full," and also added the "-SupportComputedColumn" flag
> to
> the job (as per MS KB). I created a new maintenance plan for the master
> DB to isolate the
> failing job, and I am still receiving this error, can anyone offer any
> insight?
> Thanks,
> Rob
>
>

Backup and restore of mdf database

Hi,

I've got a following problem: I have a VB 2005 application that uses SQL Server 2005 Express as DB engine. This application has it's own database stored in mdf file, e.g. invoices.mdf with corresponding log file.

I'd like to give users opportunity to backup and restore this DB file from this application (there won't be a system administrator, only end-users). Unfortunatelly after I connect to this database (using user instance of SQL Server), I'm unable to read or copy it, despite the fact, that I close all connections. And I'm sure that all connections are closed.

Database files become accessible for reading after I close the whole application.

I've tried to detach this database using sp_detach_db prcedure, but it can't be done with database I'm connected to.

I will be grateful for any help.

Tomasz

hi Tomasz,

obviously at least 1 connection is still open referencing the database you are dealing with..

try changing the database context to the master system database (that can be done in .Net via the workingConnection.ChangeDatabase("master") statement...)

a workaround to "orphaned" connections could even be an alter database statement to close all pending connections, like

USE master;

GO

ALTER DATABASE dbName

SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO-- and the counterpart is

ALTER DATABASE dbName

SET MULTI_USER WITH ROLLBACK IMMEDIATE;

but I'd prefer to find and close all pending connections...

regards

|||

We just had a couple threads about this topic in the forum over the last week. I've summarized how to use SMO to handle Backup and Restore of User Instance database in my blog. Backup should work with connection open but restore is not so forgiving.

Mike

|||

Hi,

thanks for this advice, I will try today to implement backup/restore approach according to the blog.

I'm 100% sure that I close all connections to the DB. I've made a very simple application which just connects to the database and immediatly closes this one an single connection. After that mdf file remains locked until I close the application. It seems, that there is some "stealth" connection, which I can't close directly. You can replicate this behaviour by creating a new application which on start does:

Dim lCon As New SqlClient.SqlConnection(sConnStr)

lCon.Open()

lCon.Close()

lCon.Dispose() 'even that won't help

And then try to copy mdf file to other location while this application is still running.

greetings

Tomasz

|||

Hi,

I've got the same problem as Tomasz. I read all that was written and I was wondering if there isn't a command capable of closing all connection to the database file (.mdf). I just need to upload it to a remote location not restore it.

Thanks in advance