SQL Server - 文件组，文件，备份，分区

创建文件组 USE [master] GO ALTER DATABASE [DWH_Optimizing] ADD FILEGROUP [DWH_Optimizing_1] GO ALTER DATABASE [DWH_Optimizing] ADD FILEGROUP [DWH_Optimizing_2] GO ALTER DATABASE [DWH_Optimizing] ADD FILEGROUP [DWH_Optimizing_3] GO ALTER DATABASE [DWH_Optimizing] ADD FILEGROUP [DWH_Optimizing_4] GO --创建文件 USE [master] GO ALTER DATABASE [DWH_Optimizing] ADD FILE ( NAME = N‘DWH_Optimizing_11‘, FILENAME = N‘E:\MSSQL11.BIS01\MSSQL\DATA\DWH_Optimizing_11.ndf‘ , SIZE = 512000KB , FILEGROWTH = 50%) TO FILEGROUP [DWH_Optimizing_1] GO ALTER DATABASE [DWH_Optimizing] ADD FILE ( NAME = N‘DWH_Optimizing_12‘, FILENAME = N‘E:\MSSQL11.BIS01\MSSQL\DATA\DWH_Optimizing_12.ndf‘ , SIZE = 512000KB , FILEGROWTH = 50%) TO FILEGROUP [DWH_Optimizing_1] GO ALTER DATABASE [DWH_Optimizing] ADD FILE ( NAME = N‘DWH_Optimizing_21‘, FILENAME = N‘E:\MSSQL11.BIS01\MSSQL\DATA\DWH_Optimizing_21.ndf‘ , SIZE = 512000KB , FILEGROWTH = 50%) TO FILEGROUP [DWH_Optimizing_2] GO ALTER DATABASE [DWH_Optimizing] ADD FILE ( NAME = N‘DWH_Optimizing_22‘, FILENAME = N‘E:\MSSQL11.BIS01\MSSQL\DATA\DWH_Optimizing_22.ndf‘ , SIZE = 512000KB , FILEGROWTH = 50%) TO FILEGROUP [DWH_Optimizing_2] GO ALTER DATABASE [DWH_Optimizing] ADD FILE ( NAME = N‘DWH_Optimizing_31‘, FILENAME = N‘E:\MSSQL11.BIS01\MSSQL\DATA\DWH_Optimizing_31.ndf‘ , SIZE = 512000KB , FILEGROWTH = 50%) TO FILEGROUP [DWH_Optimizing_3] GO ALTER DATABASE [DWH_Optimizing] ADD FILE ( NAME = N‘DWH_Optimizing_32‘, FILENAME = N‘E:\MSSQL11.BIS01\MSSQL\DATA\DWH_Optimizing_32.ndf‘ , SIZE = 512000KB , FILEGROWTH = 50%) TO FILEGROUP [DWH_Optimizing_3] GO ALTER DATABASE [DWH_Optimizing] ADD FILE ( NAME = N‘DWH_Optimizing_41‘, FILENAME = N‘E:\MSSQL11.BIS01\MSSQL\DATA\DWH_Optimizing_41.ndf‘ , SIZE = 512000KB , FILEGROWTH = 50%) TO FILEGROUP [DWH_Optimizing_4] GO ALTER DATABASE [DWH_Optimizing] ADD FILE ( NAME = N‘DWH_Optimizing_42‘, FILENAME = N‘E:\MSSQL11.BIS01\MSSQL\DATA\DWH_Optimizing_42.ndf‘ , SIZE = 512000KB , FILEGROWTH = 50%) TO FILEGROUP [DWH_Optimizing_4] GO ALTER DATABASE [DWH_Optimizing] MODIFY FILE ( NAME = N‘DWH_Optimizing_log‘, MAXSIZE = UNLIMITED) GO

备份：完整备份，差异备份，文件、文件组和日志备份（需要设置数据的恢复模式为Full）

