Backup SQL Server database accross more than one file

–====================================================
–=        BACKUP database accross four files
–====================================================

declare @filedate nvarchar(100)
declare @disk1 nvarchar(100)
declare @disk2 nvarchar(100)
declare @disk3 nvarchar(100)
declare @disk4 nvarchar(100)
declare @dbname nvarchar(100)
declare @backupname nvarchar(100)
declare @backupfolder nvarchar(100)

set @dbname = ‘DBA’
set @backupfolder = ‘F:\SQLBackup\’ + @dbname + ‘\’

select @filedate = replace(replace(replace(replace(convert(nvarchar,getdate(),121),’-',’_'),’ ‘,’_'),’:',”),’.',’_')
select @backupname = @dbname + ‘_backup’ + @filedate

print @filedate
print @backupname

–select @filedate

select @disk1 = @backupfolder + @dbname + ‘_backup_File1_’ + @filedate + ‘.bak’
select @disk2 = @backupfolder + @dbname + ‘_backup_File2_’ + @filedate + ‘.bak’
select @disk3 = @backupfolder + @dbname + ‘_backup_File3_’ + @filedate + ‘.bak’
select @disk4 = @backupfolder + @dbname + ‘_backup_File4_’ + @filedate + ‘.bak’

BACKUP DATABASE @dbname
TO  DISK = @disk1,
DISK = @disk2,
DISK = @disk3,
DISK = @disk4
WITH NOFORMAT, NOINIT,  NAME = @backupname, SKIP, REWIND, NOUNLOAD,  STATS = 10

declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name= @dbname and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name= @dbname )
if @backupSetId is null begin raiserror(N’Verify failed. Backup information for database ” + @dbname + ” not found.’, 16, 1) end
RESTORE VERIFYONLY FROM DISK = @disk1,  DISK = @disk2,  DISK = @disk3 ,  DISK = @disk4 WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND

Update me when site is updated

Leave a Reply

You must be logged in to post a comment.