July 20, 2006 at 11:13 am
I am having trouble backing up one of my databases. However, the transaction log backs every hour for the same database. The error log shows this error message:
BACKUP failed to complete the command BACKUP DATABASE [VIDESKTOP] TO DISK = N'D:\SQLBackup\Full\VIDESKTOP_db_200607200949.BAK' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
The job history indicates that the job completed successfully. When I kick off the job in EM and view the backup destination, the dump file shows up with 0KB and after a few seconds, the file disappears. It's really strange. I am able to backup manually in Query Analyzer without any problem.
There is ample space on the disk drive. I have tried backing up to a different drive and recreated the job as well. I would appreciate any help.
July 21, 2006 at 7:48 am
How is the backup being performed- through a job? Have you tried using Q/A to run the command?
July 21, 2006 at 8:07 am
The backup is through a job but I am able to run the command manually via QA.
July 21, 2006 at 9:07 am
When you run the job, do you have an output error file specified? When you edit the job step there is an Advanced tab where you can put a path\file for error reporting. Can you add that and see any different errors?
July 21, 2006 at 9:36 am
Here's the output. The transaction log backs up successfully. There's 49.8GB free space on the D: drive. Also, I noticed in the sql log that the database is not listed among the "starting up database ... message. Could that also be part of the problem?
The error is:
BACKUP failed to complete the command BACKUP DATABASE [videsktop] TO DISK = N'D:\SQLBackup\videsktop\videsktop_db_200607211017.BAK' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
Log output:
Job 'DB Backup Job for DB Maintenance Plan 'Videsktop Backup'' : Step 1, 'Step 1' : Began Executing 2006-07-21 10:24:01
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(null)
Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.382 Copyright (C) Microsoft Corporation, 1995 - 1998 (null)
Logged on to SQL Server 'VIDESKTOP' as 'HC\videsktop_sql_svc' (trusted) Starting maintenance plan 'Videsktop Backup' on 7/21/2006 10:24:07 AM [1] Database videsktop: Database Backup... Destination: [D:\SQLBackup\videsktop\videsktop_db_200607211024.BAK] (null)
** Execution Time: 0 hrs, 0 mins, 3 secs ** (null)
[2] Database videsktop: Delete Old Backup Files... 0 file(s) deleted. (null)
Deleting old text reports... 0 file(s) deleted. (null)
End of maintenance plan 'Videsktop Backup' on 7/21/2006 10:24:12 AM SQLMAINT.EXE Process Exit Code: 0 (Success)
July 21, 2006 at 1:27 pm
check permissions on the sql server service if it is backing up locally local system should be fine. if you are backing up to a network share make sure the service is running under a domain acount with permissions to the share.
Ditto for the agent.
Oh, drop the no format from the backup statement its not supported with disk I do belive. nounload and noskip are tape only commands as well but shouldn't generate any errors.
wes
July 21, 2006 at 1:49 pm
The SQL server service is running under an admin account not the local system. How do I drop the no format within the maintenance plan? Do you know why the database name isn't listed in the log for databases starting up?
July 21, 2006 at 1:57 pm
sorry wasn't paying close enough attention you can't modify the maint plan statement for backups.
Who is the owner of that database?
Has it been restored to that server in the past?
July 21, 2006 at 2:04 pm
The owner is a different account from the service account but it has 'dbo owner' role. There has been no restore and the backups worked fine in the past. Not sure what caused it to stop. Thanks.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply