Schedule backup/ copy backup and rename to other folder

Question

Post reply

Schedule backup/ copy backup and rename to other folder

duncan.turner.2dg

SSC Rookie

Points: 42
More actions
December 29, 2014 at 12:50 am

#296255

I'm looking to schedule a maintenance plan for my databases which I have done.
I'd like this database to be copied to another folder and the name altered to include the file name and the current date time stamp.
Is this possible in the scheduled maintenance plan?
Many thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

Shafat Husain SSCrazy Points: 2754 More actions · Answer 1

Hi,

For Scheduling backups to one or more then one designated folder you can use ola hallengren backup script.

https://ola.hallengren.com/sql-server-backup.html

If you want to move the backed up database to a new folder then i would suggest creating a batch file and schedule it in windows jobs.

Hope it Helps..!!

Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)

duncan.turner.2dg SSC Rookie Points: 42 More actions · Answer 2

Hi there,

Thanks for that... I was thinking that I could write the copying and file rewrite in TSQL, keeping it all clean and together.

But if that is the preferred option I'll go with that..

Steve Jones - SSC Editor SSC Guru Points: 736254 More actions · Answer 3

File operations are cumbersome in T-SQL. There aren't really extensions to do it well. Powershell works really well, and you can schedule these in SQL Agent.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

Heh... call a DOS command through xp_CmdShell and be done with it. If that makes you too nervous, create a job with an Command Task. If you really think you need to use PowerShell for something like this, call it the same way.

--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)