none
Database Backup and copy to Shared drive

    Question

  • Hello Experts,

    Please help me for below process. (SQL 2008R2 SP2 Enterprise Edition 64 bits). We do not have issue with full backups. Only Tlog backups issue.

    Step 1: Backup tlog to local drive (I did) ... Tlog backup comes up with some kind of timestamps unique number with datetime. (For example, northwind07222013124532.trn)

    Step 2: We want to copy the backup to SAN/Shared drive. How could I tell sql to get the file of first step?

    Thank you.

    Monday, August 05, 2013 1:07 PM

Answers

  • As was suggested, you're going to need to use something like robocopy and wild cards in the filename.

    You could do something like this:

    1. Backup using an SQL Agent job to your chosen folder
    2. Either from in SQL Agent or a Windows Task Scheduler, run a batch file

    The batch file could be something like:

    @echo off
    x:
    cd\backupfolder
    copy *.trn \\server\sharename

    Obviously, replace x: and \\server\sharename with whatever is appropriate for your environment.

    Robocopy, while much more complex than a simple copy or xcopy, has the advantage of being able to create a log file, and will only copy new files.

    Jason


    Jason A.


    Monday, August 05, 2013 6:49 PM

All replies

  • Hi,

    You could use something like the robocopy utility external to SQL Server, or mirrored backups:

    backup database mydb to disk = N'S:\mydb.bak'
    mirror to disk = N'\\10.10.10.10\backup\mydb.bak'
    with format



    Thanks, Andrew

    Monday, August 05, 2013 4:05 PM
  • If SQL Server service account has permission on the shared drive you can directly backup to that folder

    backup database mydb to disk = N'\\myserver\share\mydb.bak'

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Monday, August 05, 2013 4:14 PM
  • use this code
    declare @str varchar(50)
    set @str= <<PATH\>>+'DBName_'+replace(CONVERT(varchar(50),getdate(),21),':','.')+'.trn'
    BACKUP LOG <<DBNAME>> TO  
    DISK = @str


    Satheesh

    Monday, August 05, 2013 4:18 PM
  • I can use mirror to disk BUT we are not trying to have duplicate backup through t-sql/sql server. In short, the team do not want to touch SQL Server. Just do backup to local drive, second step to run through CMD prompt, the problem I am facing is, not able to determine what would be the tlog name?
    Monday, August 05, 2013 5:48 PM
  • We do not want to run anything on SQL Server other than backing up tlog to local drive. We want to remain SQL Server out here. Just second step to do using any CMD command in job step which takes latest tlog file and copies to shared drive.

    Monday, August 05, 2013 5:50 PM
  • As was suggested, you're going to need to use something like robocopy and wild cards in the filename.

    You could do something like this:

    1. Backup using an SQL Agent job to your chosen folder
    2. Either from in SQL Agent or a Windows Task Scheduler, run a batch file

    The batch file could be something like:

    @echo off
    x:
    cd\backupfolder
    copy *.trn \\server\sharename

    Obviously, replace x: and \\server\sharename with whatever is appropriate for your environment.

    Robocopy, while much more complex than a simple copy or xcopy, has the advantage of being able to create a log file, and will only copy new files.

    Jason


    Jason A.


    Monday, August 05, 2013 6:49 PM
  • Thank you Jason and colleagues, I will implement and get back if any issues.
    Monday, August 05, 2013 7:35 PM
  • Hi there,

    make sure your database is in full recovery model to allow transaction log backups.

    if you know how often do you want to take the logback ups? if yes you can create a Agent job for taking the log backup and copy that file to the desired location. 

    make sure the agent service account have write access to that folder. 

    If you want to perform manually, use the below command:

    BACKUP LOG [MYDATABASE] TO DISK = N'C:\destination folder\MyLog.trn'

    then copy your file across the network.

    Good luck

    Kumar

    Monday, August 05, 2013 8:47 PM
  • There is Powershell script. It looks for files that are not in destination folder and copy them. It can be scheduled as Powershell job step type.

    $backups = "\\UNCPath_or_localPath\Backups\*.trn"
    $destination = "\\UNCPath_or_localPath\Destination\"
    $files = get-childitem $backups
    foreach ($file in $files)
    {
    if ((Test-Path ($destination + "\" + $file.Name)) -ne "True")
    {
    Copy-Item $file.FullName -Destination $destination
    "Copied " + $file.Name
    }
    }


    Monday, August 05, 2013 8:49 PM