SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL BACKUP JOB Failure


SQL BACKUP JOB Failure

Author
Message
eseosaoregie
eseosaoregie
Mr or Mrs. 500
Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)

Group: General Forum Members
Points: 537 Visits: 1202
I am currently trying to investigate why our nightly full backups have been failing. The nightly full backups were working correctly but have now stopped. I have had a look in application logs and get these following messages.

Executed as user: NT AUTHORITY\SYSTEM. ...ercent processed. [SQLSTATE 01000] (Message 3211) 30 percent processed. [SQLSTATE 01000] (Message 3211) 41 percent processed. [SQLSTATE 01000] (Message 3211) 50 percent processed. [SQLSTATE 01000] (Message 3211) 61 percent processed. [SQLSTATE 01000] (Message 3211) 70 percent processed. [SQLSTATE 01000] (Message 3211) 81 percent processed. [SQLSTATE 01000] (Message 3211) 90 percent processed. [SQLSTATE 01000] (Message 3211) Processed 360 pages for database 'master', file 'master' on file 1. [SQLSTATE 01000] (Message 4035) 100 percent processed. [SQLSTATE 01000] (Message 3211) Processed 2 pages for database 'master', file 'mastlog' on file 1. [SQLSTATE 01000] (Message 4035) BACKUP DATABASE successfully processed 362 pages in 5.107 seconds (0.580 MB/sec). [SQLSTATE 01000] (Message 3014) 10 percent processed. [SQLSTATE 01000] (Message 3211) 20 percent processed. [SQLSTATE 01000] (Message 3211) 30 percent processed. [SQLSTATE 01000] (M... The step failed.

The backup data at the end of "nwcscmdsdataBACK(\\brentsan\e$\Brentvmsollis-BackupwcscmdsdataBACK.BAK)" is incorrectly formatted. Backup sets on the media might be damaged and unusable. To determine the backup sets on the media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using BACKUP WITH FORMAT, which destroys all the backup sets.

Error: 3266, Severity: 16, State: 1.

BACKUP failed to complete the command BACKUP DATABASE NWCSCMDSDataREP. Check the backup application log for detailed messages.

Error: 3041, Severity: 16, State: 1.


I am not sure if this a permissions issue or disk size issue. I think it is unlikely to be a permissions issue as the Log backups are working fine. Any help would help as to what might be the cause would be much appreciated.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62861 Visits: 19111
A couple things.

First, can you post the backup command?

Second, does this run interactively? If you run from SSMS?

Last, backups to remove drives (shares) are problematic, and flaky. The backup process throws pages out as fast as it can. If there is a network delay, it fails the backup. If a copy has a network error, it retries.

You would do better to back up locally and copy to the network (and delete locally).

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39545 Visits: 38554
I agree with Steve. Also, I'd run separate backup processes for each database.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
eseosaoregie
eseosaoregie
Mr or Mrs. 500
Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)

Group: General Forum Members
Points: 537 Visits: 1202
The BACKUP Command is shown below


DECLARE @securedb varchar(255)
IF EXISTS (SELECT * FROM NWCSCMDSData.dbo.sysobjects WHERE id = object_id(N'NWCSCMDSData.[dbo].[GPSecureDatabase]') AND xtype = 'U')
BEGIN
SET @securedb = (SELECT TOP 1 GPSecureDB FROM NWCSCMDSData..GPSecureDatabase)
END


declare dblist cursor for select name from master..sysdatabases
where not name in ('tempdb','model')
open dblist
declare @dn varchar(100)

fetch next from dblist into @dn
WHILE (@@FETCH_STATUS <> -1)
begin

exec ('BACKUP DATABASE [' + @dn + '] to [' + @dn + 'BACK] WITH NAME=''' + @dn +' Backup'' , INIT,STATS=10')


--exec ('sp_dboption ''' + @dn + ''',''autoclose'',''false''')
fetch next from dblist into @dn
end
close dblist
deallocate dblist
end



It is run as a sql job nightly monday to friday.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62861 Visits: 19111
Change the EXEC() to SELECT, get the statements, check and see if they execute.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
eseosaoregie
eseosaoregie
Mr or Mrs. 500
Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)

Group: General Forum Members
Points: 537 Visits: 1202
I have changed the exec statements to select and they execute.

Incidentally the SQL-Server instance sits on a virtual server. I am wondering if that might have any impact. We were also recently attacked by a conficker virus. However this has not impacted on the log backup or other maintenance plans that we run.
eseosaoregie
eseosaoregie
Mr or Mrs. 500
Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)

Group: General Forum Members
Points: 537 Visits: 1202
I have managed to get the backup to work. I deleted two of the backups in the folder and nightly schedule ran smooth. Two of the backups had become corrupt and the backup just seemed to keel over when it came to backing them up.

thanks for your input.
subahan.munthamadugu
subahan.munthamadugu
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 73
Hi ,
let me know you are taking backup to local disk or network path . i guess you are taking backup to network path .

possible reason :
the backup failed due to network issue or in b\w the network was disconnected .

possible soluation:
improve the network performance b\w your local system and network path and some times
reran the job also will solve the problem.



--subahan munthamadugu---
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87665 Visits: 45272
Please note: 4 year old thread.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


subahan.munthamadugu
subahan.munthamadugu
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 73
Hi ,
let me know you are taking backup to local disk or network path . i guess you are taking backup to network path and backup sizes are also big .

possible reason :
the backup failed due to network issue or in b\w the network was disconnected .

possible soluation:
1. improve the network performance b\w your local system and network path and some times
reran the job also will solve the problem.
2.suppose your databases are very big then stripe and take backup to net work path.
3. put retry option on your backup step.


--subahan munthamadugu---
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search