How take backup without overwriting or Appending the old files

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Backups » How take backup without overwriting or...

How take backup without overwriting or Appending the old files

Rate Topic

Display Mode

Topic Options

Author

Message

guruprasadurs

Posted Monday, January 09, 2012 6:57 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 20, 2013 6:14 AM
Points: 24, Visits: 66

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

Post #1232390

Dev

Posted Monday, January 09, 2012 7:04 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:05 PM
Points: 2,013, Visits: 1,566

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.

~Dev~

Post #1232395

John Mitchell-245523

Posted Monday, January 09, 2012 7:19 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 7:03 AM
Points: 4,443, Visits: 7,249

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

Post #1232404

padhis

Posted Monday, January 09, 2012 7:28 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, February 01, 2012 3:57 AM
Points: 91, Visits: 125

This may help you.

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

Post #1232410

Dev

Posted Monday, January 09, 2012 7:35 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:05 PM
Points: 2,013, Visits: 1,566

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.

~Dev~

Post #1232415

robgilbie

Posted Thursday, January 12, 2012 4:54 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, October 30, 2012 2:45 PM
Points: 2, Visits: 16

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

Post #1234665

Mike Hinds

Posted Friday, January 13, 2012 5:28 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 533, Visits: 788

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

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

Regards, Mike

Post #1235540

Divine Flame

Posted Friday, January 13, 2012 6:37 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 1,084, Visits: 1,954

guruprasadurs (1/9/2012)

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

- Divine Flame

Post #1235575

robgilbie

Posted Friday, January 13, 2012 7:58 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, October 30, 2012 2:45 PM
Points: 2, Visits: 16

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

Post #1235660

guruprasadurs

Posted Friday, January 13, 2012 10:33 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 20, 2013 6:14 AM
Points: 24, Visits: 66

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

Post #1236088

« Prev Topic | Next Topic »

Permissions