Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How take backup without overwriting or Appending the old files Expand / Collapse
Author
Message
Posted Monday, January 9, 2012 6:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Monday, January 9, 2012 7:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 5:56 AM
Points: 2,013, Visits: 1,584
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

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T) | Open Network for Data Professionals...
LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1232395
Posted Monday, January 9, 2012 7:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:28 AM
Points: 5,237, Visits: 9,489
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
Posted Monday, January 9, 2012 7:28 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, February 1, 2012 3:57 AM
Points: 91, Visits: 125
This may help you.

http://www.sqlservercentral.com/articles/Backup+%2F+Restore/72234/
Post #1232410
Posted Monday, January 9, 2012 7:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 5:56 AM
Points: 2,013, Visits: 1,584


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


Dev

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T) | Open Network for Data Professionals...
LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1232415
Posted Thursday, January 12, 2012 4:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, January 13, 2012 5:28 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 608, Visits: 861
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
Posted Friday, January 13, 2012 6:37 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:17 AM
Points: 1,312, Visits: 2,492
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
Post #1235575
Posted Friday, January 13, 2012 7:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, January 13, 2012 10:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 »

Add to briefcase

Permissions Expand / Collapse