Automte script for Database backup

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)

Why recreate the wheel? Use the maintenance feature of SQL server that comes out of the box

I am using SSMS 2017. Please explain a bit on the maintenance feature of SQL server whether it will automate taking backups.

Please see if the link helps
:slight_smile:

https://docs.microsoft.com/en-us/sql/relational-databases/maintenance-plans/use-the-maintenance-plan-wizard

1 Like

or

https://ola.hallengren.com/