backup should continue even after failure

I have created a stored procedure to backup databases,
right now if backup of one database fails then it stops
execution. I want it not to stop but move on to next
database backup.
I tried separating actual execution of the backup command
to another stored procedure hoping that if it fails then
it will return the control back to calling stored
procedure but it didn't.
Any help wil be very much appreciated.
Thanks in advance.
Well one way is to make the backup thru a call to oSql. Then if it fails it
is up to you to detect it and do what you want.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:267801c47035$984e1c50$a301280a@.phx.gbl...
> I have created a stored procedure to backup databases,
> right now if backup of one database fails then it stops
> execution. I want it not to stop but move on to next
> database backup.
> I tried separating actual execution of the backup command
> to another stored procedure hoping that if it fails then
> it will return the control back to calling stored
> procedure but it didn't.
> Any help wil be very much appreciated.
> Thanks in advance.
>

backup should continue even after failure

I have created a stored procedure to backup databases,
right now if backup of one database fails then it stops
execution. I want it not to stop but move on to next
database backup.
I tried separating actual execution of the backup command
to another stored procedure hoping that if it fails then
it will return the control back to calling stored
procedure but it didn't.
Any help wil be very much appreciated.
Thanks in advance.Well one way is to make the backup thru a call to oSql. Then if it fails it
is up to you to detect it and do what you want.
--
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:267801c47035$984e1c50$a301280a@.phx.gbl...
> I have created a stored procedure to backup databases,
> right now if backup of one database fails then it stops
> execution. I want it not to stop but move on to next
> database backup.
> I tried separating actual execution of the backup command
> to another stored procedure hoping that if it fails then
> it will return the control back to calling stored
> procedure but it didn't.
> Any help wil be very much appreciated.
> Thanks in advance.
>

backup should continue even after failure

I have created a stored procedure to backup databases,
right now if backup of one database fails then it stops
execution. I want it not to stop but move on to next
database backup.
I tried separating actual execution of the backup command
to another stored procedure hoping that if it fails then
it will return the control back to calling stored
procedure but it didn't.
Any help wil be very much appreciated.
Thanks in advance.Well one way is to make the backup thru a call to oSql. Then if it fails it
is up to you to detect it and do what you want.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:267801c47035$984e1c50$a301280a@.phx.gbl...
> I have created a stored procedure to backup databases,
> right now if backup of one database fails then it stops
> execution. I want it not to stop but move on to next
> database backup.
> I tried separating actual execution of the backup command
> to another stored procedure hoping that if it fails then
> it will return the control back to calling stored
> procedure but it didn't.
> Any help wil be very much appreciated.
> Thanks in advance.
>sql

Backup Restore Fails

Hi,

I ran a backup procedure and BAK file created.
I moved the file to another server, tried to restore and failed with following error:
"Cannot open backup device 'C:\*****.bak'. Operating system error 5(Access is denied.)"

I checked the file on the original server and the file is accessible: I can see its content with RESTORE FILELISTONLY command.

Any clues?

Thanks,
Mark

Hello Mark

Are you using the Restore dialog to do this operation or are you using T-SQL?

Either case, ensure that the account under which SQL server service is running has access to the 'C' drive.

[Venkat]
This posting is provided "AS IS" with no warranties, and confers no rights.

Backup Problem

My backup fails with:
10 percent backed up. [SQLSTATE 01000] (Message 3211) 20 percent backed up.
[SQLSTATE 01000] (Message 3211) 30 percent backed up. [SQLSTATE 01000]
(Message 3211) 40 percent backed up. [SQLSTATE 01000] (Message 3211) 50
percent backed up. [SQLSTATE 01000] (Message 3211) 60 percent backed up.
[SQLSTATE 01000] (Message 3211) 70 percent backed up. [SQLSTATE 01000]
(Message 3211) Write on 'WednesdaylogsAM' failed, status = 64. See the SQL
Server error log for more details. [SQLSTATE 42000] (Error 3202) BACKUP LOG
is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
When I look at the log I get:
BackupDiskFile::RequestDurableMedia: failure on backup device
'\\Sapbackup\GIS\Backup\LOG-WED-AM\WednesdaylogsAM.BAK'. Operating system
error 64(The specified network name is no longer available.).
I can get to the folder where this file resides, so I have ruled out
security. What does this mean?This looks like you have glitches in the network or somewhere in the
communication path of the server you are backing up to. Backup is very
touchy about errors in the communication (as it should be) between the DB
server and the destination. Most normal network traffic will just resend
the packet but the backup will usually fail.
--
Andrew J. Kelly SQL MVP
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:361DA327-4899-4CD2-9BAC-CCE310D9A76C@.microsoft.com...
> My backup fails with:
> 10 percent backed up. [SQLSTATE 01000] (Message 3211) 20 percent backed
> up.
> [SQLSTATE 01000] (Message 3211) 30 percent backed up. [SQLSTATE 01000]
> (Message 3211) 40 percent backed up. [SQLSTATE 01000] (Message 3211) 50
> percent backed up. [SQLSTATE 01000] (Message 3211) 60 percent backed up.
> [SQLSTATE 01000] (Message 3211) 70 percent backed up. [SQLSTATE 01000]
> (Message 3211) Write on 'WednesdaylogsAM' failed, status = 64. See the
> SQL
> Server error log for more details. [SQLSTATE 42000] (Error 3202) BACKUP
> LOG
> is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step
> failed.
> When I look at the log I get:
> BackupDiskFile::RequestDurableMedia: failure on backup device
> '\\Sapbackup\GIS\Backup\LOG-WED-AM\WednesdaylogsAM.BAK'. Operating system
> error 64(The specified network name is no longer available.).
> I can get to the folder where this file resides, so I have ruled out
> security. What does this mean?|||You most likely ran out of disk space during the backup. Is there
enough free space on the disk to contain the entire DB? I'm guessing
not, since only about 70% of your DB gets backed up before the OS whinges.
Cheers,
Mike.
Jack wrote:
> My backup fails with:
> 10 percent backed up. [SQLSTATE 01000] (Message 3211) 20 percent backed up.
> [SQLSTATE 01000] (Message 3211) 30 percent backed up. [SQLSTATE 01000]
> (Message 3211) 40 percent backed up. [SQLSTATE 01000] (Message 3211) 50
> percent backed up. [SQLSTATE 01000] (Message 3211) 60 percent backed up.
> [SQLSTATE 01000] (Message 3211) 70 percent backed up. [SQLSTATE 01000]
> (Message 3211) Write on 'WednesdaylogsAM' failed, status = 64. See the SQL
> Server error log for more details. [SQLSTATE 42000] (Error 3202) BACKUP LOG
> is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
> When I look at the log I get:
> BackupDiskFile::RequestDurableMedia: failure on backup device
> '\\Sapbackup\GIS\Backup\LOG-WED-AM\WednesdaylogsAM.BAK'. Operating system
> error 64(The specified network name is no longer available.).
> I can get to the folder where this file resides, so I have ruled out
> security. What does this mean?

Backup Problem

