Error 18210 Backup up SQL Server 2005 Database to network drive

I have 2 different servers that run nightly backup jobs to a network drive using the UNC format. I am noticing the larger databases encountering the following errors on a regular basis

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

'\\fnfssql3\SQLBackups\MNSQL05\<database>.BAK'. Operating system error 64(The specified network name is no longer available.).

BACKUP failed to complete the command BACKUP DATABASE <database>. Check the backup application log for detailed messages.

There are 5 databases on this server all backed up by the same job and 3 of them wortk fine and 2 of them fail so it is not a permissions problem. Have there been any problems with backing up to a network drive in 2005?

Well, backing up to networks drives is not officially *supported*, thats why the browser only display the local drives.

The reason for that and the logical explanation why you shouldn′t do that is, because the network is less reliable than local drives. If you want to make sure the database is backuped to network, you will have to either make sure the windows where the backups are streamed to the network don′t interfer with each other or you use a local backup to a drive and copy the files later over. Thats the most reliable option beside using a NAS / SAN.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Network hic-up can do this. One can see such error without sql involved.

Try trapping the error and retry the operation. Also, try backing up locally as suggested by Jens.

|||

Backing up database to a UNC path is fully supported. You must be thinking of running the database from a network drive.

http://support.microsoft.com/kb/304261

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx

|||

Oj,

the reason why I put *supported* in stars is, that it is sure supported, but not reliable in terms of just having a network share associated on a UNC share. (I thought he explanation should made that clearer, sorry for the poor explanation :-) ) The reliablity descrease due to your mentioned outages on the network. Having big backup files for the transfer over the network is there not recommended undless you have a SAN or a NAS which ensures more reliablity for the transfer. I often used the UNC path backup too on customer sites but either tried to shape the task to copy the files to different time windows or used a local backup and copied the files over with a mechanism (like xcopy / robocopy) to restart in the case of a connection loss.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Error 18204 on backup of Transaction log

I have used SQL Server 2005 Database Maintenance Plans to establish full backups and hourly transaction log backups. The jobs that stem from the DB Subplans run fine and report no errors when I view the history on them, however I have noticed the following error in my SQL Server error log. I am not seeing a similar error for my database backups, only the hourly log backups. Space is not an issue. Any guidance on this one would be greatly appreciated.

18204 :
BackupDiskFile::CreateMedia: Backup device <'Path\logname'> failed to create. Operating system
error = 2(The system cannot find the file specified.).

Thanks,

Jason

Have you checked that all directories exists that are used in your maintenance plan?

Daan

|||Is the job which to take full and log backups succeeds ? if not check check if the sql startup account has modify privileges to the path mentioned in the maintenanance plan....

Thanxx
Deepak
|||

Please check if you have all the necessary permissions on the destination driver as Deepak said. Also, please check if this KB is applicable to you: http://support.microsoft.com/kb/255235

HTH

Error 18204 on backup of Transaction log

I have used SQL Server 2005 Database Maintenance Plans to establish full backups and hourly transaction log backups. The jobs that stem from the DB Subplans run fine and report no errors when I view the history on them, however I have noticed the following error in my SQL Server error log. I am not seeing a similar error for my database backups, only the hourly log backups. Space is not an issue. Any guidance on this one would be greatly appreciated.

18204 :
BackupDiskFile::CreateMedia: Backup device <'Path\logname'> failed to create. Operating system
error = 2(The system cannot find the file specified.).

Thanks,

Jason

Have you checked that all directories exists that are used in your maintenance plan?

Daan

|||Is the job which to take full and log backups succeeds ? if not check check if the sql startup account has modify privileges to the path mentioned in the maintenanance plan....

Thanxx
Deepak
|||

Please check if you have all the necessary permissions on the destination driver as Deepak said. Also, please check if this KB is applicable to you: http://support.microsoft.com/kb/255235

HTH

sql

Error ! SQL XML Bulkload Please help

Hi guys
I am running COM component from SQL server jobs.
The COM component uses SQLXML bulk object to copy the data to SQL server.
I do get following error. The error comes some times only some. It is not consistant
can anyone tell me why it is happening some times only and how to resolve it
thanks in advance
error :-
Executed as user: myserver\Administrator. Error Code: 0 Error Source= Microsoft XML Bulkload for SQL Server Error Description: The error log file could not be created. Make sure you have the appropriate permissions and that the ErrorLogFilePath is valid
. Error on Line 1. The step failed.
Did you check the obvious permission and size issues for the log file
location?
Thanks
Michael
"Nitin" <Nitin@.discussions.microsoft.com> wrote in message
news:D780E0E2-3FA2-4F6B-BFFD-987E528682C8@.microsoft.com...
> Hi guys
> I am running COM component from SQL server jobs.
> The COM component uses SQLXML bulk object to copy the data to SQL server.
> I do get following error. The error comes some times only some. It is not
> consistant
> can anyone tell me why it is happening some times only and how to resolve
> it
> thanks in advance
> error :-
> Executed as user: myserver\Administrator. Error Code: 0 Error Source=
> Microsoft XML Bulkload for SQL Server Error Description: The error log
> file could not be created. Make sure you have the appropriate permissions
> and that the ErrorLogFilePath is valid. Error on Line 1. The step
> failed.
|||Yes
I did check the permission and size thats not the problem
funny thing is that if i run the job explicitly it does runs properly but if i schedule it to run automatically it gives me this error
thanks
Nitin
"Michael Rys [MSFT]" wrote:

