Tuesday, March 12, 2013

Script for creating a folder & taking a backup up in that folder


declare @name varchar(500)
declare @q nvarchar(MAX)
declare @foldername varchar(500)
set @foldername = 'D:\db backup\dbbackup_' + CONVERT(varchar,getdate(),110) + ''
--EXEC master.sys.xp_dirtree @DataPath
EXEC master.dbo.xp_create_subdir @foldername

set @name = @foldername + '\next_' + CONVERT(varchar,getdate(),110) + '.bak'
set @q = ' BACKUP DATABASE [Next] TO  DISK = N''' + @name + ''' WITH NOFORMAT, NOINIT,
 NAME = N''Next-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
print @q
exec sp_executesql @q

set @name = @foldername + '\Mirror_test_' + CONVERT(varchar,getdate(),110) + '.bak'
set @q = ' BACKUP DATABASE [Mirror_test] TO  DISK = N''' + @name + ''' WITH NOFORMAT, NOINIT,
 NAME = N''Mirror_test-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
print @q
exec sp_executesql @q