Backup and XP_CMDSHELL move .bak files

Question

Backup and XP_CMDSHELL move .bak files

Jake Shelton

Hall of Fame

Points: 3109
More actions
June 12, 2014 at 10:07 am

#294432

Hi all,
I'm looking for the most time-efficient way to backup db's to a local drive and then move the .bak files across to a new server, as well as batch separation, and ideally ensure that any failure to backup to/move a particular .bak file does not impede others, or is at least flagged up.
So far, I'm thinking thus...
Backup Database AfterSalesTo Disk = 'D:\ AfterSales.bak'
Exec XP_CMDSHELL 'Move "D:\ AfterSales.bak' \\UKSLO1SQL05.FRENCHUK.LOCAL\Restore'
Go
Backup Database BatchControlTo Disk = 'D:\ (add path here)\ BatchControl.bak'With Replace
Exec XP_CMDSHELL 'Move "D:\BatchControl.bak' \\UKSLO1SQL05.FRENCH.LOCAL\Restore'
Go
Backup Database Cleverly Etc...
Go
Unfortunately, this still means that SQL will proceed with it's tasks in serial fashion - is there a more time-efficient way/sequence that I can force it to proceed?
I don't have any 3rd party software onsite to back up with.
Thanks,
Jake.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 1

Quick thought, wouldn't it be simpler to have an SSIS package for shuffling the backup files around, no need for XP_CMDSHELL etc.

😎

Gazareth One Orange Chip Points: 27737 More actions · Answer 2

Is doing tasks in serial much of a problem here? Backups are only usually limited by disk throughput.

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 3

Second thought:w00t:

why not backup straight to the network drive? No need to write the backup locally!

😎

eccentricDBA SSCarpal Tunnel Points: 4844 More actions · Answer 4

Just a suggestion. When I had done something like this in the past an Poor Mans Log Shipping process in SQL 2000. I had a schedule task that I ran on a remote server where I would ROBOCOPY the backups to that remote server to keep the directory in sync every hour.

ROBOCOPY

http://technet.microsoft.com/en-us/library/cc733145.aspx

Robocopy and a Few Examples

http://social.technet.microsoft.com/wiki/contents/articles/1073.robocopy-and-a-few-examples.aspx

jfogel SSCarpal Tunnel Points: 4155 More actions · Answer 5

Put it in a stored procedure and wrap the groups in a try/catch. Log each group and send an email upon failure.

Cheers

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

Eirikur Eiriksson (6/12/2014)
Quick thought, wouldn't it be simpler to have an SSIS package for shuffling the backup files around, no need for XP_CMDSHELL etc.
😎

Since you brought it up, lets see the package that you envision for this. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

Jake Shelton (6/12/2014)
Hi all,
I'm looking for the most time-efficient way to backup db's to a local drive and then move the .bak files across to a new server, as well as batch separation, and ideally ensure that any failure to backup to/move a particular .bak file does not impede others, or is at least flagged up.
So far, I'm thinking thus...
Backup Database AfterSalesTo Disk = 'D:\ AfterSales.bak'
Exec XP_CMDSHELL 'Move "D:\ AfterSales.bak' \\UKSLO1SQL05.FRENCHUK.LOCAL\Restore'
Go
Backup Database BatchControlTo Disk = 'D:\ (add path here)\ BatchControl.bak'With Replace
Exec XP_CMDSHELL 'Move "D:\BatchControl.bak' \\UKSLO1SQL05.FRENCH.LOCAL\Restore'
Go
Backup Database Cleverly Etc...
Go
Unfortunately, this still means that SQL will proceed with it's tasks in serial fashion - is there a more time-efficient way/sequence that I can force it to proceed?
I don't have any 3rd party software onsite to back up with.
Thanks,
Jake.

I strongly recommend a COPY rather than a MOVE. If you move the files, non of your SQL Server GUI stuff will be able to find any of your backup files.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jfogel SSCarpal Tunnel Points: 4155 More actions · Answer 8

Jeff Moden (6/13/2014)
Eirikur Eiriksson (6/12/2014)
Quick thought, wouldn't it be simpler to have an SSIS package for shuffling the backup files around, no need for XP_CMDSHELL etc.
😎
Since you brought it up, lets see the package that you envision for this. 😉

I really appreciate your support of the "shell". Sometimes you just have to cut the crap, and it solves the problem.

Cheers

ScottPletcher SSC Guru Points: 101120 More actions · Answer 9

Jeff Moden (6/13/2014)
Jake Shelton (6/12/2014)
Hi all,
I'm looking for the most time-efficient way to backup db's to a local drive and then move the .bak files across to a new server, as well as batch separation, and ideally ensure that any failure to backup to/move a particular .bak file does not impede others, or is at least flagged up.
So far, I'm thinking thus...
Backup Database AfterSalesTo Disk = 'D:\ AfterSales.bak'
Exec XP_CMDSHELL 'Move "D:\ AfterSales.bak' \\UKSLO1SQL05.FRENCHUK.LOCAL\Restore'
Go
Backup Database BatchControlTo Disk = 'D:\ (add path here)\ BatchControl.bak'With Replace
Exec XP_CMDSHELL 'Move "D:\BatchControl.bak' \\UKSLO1SQL05.FRENCH.LOCAL\Restore'
Go
Backup Database Cleverly Etc...
Go
Unfortunately, this still means that SQL will proceed with it's tasks in serial fashion - is there a more time-efficient way/sequence that I can force it to proceed?
I don't have any 3rd party software onsite to back up with.
Thanks,
Jake.
I strongly recommend a COPY rather than a MOVE. If you move the files, non of your SQL Server GUI stuff will be able to find any of your backup files.

I strongly recommend you never use the SQL GUI to do restores/recoveries. In that sense, moving the files is actually advantageous, since it forces you to script your SQL restores/recoveries.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 10

Jeff Moden (6/13/2014)
Eirikur Eiriksson (6/12/2014)
Quick thought, wouldn't it be simpler to have an SSIS package for shuffling the backup files around, no need for XP_CMDSHELL etc.
😎
Since you brought it up, lets see the package that you envision for this. 😉

Elementarty:-)

Since SSIS packages can be executed from T-SQL, one can replace the xp_cmdchell. The package itself would be simple, Foreach Task loops through the source file location, FileSystemTask then copies the file across to the configured destination and in the end if everything is successful, deletes the original file. For belt and braces one can add logging, alerting, alternative destinations etc.

😎