If you are not using any backup software, you may not be able to back up large databases to a single disk. In such a case, you can split the backup with the following script.
The script takes the backup to 20 different disks in 20 parts, and then verify the backup using these files.
“M” is the name of our mounted drive. “Disk01, Disk02” shows the mounted disks. You can find detailed information about Mounted Drive in my article “What is Mounted Drive and How to Create It“.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 |
DECLARE @date_for_file as varchar(10); DECLARE @file1 varchar(50); DECLARE @file2 varchar(50); DECLARE @file3 varchar(50); DECLARE @file4 varchar(50); DECLARE @file5 varchar(50); DECLARE @file6 varchar(50); DECLARE @file7 varchar(50); DECLARE @file8 varchar(50); DECLARE @file9 varchar(50); DECLARE @file10 varchar(50); DECLARE @file11 varchar(50); DECLARE @file12 varchar(50); DECLARE @file13 varchar(50); DECLARE @file14 varchar(50); DECLARE @file15 varchar(50); DECLARE @file16 varchar(50); DECLARE @file17 varchar(50); DECLARE @file18 varchar(50); DECLARE @file19 varchar(50); DECLARE @file20 varchar(50); select @date_for_file= convert(varchar(10), getdate(), 120); select @file1= N'M:\Disk01\myDatabaseBackup' + @date_for_file + '_1.BAK' select @file2= N'M:\Disk02\myDatabaseBackup' + @date_for_file + '_1.BAK' select @file3= N'M:\Disk03\myDatabaseBackup' + @date_for_file + '_1.BAK' select @file4= N'M:\Disk04\myDatabaseBackup' + @date_for_file + '_1.BAK' select @file5= N'M:\Disk05\myDatabaseBackup' + @date_for_file + '_1.BAK' select @file6= N'M:\Disk06\myDatabaseBackup' + @date_for_file + '_1.BAK' select @file7= N'M:\Disk07\myDatabaseBackup' + @date_for_file + '_1.BAK' select @file8= N'M:\Disk08\myDatabaseBackup' + @date_for_file + '_1.BAK' select @file9= N'M:\Disk09\myDatabaseBackup' + @date_for_file + '_1.BAK' select @file10= N'M:\Disk10\myDatabaseBackup' + @date_for_file + '_1.BAK' select @file11= N'M:\Disk11\myDatabaseBackup' + @date_for_file + '_1.BAK' select @file12= N'M:\Disk12\myDatabaseBackup' + @date_for_file + '_1.BAK' select @file13= N'M:\Disk13\myDatabaseBackup' + @date_for_file + '_1.BAK' select @file14= N'M:\Disk14\myDatabaseBackup' + @date_for_file + '_1.BAK' select @file15= N'M:\Disk15\myDatabaseBackup' + @date_for_file + '_1.BAK' select @file16= N'M:\Disk16\myDatabaseBackup' + @date_for_file + '_1.BAK' select @file17= N'M:\Disk17\myDatabaseBackup' + @date_for_file + '_1.BAK' select @file18= N'M:\Disk18\myDatabaseBackup' + @date_for_file + '_1.BAK' select @file19= N'M:\Disk19\myDatabaseBackup' + @date_for_file + '_1.BAK' select @file20= N'M:\Disk20\myDatabaseBackup' + @date_for_file + '_1.BAK' BACKUP DATABASE [mydatabase] TO DISK = @file1, DISK = @file2, DISK = @file3, DISK = @file4, DISK = @file5, DISK = @file6, DISK = @file7, DISK = @file8, DISK = @file9, DISK = @file10, DISK = @file11, DISK = @file12, DISK = @file13, DISK = @file14, DISK = @file15, DISK = @file16, DISK = @file17, DISK = @file18, DISK = @file19, DISK = @file20 WITH NOFORMAT, NOINIT, NAME = N'mydatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 1; declare @backupSetId as int select @backupSetId = position from msdb..backupset where database_name=N'mydatabase' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'mydatabase' ) if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''mydatabase'' not found.', 16, 1) end RESTORE VERIFYONLY FROM DISK = @file1, DISK = @file2, DISK = @file3, DISK = @file4, DISK = @file5, DISK = @file6, DISK = @file7, DISK = @file8, DISK = @file9, DISK = @file10, DISK = @file11, DISK = @file12, DISK = @file13, DISK = @file14, DISK = @file15, DISK = @file16, DISK = @file17, DISK = @file18, DISK = @file19, DISK = @file20 WITH FILE = @backupSetId, NOUNLOAD, NOREWIND GO |
