> 데이터 베이스 > MySQL 튜토리얼 > SQLSERVER拯救某个时间点被误删除的数据

SQLSERVER拯救某个时间点被误删除的数据

WBOY
풀어 주다: 2016-06-07 15:34:29
원래의
1003명이 탐색했습니다.

SQLSERVER 拯救 某个 时间 点被误 删除 的 数据 http://blog.csdn.net/dba_huangzj/article/details/8491327 要 拯救 某个 时间 点被误 删除 的 数据 ,需要你在那个 时间 点之前有一个完整 数据 库备份。 而且,需要你的 数据 库恢复模式为:完整恢复模式

SQLSERVER拯救某个时间点被误删除数据

http://blog.csdn.net/dba_huangzj/article/details/8491327

拯救某个时间点被误删除数据,需要你在那个时间点之前有一个完整数据库备份。

而且,需要你的数据库恢复模式为:“完整恢复模式”

如何查看你的数据库恢复模式为完整恢复模式,运行下面SQL

<span>1</span> <span>SELECT</span><span>  recovery_model, recovery_model_desc
</span><span>2</span> <span>FROM</span><span>    sys.databases
</span><span>3</span> <span>WHERE</span>   name <span>=</span> <span>'</span><span>GPOSDB</span><span>'</span>
로그인 후 복사

SQLSERVER拯救某个时间点被误删除的数据

在创建完一个新数据库之后,强烈建议做一次数据库完整备份

-------------------------------------------------------------------华丽的分割线-----------------------------------------------------------------------

先创建测试表

<span> 1</span> <span>/*</span>
<span> 2</span> <span>由于tempdb永远为简单恢复模式,所以不适合做案例。
</span><span> 3</span> <span>这里使用我的测试机器里的<strong>数据</strong>库GPOSDB
</span><span> 4</span> <span>*/</span>
<span> 5</span> <span>USE</span> <span>[</span><span>GPOSDB</span><span>]</span>
<span> 6</span> <span>GO</span>
<span> 7</span> <span>IF</span> <span>OBJECT_ID</span>(<span>'</span><span>testRestore</span><span>'</span>) <span>IS</span> <span>NOT</span> <span>NULL</span> 
<span> 8</span>     <span>DROP</span> <span>TABLE</span><span> testRestore
</span><span> 9</span> <span>GO</span>
<span>10</span> <span>CREATE</span> <span>TABLE</span><span> testRestore
</span><span>11</span> <span>    (
</span><span>12</span>       id <span>INT</span> <span>IDENTITY</span>(<span>1</span>, <span>1</span><span>) ,
</span><span>13</span>       NAME <span>VARCHAR</span>(<span>50</span><span>)
</span><span>14</span>     );
로그인 후 복사

插入测试数据

<span> 1</span> <span>--</span><span>插入测试<strong>数据</strong>:   </span>
<span> 2</span> <span>INSERT</span> <span>INTO</span><span> testRestore(Name)
</span><span> 3</span> <span>SELECT</span> <span>'</span><span>test1</span><span>'</span>
<span> 4</span> <span>UNION</span> <span>ALL</span> 
<span> 5</span> <span>SELECT</span> <span>'</span><span>test2</span><span>'</span>
<span> 6</span> <span>UNION</span> <span>ALL</span> 
<span> 7</span> <span>SELECT</span> <span>'</span><span>test3</span><span>'</span>
<span> 8</span> <span>UNION</span> <span>ALL</span> 
<span> 9</span> <span>SELECT</span> <span>'</span><span>test4</span><span>'</span>
<span>10</span> <span>UNION</span> <span>ALL</span> 
<span>11</span> <span>SELECT</span> <span>'</span><span>test5</span><span>'</span>
<span>12</span> <span>UNION</span> <span>ALL</span> 
<span>13</span> <span>SELECT</span> <span>'</span><span>test6</span><span>'</span>
<span>14</span> <span>UNION</span> <span>ALL</span> 
<span>15</span> <span>SELECT</span> <span>'</span><span>test7</span><span>'</span>
<span>16</span> <span>UNION</span> <span>ALL</span> 
<span>17</span> <span>SELECT</span> <span>'</span><span>test8</span><span>'</span>
로그인 후 복사

 

<span>1</span> <span>SELECT</span> <span>*</span> <span>FROM</span> testRestore
로그인 후 복사

SQLSERVER拯救某个时间点被误删除的数据

先来一个数据库完整备份,在22:32的时候完整备份数据

<span>1</span> <span>BACKUP</span> <span>DATABASE</span> <span>[</span><span>GPOSDB</span><span>]</span>
<span>2</span> <span>TO</span> <span>DISK</span><span>=</span><span>'</span><span>D:\GPOSDB_FULLBACKUP2013-07-23.bak</span><span>'</span>
<span>3</span> <span>WITH</span><span> INIT
</span><span>4</span> <span>go</span>
로그인 후 복사

模拟删除数据,这里使用waitfor time,目的是使删除数据时间准确一点,这里选择22:34的时候删除数据方便后面还原某个时间点之前的数据

<span>1</span> <span>USE</span> <span>[</span><span>GPOSDB</span><span>]</span>
<span>2</span> <span>GO</span>
<span>3</span> <span>WAITFOR</span> TIME <span>'</span><span>22:34</span><span>'</span>
<span>4</span> <span>DELETE</span> <span>FROM</span> dbo.testRestore
로그인 후 복사


查询一下数据是否删除

<span>1</span> <span>USE</span> <span>[</span><span>GPOSDB</span><span>]</span>
<span>2</span> <span>GO</span>
<span>3</span> <span>SELECT</span> <span>*</span> <span>FROM</span> dbo.testRestore
로그인 후 복사
로그인 후 복사

SQLSERVER拯救某个时间点被误删除的数据

断开与GPOSDB数据库的连接,如果还有连接就kill掉连接,然后执行事务日志备份,在22:36的时候执行事务日志备份

<span> 1</span> <span>--</span><span><strong>删除</strong>了表中的<strong>数据</strong>之后最少要等3分钟才执行下面SQL语句,不然的话等一下还原事务日志的时候会报错</span>
<span> 2</span> <span>--</span><span>必须断开所有与GPOSDB<strong>数据</strong>库的连接</span>
<span> 3</span> <span>USE</span><span> master
</span><span> 4</span> <span>GO</span>
<span> 5</span> <span>BACKUP</span> <span>LOG</span> <span>[</span><span>GPOSDB</span><span>]</span> <span>TO</span>  <span>DISK</span> <span>=</span> N<span>'</span><span>D:\GPOSDB_LOGBACKUP2013-07-23.bak</span><span>'</span> <span>WITH</span>  NO_TRUNCATE , NOFORMAT,  NAME <span>=</span> N<span>'</span><span>GPOSDB-事务日志备份</span><span>'</span>, SKIP,  NORECOVERY ,  STATS <span>=</span> <span>10</span><span>, CHECKSUM
</span><span> 6</span> <span>GO</span>
<span> 7</span> <span>DECLARE</span> <span>@backupSetId</span> <span>AS</span> <span>INT</span>
<span> 8</span> <span>SELECT</span>  <span>@backupSetId</span> <span>=</span><span> position
</span><span> 9</span> <span>FROM</span><span>    msdb..backupset
</span><span>10</span> <span>WHERE</span>   database_name <span>=</span> N<span>'</span><span>GPOSDB</span><span>'</span> <span>AND</span> backup_set_id <span>=</span> ( <span>SELECT</span>
<span>11</span>                                                               <span>MAX</span><span>(backup_set_id)
</span><span>12</span>                                                         <span>FROM</span><span>  msdb..backupset
</span><span>13</span>                                                         <span>WHERE</span> database_name <span>=</span> N<span>'</span><span>GPOSDB</span><span>'</span>
<span>14</span> <span>                                                      )
</span><span>15</span> <span>IF</span> <span>@backupSetId</span> <span>IS</span> <span>NULL</span>  <span>--</span><span>这里是验证事务日志备份是否成功,如果没有断开与GPOSDB<strong>数据</strong>库的连接备份事务日志可能会失败</span>
<span>16</span>     <span>BEGIN</span>
<span>17</span>         <span>RAISERROR</span>(N<span>'</span><span>验证失败。找不到<strong>数据</strong>库“GPOSDB”的备份信息。</span><span>'</span>, <span>16</span>, <span>1</span><span>)
</span><span>18</span>     <span>END</span>
<span>19</span> <span>RESTORE</span> VERIFYONLY <span>FROM</span>  <span>DISK</span> <span>=</span> N<span>'</span><span>D:\GPOSDB_LOGBACKUP2013-07-23.bak</span><span>'</span> <span>WITH</span>  <span>FILE</span> <span>=</span> <span>@backupSetId</span>
<span>20</span> <span>GO</span>
로그인 후 복사