My backup fails with:
10 percent backed up. [SQLSTATE 01000] (Message 3211) 20 percent backed up.
[SQLSTATE 01000] (Message 3211) 30 percent backed up. [SQLSTATE 01000]
(Message 3211) 40 percent backed up. [SQLSTATE 01000] (Message 3211) 50
percent backed up. [SQLSTATE 01000] (Message 3211) 60 percent backed up.
[SQLSTATE 01000] (Message 3211) 70 percent backed up. [SQLSTATE 01000]
(Message 3211) Write on 'WednesdaylogsAM' failed, status = 64. See the SQL
Server error log for more details. [SQLSTATE 42000] (Error 3202) BACKUP LOG
is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
When I look at the log I get:
BackupDiskFile::RequestDurableMedia: failure on backup device
'\\Sapbackup\GIS\Backup\LOG-WED-AM\WednesdaylogsAM.BAK'. Operating system
error 64(The specified network name is no longer available.).
I can get to the folder where this file resides, so I have ruled out
security. What does this mean?
This looks like you have glitches in the network or somewhere in the
communication path of the server you are backing up to. Backup is very
touchy about errors in the communication (as it should be) between the DB
server and the destination. Most normal network traffic will just resend
the packet but the backup will usually fail.
Andrew J. Kelly SQL MVP
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:361DA327-4899-4CD2-9BAC-CCE310D9A76C@.microsoft.com...
> My backup fails with:
> 10 percent backed up. [SQLSTATE 01000] (Message 3211) 20 percent backed
> up.
> [SQLSTATE 01000] (Message 3211) 30 percent backed up. [SQLSTATE 01000]
> (Message 3211) 40 percent backed up. [SQLSTATE 01000] (Message 3211) 50
> percent backed up. [SQLSTATE 01000] (Message 3211) 60 percent backed up.
> [SQLSTATE 01000] (Message 3211) 70 percent backed up. [SQLSTATE 01000]
> (Message 3211) Write on 'WednesdaylogsAM' failed, status = 64. See the
> SQL
> Server error log for more details. [SQLSTATE 42000] (Error 3202) BACKUP
> LOG
> is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step
> failed.
> When I look at the log I get:
> BackupDiskFile::RequestDurableMedia: failure on backup device
> '\\Sapbackup\GIS\Backup\LOG-WED-AM\WednesdaylogsAM.BAK'. Operating system
> error 64(The specified network name is no longer available.).
> I can get to the folder where this file resides, so I have ruled out
> security. What does this mean?
|||You most likely ran out of disk space during the backup. Is there
enough free space on the disk to contain the entire DB? I'm guessing
not, since only about 70% of your DB gets backed up before the OS whinges.
Cheers,
Mike.
Jack wrote:
> My backup fails with:
> 10 percent backed up. [SQLSTATE 01000] (Message 3211) 20 percent backed up.
> [SQLSTATE 01000] (Message 3211) 30 percent backed up. [SQLSTATE 01000]
> (Message 3211) 40 percent backed up. [SQLSTATE 01000] (Message 3211) 50
> percent backed up. [SQLSTATE 01000] (Message 3211) 60 percent backed up.
> [SQLSTATE 01000] (Message 3211) 70 percent backed up. [SQLSTATE 01000]
> (Message 3211) Write on 'WednesdaylogsAM' failed, status = 64. See the SQL
> Server error log for more details. [SQLSTATE 42000] (Error 3202) BACKUP LOG
> is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
> When I look at the log I get:
> BackupDiskFile::RequestDurableMedia: failure on backup device
> '\\Sapbackup\GIS\Backup\LOG-WED-AM\WednesdaylogsAM.BAK'. Operating system
> error 64(The specified network name is no longer available.).
> I can get to the folder where this file resides, so I have ruled out
> security. What does this mean?

Backup pefromed by SQLSERVERAGENT fails

I just created five new BU devices, then a job for each
and executed the jobs to create the intial backups on a
new database.
Four of the jobs worked fine. In Event Viewer, the Source
= MSSQLSERVER and the USER = SQLEXEC.
One job failed. Source = SQLSERVERAGENT and USER = N/A
Where did I go wrong? Why does the failing job use a
different USER and Source?
TIA MikeYou need to give us more info. Without the error message, it is like saying: "My computer doesn't
work. What is the problem?".
Check the job history, check "show step details", and go to the relevant step. See what the error
message is. Also, see the Agent errorlog.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mike Hoyt" <mhoyt@.affiliatedhealth.org> wrote in message
news:0ec301c39d9e$db5dff70$a001280a@.phx.gbl...
> I just created five new BU devices, then a job for each
> and executed the jobs to create the intial backups on a
> new database.
> Four of the jobs worked fine. In Event Viewer, the Source
> = MSSQLSERVER and the USER = SQLEXEC.
> One job failed. Source = SQLSERVERAGENT and USER = N/A
> Where did I go wrong? Why does the failing job use a
> different USER and Source?
> TIA Mike|||The Job is failing at Step 1, which was invoked by
Schedule 1. Step 1 is the only step, and it is the basic
backup syntax. The SQLAgent error log is blank for
failures and warnings, information is the usual start up
stuff.
I know the agent is running, th eother jobs run and I get
email alerts of their completion. I get the email alert
of the failure of this job.
What I'm hoping osmebody can tell me is why this job is
run by Source = SQLSERVERAGENT and USER = N/A and the
other, sucessfull jobs, are run by MSSQLSERVER and the
USER = SQLEXEC|||I haven't played with this to see if this is the case but do
the jobs have different owners?
-Sue
On Wed, 29 Oct 2003 08:55:41 -0800,
<anonymous@.discussions.microsoft.com> wrote:
>The Job is failing at Step 1, which was invoked by
>Schedule 1. Step 1 is the only step, and it is the basic
>backup syntax. The SQLAgent error log is blank for
>failures and warnings, information is the usual start up
>stuff.
>I know the agent is running, th eother jobs run and I get
>email alerts of their completion. I get the email alert
>of the failure of this job.
>What I'm hoping osmebody can tell me is why this job is
>run by Source = SQLSERVERAGENT and USER = N/A and the
>other, sucessfull jobs, are run by MSSQLSERVER and the
>USER = SQLEXEC|||Have you checked job step history? Right click the job, select history, and
check job step details.
--
Michiel Wories, SQL Server PM
This posting is provided "AS IS" with no warranties, and confers no rights.
--
<anonymous@.discussions.microsoft.com> wrote in message
news:0e5501c39e3d$7c4f95d0$a501280a@.phx.gbl...
> The Job is failing at Step 1, which was invoked by
> Schedule 1. Step 1 is the only step, and it is the basic
> backup syntax. The SQLAgent error log is blank for
> failures and warnings, information is the usual start up
> stuff.
> I know the agent is running, th eother jobs run and I get
> email alerts of their completion. I get the email alert
> of the failure of this job.
> What I'm hoping osmebody can tell me is why this job is
> run by Source = SQLSERVERAGENT and USER = N/A and the
> other, sucessfull jobs, are run by MSSQLSERVER and the
> USER = SQLEXEC

Backup of Log fails.

It appears that the data is backing up fine but logs do not. Obviously something has changed but I cannot see what. I have plenty of space and privs but the backups fail with the below errorlog. If anyone can point me in a new direction I would greatly appreciate it.

2005-12-31 08:22:34.16 spid11 Analysis of database 'CEMS_PRO' (7) is 100% complete (approximately 0 more seconds)
2005-12-31 08:22:34.25 spid5 Starting up database 'tempdb'.
2005-12-31 08:22:34.58 spid3 Recovery complete.
2005-12-31 08:22:34.58 spid3 SQL global counter collection task is created.
2005-12-31 08:22:36.22 spid51 Using 'xpsqlbot.dll' version '2000.80.194' to execute extended stored procedure 'xp_qv'.
2005-12-31 09:11:52.18 spid173 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'xp_regread'.
2005-12-31 09:11:54.85 spid173 Using 'xplog70.dll' version '2000.80.760' to execute extended stored procedure 'xp_cmdshell'.
2005-12-31 21:00:00.75 backup BACKUP failed to complete the command BACKUP LOG CEMS_PRO TO CEMS_Log_Back2 WITH NOINIT, NOSKIP , STATS = 10, NOFORMAT
2005-12-31 22:00:00.92 backup BACKUP failed to complete the command BACKUP LOG CEMS_PRO TO CEMS_Log_Back2 WITH NOINIT, NOSKIP , STATS = 10, NOFORMAT
2005-12-31 23:00:00.86 backup BACKUP failed to complete the command BACKUP LOG CEMS_PRO TO CEMS_Log_Back2 WITH NOINIT, NOSKIP , STATS = 10, NOFORMAT
2006-01-01 00:45:00.91 backup BACKUP failed to complete the command BACKUP LOG CEMS_PRO TO CEMS_Log_Back2 WITH INIT, NOSKIP , STATS = 10, NOFORMAT
2006-01-01 02:25:22.67 backup Database backed up: Database: CEMS_PRO, creation date(time): 2005/01/17(12:36:00), pages dumped: 17259208, first LSN: 45585:18685:21, last LSN: 45586:81369:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'CEMS_Backup'}).
2006-01-01 02:25:23.03 backup BACKUP failed to complete the command backup log CEMS_PRO TO CEMS_Log_Back2 WITH INIT , NOUNLOAD , NAME = N'CEMS_Log_Backup', NOSKIP , STATS = 10, NOFORMAT