> Did you check the obvious permission and size issues for the log file
> location?
> Thanks
> Michael
> "Nitin" <Nitin@.discussions.microsoft.com> wrote in message
> news:D780E0E2-3FA2-4F6B-BFFD-987E528682C8@.microsoft.com...
>
>
|||This sounds like you run the scheduled job with less permissions than when
you do. Is the scheduled job running as a different user?
Michael
"Nitin" <Nitin@.discussions.microsoft.com> wrote in message
news:C6033E80-4C72-44D5-B59C-3B750EE27C23@.microsoft.com...[vbcol=seagreen]
> Yes
> I did check the permission and size thats not the problem
> funny thing is that if i run the job explicitly it does runs properly but
> if i schedule it to run automatically it gives me this error
> thanks
> Nitin
> "Michael Rys [MSFT]" wrote:
|||Hi Michael
The schedule job gets run by SQL server Agent and the SQL server agent is running under administrator user. The SQL server job is calling is COM+ (in which i am using SQLXML bulkupload) and it runs under seperate user which has also got the administrator
permission. I dont know this happens some times only.
thanks for your kind support
Nitin
"Michael Rys [MSFT]" wrote:

> This sounds like you run the scheduled job with less permissions than when
> you do. Is the scheduled job running as a different user?
> Michael
> "Nitin" <Nitin@.discussions.microsoft.com> wrote in message
> news:C6033E80-4C72-44D5-B59C-3B750EE27C23@.microsoft.com...
>
>
|||Hi Michael
The schedule job gets run by SQL server Agent and the SQL server agent is running under administrator user. The SQL server job is calling is COM+ (in which i am using SQLXML bulkupload) and it runs under seperate user which has also got the administrator
permission. I dont know this happens some times only.
thanks for your kind support
Nitin
"Michael Rys [MSFT]" wrote:

> This sounds like you run the scheduled job with less permissions than when
> you do. Is the scheduled job running as a different user?
> Michael
> "Nitin" <Nitin@.discussions.microsoft.com> wrote in message
> news:C6033E80-4C72-44D5-B59C-3B750EE27C23@.microsoft.com...
>
>
|||Hi Michael
The schedule job gets run by SQL server Agent and the SQL server agent is running under administrator user. The SQL server job is calling is COM+ (in which i am using SQLXML bulkupload) and it runs under seperate user which has also got the administrator
permission. I dont know this happens some times only.
thanks for your kind support
Nitin
"Michael Rys [MSFT]" wrote:

> This sounds like you run the scheduled job with less permissions than when
> you do. Is the scheduled job running as a different user?
> Michael
> "Nitin" <Nitin@.discussions.microsoft.com> wrote in message
> news:C6033E80-4C72-44D5-B59C-3B750EE27C23@.microsoft.com...
>
>
|||Hi Michael
The schedule job gets run by SQL server Agent and the SQL server agent is running under administrator user. The SQL server job is calling is COM+ (in which i am using SQLXML bulkupload) and it runs under seperate user which has also got the administrator
permission. I dont know this happens some times only.
thanks for your kind support
Nitin
"Michael Rys [MSFT]" wrote:

> This sounds like you run the scheduled job with less permissions than when
> you do. Is the scheduled job running as a different user?
> Michael
> "Nitin" <Nitin@.discussions.microsoft.com> wrote in message
> news:C6033E80-4C72-44D5-B59C-3B750EE27C23@.microsoft.com...
>
>
|||Are you possibly running two jobs at once and one can not access the file
because the other still has it open?
Irwin
Irwin Dolobowsky
Program Manager - SqlXml
http://blogs.msdn.com/irwando
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nitin" <Nitin@.discussions.microsoft.com> wrote in message
news:75EB6BBE-9E47-4279-9D89-D2D5BC418B59@.microsoft.com...[vbcol=seagreen]
> Hi Michael
> The schedule job gets run by SQL server Agent and the SQL server agent is
> running under administrator user. The SQL server job is calling is COM+
> (in which i am using SQLXML bulkupload) and it runs under seperate user
> which has also got the administrator permission. I dont know this happens
> some times only.
> thanks for your kind support
> Nitin
>
> "Michael Rys [MSFT]" wrote:
|||Irwin
Possibly this could be the cause , I will keep different file name and try to run the jobs so that it wont use same file name
will let you know whats the outcome
thanks for your help
Nitin
"Irwin Dolobowsky [MS]" wrote:

> Are you possibly running two jobs at once and one can not access the file
> because the other still has it open?
> Irwin
> --
> Irwin Dolobowsky
> Program Manager - SqlXml
> http://blogs.msdn.com/irwando
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Nitin" <Nitin@.discussions.microsoft.com> wrote in message
> news:75EB6BBE-9E47-4279-9D89-D2D5BC418B59@.microsoft.com...
>
>

Erroneously deleted stored procedures

I have a teammate that, in an excess of zeal, managed to delete a majority of stored procedures on a production database, causing numerous jobs to fail. Additionally, there were no receint backups of this database taken, but the transaction log is still in place and has not been truncated. Is there any way to recover these stored procedures that I am unaware of?If the database is in the full recovery mode, you may be
able to do point-in-time restore to get back your deleted
procedures (if the log backup chain has not been broken):
1. Do a BACKUP LOG first
2. Do (using pubs as example)
restore database pubs from disk='...'
rstore log ... -- if any
restore log pubs from disk='...' -- the last log backup
with recovery, stopat = '20031010 17:59:59'
The STOPAT time should be before the time the procedures
were deleted.
Linchi
>--Original Message--
>I have a teammate that, in an excess of zeal, managed to
delete a majority of stored procedures on a production
database, causing numerous jobs to fail. Additionally,
there were no receint backups of this database taken, but
the transaction log is still in place and has not been
truncated. Is there any way to recover these stored
procedures that I am unaware of?
>.
>