none
SQLServer 2008 R2 Backup Transaction Log to Network Drive RRS feed

  • Question

  • Anyone know what can be done to backup transaction logs to a network drive?  I've got fulls and differntials working, but transaction logs get an error.

    BackupDiskFile::CreateMedia: Backup device 'D:\Microsoft SQL Server\mmmm_50.mmmm\MSSQL\Backup\ \\remoteserver\file.trn create. Operating system error 3(The system cannot find the path specified.).

    I had to start the sqlserver service as a domain user to get the full/diff going.  Do I need to do another?  SQL Server Browser?  SQL Server VSS Writer?

    Thanks,

    Morgan

    Tuesday, February 19, 2013 11:57 PM

Answers

All replies

  • Check the path, from the error you have a couple of extra slashes in the path.

    BackupDiskFile::CreateMedia: Backup device 'D:\Microsoft SQL Server\mmmm_50.mmmm\MSSQL\Backup\ \\remoteserver\file.trn create. Operating system error 3(The system cannot find the path specified.).

    Path should be. 'D:\Microsoft SQL Server\mmmm_50.mmmm\MSSQL\Backup\remoteserver\file.trn'

    • Edited by Glenn Wilson Wednesday, February 20, 2013 2:34 AM
    Wednesday, February 20, 2013 2:33 AM
  • Hi Morgan,

    You could backup transaction logs to a network drive. Make sure you have the permission to access the network drive.

    For example,

    Backup log AdventureWorks
    To disk=\\ServerName\backup
    

    Refer to BACKUP(Transact-SQL): http://msdn.microsoft.com/en-us/library/ms186865.aspx.

    If you have any feedback on our support, please click here.

    Thanks.


    Maggie Luo
    TechNet Community Support

    Wednesday, February 20, 2013 2:54 AM
    Moderator
  • Hi Morgan

    Maggie showed how to backup the log over a network (backup is the shared  folder ) so as alternative  you can look into

    Backup log AdventureWorks
    To disk=\\ServerName\d$\anotherfolder


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Wednesday, February 20, 2013 3:41 AM
    Moderator
  • Thank you for the replies...I am using a Maintenance Plan and I had the 'Create Subfolders' feature checked.  I removed that, as the subfolders already exist from the Full/Diff jobs.  I got a different error this time.  My Full/Diff jobs are working great

    BackupIoRequest::ReportIoError: write failure on backup device '\\server\e$\DBBackups\SQLServer\folder\TLogs\db_backup_2013_02_20_120151_7386000.trn'. Operating system error 1(Incorrect function.).

    T-SQL

    BACKUP LOG [DB] TO  DISK = N'\\server\e$\DBBackups\SQLServer\folder\TLogs\VCDB_backup_2013_02_20_121149_5036000.trn' WITH  RETAINDAYS = 14, NOFORMAT, NOINIT,  NAME = N'DB_backup_2013_02_20_121149_5016000', SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 10

    Wednesday, February 20, 2013 8:15 PM
  • This is a problem with the SAN that serves the lun that I am backing up to.  I just tested to an alternative location and it works.  Now I get to figure out why...
    Wednesday, February 20, 2013 8:22 PM
  • Check the speed of the disks and the conection, I have found with the older engines when backing up to a share on another server that if there is a small drop in the network (Not enough to stop a connection) it can interupt the backup and cause it to fail. Normally I would only perform my backups to a local disk or one direclty attached.
    Wednesday, February 20, 2013 8:33 PM

  • Hi Morgan,

    I’m writing to follow up with you on this post. Was the problem resolved?

    If you are satisfied with our solution, I’d like to mark this issue as "Answered". Please also feel free to unmark the issue, with any new findings or concerns you may have.  

    Thanks.


    Maggie Luo
    TechNet Community Support

    Wednesday, February 27, 2013 10:03 AM
    Moderator