all-sql-about_bak_restore（一）：
容县ssl适用于网站、小程序/APP、API接口等需要进行数据传输应用场景，ssl证书未来市场广阔！成为创新互联公司的ssl证书销售渠道，可以享受市场价格4-6折优惠！如果有意向欢迎电话联系或者加微信：028-86922220（备注：SSL证书合作）期待与您的合作！
#完全备份
backup database record to disk = 'E:\backup\record.bak' with retaindays=9,format,init,compression;
#差异备份
backup database record to disk='E:\backup\record.bak' with differential,retaindays=9,noformat,noinit,compression;
#日志备份
backup log record to disk = 'E:\backup\record.bak' with retaindays=9,compression;
在[还原]运行前,要把数据库[多用户]模式改成[单用户]模式:
右键数据库“属性”-“选项”-“状态”-“限制访问”-把“MULTI_USER”改成“SINGLE_USER”确定，
执行以下命令：
#备份结尾日志（做数据库完全恢复之前使用，使数据库处于还原状态）
use master;backup log record to disk = 'E:\backup\log.bak' with norecovery,format,init,compression;
#查看备份介质中的备份信息
restore headeronly from disk='E:\backup\record.bak';
#列出备份集中包含的文件信息
restore filelistonly from disk='E:\backup\record.bak';
#逐个恢复备份集，恢复数据库到指定时间点
restore database test from disk ='E:\backup\record.bak' with file=1,norecovery;
restore database test from disk ='E:\backup\record.bak' with file=2,norecovery,stopat='2017-01-12 05:39:31';
restore log test from disk ='E:\backup\record.bak' with file=3,norecovery,stopat='2017-01-12 05:39:31';
restore database record with recovery;
#备份结尾日志（做数据库完全恢复之前使用，使数据库处于还原状态）
backup log record to disk = 'E:\backup\log.bak' with norecovery,format,init,compression;
--清空备份记录
use msdb;
#清空指定时间点之前的备份记录
exec sp_delete_backuphistory '2016-1-11';
#清空指定数据库的备份记录
exec sp_delete_database_backuphistory 'TEST';
all-sql-about_bak_restore（二）：
#完全备份
backup database record to disk = 'E:\backup\record.bak' with retaindays=9,format,init,compression;
#差异备份
backup database record to disk='E:\backup\record.bak' with differential,retaindays=9,noformat,noinit,compression;
#日志备份
backup log record to disk = 'E:\backup\record.bak' with retaindays=9,compression;
在[还原]运行前,要把数据库[多用户]模式改成[单用户]模式:
右键数据库“属性”-“选项”-“状态”-“限制访问”-把“MULTI_USER”改成“SINGLE_USER”确定，
执行以下命令：
#备份结尾日志（做数据库完全恢复之前使用，使数据库处于还原状态）
use master;backup log record to disk = 'E:\backup\log.bak' with norecovery,format,init,compression;
#查看备份介质中的备份信息
restore headeronly from disk='E:\backup\record.bak';
#列出备份集中包含的文件信息
restore filelistonly from disk='E:\backup\record.bak';
#逐个恢复备份集，恢复数据库到指定时间点
restore database test from disk ='E:\backup\record.bak' with file=1,norecovery;
restore database test from disk ='E:\backup\record.bak' with file=2,norecovery,stopat='2017-01-12 05:39:31';
restore log test from disk ='E:\backup\record.bak' with file=3,norecovery,stopat='2017-01-12 05:39:31';
restore database record with recovery;
#备份结尾日志（做数据库完全恢复之前使用，使数据库处于还原状态）
backup log record to disk = 'E:\backup\log.bak' with norecovery,format,init,compression;
--清空备份记录
use msdb;
#清空指定时间点之前的备份记录
exec sp_delete_backuphistory '2016-1-11';
#清空指定数据库的备份记录
exec sp_delete_database_backuphistory 'TEST';
all-sql-about_bak_restore（三）：
BACKUP DATABASE [timeRecord] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.FANTASY\MSSQL\Backup\timeRecord.bak' WITH RETAINDAYS = 9, NOFORMAT, NOINIT, NAME = N'timeRecord-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUM
GO
BACKUP DATABASE [timeRecord] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.FANTASY\MSSQL\Backup\timeRecord_diff.bak' WITH DIFFERENTIAL , RETAINDAYS = 9, NOFORMAT, NOINIT, NAME = N'timeRecord-差异 数据库 备份', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
BACKUP LOG [timeRecord] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.FANTASY\MSSQL\Backup\timeRecord_log.bak' WITH DESCRIPTION = '日志备份', RETAINDAYS = 7, FORMAT, INIT, NAME = 'timeRecord-日志备份', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup
============================
BACKUP DATABASE [timeRecord] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.FANTASY\MSSQL\Backup\timeRecord_diff.bak'
WITH RETAINDAYS = 9, NOFORMAT, NOINIT, COMPRESSION, DIFFERENTIAL;
GO
========================
#完全备份
backup database record to disk = 'E:\backup\record.bak' with retaindays=9,format,init,compression;
#差异备份
backup database record to disk='E:\backup\record.bak' with differential,retaindays=9,noformat,noinit,compression;
#日志备份
backup log record to disk = 'E:\backup\record.bak' with retaindays=9,compression;
#查看备份介质中的备份信息
restore headeronly from disk='E:\backup\record.bak';
#列出备份集中包含的文件信息
restore filelistonly from disk='E:\backup\record.bak';
=========================================
12号:
backup database zhaotest to disk = 'C:\mssql\backup\zhaotest.all.1.bak'
with retaindays=9,format,init,compression;
backup database zhaotest to disk='C:\mssql\backup\zhaotest.all.2.bak'
with differential,retaindays=9,noformat,noinit,compression;
backup log zhaotest to disk = 'C:\mssql\backup\zhaotest.all.3.bak'
with retaindays=9,compression;
======================================================
backup database zhaotest to disk = 'C:\mssql\backup\zhaotest.bak'
with retaindays=9,format,init,compression;
backup database zhaotest to disk='C:\mssql\backup\zhaotest.all.2.bak'
with differential,retaindays=9,noformat,noinit,compression;
backup log zhaotest to disk = 'C:\mssql\backup\zhaotest.all.4.bak'
with retaindays=9,compression;
restore database zhaotest from disk= 'C:\mssql\backup\zhaotest.all.3.bak'
with file=1,norecovery;
restore database zhaotest from disk='C:\MSSQL\Backup\zhaotest.all.2.bak'
with file=2,norecovery,stopat='2017-01-12 12:23:31';
restore log zhaotest from disk='C:\MSSQL\Backup\zhaotest.all.3.bak'
with file=3,norecovery,stopat='2017-01-12 19:23:21';
restore database zhaotest with recovery;
all-sql-about_bak_restore（四）：
实验一 SQLserver完全备份，差异备份，日志备份并且恢复到指定的时间点
示例：
备份（要求在每次备份前，都要向数据库里更新数据）
恢复到指定时间点
要求，把操作的步骤截图，贴到下面（至少要含有备份成功，恢复成功的步骤），如果恢复遇到数据库被占用的问题，请参考附录进行解决
附录：
还原sql server数据库遇到如下问题
该问题主要是你在还原的时候还有其他进程连在上面，导致无法获得独占造成的。
解决思路：
步骤
USE Master
ALTER DATABASE 数据库名称 SET OFFLINE WITH ROLLBACK IMMEDIATE
如果此时还原还是不行。可能是删完进程马上有新的进程连进来，导致一直失败。应用程序一直不停的进行数据库链接
这时，可以在单用户下还原。
设置方式：选中要还原的数据库-->属性-->选项-->限制访问
该值从MULTI_USER修改为SINGLE_USER,此时该数据库就会显示为单用户模式
这是GUI的模式，语句的办法比较简单
USE MASTER
Go
ALTER DATABASE 数据库名字 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
设置单用户数据库必须要超级用户
或者使用
删除连在上面的进程，回滚未提交的事务，然后还原。
1.查询要还原的数据库ID
Select * from master..sysdatabases where name = '';
2.获取该数据库的进程
Select * from sys.sysprocesses a where a.dbid = '';
3.杀掉连接在上面的进程
kill @spid;