Automate script for SQL Server Database backup

• VSSGeorge

  SSCrazy Eights

  Points: 8146

  More actions

  September 23, 2019 at 2:52 am

  #3682369

  I am writing a script for automating database backup, which is not working properly. Please help me fix this.

  DECLARE @dateString CHAR(12), @dayStr CHAR(2), @monthStr CHAR(2), @hourStr CHAR(2), @minStr CHAR(2)

  --month variable

  IF (SELECT LEN(CAST(MONTH(GETDATE()) AS CHAR(2))))=2

  SET @monthSTR=CAST(MONTH(GETDATE()) AS CHAR(2))

  ELSE

  SET @monthSTR= '0' + CAST(MONTH(GETDATE()) AS CHAR(2))

  --day variable

  IF (SELECT LEN(CAST(DAY(GETDATE()) AS CHAR(2))))=2

  SET @daySTR=CAST(DAY(GETDATE()) AS CHAR(2))

  ELSE

  SET @daySTR='0' + CAST(DAY(GETDATE()) AS CHAR(2))

  --hour variable

  IF (SELECT LEN(DATEPART(hh, GETDATE())))=2

  SET @hourStr=CAST(DATEPART(hh, GETDATE()) AS CHAR(2))

  ELSE

  SET @hourStr= '0' + CAST(DATEPART(hh, GETDATE()) AS CHAR(2))

  --minute variable

  IF (SELECT LEN(DATEPART(mi, GETDATE())))=2

  SET @minStr=CAST(DATEPART(mi, GETDATE()) AS CHAR(2))

  ELSE

  SET @minStr= '0' + CAST(DATEPART(mi, GETDATE()) AS CHAR(2))

  --name variable based on time stamp

  SET @dateString=CAST(YEAR(GETDATE()) AS CHAR(4)) + @monthStr + @dayStr + @hourStr + @minStr

  --=================================================================

  DECLARE @IDENT INT, @sql VARCHAR(1000), @DBNAME VARCHAR(200) = 'My SQLDB'

  SELECT @SQL = 'BACKUP DATABASE '+@DBNAME+' TO DISK = ''D:\SQL_Backup_RegTrac_Dbs'+@DBNAME+'db' + @dateString +'.BAK'' '

  EXEC (@SQL)

• Eddie Wuerch

  SSChampion

  Points: 12532

  More actions

  September 23, 2019 at 2:49 pm

  #3682570

  -- The database name 'My SQLDB' has a space in it, so you must wrap the name in [brackets]
  SELECT @SQL = 'BACKUP DATABASE ['+@DBNAME+'] TO DISK = ''D:\SQL_Backup_RegTrac_Dbs'+@DBNAME+'db' + @dateString +'.BAK'' '

  Eddie Wuerch
  MCM: SQL

• Steve Jones - SSC Editor

  SSC Guru

  Points: 734552

  More actions

  September 23, 2019 at 3:13 pm

  #3682586

  Honestly, while this is a good exercise, there is a script that does this very well and has been thoroughly tested.  I would recommend: https://www.sqlservercentral.com/forums/topic/recommended-sql-backup-software-2/#post-3681962

• paulo.margarido

  Ten Centuries

  Points: 1245

  More actions

  October 1, 2019 at 1:45 pm

  #3684995

  why dont you just do

  DECLARE @IDENT INT, @sql VARCHAR(1000), @DBNAME VARCHAR(200) = 'My SQLDB'
  
  SELECT @SQL = 'BACKUP DATABASE '+@DBNAME+' TO DISK = ''D:\SQL_Backup_RegTrac_Dbs'+@DBNAME+'db' 
  + CONVERT(VARCHAR(12), GETDATE(), 112) + REPLACE(CONVERT(VARCHAR(5), GETDATE(), 114), ':','') 
  +'.BAK'' '
  
  EXEC (@SQL)

  Date and Time styles @https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017

   

  and by the way ... when you do this kind of stuff

  IF (SELECT LEN(CAST(MONTH(GETDATE()) AS CHAR(2))))=2
  
  SET @monthSTR=CAST(MONTH(GETDATE()) AS CHAR(2))
  
  ELSE
  
  SET @monthSTR= '0' + CAST(MONTH(GETDATE()) AS CHAR(2))

  you can simply do just this

  SET @monthSTR = RIGHT('00' + CAST(MONTH(GETDATE()) AS CHAR(2)), 2)