Post reply

Copy backup of all databases to different locations.

  • July 2, 2012 at 9:15 am

    #263108

    Hi,

    Can you please suggest an appropriate script through that my below purpose would be achieved :

    1. Generate backup of all databases and place that to two different locations..(1. local drive; 2. network location).

    2. Need to run a step in the maintenance plan to keep 2 days of backups of the databases located in local drive and 7 days of the backups located in the network drive.

    Would helpful if someone can reply a bit quickly!

    Cheers

    Thanks.

  • July 2, 2012 at 9:29 am

    #1507780

    Hope this helps.

    option 1 : create 2 step of backup for local and network, create maitanence cleanup task for local and network.

    option 2: create script for each db on one SQL task using the below

    -- Code for full back up

    BACKUP DATABASE [DBName] TO DISK = N'Drive or Network with file name' WITH NOFORMAT, NOINIT, NAME = N'Backup NAme', SKIP, REWIND, NOUNLOAD, STATS = 10

    -- Code for backup file deletion

    EXECUTE master.dbo.xp_delete_file 0,N'Drive or Network',N'File Extension to delete',N'date before to delete'

    Regards
    Durai Nagarajan

  • July 2, 2012 at 9:33 am

    #1507784

    Durai ,

    We need the same backup copies of all the databases to be available in local disk and also in the network drive!

    The script which you sent will either place that onto local drive or to the network drive.

    I assume I need xcopy/robocopy utility.

    This solution won't work for me!

    Finally, I need to run cleanup step ,keeping 2 days of retention for the database files in local drive and also retaining 7 days of backup files to the network drive!

    Thanks.

  • July 2, 2012 at 9:38 am

    #1507787

    look at the MIRROR TO option in the standard backup command;

    that can make an additional copy of the backup to another network/local loccation, so there are two copies out there.

    the only problem you might trip over is if the network resource were unavailable, didn't have permissions, ran out of space, etc, the whole backup will fail...it's an all or nothing kind of thing.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 2, 2012 at 9:41 am

    #1507790

    MIRROR TO OPtion is good, but unfortunately this too won't help...due to version issues.

    Msg 3218, Level 16, State 1, Line 29

    Backup mirroring is not available in this edition of SQL Server. See Books Online for more details on feature support in different SQL Server editions.

    Msg 3013, Level 16, State 1, Line 29

    BACKUP DATABASE is terminating abnormally.

    MY SQL Server version is ::

    SQL Server 2008

    Version : 10.0.4064

    Any other fruitful solution?

    I am basically for a good script to automate the work!

    Thanks.

  • July 2, 2012 at 9:53 am

    #1507798

    Sourav-657741 (7/2/2012)

    Durai ,

    We need the same backup copies of all the databases to be available in local disk and also in the network drive!

    The script which you sent will either place that onto local drive or to the network drive.

    Saurav try 2 backup steps and 2 Cleanup task defenitely will work.

    Regards
    Durai Nagarajan

  • July 2, 2012 at 9:56 am

    #1507800

    I can't run it twice!!! 🙁

    Since the timestamp would be different while running twice! So typically that would be two seperate backup copies.

    Can you provide any other suggestion instead please?

    Somehow I am unable to take it further on my own.

    Thanks.

  • July 2, 2012 at 10:00 am

    #1507804

    Looks like MIRROR TO requires the Enterprise Edition to work.

    At a previous employer I setup robocopy to automatically copy backup files from the local backup directory to an offsite location as the backup files were created, worked without any problems.

  • July 2, 2012 at 10:04 am

    #1507807

    Lynn,

    Can you share the script to use?

    Thanks.

  • July 2, 2012 at 10:05 am

    #1507810

    The copy piece isn't a part of anything native in SQL Server. Some third party utilities, like SQL Backup Pro from Red Gate, will do this, but you have to write your own in SQL Server.

    There are lots of scripts on this site which might help. Search through them and see which ones might fit your needs. Basically you need to get the file name of the backup and then make a copy.

    http://qa.sqlservercentral.com/search/?q=copy+backup&t=s

    For the cleanup, the maintenance task in a maintenance plan should help you. It can handle the 2 days part. You might be able to add a second task that looks in your remote location and does the same for 7 days.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • July 2, 2012 at 10:08 am

    #1507814

    Sourav-657741 (7/2/2012)

    Lynn,

    Can you share the script to use?

    As I said, at a previous employer. I don't have access to the script I used to setup Robocopy to copy the backup files. It was setup as a service basically and worked outside the backup process. As soon as the backup file (t-log, diff, or full) was completed, Robocopy copied the file to the second location.

    You'll have to lookup Robocopy in the Windows Server documentation to learn more.

  • July 3, 2012 at 1:43 am

    #1508041

    Have you looked into creating your own script to do this?

    I have an AutoIT script that checks the file dates and deletes files older than 1 month that are not the first of the month.

    You could easily change it to copy the backup files from your local backup location to a sub folder and a network share, then check the sub folder and network share for old files to delete.


    For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.[/url]

    "Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort

  • July 3, 2012 at 3:53 am

    #1508110

    I backup loads of servers using sql agent jobs and step1 does the backup using the TSQL backup command and step 2 is an operating system (CmdExec) copy command to copy the .bak file to a different server location on the network.

    For some servers I use the Ola Hallengren backup solution (available in the scripts on this site) and then copy all backups across with a cmdexec step.

  • July 3, 2012 at 4:20 am

    #1508128

    Hi D.Post,

    Can you please share the script with me which automate the entire process?

    Cheers

    Thanks.

  • July 3, 2012 at 4:25 am

    #1508134

    http://ss64.com/nt/robocopy.html

    Use the above link to create your own ROBOCOPY command which does what you want it to do.

    The link tells you about the main switches that you will need, then have the task execute at the end of the backup via a new jobstep to copy the backups from local disk to network drive.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply