Sql Server备份与还原

最新推荐文章于 2024-07-29 18:16:59 发布
原创 最新推荐文章于 2024-07-29 18:16:59 发布 · 1k 阅读 · 1 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文介绍数据库备份的不同类型，包括完全备份、差异备份和日志备份的方法及步骤，并演示如何通过SQL语句实现这些备份和恢复操作。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

1、完全备份与还原

create table dept
(
	dept_id int identity(1,1) primary key,
	dept_name nvarchar(256)
	
);

insert into dept(dept_name) values('张三001');
insert into dept(dept_name) values('张三002');
insert into dept(dept_name) values('张三003');
insert into dept(dept_name) values('张三004');

select * from dept;

backup database test to  disk='D:\test001.bak';

truncate table dept;

use master;

restore database test from disk='D:\test001.bak' with replace;

use test;

select * from dept;

完全备份简单，恢复也很简单，但是如果数据库太大，可能会花很长时间，针对这个问题吗，需要制定好备份计划，

比如说：

周日：全备份

周一：日志备份

周二：差异备份

周三：日志备份

周四：日志备份

周五：差异备份

周六：日志备份

2、差异备份与还原 

if exists(select * from sys.tables where name='dept')
	drop table dept
GO

create table dept
(
	dept_id int identity(1,1) primary key,
	dept_name nvarchar(256)
	
)
GO

insert into dept(dept_name) values('张三001');
insert into dept(dept_name) values('张三002');
insert into dept(dept_name) values('张三003');
insert into dept(dept_name) values('张三004');

select * from dept;

backup database test to  disk='D:\test001.bak';

truncate table dept;

insert into dept(dept_name) values('李四001');
insert into dept(dept_name) values('李四002');
insert into dept(dept_name) values('李四003');
insert into dept(dept_name) values('李四004');

backup database test to  disk='D:\test001_001.diff' with differential;

use master;

restore database test from disk='D:\test001.bak' with norecovery,replace;
restore database test from disk='D:\test001_001.diff' with recovery;

use test;

select * from dept;

差异备份的还原需要建立在一次全备份基础之上，并且中间不能有间隔，由于数据比较重要建议使用完整恢复模式

alter database test set  recovery full;

3、日志备份与还原

同样道理，日至还原也是需要建立在一次全备份基础之上，中间不能有间隔，最好使用完全恢复模式

if exists(select * from sys.tables where name='dept')
	drop table dept
GO

create table dept
(
	dept_id int identity(1,1) primary key,
	dept_name nvarchar(256)
	
)
GO

insert into dept(dept_name) values('张三001');
insert into dept(dept_name) values('张三002');
insert into dept(dept_name) values('张三003');
insert into dept(dept_name) values('张三004');

select * from dept;

backup database test to  disk='D:\test001.bak';

delete from dept where dept_id=1;

insert into dept(dept_name) values('李四001');
insert into dept(dept_name) values('李四002');
insert into dept(dept_name) values('李四003');
insert into dept(dept_name) values('李四004');

backup log test to  disk='D:\test001_001.trn';

delete from dept where dept_id=5;

insert into dept(dept_name) values('王五001');
insert into dept(dept_name) values('王五002');
insert into dept(dept_name) values('王五003');
insert into dept(dept_name) values('王五004');

backup log test to  disk='D:\test001_002.trn';
use master;

restore database test from disk='D:\test001.bak' with norecovery,replace;
restore log test from disk='D:\test001_001.trn' with norecovery;
restore log test from disk='D:\test001_002.trn' with recovery;

use test;

select * from dept;

日志还原还可以还原到时间点： 

restore database test from disk='D:\test001.bak' with norecovery,replace;
restore log test from disk='D:\test001_001.trn' with norecovery;
restore log test from disk='D:\test001_002.trn' with norecovery,stopat='2013-07-10 16:18:50';
restore database test with recovery

------------------------------------------------------------------------------------------

完全备份、差异备份与日志备份结合还原(晚上12点备份)

周日：完全备份  d:\test_001.bak

周一：日志备份  d:\test_001.trn

周二：差异备份  d:\test_001.diff

周三：日志备份  d:\test_002.trn

周四：差异备份  d:test_002.diff