You might try running the sql profiler tool to capture the error messages that are being sent back to the backup application.

If you are running this as part of a maintenance plan, then there should be job history logs that capture the error messages.

|||

We are recieving the exact same error:

SQL Profiler is a powerful tool for debugging SQL. What parts do you recommend to monitor?

Thanks

|||What happens if you take the command which is shown in the log and run it in QA or SSMS?

Backup of Log fails.

It appears that the data is backing up fine but logs do not. Obviously something has changed but I cannot see what. I have plenty of space and privs but the backups fail with the below errorlog. If anyone can point me in a new direction I would greatly appreciate it.

2005-12-31 08:22:34.16 spid11 Analysis of database 'CEMS_PRO' (7) is 100% complete (approximately 0 more seconds)
2005-12-31 08:22:34.25 spid5 Starting up database 'tempdb'.
2005-12-31 08:22:34.58 spid3 Recovery complete.
2005-12-31 08:22:34.58 spid3 SQL global counter collection task is created.
2005-12-31 08:22:36.22 spid51 Using 'xpsqlbot.dll' version '2000.80.194' to execute extended stored procedure 'xp_qv'.
2005-12-31 09:11:52.18 spid173 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'xp_regread'.
2005-12-31 09:11:54.85 spid173 Using 'xplog70.dll' version '2000.80.760' to execute extended stored procedure 'xp_cmdshell'.
2005-12-31 21:00:00.75 backup BACKUP failed to complete the command BACKUP LOG CEMS_PRO TO CEMS_Log_Back2 WITH NOINIT, NOSKIP , STATS = 10, NOFORMAT
2005-12-31 22:00:00.92 backup BACKUP failed to complete the command BACKUP LOG CEMS_PRO TO CEMS_Log_Back2 WITH NOINIT, NOSKIP , STATS = 10, NOFORMAT
2005-12-31 23:00:00.86 backup BACKUP failed to complete the command BACKUP LOG CEMS_PRO TO CEMS_Log_Back2 WITH NOINIT, NOSKIP , STATS = 10, NOFORMAT
2006-01-01 00:45:00.91 backup BACKUP failed to complete the command BACKUP LOG CEMS_PRO TO CEMS_Log_Back2 WITH INIT, NOSKIP , STATS = 10, NOFORMAT
2006-01-01 02:25:22.67 backup Database backed up: Database: CEMS_PRO, creation date(time): 2005/01/17(12:36:00), pages dumped: 17259208, first LSN: 45585:18685:21, last LSN: 45586:81369:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'CEMS_Backup'}).
2006-01-01 02:25:23.03 backup BACKUP failed to complete the command backup log CEMS_PRO TO CEMS_Log_Back2 WITH INIT , NOUNLOAD , NAME = N'CEMS_Log_Backup', NOSKIP , STATS = 10, NOFORMAT

You might try running the sql profiler tool to capture the error messages that are being sent back to the backup application.

If you are running this as part of a maintenance plan, then there should be job history logs that capture the error messages.

|||

We are recieving the exact same error:

SQL Profiler is a powerful tool for debugging SQL. What parts do you recommend to monitor?

Thanks

|||What happens if you take the command which is shown in the log and run it in QA or SSMS?

Backup of Log fails.

I've recently been having problems similar to this, try doing the following:

1. Make sure you have the latest patches & SPK for SQL

2. Defrag your drives to stop memory related issues

3. Set the tempdb DB properties to use Recovery (FULL) not simple

NB>You can change all of them to Full if you want however, DO NOT change "master" to FULL! Anyway at least that is what M$ says...

4. In maintenance plan jobs make sure you specify the location and add -SupportComputedColumn'

i.e. EXECUTE master.dbo.xp_sqlmaint N'-S Servername\SQLServername -PlanIDXXXXXXXXXXXXXXXX.....-SupportComputedColumn'

5. Use a seperate Maintenance plan for "transaction backup logs" and don't include the "masterDB" as that doesn't work.

Double check

Recovery model of your databases should be FULL

Backup of database fails ...

