January 20, 2011 at 8:12 am
I would like to backup sql databases to a local drive and then copy all of them to netwrok drive and finally after successfull copy of the file i would like to delete on the local drive.
Is there a sql script out there to do this process and making sure file is copied.
January 20, 2011 at 8:28 am
Personally, I wouldn't run it as a script. I'd use SQL Agent and a SQL Agent job with steps. It'll be much more functional, better error trapping, etc. If not that, I'd look at using PowerShell. Again, better error trapping, etc., than trying to do this through TSQL.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 20, 2011 at 3:24 pm
Use SQL Agent job as Grant advised wherein each of the tasks you outlined is a step in the job. I have something similar in place and below are what my job steps include:
step (1) integrity Check (t-sql) => on success move to step(2); on failure quit
step (2) local database backup (t-sql) => on success move to step(3); on failure quit
step (3) local backup network transfer (sproc leveraging xp_cmdshell) => on success move to step(4); on failure quit
step (4) network cleanup (backups older than x number of days - I'm actually using ActiveX to accomplish this step) => on success move to step(5); on failure quit
step (5) delete local backup => on success report success, on failure report failure
The job steps will allow you to control on-success and on-failure actions which meets your requirement for never getting to "delete local backup" if network transfer fails.
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply