How to Datewise backup

• prash7shinde

  SSC Rookie

  Points: 33

  More actions

  May 12, 2011 at 6:56 am

  #250168

  i m using SQL 2008 and i just want to know ,,How i will take a database backup datewise in a same folder.

  I did using job scheduling but database file .bak is append or overwrite..

  And i want database should be backup in a single folder with the name and date...

  If some one knw ..pls help me

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  May 12, 2011 at 7:02 am

  #1323628

  I generally use an SSIS package with the following to generate the back-up queries: -

  SELECT

  'BACKUP DATABASE [' + name + '] TO [' + name + '] WITH ' + CASE WHEN Datepart(dw, Getdate()) <> 2

  THEN 'DIFFERENTIAL,'

  ELSE '' END + ' NOFORMAT, NOINIT, NAME = N''' +

  name + '-' + CASE WHEN Datepart(dw, Getdate()) <> 2

  THEN 'Differential'

  ELSE 'Full' END + ' Database Backup' + CONVERT(VARCHAR(12), Getdate(), 102) + ''', SKIP, NOREWIND, NOUNLOAD, STATS = 10'

  FROM MASTER..sysdatabases

  Then a ForEach loop that executes each query.

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• AverageDBA

  Grasshopper

  Points: 24

  More actions

  May 12, 2011 at 7:32 am

  #1323653

  Really recommend Ola Hallengren scripts here, they cover pretty much anything you could want.