Using
below script you can back up all databases on your SQL Server. Using this you
can backup databases in the multiple disk drives in a compress mode.
If
you need please change the script according to your requirement.
First
create a Backup folder on disk drives.
DECLARE @FileName01 AS VARCHAR(200) -- Filename for backup 1
DECLARE @FileName02 AS VARCHAR(200) -- Filename for backup 2
DECLARE @FileName03 AS VARCHAR(200) -- Filename for backup 3
DECLARE @FileName04 AS VARCHAR(200) -- Filename for backup 4
DECLARE @FileDate AS VARCHAR(10) -- Used for file name
(Backup date)
DECLARE @DBName AS
VARCHAR(100) -- Database name
DECLARE @Path01 AS
VARCHAR(200) -- Path for backup files
1
DECLARE @Path02 AS
VARCHAR(200) -- Path for backup files 2
DECLARE @Path03 AS
VARCHAR(200) -- Path for backup files 3
DECLARE @Path04 AS
VARCHAR(200) -- Path for backup files 4
DECLARE @BKPName AS
VARCHAR(200) -- Backup name
DECLARE @ErrorMsg AS VARCHAR(200)
Set @Path01 ='G:\Backup\'
Set @Path02 ='H:\Backup\'
Set @Path03 ='M:\Backup\'
Set @Path04 ='I:\Backup\'
DECLARE db_Cursor CURSOR FOR
Select
name FROM master.dbo.sysdatabases
WHERE
name NOT IN ('tempdb') ORDER BY name -- Exclude Tempdb databases
OPEN db_Cursor
FETCH NEXT
FROM db_Cursor INTO
@DBName
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @FileDate =CONVERT(VARCHAR(10),GETDATE()-1,112)
SET @FileName01 =@Path01+@DBName+'_01_'+@FileDate+'.BAK'
SET @FileName02 =@Path02+@DBName+'_02_'+@FileDate+'.BAK'
SET @FileName03 =@Path03+@DBName+'_03_'+@FileDate+'.BAK'
SET @FileName04 =@Path04+@DBName+'_04_'+@FileDate+'.BAK'
SET @BKPName =@DBName
+ '-Full Database
Backup'
/* Backup */
BACKUP DATABASE
@DBName TO
DISK =
@FileName01,
DISK = @FileName02,
DISK = @FileName03,
DISK = @FileName04 WITH NOFORMAT, INIT,
NAME
= @BKPName, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
/* Verify Backup */
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
)
SET @ErrorMsg = 'Verify failed. Backup information for database '
+@DBName+' not found.'
if @backupSetId is null begin raiserror(@Error, 16, 1) end
print '****************** '+ @DBName +' Verify Backup
******************'
RESTORE VERIFYONLY FROM DISK = @FileName01, DISK = @FileName02, DISK = @FileName03, DISK =@FileName04 WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
print '******************************************************************'
FETCH NEXT FROM db_Cursor INTO
@DBName
END
CLOSE db_Cursor
DEALLOCATE db_Cursor
And
also you can use Maintenance Plan, it will
create the script and job for you.