SQLserver完全、差异、日志备份与恢复命令

all-sql-about_bak_restore（一）：

#完全备份
backup database record to disk = 'E:backuprecord.bak' with retaindays=9,format,init,compression;

#差异备份
backup database record to disk='E:backuprecord.bak' with differential,retaindays=9,noformat,noinit,compression;

#日志备份
backup log record to disk = 'E:backuprecord.bak' with retaindays=9,compression;

在[还原]运行前,要把数据库[多用户]模式改成[单用户]模式:
右键数据库“属性”-“选项”-“状态”-“限制访问”-把“MULTI_USER”改成“SINGLE_USER”确定，
执行以下命令：
#备份结尾日志（做数据库完全恢复之前使用，使数据库处于还原状态）
use master;backup log record to disk = 'E:backuplog.bak' with norecovery,compression;

#查看备份介质中的备份信息
restore headeronly from disk='E:backuprecord.bak';

#列出备份集中包含的文件信息
restore filelistonly from disk='E:backuprecord.bak';

#逐个恢复备份集，恢复数据库到指定时间点
restore database test from disk ='E:backuprecord.bak' with file=1,norecovery;
restore database test from disk ='E:backuprecord.bak' with file=2,norecovery,stopat='2017-01-12 05:39:31';
restore log test from disk ='E:backuprecord.bak' with file=3,stopat='2017-01-12 05:39:31';
restore database record with recovery;
#备份结尾日志（做数据库完全恢复之前使用，使数据库处于还原状态）
backup log record to disk = 'E:backuplog.bak' with norecovery,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:backuprecord.bak' with retaindays=9,0);">all-sql-about_bak_restore（三）：

BACKUP DATABASE [timeRecord] TO? DISK = N'C:Program FilesMicrosoft SQL ServerMSSQL12.FANTASYMSSQLBackuptimeRecord.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 FilesMicrosoft SQL ServerMSSQL12.FANTASYMSSQLBackuptimeRecord_diff.bak' WITH? DIFFERENTIAL,? RETAINDAYS = 9,? NAME = N'timeRecord-差异 数据库 备份',? STATS = 10
GO

BACKUP LOG [timeRecord] TO? DISK = 'C:Program FilesMicrosoft SQL ServerMSSQL12.FANTASYMSSQLBackuptimeRecord_log.bak' WITH? DESCRIPTION = '日志备份',? RETAINDAYS = 7,FORMAT,INIT,? NAME = 'timeRecord-日志备份',? STATS = 10
GO

C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBackup

============================

BACKUP DATABASE [timeRecord] TO? DISK = N'C:Program FilesMicrosoft SQL ServerMSSQL12.FANTASYMSSQLBackuptimeRecord_diff.bak'

WITH? RETAINDAYS = 9,? COMPRESSION,?? DIFFERENTIAL;
GO

========================

#完全备份
backup database record to disk = 'E:backuprecord.bak' with retaindays=9,compression;
#差异备份
backup database record to disk='E:backuprecord.bak' with differential,compression;
#日志备份
backup log record to disk = 'E:backuprecord.bak' with retaindays=9,compression;

#查看备份介质中的备份信息
restore headeronly from disk='E:backuprecord.bak';
#列出备份集中包含的文件信息
restore filelistonly from disk='E:backuprecord.bak';
?
=========================================
12号:

backup database zhaotest to disk = 'C:mssqlbackupzhaotest.all.1.bak'
with retaindays=9,compression;

backup database zhaotest to disk='C:mssqlbackupzhaotest.all.2.bak'?
with differential,compression;

backup log zhaotest to disk = 'C:mssqlbackupzhaotest.all.3.bak'?
with retaindays=9,compression;
======================================================
backup database zhaotest to disk = 'C:mssqlbackupzhaotest.bak'
with retaindays=9,compression;

backup log zhaotest to disk = 'C:mssqlbackupzhaotest.all.4.bak'?
with retaindays=9,compression;

restore database zhaotest from disk= 'C:mssqlbackupzhaotest.all.3.bak'?
with file=1,norecovery;

restore database zhaotest from disk='C:MSSQLBackupzhaotest.all.2.bak'
with file=2,stopat='2017-01-12 12:23:31';

restore log zhaotest from disk='C:MSSQLBackupzhaotest.all.3.bak'
with file=3,stopat='2017-01-12 19:23:21';

restore database zhaotest with recovery;

all-sql-about_bak_restore（四）：

实验一?SQLserver完全备份，差异备份，日志备份并且恢复到指定的时间点

示例：

备份（要求在每次备份前，都要向数据库里更新数据）

恢复到指定时间点

要求，把操作的步骤截图，贴到下面（至少要含有备份成功，恢复成功的步骤），如果恢复遇到数据库被占用的问题，请参考附录进行解决

??

附录：

还原sql server数据库遇到如下问题

该问题主要是你在还原的时候还有其他进程连在上面，导致无法获得独占造成的。

解决思路：

步骤

USEMaster

ALTERDATABASE?数据库名称?SETOFFLINEWITHROLLBACKIMMEDIATE

如果此时还原还是不行。可能是删完进程马上有新的进程连进来，导致一直失败。应用程序一直不停的进行数据库链接

这时，可以在单用户下还原。

设置方式：选中要还原的数据库-->属性选项限制访问

该值从MULTI_USER修改为SINGLE_USER,此时该数据库就会显示为单用户模式

这是GUI的模式，语句的办法比较简单

MASTER

Go

?数据库名字?SINGLE_USERIMMEDIATE;???

GO

设置单用户数据库必须要超级用户

或者使用

删除连在上面的进程，回滚未提交的事务，然后还原。

1.查询要还原的数据库ID

Select*frommaster..sysdatabaseswherename='';

2.获取该数据库的进程

sys.sysprocessesaa.dbid3.杀掉连接在上面的进程

kill@spid;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!