<span style="color: #008080">--</span><span style="color: #008080">文件组备份</span>
<span style="color: #0000ff">BACKUP</span> <span style="color: #0000ff">DATABASE</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">DWH_Optimizing</span><span style="color: #ff0000">]</span> FILEGROUP <span style="color: #808080">=</span> N<span style="color: #ff0000">‘</span><span style="color: #ff0000">DWH_Optimizing_1</span><span style="color: #ff0000">‘</span> <span style="color: #0000ff">TO</span>  <span style="color: #0000ff">DISK</span> <span style="color: #808080">=</span> N<span style="color: #ff0000">‘</span><span style="color: #ff0000">F:\MSSQL11.BIS01\MSSQL\Backup\DWH_Optimizing.bak</span><span style="color: #ff0000">‘</span> <span style="color: #0000ff">WITH</span> NOFORMAT, NOINIT,  NAME <span style="color: #808080">=</span> N<span style="color: #ff0000">‘</span><span style="color: #ff0000">DWH_Optimizing-Full Filegroup Backup</span><span style="color: #ff0000">‘</span>, SKIP, NOREWIND, NOUNLOAD,  STATS <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">10</span>
<span style="color: #0000ff">GO</span>
<span style="color: #008080">--</span><span style="color: #008080">文件备份</span>
<span style="color: #0000ff">BACKUP</span> <span style="color: #0000ff">DATABASE</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">DWH_Optimizing</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">FILE</span> <span style="color: #808080">=</span> N<span style="color: #ff0000">‘</span><span style="color: #ff0000">DWH_Optimizing_11</span><span style="color: #ff0000">‘</span> <span style="color: #0000ff">TO</span>  <span style="color: #0000ff">DISK</span> <span style="color: #808080">=</span> N<span style="color: #ff0000">‘</span><span style="color: #ff0000">F:\MSSQL11.BIS01\MSSQL\Backup\DWH_Optimizing.bak</span><span style="color: #ff0000">‘</span> <span style="color: #0000ff">WITH</span> NOFORMAT, NOINIT,  NAME <span style="color: #808080">=</span> N<span style="color: #ff0000">‘</span><span style="color: #ff0000">DWH_Optimizing-Full File Backup</span><span style="color: #ff0000">‘</span>, SKIP, NOREWIND, NOUNLOAD,  STATS <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">10</span>
<span style="color: #0000ff">GO</span>
<span style="color: #008080">--</span><span style="color: #008080">完整备份</span>
<span style="color: #0000ff">BACKUP</span> <span style="color: #0000ff">DATABASE</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">DWH_Optimizing</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">TO</span>  <span style="color: #0000ff">DISK</span> <span style="color: #808080">=</span> N<span style="color: #ff0000">‘</span><span style="color: #ff0000">F:\MSSQL11.BIS01\MSSQL\Backup\DWH_Optimizing.bak</span><span style="color: #ff0000">‘</span> <span style="color: #0000ff">WITH</span> NOFORMAT, NOINIT,  NAME <span style="color: #808080">=</span> N<span style="color: #ff0000">‘</span><span style="color: #ff0000">DWH_Optimizing-Full Database Backup</span><span style="color: #ff0000">‘</span>, SKIP, NOREWIND, NOUNLOAD,  STATS <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">10</span>
<span style="color: #0000ff">GO</span>
<span style="color: #008080">--</span><span style="color: #008080">差异备份</span>
<span style="color: #0000ff">BACKUP</span> <span style="color: #0000ff">DATABASE</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">DWH_Optimizing</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">TO</span>  <span style="color: #0000ff">DISK</span> <span style="color: #808080">=</span> N<span style="color: #ff0000">‘</span><span style="color: #ff0000">F:\MSSQL11.BIS01\MSSQL\Backup\DWH_Optimizing.bak</span><span style="color: #ff0000">‘</span> <span style="color: #0000ff">WITH</span>  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME <span style="color: #808080">=</span> N<span style="color: #ff0000">‘</span><span style="color: #ff0000">DWH_Optimizing-Differential Database Backup</span><span style="color: #ff0000">‘</span>, SKIP, NOREWIND, NOUNLOAD,  STATS <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">10</span>
<span style="color: #0000ff">GO</span>
<span style="color: #008080">--</span><span style="color: #008080">事物日志备份</span>
<span style="color: #0000ff">BACKUP</span> <span style="color: #ff00ff">LOG</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">DWH_Optimizing</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">TO</span>  <span style="color: #0000ff">DISK</span> <span style="color: #808080">=</span> N<span style="color: #ff0000">‘</span><span style="color: #ff0000">F:\MSSQL11.BIS01\MSSQL\Backup\DWH_Optimizing.bak</span><span style="color: #ff0000">‘</span> <span style="color: #0000ff">WITH</span> NOFORMAT, NOINIT,  NAME <span style="color: #808080">=</span> N<span style="color: #ff0000">‘</span><span style="color: #ff0000">DWH_Optimizing-Transaction Log  Backup</span><span style="color: #ff0000">‘</span>, SKIP, NOREWIND, NOUNLOAD,  STATS <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">10</span>
<span style="color: #0000ff">GO</span>

分区，可以将表按照某个字段进行分区

<span style="color: #008080">--</span><span style="color: #008080">创建分区函数</span>
<span style="color: #0000ff">CREATE</span> PARTITION <span style="color: #0000ff">FUNCTION</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">PF_BICompany</span><span style="color: #ff0000">]</span>(<span style="color: #0000ff">int</span>) <span style="color: #0000ff">AS</span> RANGE <span style="color: #808080">RIGHT</span> <span style="color: #0000ff">FOR</span> <span style="color: #0000ff">VALUES</span> (<span style="color: #800000; font-weight: bold">2</span>, <span style="color: #800000; font-weight: bold">3</span>, <span style="color: #800000; font-weight: bold">4</span>, <span style="color: #800000; font-weight: bold">5</span><span style="color: #000000">)
</span><span style="color: #0000ff">GO</span>
<span style="color: #008080">--</span><span style="color: #008080">创建分区架构</span>
<span style="color: #0000ff">CREATE</span> PARTITION SCHEME <span style="color: #ff0000">[</span><span style="color: #ff0000">PS_BICompany</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">AS</span> PARTITION <span style="color: #ff0000">[</span><span style="color: #ff0000">PF_BICompany</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">TO</span> (<span style="color: #ff0000">[</span><span style="color: #ff0000">DWH_Optimizing_1</span><span style="color: #ff0000">]</span>, <span style="color: #ff0000">[</span><span style="color: #ff0000">DWH_Optimizing_2</span><span style="color: #ff0000">]</span>, <span style="color: #ff0000">[</span><span style="color: #ff0000">DWH_Optimizing_3</span><span style="color: #ff0000">]</span>, <span style="color: #ff0000">[</span><span style="color: #ff0000">DWH_Optimizing_4</span><span style="color: #ff0000">]</span>, <span style="color: #ff0000">[</span><span style="color: #ff0000">PRIMARY</span><span style="color: #ff0000">]</span><span style="color: #000000">)
</span><span style="color: #0000ff">GO</span>

 

SQL Server - 文件组，文件，备份，分区

标签：files   tran   lte   pre   master   size   com   weight   div