The blog can give you good understanding on "MSSQLServer stripped backup".
Why anyone wants to go to stripped backups?
The answer is that when you have your drives on SAN and you want to use the I\O
from each more than one SAN while taking the backup then this is a good option to go for the stripped backups. Another point is that in some scenario the free space in one drive is not enough to accomodate your complete backup file.
After you are clear on why you decide stripping the backup file, lets talk on how to stripe the backupfile.
Command to create the stripped backups
BACKUP TO
DISK = 'E:\MSSQL\BAK\Tuitionaffordable_1of2.BAK'
,DISK = 'J:\MSSQL\BAK\Tuitionaffordable_2of2.BAK'
WITH INIT
Let's go ahead and restore the command
RESTORE DATABASE Tuitionaffordable
WITH RECOVERY, REPLACE
FROM DISK= 'K:\MSSQL\Test\Tuitionaffordable_1of4.BAK'
,DISK = 'K:\MSSQL\Test\Tuitionaffordable_2of4.BAK'
,DISK= 'K:\MSSQL\Test\Tuitionaffordable_3of4.BAK'
,DISK= 'K:\MSSQL\Test\Tuitionaffordable_4of4.BAK'
There is one more important thing. In case your destination server doesn't have enouth free space in the corresponding drives then above command will fail. Backup file has the information where it needs to create the mdf, ndf and ldf files. Let's take a scenario when you are going to MOVE all the files to only one drive eg K because you have enough free space only in K drive of your destination server. The command will be as follows:
RESTORE DATABASE Tuitionaffordable
FROM DISK= 'K:\MSSQL\Test\Tuitionaffordable_1of4.BAK',
DISK= 'K:\MSSQL\Test\Tuitionaffordable_2of4.BAK',
DISK= 'K:\MSSQL\Test\Tuitionaffordable_3of4.BAK',
DISK= 'K:\MSSQL\Test\Tuitionaffordable_4of4.BAK',
WITH RECOVERY, REPLACE
MOVE 'Tuitionaffordable' TO 'K:\MSSQL\Test\Tuitionaffordable.MDF',
MOVE 'Tuitionaffordable_Data2' TO 'K:\MSSQL\Test\Tuitionaffordable_Data2.NDF',
MOVE 'Tuitionaffordable_Data3' TO 'K:\MSSQL\Test\Tuitionaffordable_Data3.NDF',
MOVE 'Tuitionaffordable_Data4' TO 'K:\MSSQL\Test\Tuitionaffordable_Data4.NDF',
MOVE 'Tuitionaffordable_Data5' TO 'K:\MSSQL\Test\Tuitionaffordable_Data5.NDF',
MOVE 'Tuitionaffordable_Data6' TO 'K:\MSSQL\Test\Tuitionaffordable_Data6.NDF',
MOVE 'Tuitionaffordable_Data7' TO 'K:\MSSQL\Test\Tuitionaffordable_Data7.NDF',
MOVE 'Tuitionaffordable_Data8' TO 'K:\MSSQL\Test\Tuitionaffordable_Data8.NDF',
MOVE 'Tuitionaffordable_Data9' TO 'K:\MSSQL\Test\Tuitionaffordable_Data9.NDF',
MOVE 'Tuitionaffordable_Index' TO 'K:\MSSQL\Test\Tuitionaffordable_Index.NDF',
MOVE 'Tuitionaffordable_Index2' TO 'K:\MSSQL\Test\Tuitionaffordable_Index2.NDF',
MOVE 'Tuitionaffordable_Log' TO 'K:\MSSQL\Test\Tuitionaffordable_Log.lDF'
Why anyone wants to go to stripped backups?
The answer is that when you have your drives on SAN and you want to use the I\O
from each more than one SAN while taking the backup then this is a good option to go for the stripped backups. Another point is that in some scenario the free space in one drive is not enough to accomodate your complete backup file.
After you are clear on why you decide stripping the backup file, lets talk on how to stripe the backupfile.
Command to create the stripped backups
BACKUP TO
DISK = 'E:\MSSQL\BAK\Tuitionaffordable_1of2.BAK'
,DISK = 'J:\MSSQL\BAK\Tuitionaffordable_2of2.BAK'
WITH INIT
Let's go ahead and restore the command
RESTORE DATABASE Tuitionaffordable
WITH RECOVERY, REPLACE
FROM DISK= 'K:\MSSQL\Test\Tuitionaffordable_1of4.BAK'
,DISK = 'K:\MSSQL\Test\Tuitionaffordable_2of4.BAK'
,DISK= 'K:\MSSQL\Test\Tuitionaffordable_3of4.BAK'
,DISK= 'K:\MSSQL\Test\Tuitionaffordable_4of4.BAK'
There is one more important thing. In case your destination server doesn't have enouth free space in the corresponding drives then above command will fail. Backup file has the information where it needs to create the mdf, ndf and ldf files. Let's take a scenario when you are going to MOVE all the files to only one drive eg K because you have enough free space only in K drive of your destination server. The command will be as follows:
RESTORE DATABASE Tuitionaffordable
FROM DISK= 'K:\MSSQL\Test\Tuitionaffordable_1of4.BAK',
DISK= 'K:\MSSQL\Test\Tuitionaffordable_2of4.BAK',
DISK= 'K:\MSSQL\Test\Tuitionaffordable_3of4.BAK',
DISK= 'K:\MSSQL\Test\Tuitionaffordable_4of4.BAK',
WITH RECOVERY, REPLACE
MOVE 'Tuitionaffordable' TO 'K:\MSSQL\Test\Tuitionaffordable.MDF',
MOVE 'Tuitionaffordable_Data2' TO 'K:\MSSQL\Test\Tuitionaffordable_Data2.NDF',
MOVE 'Tuitionaffordable_Data3' TO 'K:\MSSQL\Test\Tuitionaffordable_Data3.NDF',
MOVE 'Tuitionaffordable_Data4' TO 'K:\MSSQL\Test\Tuitionaffordable_Data4.NDF',
MOVE 'Tuitionaffordable_Data5' TO 'K:\MSSQL\Test\Tuitionaffordable_Data5.NDF',
MOVE 'Tuitionaffordable_Data6' TO 'K:\MSSQL\Test\Tuitionaffordable_Data6.NDF',
MOVE 'Tuitionaffordable_Data7' TO 'K:\MSSQL\Test\Tuitionaffordable_Data7.NDF',
MOVE 'Tuitionaffordable_Data8' TO 'K:\MSSQL\Test\Tuitionaffordable_Data8.NDF',
MOVE 'Tuitionaffordable_Data9' TO 'K:\MSSQL\Test\Tuitionaffordable_Data9.NDF',
MOVE 'Tuitionaffordable_Index' TO 'K:\MSSQL\Test\Tuitionaffordable_Index.NDF',
MOVE 'Tuitionaffordable_Index2' TO 'K:\MSSQL\Test\Tuitionaffordable_Index2.NDF',
MOVE 'Tuitionaffordable_Log' TO 'K:\MSSQL\Test\Tuitionaffordable_Log.lDF'
No comments:
Post a Comment