周五：日志备份  d:\test_003.trn

周六：差异备份  d:test_003.diff


1、还原到周四的10:10:10时间点

2、周三的日志备份丢失

还原方案：用周日的完全备份 + 周四差异备份 + 周五日志备份

因为周五的日志包含从周三日志备份这个时间点 到 周五日志备份之间的日志


restore database test from disk='D:\test_001.bak' with norecovery,replace;
restore database test from disk='D:\test_002.diff' with norecovery;
restore log test from disk='D:\test_003.trn' with recovery,stopat='2013-07-10 10:10:10';

注：针对差异备份与日志备份的还原，必须建议在一次全备份基础之上，并且中间的备份不能出现间隔

例如：2013-07-09 16:20:13  全备份

2013-07-10 16:20:13 差异(日志)备份

2013-07-11 16:20:13 差异(日志)备份

2013-07-12 16:20:13 差异(日志)备份

然后我想还原到2013-07-11 09:50:10这个时间点，但是发现2013-07-09 16:20:13这个备份丢失了，

这样肯定就不行了

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

关于备份与恢复还有许多具体的参数，可以参考微软提供的文档

全备份：
BACKUP DATABASE { database_name | @database_name_var } 
  TO <backup_device> [ ,...n ] 
  [ <MIRROR TO clause> ] [ next-mirror-to ]
  [ WITH { DIFFERENTIAL | <general_WITH_options> [ ,...n ] } ]
[;]

文件、文件组备份：
BACKUP DATABASE { database_name | @database_name_var } 
 <file_or_filegroup> [ ,...n ] 
  TO <backup_device> [ ,...n ] 
  [ <MIRROR TO clause> ] [ next-mirror-to ]
  [ WITH { DIFFERENTIAL | <general_WITH_options> [ ,...n ] } ]
[;]

部分备份：
BACKUP DATABASE { database_name | @database_name_var } 
 READ_WRITE_FILEGROUPS [ , <read_only_filegroup> [ ,...n ] ]
  TO <backup_device> [ ,...n ] 
  [ <MIRROR TO clause> ] [ next-mirror-to ]
  [ WITH { DIFFERENTIAL | <general_WITH_options> [ ,...n ] } ]
[;]
日志备份 (full and bulk-logged recovery models)
BACKUP LOG { database_name | @database_name_var } 
  TO <backup_device> [ ,...n ] 
  [ <MIRROR TO clause> ] [ next-mirror-to ]
  [ WITH { <general_WITH_options> | <log-specific_optionspec> } [ ,...n ] ]
[;]

<backup_device>::= 
 {
   { logical_device_name | @logical_device_name_var } 
 | { DISK | TAPE } = 
     { 'physical_device_name' | @physical_device_name_var }
 } 

<MIRROR TO clause>::=
 MIRROR TO <backup_device> [ ,...n ]

<file_or_filegroup>::=
 {
   FILE = { logical_file_name | @logical_file_name_var } 
 | FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }
 } 

<read_only_filegroup>::=
FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }

<general_WITH_options> [ ,...n ]::= 
--Backup Set Options
   COPY_ONLY 
 | { COMPRESSION | NO_COMPRESSION } 
 | DESCRIPTION = { 'text' | @text_variable } 
 | NAME = { backup_set_name | @backup_set_name_var } 
 | PASSWORD = { password | @password_variable } 
 | { EXPIREDATE = { 'date' | @date_var } 
        | RETAINDAYS = { days | @days_var } } 

--Media Set Options
   { NOINIT | INIT } 
 | { NOSKIP | SKIP } 
 | { NOFORMAT | FORMAT } 
 | MEDIADESCRIPTION = { 'text' | @text_variable } 
 | MEDIANAME = { media_name | @media_name_variable } 
 | MEDIAPASSWORD = { mediapassword | @mediapassword_variable } 
 | BLOCKSIZE = { blocksize | @blocksize_variable } 

--Data Transfer Options
   BUFFERCOUNT = { buffercount | @buffercount_variable } 
 | MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }

--Error Management Options
   { NO_CHECKSUM | CHECKSUM }
 | { STOP_ON_ERROR | CONTINUE_AFTER_ERROR }

