SQL Server-filegroups, files, backups, partitions

FileGroup: Filegroups, which are logically divided; files: A file, for the actual file, you need to specify which filegroup the file belongs to.

A bit of using multiple files: You can distribute the disk I/O pressure and provide backup by file and filegroup (the recovery mode for which you need to set up data by file and filegroup).

--Creating Filegroups Use [Master]GOALTER DATABASE [dwh_optimizing] ADDFILEGROUP[dwh_optimizing_1]GOALTER DATABASE [dwh_optimizing] ADDFILEGROUP[dwh_optimizing_2]GOALTER DATABASE [dwh_optimizing] ADDFILEGROUP[Dwh_optimizing_3]GOALTER DATABASE [dwh_optimizing] ADDFILEGROUP[Dwh_optimizing_4]GO--Create a file Use [Master]GOALTER DATABASE [dwh_optimizing] ADD FILE(NAME=N'Dwh_optimizing_11', FILENAME=N'E:\MSSQL11. BIS01\MSSQL\DATA\DWH_OPTIMIZING_11.NDF', SIZE=512000KB, FileGrowth=  -%) toFILEGROUP[dwh_optimizing_1]GOALTER DATABASE [dwh_optimizing] ADD FILE(NAME=N'Dwh_optimizing_12', FILENAME=N'E:\MSSQL11. BIS01\MSSQL\DATA\DWH_OPTIMIZING_12.NDF', SIZE=512000KB, FileGrowth=  -%) toFILEGROUP[dwh_optimizing_1]GOALTER DATABASE [dwh_optimizing] ADD FILE(NAME=N'dwh_optimizing_21', FILENAME=N'E:\MSSQL11. BIS01\MSSQL\DATA\DWH_OPTIMIZING_21.NDF', SIZE=512000KB, FileGrowth=  -%) toFILEGROUP[dwh_optimizing_2]GOALTER DATABASE [dwh_optimizing] ADD FILE(NAME=N'dwh_optimizing_22', FILENAME=N'E:\MSSQL11. BIS01\MSSQL\DATA\DWH_OPTIMIZING_22.NDF', SIZE=512000KB, FileGrowth=  -%) toFILEGROUP[dwh_optimizing_2]GOALTER DATABASE [dwh_optimizing] ADD FILE(NAME=N'dwh_optimizing_31', FILENAME=N'E:\MSSQL11. BIS01\MSSQL\DATA\DWH_OPTIMIZING_31.NDF', SIZE=512000KB, FileGrowth=  -%) toFILEGROUP[Dwh_optimizing_3]GOALTER DATABASE [dwh_optimizing] ADD FILE(NAME=N'dwh_optimizing_32', FILENAME=N'E:\MSSQL11. BIS01\MSSQL\DATA\DWH_OPTIMIZING_32.NDF', SIZE=512000KB, FileGrowth=  -%) toFILEGROUP[Dwh_optimizing_3]GOALTER DATABASE [dwh_optimizing] ADD FILE(NAME=N'dwh_optimizing_41', FILENAME=N'E:\MSSQL11. BIS01\MSSQL\DATA\DWH_OPTIMIZING_41.NDF', SIZE=512000KB, FileGrowth=  -%) toFILEGROUP[Dwh_optimizing_4]GOALTER DATABASE [dwh_optimizing] ADD FILE(NAME=N'dwh_optimizing_42', FILENAME=N'E:\MSSQL11. BIS01\MSSQL\DATA\DWH_OPTIMIZING_42.NDF', SIZE=512000KB, FileGrowth=  -%) toFILEGROUP[Dwh_optimizing_4]GOALTER DATABASE [dwh_optimizing]MODIFYFILE(NAME=N'Dwh_optimizing_log', MAXSIZE=UNLIMITED)GO

Backup: Full backup, differential backup, files, filegroups, and log backups (need to set the recovery mode for the data to complete)

--File Group BackupBACKUP DATABASE [dwh_optimizing]FILEGROUP=N'dwh_optimizing_1'  to  DISK =N'F:\MSSQL11. Bis01\mssql\backup\dwh_optimizing.bak'  withNoformat, Noinit, NAME=N'dwh_optimizing-full Filegroup Backup', SKIP, Norewind, Nounload, STATS= TenGO--file BackupBACKUP DATABASE [dwh_optimizing] FILE =N'Dwh_optimizing_11'  to  DISK =N'F:\MSSQL11. Bis01\mssql\backup\dwh_optimizing.bak'  withNoformat, Noinit, NAME=N'dwh_optimizing-full File Backup', SKIP, Norewind, Nounload, STATS= TenGO--Full BackupBACKUP DATABASE [dwh_optimizing]  to  DISK =N'F:\MSSQL11. Bis01\mssql\backup\dwh_optimizing.bak'  withNoformat, Noinit, NAME=N'dwh_optimizing-full Database Backup', SKIP, Norewind, Nounload, STATS= TenGO--Differential BackupBACKUP DATABASE [dwh_optimizing]  to  DISK =N'F:\MSSQL11. Bis01\mssql\backup\dwh_optimizing.bak'  withDifferential, Noformat, Noinit, NAME=N'dwh_optimizing-differential Database Backup', SKIP, Norewind, Nounload, STATS= TenGO--Thing log backupBACKUP LOG [dwh_optimizing]  to  DISK =N'F:\MSSQL11. Bis01\mssql\backup\dwh_optimizing.bak'  withNoformat, Noinit, NAME=N'dwh_optimizing-transaction Log Backup', SKIP, Norewind, Nounload, STATS= TenGO

partition, you can partition the table according to a field

--Create a partition functionCREATEPARTITIONFUNCTION [Pf_bicompany](int) asRANGE Right  for VALUES(2,3,4,5)GO--creating a partition schemaCREATEPARTITION SCHEME[Ps_bicompany]  asPARTITION[Pf_bicompany]  to([dwh_optimizing_1],[dwh_optimizing_2],[Dwh_optimizing_3],[Dwh_optimizing_4],[PRIMARY])GO

SQL Server-filegroups, files, backups, partitions