Auto Backup Script

Question

Post reply

Auto Backup Script

ArjunaReddy

Default port

Points: 1467
More actions
July 7, 2009 at 4:56 am

#211829

Hi SQL Gurus,
I am new to SQL Server and i am trying to create a SQL Script which will create a directory with name todays date and will backup the test database. I am getting errors while the script starts backingup the database.
Seems there is some problem with the temporary variables (@). Please look at the below code and let me know the issue.
################################################
-- 1 - Declare variables
DECLARE @CMD1 varchar(8000)
DECLARE @RestoreRootDirectory varchar(255)
DECLARE @CurrentDate datetime
DECLARE @CurrentName varchar(8)
-- 2 - Initialize variables
SET @CMD1 = ''
SET @RestoreRootDirectory = 'D:\backups\test\'
SET @CurrentDate = GETDATE()
SELECT @CurrentName = CONVERT(varchar(8), @CurrentDate, 112)
-- 3a - Create the current directory
SELECT @CMD1 = 'EXEC master.dbo.xp_cmdshell ' + char(39) + 'mkdir ' + @RestoreRootDirectory + @CurrentName + '\' + char(39)
-- SELECT @CMD1
EXEC(@CMD1)
BACKUP DATABASE [test] TO DISK = N'@RestoreRootDirectory + @CurrentName '+dd.bak'' WITH NOFORMAT, NOINIT, NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'test' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'test' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''test'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'@RestoreRootDirectory + @CurrentName '+dd.bak'' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
################################################

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 1

Try this:-

-- 1 - Declare variables

DECLARE @CMD1 varchar(8000)

DECLARE @RestoreRootDirectory varchar(255)

DECLARE @CurrentDate datetime

DECLARE @CurrentName varchar(8)

-- 2 - Initialize variables

SET @CMD1 = ''

SET @RestoreRootDirectory = 'c:\backups\test\'

SET @CurrentDate = GETDATE()

SELECT @CurrentName = CONVERT(varchar(8), @CurrentDate, 112)

-- 3a - Create the current directory

SELECT @CMD1 = 'EXEC master.dbo.xp_cmdshell ' + char(39) + 'mkdir ' + @RestoreRootDirectory + @CurrentName + '\' + char(39)

-- SELECT @CMD1

EXEC(@CMD1)

DECLARE @FileName varchar(255)

SET @FileName = @RestoreRootDirectory + @CurrentName + '\dd.bak'

BACKUP DATABASE [test] TO DISK = @FileName WITH NOFORMAT, NOINIT, NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

ArjunaReddy Default port Points: 1467 More actions · Answer 2

Perfect Guru! It went like a rocket! Thanks a lot!!!

Thanks,

Nagarjun.

Sudeepta SSCommitted Points: 1646 More actions · Answer 3

Nice work... but what was the error you are getting initially?:-)

Thanks & Regards,
Sudeepta.
http://twitter.com/skganguly

ArjunaReddy Default port Points: 1467 More actions · Answer 4

Hey Sudeep,

The code in the red is my earlier code which was giving errors and the code in the green is the correct one. I know its a stupid doubt 🙂 but i am an oracle DBA guy not SQL Server so have to.

SET @FileName = @RestoreRootDirectory + @CurrentName + '\SFB10M01.bak'

BACKUP DATABASE [SFB10M01] TO DISK = @FileName WITH NOFORMAT, NOINIT, NAME = N'SFB10M01-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

BACKUP DATABASE [test] TO DISK = N'@RestoreRootDirectory + @CurrentName '+dd.bak'' WITH NOFORMAT, NOINIT, NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

Thanks,

Nagarjun.