--Compatibility Options
   RESTART 

--Monitoring Options
   STATS [ = percentage ] 

--Tape Options
   { REWIND | NOREWIND } 
 | { UNLOAD | NOUNLOAD } 

--Log-specific Options
   { NORECOVERY | STANDBY = undo_file_name }
 | NO_TRUNCATE
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
RESTORE DATABASE { database_name | @database_name_var } 
 [ FROM <backup_device> [ ,...n ] ]
 [ WITH 
   {
    [ RECOVERY | NORECOVERY | STANDBY = 
        {standby_file_name | @standby_file_name_var } 
       ]
   | ,  <general_WITH_options> [ ,...n ]
   | , <replication_WITH_option>
   | , <change_data_capture_WITH_option>
   | , <service_broker_WITH options> 
   | , <point_in_time_WITH_options—RESTORE_DATABASE> 
   } [ ,...n ]
 ]
[;]

--To perform the first step of the initial restore sequence
-- of a piecemeal restore:
RESTORE DATABASE { database_name | @database_name_var } 
   <files_or_filegroups> [ ,...n ]
 [ FROM <backup_device> [ ,...n ] ] 
   WITH 
      PARTIAL, NORECOVERY 
      [  , <general_WITH_options> [ ,...n ] 
       | , <point_in_time_WITH_options—RESTORE_DATABASE> 
      ] [ ,...n ] 
[;]

--To Restore Specific Files or Filegroups: 
RESTORE DATABASE { database_name | @database_name_var } 
   <file_or_filegroup> [ ,...n ]
 [ FROM <backup_device> [ ,...n ] ] 
   WITH 
   {
      [ RECOVERY | NORECOVERY ]
      [ , <general_WITH_options> [ ,...n ] ]
   } [ ,...n ] 
[;]

--To Restore Specific Pages: 
RESTORE DATABASE { database_name | @database_name_var } 
   PAGE = 'file:page [ ,...n ]' 
  [ , <file_or_filegroups> ] [ ,...n ]
 [ FROM <backup_device> [ ,...n ] ] 
   WITH 
       NORECOVERY   
      [ , <general_WITH_options> [ ,...n ] ]
[;]

--To Restore a Transaction Log:
RESTORE LOG { database_name | @database_name_var } 
 [ <file_or_filegroup_or_pages> [ ,...n ] ]
 [ FROM <backup_device> [ ,...n ] ] 
 [ WITH 
   {
     [ RECOVERY | NORECOVERY | STANDBY = 
        {standby_file_name | @standby_file_name_var } 
       ]
    | ,  <general_WITH_options> [ ,...n ]
    | , <replication_WITH_option>
    | , <point_in_time_WITH_options—RESTORE_LOG> 
   } [ ,...n ]
 ] 
[;]

--To Revert a Database to a Database Snapshot:   
RESTORE DATABASE { database_name | @database_name_var } 
FROM DATABASE_SNAPSHOT = database_snapshot_name 

<backup_device>::=
{ 
   { logical_backup_device_name |
            @logical_backup_device_name_var }
 | { DISK | TAPE } = { 'physical_backup_device_name' |
            @physical_backup_device_name_var } 
} 

<files_or_filegroups>::= 
{ 
   FILE = { logical_file_name_in_backup | @logical_file_name_in_backup_var } 
 | FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var } 
 | READ_WRITE_FILEGROUPS
} 

<general_WITH_options> [ ,...n ]::= 
--Restore Operation Options
   MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' 
          [ ,...n ] 
 | REPLACE 
 | RESTART 
 | RESTRICTED_USER 

--Backup Set Options
 | FILE = { backup_set_file_number | @backup_set_file_number } 
 | PASSWORD = { password | @password_variable } 

--Media Set Options
 | MEDIANAME = { media_name | @media_name_variable } 
 | MEDIAPASSWORD = { mediapassword | @mediapassword_variable } 
 | BLOCKSIZE = { blocksize | @blocksize_variable } 

--Data Transfer Options
 | BUFFERCOUNT = { buffercount | @buffercount_variable } 
 | MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }

--Error Management Options
 | { CHECKSUM | NO_CHECKSUM } 
 | { STOP_ON_ERROR | CONTINUE_AFTER_ERROR } 