SQLSERVER拯救某个时间点被误删除的数据

<span>1</span> <span>--</span><span>查询是否备份成功</span>
<span>2</span> <span>SELECT</span> <span>TOP</span> <span>10</span> <span>*</span> <span>FROM</span>    msdb..backupset <span>ORDER</span> <span>BY</span> <span>[</span><span>backup_set_id</span><span>]</span> <span>DESC</span> 
로그인 후 복사

SQLSERVER拯救某个时间点被误删除的数据

还原数据库完整备份

<span>1</span> <span>--</span><span><strong>数据</strong>库处于正在还原状态</span>
<span>2</span> <span>RESTORE</span> <span>DATABASE</span> <span>[</span><span>GPOSDB</span><span>]</span> <span>FROM</span> <span>DISK</span><span>=</span><span>'</span><span>D:\GPOSDB_FULLBACKUP2013-07-23.bak</span><span>'</span> <span>WITH</span> <span>replace</span>, NORECOVERY 
로그인 후 복사

SQLSERVER拯救某个时间点被误删除的数据

 还原事务日志

<span>1</span> <span>--</span><span>因为<strong>删除</strong><strong>数据</strong>是在'22:34' 所以还原到22:33</span>
<span>2</span> <span>RESTORE</span> <span>LOG</span> <span>[</span><span>GPOSDB</span><span>]</span> <span>FROM</span> <span>DISK</span><span>=</span><span>'</span><span>D:\GPOSDB_LOGBACKUP2013-07-23.bak</span><span>'</span> <span>WITH</span> RECOVERY ,STOPAT<span>=</span><span>'</span><span>2013-7-23 22:33</span><span>'</span>
로그인 후 복사

如果事务日志还原失败的话,可以使用GUI界面

SQLSERVER拯救某个时间点被误删除的数据

SQLSERVER拯救某个时间点被误删除的数据

查看表中的数据

<span>1</span> <span>USE</span> <span>[</span><span>GPOSDB</span><span>]</span>
<span>2</span> <span>GO</span>
<span>3</span> <span>SELECT</span> <span>*</span> <span>FROM</span> dbo.testRestore
로그인 후 복사
로그인 후 복사

SQLSERVER拯救某个时间点被误删除的数据

 -----------------------------------------------------------华丽的分割线--------------------------------------------

要注意的两个地方

(1)如果你发现误操作以后还有很多人做了操作,那么你还原成功后,别人的操作就会冲掉,所以发生误操作后,要马上停止别人对数据库的操作。

(2)这个方法要对数据库独占

원천:php.cn
본 웹사이트의 성명
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.
인기 튜토리얼
더>
최신 다운로드
더>
웹 효과
웹사이트 소스 코드
웹사이트 자료
프론트엔드 템플릿