Cannot backup databases from SQL Server 2008

Question

I'm trying to back up the SQL Server 2008 database on Windows Server 2008, using instructions from: http://msdn.microsoft.com/en-us/library/ms253070%28VS.80%29.aspx

Everything is pretty straightforward (just follow through the wizard to trigger a backup) however the SQL backup fails with the following error:

--- start error ---

Execute Maintenance Plan

Execute maintenance plan. TFS backup (Error) Messages Execution failed. See the maintenance plan and SQL Server Agent job history logs for details.

ADDITIONAL INFORMATION:

Job 'TFS backup.Subplan_1' failed. (SqlManagerUI)

--- end error ---

Here is a copy'n'paste from the log (there were only 2 relevant entries):

--- start log entry ---

Date,Source,Severity,Log ID,Message,Process ID,Mail Item ID,Account ID,Last Modified,Last Modified By,Category,Event,User,Computer

07/07/2009 12:21:22,Service Control Manager,Information,,The description for Event ID '1073748860' in Source 'Service Control Manager' cannot be found. The local computer may not have the necessary registry information or message DLL files to display the message or you may not have permission to access them. The following information is part of the event:'Diagnostic System Host' 'running',,,,,,(0),1073748860,,WIN2008-TFS

07/07/2009 12:20:42,SQLSERVERAGENT,Warning,,SQL Server Scheduled Job 'TFS backup.Subplan_1' (0x0140518D9AD209468CD45ECA97EC0B93) - Status: Failed - Invoked on: 2009-07-07 12:20:41 - Message: The job failed. The Job was invoked by User WIN2008-TFS\Administrator. The last step to run was step 1 (Subplan_1).,,,,,,Job Engine,1073742032,,WIN2008-TFS

--- end log entry ---

UPDATE - 2009-07-08 - moved VMware machine to different HD as I suspect the HD might have been going bad as it was exhibiting bad sectors. — Contango, Jul 08 '09 at 18:39
UPDATE - 2009-07-08 - using Acronis, rolled server machine back 25 days to a known good copy on a known good HD. — Contango, Jul 08 '09 at 18:40
UPDATE - 2009-07-08 - SP1 did the trick - everything works perfectly! — Contango, Jul 08 '09 at 19:00

score 1 · Answer 1 · answered Aug 20 '10 at 16:54

1

Make sure Intergration Services are installed

answered Aug 20 '10 at 16:54

score 1 · Answer 2 · answered Jul 07 '09 at 14:22

First, this is really a standard SQL Server backup. TFS data is there, but that's not relevant.

Second, in SSMS, open your maintenance plan for editing. Make sure that you have logging enabled, and then run the plan. This will create text files in your /LOG folder under the SQL Server installation folder. Check that and it will have more details about what specifically is wrong. Post that and we can help you debug the issue.

score 0 · Answer 3 · answered Jul 07 '09 at 13:53

0

What does the Windows Event Viewer say, look under System and Applications.

The only information I have found for 1073748860 is "The service has changed its running state.", so does the SQL Server crash when doing the backup?

In your question you say you are wanting to backup a SQL 2008 database, however the article you are following is for a Windows Team Foundation Server (I am not sure what this is so it might be what you are after?), but the following are links for backing up a SQL 2008 database:

T-SQL: http://msdn.microsoft.com/en-us/library/ms186865.aspx Management Studio: http://msdn.microsoft.com/en-us/library/ms187510.aspx

Lima

answered Jul 07 '09 at 13:53

Lima

374
3
12

I am actually trying to back up Windows Team Foundation Server, but though it was better to keep the question generic. – Contango Jul 07 '09 at 17:16
I turned on every single log, the only event that occurred was this: 07/07/2009 18:25:51,SQLSERVERAGENT,Warning,,SQL Server Scheduled Job 'TFS backup.Subplan_1' (0x0140518D9AD209468CD45ECA97EC0B93) - Status: Failed - Invoked on: 2009-07-07 18:25:51 - Message: The job failed. The Job was invoked by User WIN2008-TFS\Administrator. The last step to run was step 1 (Subplan_1).,,,,,,Job Engine,1073742032,,WIN2008-TFS – Contango Jul 07 '09 at 17:29

score 0 · Answer 4 · answered Jul 07 '09 at 13:59

Are you backing up to a network share, or a folder that the account running the backup may not have access to? You won't be able to backup to a UNC name if the SQL Server service is running as LocalSystem.

From a command prompt try:

osql /S myserver /E /Q "backup database mydb to disk='D:\somefolder\mydb.bak' with init"

to check there's nothing basically wrong. Replace "myserver", "mydb" and the folder name with whatever you're using in the backup job.

JR

I'm backing up to a directory on C:. – Contango Jul 07 '09 at 17:14 — Contango, Jul 07 '09 at 17:14

Contango · Accepted Answer · 2009-07-08T19:00:07.677

0

Installed SP1. This fixed everything, it works 100% now.

edited Jul 08 '09 at 19:00

answered Jul 07 '09 at 17:57

Contango

1,150
5
15
31

Cannot backup databases from SQL Server 2008

5 Answers5