--Monitoring Options
 | STATS [ = percentage ] 

--Tape Options
 | { REWIND | NOREWIND } 
 | { UNLOAD | NOUNLOAD } 

<replication_WITH_option>::=
 | KEEP_REPLICATION 

<change_data_capture_WITH_option>::=
 | KEEP_CDC

<service_broker_WITH_options>::= 
 | ENABLE_BROKER 
 | ERROR_BROKER_CONVERSATIONS 
 | NEW_BROKER 


<point_in_time_WITH_options—RESTORE_DATABASE>::= 
 | {
   STOPAT = { 'datetime' | @datetime_var } 
 | STOPATMARK = { 'lsn:lsn_number' }
                 [ AFTER 'datetime' ] 
 | STOPBEFOREMARK = {  'lsn:lsn_number' }
                 [ AFTER 'datetime' ] 
   } 

<point_in_time_WITH_options—RESTORE_LOG>::= 
 | {
   STOPAT = { 'datetime' | @datetime_var } 
 | STOPATMARK = { 'mark_name' | 'lsn:lsn_number' }
                 [ AFTER 'datetime' ] 
 | STOPBEFOREMARK = { 'mark_name' | 'lsn:lsn_number' }
                 [ AFTER 'datetime' ] 
   }



RESTORE DATABASE命令还原SQLServer 2005 数据库
03-20
RESTORE DATABASE命令还原SQLServer 2005 数据库
简化SQL Server备份与还原到云工作原理及操作方法
12-15
在SQL Server 户内实例上的SQLServer Databases或在宿主环境中，如运行在 Windows Azure VMs上的 SQL Server 实例都可以利用此功能。 好处： · 灵活、 可靠和无限的线下存储来提高灾难恢复： 将您的备份存储在 ...
Sql server备份与还原
xiaoxiong1212的专栏
06-18 1164
一．备份数据库 BACKUP DATABASE test to disk ='D:/cbmBack/previousDay.bak' with init,name='full backup' 备注：with init表示指针移动文件头部去写。   二．备份日志 BACKUP LOG  test to disk ='D:/cbmBack/log.bak' with init 备
SQLserver备份与还原
canNick的专栏
04-06 1103
BACKUP备份整个数据库、事务日志，或者备份一个或多个文件或文件组。有关数据库备份和恢复操作的更多信息，请参见备份和还原数据库。   语法备份整个数据库：BACKUP   DATABASE   {   database_name   |   @database_name_var   }   TO     [   ,...n   ]   [   WITH        [   BLOCKSIZE
RESTORE DATABASE命令还原SQLServer 数据库 bak
weixin_34125592的博客
10-14 750
今天在sqlServer20005 的management studio里使用bak文件还原数据库的时候,总是失败!Restore failed for Server 'ADANDELI'.  (Microsoft.SqlServer.Smo)An exception occurred while executing a Transact-SQL statement or batch. (Micr...
sql server备份与还原
10-19
好的，关于 SQL Server 备份与还原，您有什么问题需要我解答呢？ 备份是指将数据库的数据和日志文件复制到另一个位置，以便在需要时可以还原数据库。还原是指将备份文件还原到数据库中，以恢复数据库到备份时的状态...
服务器SQL Server 自动定时备份和还原的工具
09-01
本工具是自己开发的，用于SQL Server 数据库的备份与还原用，有了本工具，设定好每一个数据库的备份时间，可以按年、月、日、时、分设置好后，到时间自动备份，工具还有备份数据为多少天前的可以自动删除，同时也有...
SQL Server备份与还原工具——Acronis.pdf
09-19
SQL Server备份与还原工具——Acronis.pdf
SQL Server 数据库备份和还原
weixin_34023982的博客
09-06 306
一、SQL命令   备份BACKUP DATABASE TestDb TO DISK='d:\TestDb.bak'还原RESTORE DATABASE TestDb FROM DISK='d:\TestDb.bak' 二、使用管理工具 　　文章较长，但比较简单，直接给链接：https://www.cnblogs.com/luckyboy/p/5564880.html 三、使用sqlcm...
SQL Server数据库的备份和还
weixin_30449453的博客
06-30 622
转：http://blog.youkuaiyun.com/zwj7612356/article/details/8188025 在sql server数据库中，备份和还原都只能在服务器上进行，备份的数据文件在服务器上，还原的数据文件也只能在服务器上，当在非服务器的机器上启动sql server客户端的时候，也可以通过该客户端来备份和还原数据库，但是这种操作实质是在服务器上进行的，备份的数据文件在服务...
保姆级教程 ！SQL Server数据库的备份和还原
最新发布
qq_45631767的博客
07-29 1万+
选中Step 3新建的数据库，右击依次点击Tasks -->Restore -->Database。选择一个目录用来保存备份文件，并填写备份文件的文件名，要以.bak结尾，并点击OK。右击选中的数据库，点击Tasks --> Back Up。在Step 7选中的目录下面即可找到备份的数据库文件。选中新建的数据库，展开Tbles,即可看到还原的表。右击Database，点击New Database。选中Device，点击 …输入你想要的数据库名字，并点击OK。选择数据库备份文件，点击OK。
如何用Restore DataBase把数据库还原到指定的路？
u012732371的专栏
12-20 1万+
 restore database 数据库名 from disk='备份文件路径' with move '数据库文件名' to '数据库文件放置路径', move '日志文件名' to '日志文件存放置路径' go 如： restore database EaseWe from disk='H:\EaseWe.bak' with move 'EaseWe' to
Sql server restore script（还原数据库正确的步骤）
weixin_33857679的博客
11-14 556
Database YourDB has full backup YourBaackUpFile.bak. It can be restored using following two steps. Step 1:Retrive the Logical file name of the database from backup.RESTOREFILEL...
(转载)RESTORE DATABASE命令还原SQLServer 2005 数据库
weixin_30783913的博客
04-15 172
　　 今天恢复一个SQLServer2008R2，发现问题，然后通过园友的文章解决了问题，特记录备用 原文地址：http://www.cnblogs.com/adandelion/archive/2006/09/30/519534.html 　　今天在sqlServer20005 的management studio里使用bak文件还原数据库的时候,总是失败! 　　Res...
Sqlserver数据库还原与备份
weixin_48175309的博客
09-10 2259
前言:我这个是Sqlserver2019英文版，版本大同小异，不过步骤一般都是一致的，步骤我写的都很详细 打开数据库，新建连接 自己建一个数据库test 一，备份数据库（点击新建的数据库test——Task(任务)——Back Up(备份)） 先删除，再添加 备份的路径 点OK -OK-OK 备份成功 二，还原数据库 新建一个test数据库——Task(任务)——Restore（还原）——Database(数...
sql-server数据库的备份与还原
qq_31532983的博客
06-20 488
选项来指定新的数据文件和日志文件的路径，这样 SQL Server 就会把还原的数据和日志放到新的位置，而不是尝试覆盖现有的文件。由于这些文件正在被使用，所以 SQL Server 无法直接覆盖它们。错误消息表明你尝试还原的备份文件。要解决这个问题，你需要使用。数据库使用的数据文件（
SQL SERVER 数据库备份与还原
枫的专栏
08-29 5513
指定应将给定的 logical_file_name 移到 operating_system_file_name。默认情况下，logical_file_name 将还原到其原始位置。语句将数据库复制到相同或不同的服务器上，则可能需要使用 MOVE 选项重。新定位数据库文件以避免与现有文件冲突。--返回由备份集内包含的数据库和日志文件列表组成的结果集。--指定还原后的数据库物理文件名称及路径。参数，则在现有数据库基础上强制还原。--还原由BACKUP备份的数据库。数据库内的每个逻辑文件。
SQL Server数据库备份与还原
一只没有感情的AI机械猿
11-23 1035
在 SQL Server 中，要还原（导入）数据库，可以使用 SQL Server Management Studio (SSMS) 或 Transact-SQL（T-SQL）语句。在 SQL Server 中，你可以使用 SQL Server Management Studio (SSMS) 或 Transact-SQL 语句来手动备份数据库。3. 在 "对象资源管理器" 中，展开 "数据库" 节点，找到你要备份的数据库。4. 右键单击要备份的数据库，选择 "任务" -> "备份"。WITH INIT;
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值