If anyone has any ideas on this one ... I'd appreciate it very
much ...
... I've got one database on our server that refuses to backup ...
several other database work just fine ...
... when I issue ...
BACKUP DATABASE [Kurtz_Inc_MSCRM] TO DISK = N'D:\MSSQL\BACKUP
\Kurtz_Inc_MSCRM_backup_manual.bak'
... it responds immediately with ...
Msg 3636, Level 16, State 2, Line 1
An error occurred while processing 'BackupMetadata' metadata for
database id 8 file id 65537.
Msg 3046, Level 16, State 2, Line 1
Inconsistent metadata has been encountered. The only possible backup
operation is a tail-log backup using the WITH CONTINUE_AFTER_ERROR or
NO_TRUNCATE option.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
... if I include the "WITH CONTINUE_AFTER_ERROR" ... I still get the
same error.
Thoughts? (If it matters, the recovery model is "Full")
- Richard
Richard
If you add WITH CONTINUE_AFTER_ERROR your backup should complered
succefully
<Richard.Gohs@.pa-tech.com> wrote in message
news:f98ed696-ed40-4655-98ad-3d9849c0404a@.n75g2000hsh.googlegroups.com...
> If anyone has any ideas on this one ... I'd appreciate it very
> much ...
> ... I've got one database on our server that refuses to backup ...
> several other database work just fine ...
> ... when I issue ...
> BACKUP DATABASE [Kurtz_Inc_MSCRM] TO DISK = N'D:\MSSQL\BACKUP
> \Kurtz_Inc_MSCRM_backup_manual.bak'
> ... it responds immediately with ...
> Msg 3636, Level 16, State 2, Line 1
> An error occurred while processing 'BackupMetadata' metadata for
> database id 8 file id 65537.
> Msg 3046, Level 16, State 2, Line 1
> Inconsistent metadata has been encountered. The only possible backup
> operation is a tail-log backup using the WITH CONTINUE_AFTER_ERROR or
> NO_TRUNCATE option.
> Msg 3013, Level 16, State 1, Line 1
> BACKUP DATABASE is terminating abnormally.
> ... if I include the "WITH CONTINUE_AFTER_ERROR" ... I still get the
> same error.
> Thoughts? (If it matters, the recovery model is "Full")
> - Richard
|||On Mar 13, 5:18Xpm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> I interpret the error message such as you have a corrupt database and the message suggest you to do
> the very last log backup (TRUNCATE_ONLY) before you clean up the database by restoring a clean
> backup and all subsequent log backups. Did you DBCC CHECKDB the database?
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> <Richard.G...@.pa-tech.com> wrote in message
> news:f98ed696-ed40-4655-98ad-3d9849c0404a@.n75g2000hsh.googlegroups.com...
>
>
>
>
>
>
> - Show quoted text -
Tibor,
FYI ... the reason I can't really go back to the backup is that the
database appears to be functional from the users point of view ...
(it's a Microsoft CRM 3.0 install) ... and users have been putting
additional data into it - and - I don't have log backups back to the
time of the last full backup (which I suspect means I'm hosed)
Running dbcc checkdb results in ...
.
.
.
CHECKDB found 0 allocation errors and 0 consistency errors in database
'Kurtz_Inc_MSCRM'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Thanks,
Richard
|||On Mar 16, 6:15Xam, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Richard
> If you add WITH CONTINUE_AFTER_ERROR Xyour backup should complered
> succefully
> <Richard.G...@.pa-tech.com> wrote in message
> news:f98ed696-ed40-4655-98ad-3d9849c0404a@.n75g2000hsh.googlegroups.com...
>
>
>
>
>
>
> - Show quoted text -
Uri,
That's one of my problems (from the original post) ... adding "WITH
CONTINUE_AFTER_ERROR" seems to have no effect ... I get same error
immediately after issuing the backup command.
Thanks,
Richard

Backup of database fails ...

If anyone has any ideas on this one ... I'd appreciate it very
much ...
... I've got one database on our server that refuses to backup ...
several other database work just fine ...
... when I issue ...
BACKUP DATABASE [Kurtz_Inc_MSCRM] TO DISK = N'D:\MSSQL\BACKUP
\Kurtz_Inc_MSCRM_backup_manual.bak'
... it responds immediately with ...
Msg 3636, Level 16, State 2, Line 1
An error occurred while processing 'BackupMetadata' metadata for
database id 8 file id 65537.
Msg 3046, Level 16, State 2, Line 1
Inconsistent metadata has been encountered. The only possible backup
operation is a tail-log backup using the WITH CONTINUE_AFTER_ERROR or
NO_TRUNCATE option.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
... if I include the "WITH CONTINUE_AFTER_ERROR" ... I still get the
same error.
Thoughts? (If it matters, the recovery model is "Full")
- RichardI interpret the error message such as you have a corrupt database and the message suggest you to do
the very last log backup (TRUNCATE_ONLY) before you clean up the database by restoring a clean
backup and all subsequent log backups. Did you DBCC CHECKDB the database?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<Richard.Gohs@.pa-tech.com> wrote in message
news:f98ed696-ed40-4655-98ad-3d9849c0404a@.n75g2000hsh.googlegroups.com...
> If anyone has any ideas on this one ... I'd appreciate it very
> much ...
> ... I've got one database on our server that refuses to backup ...
> several other database work just fine ...
> ... when I issue ...
> BACKUP DATABASE [Kurtz_Inc_MSCRM] TO DISK = N'D:\MSSQL\BACKUP
> \Kurtz_Inc_MSCRM_backup_manual.bak'
> ... it responds immediately with ...
> Msg 3636, Level 16, State 2, Line 1
> An error occurred while processing 'BackupMetadata' metadata for
> database id 8 file id 65537.
> Msg 3046, Level 16, State 2, Line 1
> Inconsistent metadata has been encountered. The only possible backup
> operation is a tail-log backup using the WITH CONTINUE_AFTER_ERROR or
> NO_TRUNCATE option.
> Msg 3013, Level 16, State 1, Line 1
> BACKUP DATABASE is terminating abnormally.
> ... if I include the "WITH CONTINUE_AFTER_ERROR" ... I still get the
> same error.
> Thoughts? (If it matters, the recovery model is "Full")
> - Richard|||Richard
If you add WITH CONTINUE_AFTER_ERROR your backup should complered
succefully
<Richard.Gohs@.pa-tech.com> wrote in message
news:f98ed696-ed40-4655-98ad-3d9849c0404a@.n75g2000hsh.googlegroups.com...
> If anyone has any ideas on this one ... I'd appreciate it very
> much ...
> ... I've got one database on our server that refuses to backup ...
> several other database work just fine ...
> ... when I issue ...
> BACKUP DATABASE [Kurtz_Inc_MSCRM] TO DISK = N'D:\MSSQL\BACKUP
> \Kurtz_Inc_MSCRM_backup_manual.bak'
> ... it responds immediately with ...
> Msg 3636, Level 16, State 2, Line 1
> An error occurred while processing 'BackupMetadata' metadata for
> database id 8 file id 65537.
> Msg 3046, Level 16, State 2, Line 1
> Inconsistent metadata has been encountered. The only possible backup
> operation is a tail-log backup using the WITH CONTINUE_AFTER_ERROR or
> NO_TRUNCATE option.
> Msg 3013, Level 16, State 1, Line 1
> BACKUP DATABASE is terminating abnormally.
> ... if I include the "WITH CONTINUE_AFTER_ERROR" ... I still get the
> same error.
> Thoughts? (If it matters, the recovery model is "Full")
> - Richard|||On Mar 13, 5:18=A0pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> I interpret the error message such as you have a corrupt database and the =message suggest you to do
> the very last log backup (TRUNCATE_ONLY) before you clean up the database =by restoring a clean
> backup and all subsequent log backups. Did you DBCC CHECKDB the database?
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph=
ttp://sqlblog.com/blogs/tibor_karaszi
> <Richard.G...@.pa-tech.com> wrote in message
> news:f98ed696-ed40-4655-98ad-3d9849c0404a@.n75g2000hsh.googlegroups.com...
>
> > If anyone has any ideas on this one ... I'd appreciate it very
> > much ...
> > ... I've got one database on our server that refuses to backup ...
> > several other database work just fine ...
> > ... when I issue ...
> > BACKUP DATABASE [Kurtz_Inc_MSCRM] TO =A0DISK =3D N'D:\MSSQL\BACKUP
> > \Kurtz_Inc_MSCRM_backup_manual.bak'
> > ... it responds immediately with ...
> > Msg 3636, Level 16, State 2, Line 1
> > An error occurred while processing 'BackupMetadata' metadata for
> > database id 8 file id 65537.
> > Msg 3046, Level 16, State 2, Line 1
> > Inconsistent metadata has been encountered. The only possible backup
> > operation is a tail-log backup using the WITH CONTINUE_AFTER_ERROR or
> > NO_TRUNCATE option.
> > Msg 3013, Level 16, State 1, Line 1
> > BACKUP DATABASE is terminating abnormally.
> > ... if I include the "WITH CONTINUE_AFTER_ERROR" ... I still get the
> > same error.
> > Thoughts? =A0(If it matters, the recovery model is "Full")
> > -Richard- Hide quoted text -
> - Show quoted text -
Tibor,
FYI ... the reason I can't really go back to the backup is that the
database appears to be functional from the users point of view ...
(it's a Microsoft CRM 3.0 install) ... and users have been putting
additional data into it - and - I don't have log backups back to the
time of the last full backup (which I suspect means I'm hosed)
Running dbcc checkdb results in ...
=2E
=2E
=2E
CHECKDB found 0 allocation errors and 0 consistency errors in database
'Kurtz_Inc_MSCRM'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Thanks,
Richard|||On Mar 16, 6:15=A0am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Richard
> If you add WITH CONTINUE_AFTER_ERROR =A0your backup should complered
> succefully
> <Richard.G...@.pa-tech.com> wrote in message
> news:f98ed696-ed40-4655-98ad-3d9849c0404a@.n75g2000hsh.googlegroups.com...
>
> > If anyone has any ideas on this one ... I'd appreciate it very
> > much ...
> > ... I've got one database on our server that refuses to backup ...
> > several other database work just fine ...
> > ... when I issue ...
> > BACKUP DATABASE [Kurtz_Inc_MSCRM] TO =A0DISK =3D N'D:\MSSQL\BACKUP
> > \Kurtz_Inc_MSCRM_backup_manual.bak'
> > ... it responds immediately with ...
> > Msg 3636, Level 16, State 2, Line 1
> > An error occurred while processing 'BackupMetadata' metadata for
> > database id 8 file id 65537.
> > Msg 3046, Level 16, State 2, Line 1
> > Inconsistent metadata has been encountered. The only possible backup
> > operation is a tail-log backup using the WITH CONTINUE_AFTER_ERROR or
> > NO_TRUNCATE option.
> > Msg 3013, Level 16, State 1, Line 1
> > BACKUP DATABASE is terminating abnormally.
> > ... if I include the "WITH CONTINUE_AFTER_ERROR" ... I still get the
> > same error.
> > Thoughts? =A0(If it matters, the recovery model is "Full")
> > -Richard- Hide quoted text -
> - Show quoted text -
Uri,
That's one of my problems (from the original post) ... adding "WITH
CONTINUE_AFTER_ERROR" seems to have no effect ... I get same error
immediately after issuing the backup command.
Thanks,
Richard|||Hmm, if CHECKDB comes out clean but you get below error for BACKUP DATABASE then I would be very
nervous and open a case immediately with MS Support...
> > An error occurred while processing 'BackupMetadata' metadata for
> > database id 8 file id 65537.
> > Msg 3046, Level 16, State 2, Line 1
> > Inconsistent metadata has been encountered. The only possible backup
> > operation is a tail-log backup using the WITH CONTINUE_AFTER_ERROR or
If course assuming that the database ID above mentioned is the same as the one you checkdb and
attempt to backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<Richard.Gohs@.pa-tech.com> wrote in message
news:e0d09f1c-ff0b-4899-abd1-5eee822f08a7@.e39g2000hsf.googlegroups.com...
On Mar 13, 5:18 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> I interpret the error message such as you have a corrupt database and the message suggest you to
> do
> the very last log backup (TRUNCATE_ONLY) before you clean up the database by restoring a clean
> backup and all subsequent log backups. Did you DBCC CHECKDB the database?
> --
> Tibor Karaszi, SQL Server
> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> <Richard.G...@.pa-tech.com> wrote in message
> news:f98ed696-ed40-4655-98ad-3d9849c0404a@.n75g2000hsh.googlegroups.com...
>
> > If anyone has any ideas on this one ... I'd appreciate it very
> > much ...
> > ... I've got one database on our server that refuses to backup ...
> > several other database work just fine ...
> > ... when I issue ...
> > BACKUP DATABASE [Kurtz_Inc_MSCRM] TO DISK = N'D:\MSSQL\BACKUP
> > \Kurtz_Inc_MSCRM_backup_manual.bak'
> > ... it responds immediately with ...
> > Msg 3636, Level 16, State 2, Line 1
> > An error occurred while processing 'BackupMetadata' metadata for
> > database id 8 file id 65537.
> > Msg 3046, Level 16, State 2, Line 1
> > Inconsistent metadata has been encountered. The only possible backup
> > operation is a tail-log backup using the WITH CONTINUE_AFTER_ERROR or
> > NO_TRUNCATE option.
> > Msg 3013, Level 16, State 1, Line 1
> > BACKUP DATABASE is terminating abnormally.
> > ... if I include the "WITH CONTINUE_AFTER_ERROR" ... I still get the
> > same error.
> > Thoughts? (If it matters, the recovery model is "Full")
> > -Richard- Hide quoted text -
> - Show quoted text -
Tibor,
FYI ... the reason I can't really go back to the backup is that the
database appears to be functional from the users point of view ...
(it's a Microsoft CRM 3.0 install) ... and users have been putting
additional data into it - and - I don't have log backups back to the
time of the last full backup (which I suspect means I'm hosed)
Running dbcc checkdb results in ...
.
.
.
CHECKDB found 0 allocation errors and 0 consistency errors in database
'Kurtz_Inc_MSCRM'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Thanks,
Richard

Backup Maintence Plan Fails

I get the error every day. i've checked everything i know
of to make sure that nothing including the backup job is
accessing the database. This job has run for years and is
suddenly failing for the last few weeks. Any help would be
appreciated. Win2k Server SQL2000 with SP3.
Lamar
App: E 'Wed Oct 01 00:24:26 2003': MSSQLSERVER - "
3041 : BACKUP failed to complete the command BACKUP
DATABASE [DMD1] TO DISK = N'd:\MSSQL\BACKUP\DMD1_db_200309302200.BAK' WITH INIT ,
NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT "
App: E 'Wed Oct 01 00:24:26 2003': MSSQLSERVER - "
18210 : BackupMedium::ReportIoError: write failure on
backup device 'd:\MSSQL\BACKUP\DMD1_db_200309302200.BAK'.
Operating system error 33(The process cannot access the
file because another process has locked a portion of the
file.). "Seems like some other program is using and locking the backup file. You have to hunt that down.
Could be some anti-virus program, for instance. I thinks that www.sysinternals has tools for this.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Lamar Mrris" <LamarMorris@.ti.com> wrote in message news:064701c39d5b$2d38fc80$a001280a@.phx.gbl...
> I get the error every day. i've checked everything i know
> of to make sure that nothing including the backup job is
> accessing the database. This job has run for years and is
> suddenly failing for the last few weeks. Any help would be
> appreciated. Win2k Server SQL2000 with SP3.
> Lamar
> App: E 'Wed Oct 01 00:24:26 2003': MSSQLSERVER - "
> 3041 : BACKUP failed to complete the command BACKUP
> DATABASE [DMD1] TO DISK => N'd:\MSSQL\BACKUP\DMD1_db_200309302200.BAK' WITH INIT ,
> NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT "
> App: E 'Wed Oct 01 00:24:26 2003': MSSQLSERVER - "
> 18210 : BackupMedium::ReportIoError: write failure on
> backup device 'd:\MSSQL\BACKUP\DMD1_db_200309302200.BAK'.
> Operating system error 33(The process cannot access the
> file because another process has locked a portion of the
> file.). "
>
>|||... or maybe the backup file is being backed up -- to tape, for example.
Whatever the case, checking with the system admin should provide some help,
at least eliminate some candidate problem sources.
Quentin
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:u3gxJDWnDHA.2652@.TK2MSFTNGP09.phx.gbl...
> Seems like some other program is using and locking the backup file. You
have to hunt that down.
> Could be some anti-virus program, for instance. I thinks that
www.sysinternals has tools for this.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Lamar Mrris" <LamarMorris@.ti.com> wrote in message
news:064701c39d5b$2d38fc80$a001280a@.phx.gbl...
> > I get the error every day. i've checked everything i know
> > of to make sure that nothing including the backup job is
> > accessing the database. This job has run for years and is
> > suddenly failing for the last few weeks. Any help would be
> > appreciated. Win2k Server SQL2000 with SP3.
> >
> > Lamar
> > App: E 'Wed Oct 01 00:24:26 2003': MSSQLSERVER - "
> > 3041 : BACKUP failed to complete the command BACKUP
> > DATABASE [DMD1] TO DISK => > N'd:\MSSQL\BACKUP\DMD1_db_200309302200.BAK' WITH INIT ,
> > NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT "
> > App: E 'Wed Oct 01 00:24:26 2003': MSSQLSERVER - "
> > 18210 : BackupMedium::ReportIoError: write failure on
> > backup device 'd:\MSSQL\BACKUP\DMD1_db_200309302200.BAK'.
> > Operating system error 33(The process cannot access the
> > file because another process has locked a portion of the
> > file.). "
> >
> >
> >
>|||Thanks for the input, i've already excluded the whole directory from
Virus scanning or realtime protection.
Lamar
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||To add to what Tibor said, you can get a utility called
nthandle.exe from www.sysinternals.com.
To help you determine what process is holding that backup
file, you can modify your backup job to add a step before
the backup step. In this step, run:
path>nthandle.exe DMD1_db
and make sure that you specify the output file for this
step.
When the backup job fails, you can then review the output
file for the first step to identify the offending process
that was holding your backup file.
My guess would be: it's your tape backup process if you
are running a tape backup process to backup the files on
the disk.
Linchi
>--Original Message--
>Seems like some other program is using and locking the
backup file. You have to hunt that down.
>Could be some anti-virus program, for instance. I thinks
that www.sysinternals has tools for this.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Lamar Mrris" <LamarMorris@.ti.com> wrote in message
news:064701c39d5b$2d38fc80$a001280a@.phx.gbl...
>> I get the error every day. i've checked everything i
know
>> of to make sure that nothing including the backup job is
>> accessing the database. This job has run for years and
is
>> suddenly failing for the last few weeks. Any help would
be
>> appreciated. Win2k Server SQL2000 with SP3.
>> Lamar
>> App: E 'Wed Oct 01 00:24:26 2003': MSSQLSERVER - "
>> 3041 : BACKUP failed to complete the command BACKUP
>> DATABASE [DMD1] TO DISK =>> N'd:\MSSQL\BACKUP\DMD1_db_200309302200.BAK' WITH INIT ,
>> NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT "
>> App: E 'Wed Oct 01 00:24:26 2003': MSSQLSERVER - "
>> 18210 : BackupMedium::ReportIoError: write failure on
>> backup
device 'd:\MSSQL\BACKUP\DMD1_db_200309302200.BAK'.
>> Operating system error 33(The process cannot access the
>> file because another process has locked a portion of the
>> file.). "
>>
>
>.
>

Backup Maintenance job failing (-1073548784)

Hi,
I have created a maintenance job that fails at cleaning up the old backup
files. Messages of the Maintenance job are:
Task detail:
>Maintenance Cleanup on Local server connection
>Cleanup Database Backup files
>Age: Older than 3 Days
Error Number:
>-1073548784
Error Message:
>Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2007-12-09T11:00:24'
>" failed with the following error: "Error executing extended stored procedure: Invalid Parameter". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Any ideas why the cleanup is not working, and how to fix this ?Ensure that you defined a Folder path in the Define Maintanence Cleanup Task
window. It's empty by default and if you leave it empty then you get this
error.
--
Ekrem Ã?nsoy
"Hen" <Hen@.discussions.microsoft.com> wrote in message
news:F3B48F39-727F-4364-872D-472A4F0972E7@.microsoft.com...
> Hi,
> I have created a maintenance job that fails at cleaning up the old backup
> files. Messages of the Maintenance job are:
> Task detail:
>>Maintenance Cleanup on Local server connection
>>Cleanup Database Backup files
>>Age: Older than 3 Days
> Error Number:
>>-1073548784
> Error Message:
>>Executing the query "EXECUTE master.dbo.xp_delete_file
>>0,N'',N'',N'2007-12-09T11:00:24'
>>" failed with the following error: "Error executing extended stored
>>procedure: Invalid Parameter". Possible failure reasons: Problems with the
>>query, "ResultSet" property not set correctly, parameters not set
>>correctly, or connection not established correctly.
> Any ideas why the cleanup is not working, and how to fix this ?|||The path is indeed empty. When I try to modify/edit the database maintenance
cleanup task, the dialogs let me fill in a path but the OK button remains
greyed out. Is there a special procedure required to edit this ?.
"Ekrem Ã?nsoy" wrote:
> Ensure that you defined a Folder path in the Define Maintanence Cleanup Task
> window. It's empty by default and if you leave it empty then you get this
> error.
> --
> Ekrem Ã?nsoy
>
> "Hen" <Hen@.discussions.microsoft.com> wrote in message
> news:F3B48F39-727F-4364-872D-472A4F0972E7@.microsoft.com...
> > Hi,
> >
> > I have created a maintenance job that fails at cleaning up the old backup
> > files. Messages of the Maintenance job are:
> >
> > Task detail:
> >>Maintenance Cleanup on Local server connection
> >>Cleanup Database Backup files
> >>Age: Older than 3 Days
> > Error Number:
> >>-1073548784
> > Error Message:
> >>Executing the query "EXECUTE master.dbo.xp_delete_file
> >>0,N'',N'',N'2007-12-09T11:00:24'
> >>" failed with the following error: "Error executing extended stored
> >>procedure: Invalid Parameter". Possible failure reasons: Problems with the
> >>query, "ResultSet" property not set correctly, parameters not set
> >>correctly, or connection not established correctly.
> >
> > Any ideas why the cleanup is not working, and how to fix this ?
>|||Found it:
Had to fill in the extension, and now the OK button is available.
"Hen" wrote:
> The path is indeed empty. When I try to modify/edit the database maintenance
> cleanup task, the dialogs let me fill in a path but the OK button remains
> greyed out. Is there a special procedure required to edit this ?.
>
> "Ekrem Ã?nsoy" wrote:
> > Ensure that you defined a Folder path in the Define Maintanence Cleanup Task
> > window. It's empty by default and if you leave it empty then you get this
> > error.
> >
> > --
> > Ekrem Ã?nsoy
> >
> >
> >
> > "Hen" <Hen@.discussions.microsoft.com> wrote in message
> > news:F3B48F39-727F-4364-872D-472A4F0972E7@.microsoft.com...
> > > Hi,
> > >
> > > I have created a maintenance job that fails at cleaning up the old backup
> > > files. Messages of the Maintenance job are:
> > >
> > > Task detail:
> > >>Maintenance Cleanup on Local server connection
> > >>Cleanup Database Backup files
> > >>Age: Older than 3 Days
> > > Error Number:
> > >>-1073548784
> > > Error Message:
> > >>Executing the query "EXECUTE master.dbo.xp_delete_file
> > >>0,N'',N'',N'2007-12-09T11:00:24'
> > >>" failed with the following error: "Error executing extended stored
> > >>procedure: Invalid Parameter". Possible failure reasons: Problems with the
> > >>query, "ResultSet" property not set correctly, parameters not set
> > >>correctly, or connection not established correctly.
> > >
> > > Any ideas why the cleanup is not working, and how to fix this ?
> >

Backup Maintenance job failing (-1073548784)

Hi,
I have created a maintenance job that fails at cleaning up the old backup
files. Messages of the Maintenance job are:
Task detail:
>Maintenance Cleanup on Local server connection
>Cleanup Database Backup files
>Age: Older than 3 Days
Error Number:
>-1073548784
Error Message:
>Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2007-12-09T11:00:24'
>" failed with the following error: "Error executing extended stored procedure: Invalid Parameter". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Any ideas why the cleanup is not working, and how to fix this ?
Ensure that you defined a Folder path in the Define Maintanence Cleanup Task
window. It's empty by default and if you leave it empty then you get this
error.
Ekrem ?nsoy
"Hen" <Hen@.discussions.microsoft.com> wrote in message
news:F3B48F39-727F-4364-872D-472A4F0972E7@.microsoft.com...
> Hi,
> I have created a maintenance job that fails at cleaning up the old backup
> files. Messages of the Maintenance job are:
> Task detail:
> Error Number:
> Error Message:
> Any ideas why the cleanup is not working, and how to fix this ?
|||The path is indeed empty. When I try to modify/edit the database maintenance
cleanup task, the dialogs let me fill in a path but the OK button remains
greyed out. Is there a special procedure required to edit this ?.
"Ekrem ?nsoy" wrote:

> Ensure that you defined a Folder path in the Define Maintanence Cleanup Task
> window. It's empty by default and if you leave it empty then you get this
> error.
> --
> Ekrem ?nsoy
>
> "Hen" <Hen@.discussions.microsoft.com> wrote in message
> news:F3B48F39-727F-4364-872D-472A4F0972E7@.microsoft.com...
>
|||Found it:
Had to fill in the extension, and now the OK button is available.
"Hen" wrote:
[vbcol=seagreen]
> The path is indeed empty. When I try to modify/edit the database maintenance
> cleanup task, the dialogs let me fill in a path but the OK button remains
> greyed out. Is there a special procedure required to edit this ?.
>
> "Ekrem ?nsoy" wrote:

Backup Maintenance job failing (-1073548784)

Hi,
I have created a maintenance job that fails at cleaning up the old backup
files. Messages of the Maintenance job are:
Task detail:
>Maintenance Cleanup on Local server connection
>Cleanup Database Backup files
>Age: Older than 3 Days
Error Number:
>-1073548784
Error Message:
>Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2007-12-
09T11:00:24'
>" failed with the following error: "Error executing extended stored procedure: Inva
lid Parameter". Possible failure reasons: Problems with the query, "ResultSet" prope
rty not set correctly, parameters not set correctly, or connection not established c
orr
ectly.
Any ideas why the cleanup is not working, and how to fix this ?Ensure that you defined a Folder path in the Define Maintanence Cleanup Task
window. It's empty by default and if you leave it empty then you get this
error.
Ekrem ?nsoy
"Hen" <Hen@.discussions.microsoft.com> wrote in message
news:F3B48F39-727F-4364-872D-472A4F0972E7@.microsoft.com...
> Hi,
> I have created a maintenance job that fails at cleaning up the old backup
> files. Messages of the Maintenance job are:
> Task detail:
> Error Number:
> Error Message:
> Any ideas why the cleanup is not working, and how to fix this ?|||The path is indeed empty. When I try to modify/edit the database maintenance
cleanup task, the dialogs let me fill in a path but the OK button remains
greyed out. Is there a special procedure required to edit this ?.
"Ekrem ?nsoy" wrote:

> Ensure that you defined a Folder path in the Define Maintanence Cleanup Ta
sk
> window. It's empty by default and if you leave it empty then you get this
> error.
> --
> Ekrem ?nsoy
>
> "Hen" <Hen@.discussions.microsoft.com> wrote in message
> news:F3B48F39-727F-4364-872D-472A4F0972E7@.microsoft.com...
>|||Found it:
Had to fill in the extension, and now the OK button is available.
"Hen" wrote:
[vbcol=seagreen]
> The path is indeed empty. When I try to modify/edit the database maintenan
ce
> cleanup task, the dialogs let me fill in a path but the OK button remains
> greyed out. Is there a special procedure required to edit this ?.
>
> "Ekrem ?nsoy" wrote:
>

Backup LOG fails when trying to backup database in SIMPLE recovery mode

I'm running a backup like this:

BACKUP LOG @.database_name TO DISK = @.log_location_string WITH INIT , NAME = @.database_name

The log backup fails when my script hits a database that is set to simple. Problem is, it won't skip that database and continue on, it just stops right there, leaving the rest of the database logs not backed up!

How can I make my job skip backing up logs if the database is set to simple?

Thanks
TommyCorrection! It looks like the Log backup job IS skipping the SIMPLE database and backing up the rest. However, in my Transact-SQL Output file, the last line in the log file stops with:

Msg 3013, Sev 16: BACKUP LOG is terminating abnormally. [SQLSTATE 42000]
Msg 4208, Sev 16: The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE. [SQLSTATE 42000]
Msg 4035, Sev 16: Processed 1 pages for database 'DBNAME', file 'DBNAME_log' on file 1. [SQLSTATE 01000]

Why the heck doesn't the log file show the rest of the results?
I have options set:
Overwrite
Append output to step history

Thanks
Tommy|||I'm not really sure I understand what you're talking about, but it sounds like you are using the sql maintenance plan. If you are using maintenance plan, maybe you should consider breaking the database that's using simple recovery off to a separate plan. You could write the log to the same file, and have the first plan overwrite the log, and the second one can append.

Sorry if I have missed your point.|||My point is that I have scheduled a job that outputs it's results to a text file. The job backs up database logs, and writes each successful database to the txt file. However, when it fails on one database (because it's set to simple) it skips it, and continues on, but does not keep writing to the txt file.

Thanks
Tommy

backup job sometimes fails

Hi

We have a nightly job on a production server which backs up all databases
(about 90 on the server)

Sometimes it'll only backup some of them, yet it reports success.

Our proc to manage the process is below:

Any ideas on what we need to do to make it reliable ?

--

DECLARE @.DB_Name varchar(32)
DECLARE @.Backup_Path varchar(255)
DECLARE @.Backup_Name varchar(255)

DECLARE DB_Cursor CURSOR FOR SELECT NAME FROM sysdatabases

OPEN DB_Cursor

FETCH NEXT FROM DB_Cursor INTO @.DB_Name

WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.DB_Name <> 'tempdb' AND @.DB_Name <> 'model'
BEGIN
print '--<< ' + @.db_name + '
>>--'
SET @.Backup_Path = N'd:\sql2005backups\nightly\' + @.DB_Name + 'Daily' +
'.bak'
SET @.Backup_Name = @.DB_Name + N' backup'
BACKUP DATABASE @.DB_Name TO DISK = @.Backup_Path WITH INIT
print ''
END
FETCH NEXT FROM DB_Cursor INTO @.DB_Name
END

Print "Finished backing up the databases"

CLOSE DB_cursor
DEALLOCATE DB_cursor

My suggestion is to look into a 3rd party package for reliable backups of business critical data....SQL 2005 just isn't there yet in this area.

Jeff

|||create a job with the above script and keep the script in step1 of the job and then in the advanced options set an output file so that the output will be written to a text file, which will be used for troubleshooting purposes..............in the step2 you can have the mail step......and see whats the output after the backup completes.......is this box with sql 2005 sp1? if yes better go to SP2 as there are few bugs which are fixed in sp2.........also try to go with maintenance plan and see whats the result if the above option fails....|||

Hi

Thanks for this

We already write the output to a file - it doesn't contain any errors or anything unusual - most strange...

The box is running SQL 2005 sp2.

I could go and buy something like Red-gate backup - but I don't see why I should have to ! Most frustrating..

thanks

Bruce

|||

You don't need a third party solution - SQL native backups were fine and have for years. Third party solutions are the ones that tend to be a bit more problematic. The only problem I can think of that Jeff would have been referring to is maintenance plans. The problem is with maintenance plans, not SQL Server backups.

I'm curious - you are using both single quotes as well as double quotes for print statements? Is that the actual script you are using? What is in the output file? Does it have the name of the database that are skipped or not? What is in the error log? Have you tried running a server side trace during the times you run the backups to see what is actually being executed?

-Sue

|||

Hi Sue

Thanks for replying..

The line with the double-quotes we ended up commenting out.

I also put the backup statement in a 'try catch' as follows below and there was no error logged

No - it doesn't have the name of the databases that are skipped - it just seems to exit without logging any errors - and it's not consistent - some nights it gets to the end.

There is nothing logged in the error logs.... No I haven't tried running a server side trace..

thanks, Bruce.

DECLARE @.DB_Name varchar(32)
DECLARE @.Backup_Path varchar(255)
DECLARE @.Backup_Name varchar(255)

DECLARE DB_Cursor CURSOR FOR SELECT NAME FROM sysdatabases order by name

OPEN DB_Cursor

FETCH NEXT FROM DB_Cursor INTO @.DB_Name

WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.DB_Name <> 'tempdb' AND @.DB_Name <> 'model'
BEGIN
print '--<< ' + @.db_name + ' >>--'
SET @.Backup_Path = N'd:\sql2005backups\nightly\' + @.DB_Name + 'Daily' + '.bak'
SET @.Backup_Name = @.DB_Name + N' backup'
BEGIN TRY
BACKUP DATABASE @.DB_Name TO DISK = @.Backup_Path WITH INIT ;
END TRY
BEGIN CATCH
Print 'Error -- ' + ERROR_MESSAGE();
END CATCH;
print ''
END
FETCH NEXT FROM DB_Cursor INTO @.DB_Name
END

CLOSE DB_cursor
DEALLOCATE DB_cursor

print 'Finished backing up'

|||

So you need to run a trace during the backups (and for a time prior to them starting) to see more of what is going on. You'll also want to look at the logs prior to and during the backups.

-Sue

|||

Bruce,

I too have something like this. I noticed that this happens when you have many databases needs to be backed up.

What we did here is to split this backup jobs into multiple steps. Each Job will backup few databases may be 50.

Try this and let me know.

Thanks

Shilpi

|||Can you check whether the existing backup file is in use? If possible alter the script to append date at the end of backup filename. Since we are backingup nearly 88 databases but didnt find any issues.

In which account is the job running and does the account has backup operator privilege to those failed db's?

backup job sometimes fails

Hi

We have a nightly job on a production server which backs up all databases
(about 90 on the server)

Sometimes it'll only backup some of them, yet it reports success.

Our proc to manage the process is below:

Any ideas on what we need to do to make it reliable ?

--

DECLARE @.DB_Name varchar(32)
DECLARE @.Backup_Path varchar(255)
DECLARE @.Backup_Name varchar(255)

DECLARE DB_Cursor CURSOR FOR SELECT NAME FROM sysdatabases

OPEN DB_Cursor

FETCH NEXT FROM DB_Cursor INTO @.DB_Name

WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.DB_Name <> 'tempdb' AND @.DB_Name <> 'model'
BEGIN
print '--<< ' + @.db_name + '
>>--'
SET @.Backup_Path = N'd:\sql2005backups\nightly\' + @.DB_Name + 'Daily' +
'.bak'
SET @.Backup_Name = @.DB_Name + N' backup'
BACKUP DATABASE @.DB_Name TO DISK = @.Backup_Path WITH INIT
print ''
END
FETCH NEXT FROM DB_Cursor INTO @.DB_Name
END

Print "Finished backing up the databases"

CLOSE DB_cursor
DEALLOCATE DB_cursor

My suggestion is to look into a 3rd party package for reliable backups of business critical data....SQL 2005 just isn't there yet in this area.

Jeff

|||create a job with the above script and keep the script in step1 of the job and then in the advanced options set an output file so that the output will be written to a text file, which will be used for troubleshooting purposes..............in the step2 you can have the mail step......and see whats the output after the backup completes.......is this box with sql 2005 sp1? if yes better go to SP2 as there are few bugs which are fixed in sp2.........also try to go with maintenance plan and see whats the result if the above option fails....|||

Hi

Thanks for this

We already write the output to a file - it doesn't contain any errors or anything unusual - most strange...

The box is running SQL 2005 sp2.

I could go and buy something like Red-gate backup - but I don't see why I should have to ! Most frustrating..

thanks

Bruce

|||

You don't need a third party solution - SQL native backups were fine and have for years. Third party solutions are the ones that tend to be a bit more problematic. The only problem I can think of that Jeff would have been referring to is maintenance plans. The problem is with maintenance plans, not SQL Server backups.

I'm curious - you are using both single quotes as well as double quotes for print statements? Is that the actual script you are using? What is in the output file? Does it have the name of the database that are skipped or not? What is in the error log? Have you tried running a server side trace during the times you run the backups to see what is actually being executed?

-Sue

|||

Hi Sue

Thanks for replying..

The line with the double-quotes we ended up commenting out.

I also put the backup statement in a 'try catch' as follows below and there was no error logged

No - it doesn't have the name of the databases that are skipped - it just seems to exit without logging any errors - and it's not consistent - some nights it gets to the end.

There is nothing logged in the error logs.... No I haven't tried running a server side trace..

thanks, Bruce.

DECLARE @.DB_Name varchar(32)
DECLARE @.Backup_Path varchar(255)
DECLARE @.Backup_Name varchar(255)

DECLARE DB_Cursor CURSOR FOR SELECT NAME FROM sysdatabases order by name

OPEN DB_Cursor

FETCH NEXT FROM DB_Cursor INTO @.DB_Name

WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.DB_Name <> 'tempdb' AND @.DB_Name <> 'model'
BEGIN
print '--<< ' + @.db_name + ' >>--'
SET @.Backup_Path = N'd:\sql2005backups\nightly\' + @.DB_Name + 'Daily' + '.bak'
SET @.Backup_Name = @.DB_Name + N' backup'
BEGIN TRY
BACKUP DATABASE @.DB_Name TO DISK = @.Backup_Path WITH INIT ;
END TRY
BEGIN CATCH
Print 'Error -- ' + ERROR_MESSAGE();
END CATCH;
print ''
END
FETCH NEXT FROM DB_Cursor INTO @.DB_Name
END

CLOSE DB_cursor
DEALLOCATE DB_cursor

print 'Finished backing up'

|||

So you need to run a trace during the backups (and for a time prior to them starting) to see more of what is going on. You'll also want to look at the logs prior to and during the backups.

-Sue

|||

Bruce,

I too have something like this. I noticed that this happens when you have many databases needs to be backed up.

What we did here is to split this backup jobs into multiple steps. Each Job will backup few databases may be 50.

Try this and let me know.

Thanks

Shilpi

|||Can you check whether the existing backup file is in use? If possible alter the script to append date at the end of backup filename. Since we are backingup nearly 88 databases but didnt find any issues.

In which account is the job running and does the account has backup operator privilege to those failed db's?

Backup job Fails every time:

Dear Friends!
I have a scheduled job on my production server which always fails. When I
see the job history step details I can see 100 % backedup and along with
that I receive error
[SQLSTATE 01000] (Message 3211) Processed 1 pages for database 'SLNKMIL',
file 'SLNKMIL_Log' on file 1. [SQLSTATE 01000] (Message 4035) BACKUP
DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The
step failed.
Pl, guide me. Job fail isn't affordable on my production database.
I sincerly thank you!
S.Lakshminarayanan.
Message posted via http://www.sqlmonster.com
Can you try issuing a BACKUP DATABASE statement via T-SQL through Query
Analyzer? It would be helpful to see the errors (if there are any). Before
running the backup make sure that you have results set to text.
The syntax is easy enough:
BACKUP DATABASE SLNKMIL TO DISK = 'x:\SLNKMIL.bak' WITH INIT
Keith
"lakshminarayan iyer via SQLMonster.com" <forum@.SQLMonster.com> wrote in
message news:67ccca76be724649be8629b2da37796a@.SQLMonster.c om...
> Dear Friends!
> I have a scheduled job on my production server which always fails. When I
> see the job history step details I can see 100 % backedup and along with
> that I receive error
> [SQLSTATE 01000] (Message 3211) Processed 1 pages for database
'SLNKMIL',
> file 'SLNKMIL_Log' on file 1. [SQLSTATE 01000] (Message 4035) BACKUP
> DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The
> step failed.
> Pl, guide me. Job fail isn't affordable on my production database.
> I sincerly thank you!
> S.Lakshminarayanan.
> --
> Message posted via http://www.sqlmonster.com
|||Is the database in Simple recovery mode? If so you can not do a log backup.
Andrew J. Kelly SQL MVP
"lakshminarayan iyer via SQLMonster.com" <forum@.SQLMonster.com> wrote in
message news:67ccca76be724649be8629b2da37796a@.SQLMonster.c om...
> Dear Friends!
> I have a scheduled job on my production server which always fails. When I
> see the job history step details I can see 100 % backedup and along with
> that I receive error
> [SQLSTATE 01000] (Message 3211) Processed 1 pages for database 'SLNKMIL',
> file 'SLNKMIL_Log' on file 1. [SQLSTATE 01000] (Message 4035) BACKUP
> DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The
> step failed.
> Pl, guide me. Job fail isn't affordable on my production database.
> I sincerly thank you!
> S.Lakshminarayanan.
> --
> Message posted via http://www.sqlmonster.com