差异备份
1 --差异备份 2 Backup Database [pratice] 3 To disk='d:\pratice_Diff_20070908.bak' 4 With Differential
备份时候，查询窗口的输出
1 已为数据库 'pratice'，文件 'pratice' (位于文件 1 上)处理了 40 页。 2 已为数据库 'pratice'，文件 'pratice_log' (位于文件 1 上)处理了 1 页。 3 BACKUP DATABASE WITH DIFFERENTIAL 成功处理了 41 页，花费 0.016 秒(20.019 MB/秒)。
错误日志输出
1 2013-12-01 19:42:29.790 spid56 BackupDatabase: Database pratice 2 2013-12-01 19:42:29.820 spid56 Backup: Media open 3 2013-12-01 19:42:29.820 spid56 Backup: Media ready to backup 4 2013-12-01 19:42:29.820 spid56 BackupDatabase: Checkpoint done 5 2013-12-01 19:42:29.820 spid56 Backup: Scanning allocation bitmaps 6 2013-12-01 19:42:29.830 spid56 Backup: Done with allocation bitmaps 7 2013-12-01 19:42:29.830 spid56 BackupDatabase: Work estimates done 8 2013-12-01 19:42:29.830 spid56 Backup: Leading metadata div done 9 2013-12-01 19:42:29.830 spid56 Backup:Copying data 10 2013-12-01 19:42:29.830 spid56 Backup: DBReaderCount = 1 11 2013-12-01 19:42:29.830 spid56 Started file D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf 12 2013-12-01 19:42:29.830 spid56 Completed file D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf 13 2013-12-01 19:42:29.830 spid56 Padding MSDA with 7206 bytes 14 2013-12-01 19:42:29.840 spid56 BackupDatabase: Database files done 15 2013-12-01 19:42:29.840 spid56 慂正灵慄慴慢敳退慂正灵潌g邐%s : Log files done 16 : Log files done 17 2013-12-01 19:42:29.840 spid56 Backup: Trailing config done 18 2013-12-01 19:42:29.840 spid56 Backup: MBC done 19 2013-12-01 19:42:29.840 spid56 BackupDatabase: Writing history records 20 2013-12-01 19:42:29.840 备份 Database differential changes were backed up. Database: pratice, creation date(time): 2013/11/19(19:35:23), pages dumped: 139, first LSN: 3087:10184:34, last LSN: 3087:10200:1, full backup LSN: 3087:10159:37, number of dump devices: 1, device information: 21 2013-12-01 19:42:29.850 spid56 Writing backup history records 22 2013-12-01 19:42:29.860 spid56 BackupDatabase: Finished
跟完整备份的错误日志差不多
日志备份
1 --日志备份，默认截断日志 2 Backup Log [pratice] 3 To disk='d:\pratice_Log_20070908.bak'
备份时候，查询窗口的输出
1 已为数据库 'pratice'，文件 'pratice_log' (位于文件 1 上)处理了 29 页。 2 BACKUP LOG 成功处理了 29 页，花费 0.008 秒(27.954 MB/秒)。
错误日志输出
1 2013-12-01 19:48:02.080 spid56 BackupLog: database pratice
2 2013-12-01 19:48:02.100 spid56 BackupLog: SafeCheckpoint done
3 2013-12-01 19:48:02.110 spid56 Backup: Media open
4 2013-12-01 19:48:02.110 spid56 Backup: Media ready to backup
5 2013-12-01 19:48:02.110 spid56 BackupLog: Work estimate done
6 2013-12-01 19:48:02.110 spid56 Backup: Leading metadata div done
7 2013-12-01 19:48:02.110 spid56 慂正灵潌g邐%s : Log files done
8 : Log files done
9 2013-12-01 19:48:02.110 spid56 Backup: Trailing config done
10 2013-12-01 19:48:02.110 spid56 Backup: MBC done
11 2013-12-01 19:48:02.120 spid56 BackupLog: Writing history records
12 2013-12-01 19:48:02.120 备份 Log was backed up. Database: pratice, creation date(time): 2013/11/19(19:35:23), first LSN: 3087:9742:1, last LSN: 3087:10200:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'d:\pratice_Log_20070908.bak'}). This is an informational m
13 2013-12-01 19:48:02.120 spid56 Writing backup history records
14 2013-12-01 19:48:02.140 spid56 BackupLog: Finished
日志备份里只有这句话跟差异备份和完整备份不一样
1 2013-12-01 19:48:02.100 spid56 BackupLog: SafeCheckpoint done
文件备份
1 --文件备份 2 Exec Sp_Helpdb pratice --查看数据文件 3 Backup Database pratice 4 File='pratice' --数据文件的逻辑名 5 To disk='d:\pratice_File_20070908.bak'
备份时候，查询窗口的输出
1 已为数据库 'pratice'，文件 'pratice' (位于文件 1 上)处理了 28152 页。 2 已为数据库 'pratice'，文件 'pratice_log' (位于文件 1 上)处理了 2 页。 3 BACKUP DATABASE...FILE=成功处理了 28154 页，花费 1.663 秒(132.258 MB/秒)。
错误日志输出
1 2013-12-01 20:53:13.480 spid56 BackupDatabase: Database pratice 2 2013-12-01 20:53:13.500 spid56 Backup: Media open 3 2013-12-01 20:53:13.500 spid56 Backup: Media ready to backup 4 2013-12-01 20:53:13.500 spid56 Backup: Clearing differential bitmaps 5 2013-12-01 20:53:13.500 spid56 Backup: Bitmaps cleared 6 2013-12-01 20:53:13.510 spid56 BackupDatabase: Checkpoint done 7 2013-12-01 20:53:13.510 spid56 Backup: Scanning allocation bitmaps 8 2013-12-01 20:53:13.510 spid56 Backup: Done with allocation bitmaps 9 2013-12-01 20:53:13.510 spid56 BackupDatabase: Work estimates done 10 2013-12-01 20:53:13.510 spid56 Backup: Leading metadata div done 11 2013-12-01 20:53:13.510 spid56 Backup:Copying data 12 2013-12-01 20:53:13.520 spid56 Backup: DBReaderCount = 1 13 2013-12-01 20:53:13.520 spid56 Started file D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf 14 2013-12-01 20:53:15.200 spid56 Completed file D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf 15 2013-12-01 20:53:15.200 spid56 Padding MSDA with 65536 bytes 16 2013-12-01 20:53:15.200 spid56 BackupDatabase: Database files done 17 2013-12-01 20:53:15.210 spid56 慂正灵慄慴慢敳退慂正灵潌g邐%s : Log files done 18 : Log files done 19 2013-12-01 20:53:15.210 spid56 Backup: Trailing config done 20 2013-12-01 20:53:15.210 spid56 Backup: MBC done 21 2013-12-01 20:53:15.220 spid56 BackupDatabase: Writing history records 22 2013-12-01 20:53:15.220 备份 Database file was backed up. Database: pratice, creation date(time): 2013/11/19(19:35:23), file list: (pratice(D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf)), pages dumped: 28171, number of dump devices: 1, device informatio 23 2013-12-01 20:53:15.230 spid56 Writing backup history records 24 2013-12-01 20:53:15.240 spid56 BackupDatabase: Finished
文件组备份
1 --文件组备份 2 Exec Sp_Helpdb pratice --查看数据文件 3 Backup Database pratice 4 FileGroup='Primary' --数据文件的逻辑名 5 To disk='d:\pratice_FileGroup_20070908.bak' 6 With init
备份时候，查询窗口的输出
1 已为数据库 'pratice'，文件 'pratice' (位于文件 1 上)处理了 28152 页。 2 已为数据库 'pratice'，文件 'pratice_log' (位于文件 1 上)处理了 2 页。 3 BACKUP DATABASE...FILE=成功处理了 28154 页，花费 1.679 秒(130.998 MB/秒)。
错误日志输出
1 2013-12-01 20:58:49.790 spid56 BackupDatabase: Database pratice 2 2013-12-01 20:58:49.800 spid56 Backup: Media open 3 2013-12-01 20:58:49.800 spid56 Backup: Media ready to backup 4 2013-12-01 20:58:49.800 spid56 Backup: Clearing differential bitmaps 5 2013-12-01 20:58:49.810 spid56 Backup: Bitmaps cleared 6 2013-12-01 20:58:49.810 spid56 BackupDatabase: Checkpoint done 7 2013-12-01 20:58:49.810 spid56 Backup: Scanning allocation bitmaps 8 2013-12-01 20:58:49.820 spid56 Backup: Done with allocation bitmaps 9 2013-12-01 20:58:49.820 spid56 BackupDatabase: Work estimates done 10 2013-12-01 20:58:49.820 spid56 Backup: Leading metadata div done 11 2013-12-01 20:58:49.820 spid56 Backup:Copying data 12 2013-12-01 20:58:49.820 spid56 Backup: DBReaderCount = 1 13 2013-12-01 20:58:49.820 spid56 Started file D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf 14 2013-12-01 20:58:51.480 spid56 Completed file D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf 15 2013-12-01 20:58:51.480 spid56 Padding MSDA with 65536 bytes 16 2013-12-01 20:58:51.480 spid56 BackupDatabase: Database files done 17 2013-12-01 20:58:51.480 spid56 慂正灵慄慴慢敳退慂正灵潌g邐%s : Log files done 18 : Log files done 19 2013-12-01 20:58:51.480 spid56 Backup: Trailing config done 20 2013-12-01 20:58:51.480 spid56 Backup: MBC done 21 2013-12-01 20:58:51.500 spid56 BackupDatabase: Writing history records 22 2013-12-01 20:58:51.500 备份 Database file was backed up. Database: pratice, creation date(time): 2013/11/19(19:35:23), file list: (pratice(D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf)), pages dumped: 28171, number of dump devices: 1, device informatio 23 2013-12-01 20:58:51.500 spid56 Writing backup history records 24 2013-12-01 20:58:51.520 spid56 BackupDatabase: Finished
在完整备份和差异备份里面为什麽，查询窗口输出的“成功处理页面”比 错误日志里pages dumped的页面还要少？？
就算加上数据库头部的一些关键页面PFS 、DCM、 BCM、 IAM页面加起来也没有那麽多
数据库头部的一些关键页面PFS 、DCM、 BCM、 IAM参考文章：查看SQLSERVER内部数据页面的小插件Internals Viewer
下面是完整备份时候的输出，多了17页
BACKUP DATABASE 成功处理了 28154 页
pages dumped: 28171
其实文章里也有提示：还原/备份时做了些什么
比如，你有个1TB的数据库，但是只储存了100GB的数据，那么备份的时候，只需要备份这100GB的数据。
然而，在还原数据库的时候，你必须重构1TB的数据库，那将意味着大量的时间将被消耗
这篇文章也有一些提示：谁用掉了我的数据库空间？
SQL Server在使用数据页的时候，为了提高速度，会先把一些页面一次预留”reserve”给表格，然后真正有数据插入的时候，再使用
查询窗口里输出的是实际就是数据库真正存储数据的页面的数量
BACKUP DATABASE 成功处理了 28154 页
而错误日志就是所有页面，不论是存储数据还是PFS页面还是预留页面
相关实验：下面的实验创建了一个3GB大小的数据库，然后插入了一些数据到数据库中
（1）不对数据库进行收缩，直接备份数据库，然后还原
（2）先对数据库进行收缩，备份数据库，然后还原
USE master
GO
--创建数据库 初始大小为3G
CREATE DATABASE [testbackupdb] ON PRIMARY
( NAME = N'testbackupdb',
FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\testbackupdb.mdf' , SIZE = 3072000KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'testbackupdb_log',
FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\testbackupdb_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
--创建表
USE [testbackupdb]
GO
CREATE TABLE aa (id INT,NAME NVARCHAR(MAX))
GO
--插入数据
INSERT INTO aa
SELECT 1,REPLICATE('aa',500)
GO 10000
--查看表使用的空间 大概26MB
sys.sp_spaceused aa
sys.sp_helpdb [testbackupdb]
--备份数据库
BACKUP DATABASE [testbackupdb] TO DISK ='d:\testbackupdb_fullbackup.bak'
--删除数据库
DROP DATABASE [testbackupdb]
--1、还原数据库 没有收缩数据库的情况下
SET STATISTICS TIME ON
RESTORE DATABASE [testbackupdb] FROM DISK = N'D:\testbackupdb_fullbackup.bak' WITH FILE = 1,
MOVE N'testbackupdb' TO N'D:\testbackupdb.mdf',
MOVE N'testbackupdb_log' TO N'D:\testbackupdb_1.ldf',
NOUNLOAD, REPLACE, STATS = 10
GO
SET STATISTICS TIME OFF
SQL Server 执行时间:
CPU 时间 = 140 毫秒，占用时间 = 3024 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
-------------------------------------------------------------------------------------------
--收缩数据库为100MB
USE [testbackupdb]
GO
DBCC SHRINKFILE (N'testbackupdb' , 100)
GO
--备份数据库
BACKUP DATABASE [testbackupdb] TO DISK ='d:\testbackupdb_fullbackup.bak'
--删除数据库
DROP DATABASE [testbackupdb]
--2、还原数据库 没有收缩数据库的情况下
SET STATISTICS TIME ON
RESTORE DATABASE [testbackupdb] FROM DISK = N'D:\testbackupdb_fullbackup.bak' WITH FILE = 1,
MOVE N'testbackupdb' TO N'D:\testbackupdb.mdf',
MOVE N'testbackupdb_log' TO N'D:\testbackupdb_1.ldf',
NOUNLOAD, REPLACE, STATS = 10
GO
SET STATISTICS TIME OFF
SQL Server 执行时间:
CPU 时间 = 140 毫秒，占用时间 = 1549 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 10 毫秒。
--查看数据库大小
sys.sp_spaceused aa
sys.sp_helpdb [testbackupdb]
--结论：备份数据库前先收缩，还原数据库的时候会比较快
--因为收缩了数据库之后，数据库的size变成了收缩之后的大小，其实还原数据库的时候
--SQLSERVER根据根据这个初始大小先初始化出这个数据库初始大小的磁盘空间，然后再复制数据
View Code
结论：备份数据库前先收缩，还原数据库的时候会比较快
因为收缩了数据库之后，数据库的size变成了收缩之后的大小，其实还原数据库的时候
SQLSERVER根据根据这个初始大小先初始化出这个数据库初始大小的磁盘空间，然后再复制数据
数据库还原时候的信息
执行下面脚本先kill掉所有连接到pratice数据库的连接
1 --执行下面脚本先kill掉所有连接到pratice数据库的连接
2 USE master
3 GO
4 DECLARE @spid INT
5 DECLARE @count INT
6 WHILE @count > 0
7 BEGIN
8 SELECT @count = COUNT(*)
9 FROM sys.[sysprocesses]
10 WHERE [dbid] = DB_ID('pratice')
11 SELECT @spid = [spid]
12 FROM sys.[sysprocesses]
13 WHERE [dbid] = DB_ID('pratice')
14 EXEC('kill '+@spid)
15 END
1 --完整备份还原 2 RESTORE DATABASE [pratice] 3 FROM DISK ='D:\pratice_backup_201207160152.bak' WITH REPLACE
还原的时候，查询窗口的输出
1 已为数据库 'pratice'，文件 'pratice' (位于文件 1 上)处理了 28152 页。 2 已为数据库 'pratice'，文件 'pratice_log' (位于文件 1 上)处理了 2 页。 3 RESTORE DATABASE 成功处理了 28154 页，花费 2.267 秒(97.020 MB/秒)。
错误日志输出
1 2013-12-01 21:20:47.490 spid56 RestoreDatabase: Database pratice
2 2013-12-01 21:20:47.490 spid56 Opening backup set --打开备份集
3 2013-12-01 21:20:47.500 spid56 SetTargetRestoreAge: 0
4 2013-12-01 21:20:47.500 spid56 Restore: Configuration div loaded --读取数据库配置，例如数据库的初始大小
5 2013-12-01 21:20:47.500 spid56 Restore: Backup set is open
6 2013-12-01 21:20:47.500 spid56 Restore: Planning begins
7 2013-12-01 21:20:47.520 spid56 Halting FullText crawls on database pratice --因为pratice数据库使用了全文搜索，这里会Halting停止全文搜索抓取
8 2013-12-01 21:20:47.520 spid56 Dismounting FullText catalogs --卸载FullText目录
9 2013-12-01 21:20:47.520 spid56 X-locking database: pratice --用排他锁锁住pratice数据库
10 2013-12-01 21:20:47.520 spid56 Resuming any halted fulltext crawls 恢复全文搜索抓取
11 2013-12-01 21:21:28.310 spid56 RestoreDatabase: Database pratice
12 2013-12-01 21:21:28.310 spid56 Opening backup set
13 2013-12-01 21:21:28.320 spid56 SetTargetRestoreAge: 0
14 2013-12-01 21:21:28.320 spid56 Restore: Configuration div loaded
15 2013-12-01 21:21:28.320 spid56 Restore: Backup set is open
16 2013-12-01 21:21:28.320 spid56 Restore: Planning begins
17 2013-12-01 21:21:28.340 spid56 Halting FullText crawls on database pratice
18 2013-12-01 21:21:28.340 spid56 Dismounting FullText catalogs
19 2013-12-01 21:21:28.340 spid56 X-locking database: pratice
20 2013-12-01 21:21:28.340 spid56 Restore: Planning complete
21 2013-12-01 21:21:28.340 spid56 Restore: BeginRestore (offline) on pratice
22 2013-12-01 21:21:28.340 spid56 AppDomain 2 (pratice.dbo[runtime].1) is marked for unload due to common language runtime (CLR) or security data definition language (DDL) operations. --卸载CLR
23 2013-12-01 21:21:28.350 spid20s AppDomain 2 (pratice.dbo[runtime].1) unloaded.
24 2013-12-01 21:21:28.360 spid56 Restore: PreparingContainers --准备容器
25 2013-12-01 21:21:28.390 spid56 Restore: Containers are ready --容器准备完成
26 2013-12-01 21:21:28.390 spid56 Zeroing D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice_log.ldf from page 1 to 6528 (0x2000 to 0x3300000) --日志填零初始化
27 2013-12-01 21:21:28.430 spid56 Restore: Restoring backup set
28 2013-12-01 21:21:28.430 spid56 Restore: Transferring data to pratice --传输数据到pratice数据库
29 2013-12-01 21:21:28.570 spid56 Zeroing completed on D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice_log.ldf
30 http://blogs.msdn.com/b/apsd/archive/2013/06/17/sql-server-log-write.aspx
31 2013-12-01 21:21:30.670 spid56 Restore: Waiting for log zero on pratice
32 2013-12-01 21:21:30.680 spid56 Restore: LogZero complete --日志填零完毕
33 2013-12-01 21:21:30.680 spid56 SetTargetRestoreAge: 0
34 2013-12-01 21:21:30.700 spid56 FileHandleCache: 0 files opened. CacheSize: 12
35 2013-12-01 21:21:30.700 spid56 Restore: Data transfer complete on pratice --数据传输完毕
36 2013-12-01 21:21:30.710 spid56 Restore: Backup set restored --备份集已经还原
37 2013-12-01 21:21:30.720 spid56 Starting up database 'pratice'. --启动pratice数据库
38 2013-12-01 21:21:30.780 spid56 The database 'pratice' is marked RESTORING and is in a state that does not allow recovery to be run. --数据库处于正在还原状态，还未能完全恢复进行运行
39 --SQLSERVER数据库主要状态和切换路径 http://www.cnblogs.com/lyhabc/archive/2012/09/15/2687076.html
40 2013-12-01 21:21:30.780 spid56 Restore-Redo begins on database pratice --重做日志!!
41 2013-12-01 21:21:30.790 spid56 RunOfflineRedo logIter.Init(): FirstLsn(PruId: 0): 0xc0f:0x27dd:0x25
42 2013-12-01 21:21:30.790 spid56 RunOfflineRedo logIter.Init(): LastLsn(PruId: 0): 0xc0f:0x27ee:0x1
43 2013-12-01 21:21:30.790 spid56 OfflineRollforward: StopLsn/LastLsn(PruId: 0): 0xc0f:0x27ee:0x1
44 2013-12-01 21:21:30.790 spid56 Rollforward complete on database pratice --为什麽会有Rollforward http://www.cnblogs.com/lyhabc/p/3448449.html
45 2013-12-01 21:21:30.810 spid56 Restore: Done with fixups --checkdb检查完毕 fixup：修正
46 2013-12-01 21:21:30.810 spid56 Restore: Transitioning database to ONLINE
47 2013-12-01 21:21:30.810 spid56 Restore: Restarting database for ONLINE
48 2013-12-01 21:21:30.820 spid56 Starting up database 'pratice'.
49 2013-12-01 21:21:30.860 spid56 FixupLogTail(progress) zeroing D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice_log.ldf from 0x1efdc00 to 0x1efe000. --修正日志尾部
50 2013-12-01 21:21:30.860 spid56 Zeroing D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice_log.ldf from page 3967 to 4447 (0x1efe000 to 0x22be000)
51 2013-12-01 21:21:30.870 spid56 Zeroing completed on D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice_log.ldf
52 2013-12-01 21:21:30.870 spid56 PostRestoreContainerFixups: fixups complete
53 2013-12-01 21:21:30.870 spid56 CHECKDB for database 'pratice' finished without errors on 2013-11-23 23:35:18.670 (local time). This is an informational message only; no user action is required.
54 2013-12-01 21:21:30.880 spid56 PostRestoreReplicationFixup for pratice starts --复制相关
55 2013-12-01 21:21:30.970 spid56 PostRestoreReplicationFixup for pratice complete
56 2013-12-01 21:21:30.970 spid56 Restore: Database is restarted --数据库已经重启
57 2013-12-01 21:21:30.970 备份 Restore is complete on database 'pratice'. The database is now available. --数据库还原完成，数据库现在是可用状态
58 2013-12-01 21:21:30.980 spid56 Resuming any halted fulltext crawls --恢复全文搜索抓取
59 2013-12-01 21:21:30.990 spid56 Restore: Writing history records --还原数据库记录写入msdb里相关的表
60 2013-12-01 21:21:30.990 备份 Database was restored: Database: pratice, creation date(time): 2013/11/19(19:35:23), first LSN: 3087:10205:37, last LSN: 3087:10222:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:\pratice_backup_201207160152.bak'}). Informational
61 2013-12-01 21:21:30.990 spid56 Writing backup history records --备份历史记录写入到错误日志
62 2013-12-01 21:21:31.010 spid56 Restore: Done with MS
下载本文