Copy backup of all databases to different locations.

Question

Post reply

Copy backup of all databases to different locations.

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
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.

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

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

durai nagarajan SSCoach Points: 15778 More actions · Answer 1

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

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 2

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.

Lowell SSC Guru Points: 323518 More actions · Answer 3

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!

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 4

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.

durai nagarajan SSCoach Points: 15778 More actions · Answer 5

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

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 6

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

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.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 8

SQL_Hunt

SSC-Dedicated

Points: 33518

July 2, 2012 at 10:04 am

#1507807

Lynn,

Can you share the script to use?

Thanks.

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 9

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://www.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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

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.

Dennis Post SSCarpal Tunnel Points: 4473 More actions · Answer 11

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

P Jones SSChampion Points: 12361 More actions · Answer 12

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.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 13

Hi D.Post,

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

Cheers

Thanks.

Ant-Green SSC Guru Points: 113507 More actions · Answer 14

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.