How take backup without overwriting or Appending the old files

Question

How take backup without overwriting or Appending the old files

guruprasadurs

Mr or Mrs. 500

Points: 530
More actions
January 9, 2012 at 6:57 am

#254702

Hi,
I want to schedule back up DB in ms sql server 2005 using maintainence plan wizard. I want to know Is it possible to schedule backup like Test09jan2012.bak, Test10jan2012.bak, Test11jan2012.bak in backup folder. Like individula files.
Please let me know if possible.
Thnaks in Advance
Regards,
GP

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

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

Devendra Shirbad SSC-Forever Points: 42493 More actions · Answer 1

Not very sure if Maintenance Plans works that way (try it) but you can write your own scripts to achieve the same. At run time you have to add timestamp (or whatever) indicator in the backup file name.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Yes, maintenance plans will do that for you. You don't have any control, as far as I know, over the actual name of the file, but you'll get each day's backup in a separate file whose name contains the database name and a timestamp.

John

padhis SSCommitted Points: 1762 More actions · Answer 3

This may help you.

http://www.sqlservercentral.com/articles/Backup+%2F+Restore/72234/

Devendra Shirbad SSC-Forever Points: 42493 More actions · Answer 4

padhis (1/9/2012)
This may help you.
http://www.sqlservercentral.com/articles/Backup+%2F+Restore/72234/

Nice Pointer but it might not help OP because backup file won't have the indicators (prefix/suffix) he might be interested in.

robgilbie SSC Journeyman Points: 92 More actions · Answer 5

Hi as Dev indicated your best bet would be to script a backup in SQL agent using a time stamp token.

For example in 2008 to append the machine name, date and time to a backup file name the script would look like this:

backup database MSDB to disk = N'c:\sql backup\MSDB_$(ESCAPE_NONE(MACH))_$(ESCAPE_NONE(DATE))_$(ESCAPE_NONE(TIME)).BAK'

go

In 2000 to append the date to the backup file name it would be:

backup database MSDB to disk = N'c:\sql backup\MSDBE_[DATE].BAK'

go

This switches only work in the agent, if you run in a query you will just append the commands

For a full list of tokens in job steps see the following link:

http://msdn.microsoft.com/en-us/library/ms175575.aspx

Mike Hinds SSCarpal Tunnel Points: 4258 More actions · Answer 6

robgilbie (1/12/2012)

For example in 2008 to append the machine name, date and time to a backup file name the script would look like this:

backup database MSDB to disk = N'c:\sql backup\MSDB_$(ESCAPE_NONE(MACH))_$(ESCAPE_NONE(DATE))_$(ESCAPE_NONE(TIME)).BAK'

go

...

For a full list of tokens in job steps see the following link:

http://msdn.microsoft.com/en-us/library/ms175575.aspx%5B/quote%5D

Very helpful and useful. I've never seen "ESCAPE_NONE" or the other job tokens before. Thanks a bunch!

Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS

Divine Flame SSCoach Points: 15943 More actions · Answer 7

guruprasadurs (1/9/2012)
Hi,
I want to schedule back up DB in ms sql server 2005 using maintainence plan wizard. I want to know Is it possible to schedule backup like Test09jan2012.bak, Test10jan2012.bak, Test11jan2012.bak in backup folder. Like individula files.
Please let me know if possible.
Thnaks in Advance
Regards,
GP

Maintenance Plan does that only. It automatically adds the timestamp in backup file name. Also, it asks you whether you want to store the backup files in a single backup directory or you want to create new directory for each & every database backup. Choice is yours!!

Sujeet Singh

robgilbie SSC Journeyman Points: 92 More actions · Answer 8

Hi Mike

Neither had I mate till a few weeks ago, just start MCTS 🙂

Going back to the original request, as already stated you can use maintenance plans, sorry should have said,

just trying to get out of the habit of using them myself 🙂

guruprasadurs Mr or Mrs. 500 Points: 530 More actions · Answer 9

Sorry to all for late reply. Its working fine now. Thanks to all