September 23, 2019 at 2:52 am
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)
September 23, 2019 at 2:49 pm
-- 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
September 23, 2019 at 3:13 pm
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
October 1, 2019 at 1:45 pm
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)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy