sqlserver常用数据库恢复操作

方式一: SQL Server数据库备份有两种方式&＃xff0c;一种是使用BACKUP DATABASE将数据库文件备份出去&＃xff0c;另外一种就是直接拷贝数据库文件mdf和日志文件ldf的方式。下面将主要讨论一下后者的备份与恢复。本文假定您能熟练使用SQL Server Enterprise Manager(SQL Server企业管理器)和SQL Server Quwey Analyser(SQL Server查询分析器) 1、正常的备份、SQL数据库恢复方式 正常方式下&＃xff0c;我们要备份一个数据库&＃xff0c;首先要先将该数据库从运行的数据服务器中断开&＃xff0c;或者停掉整个数据库服务器&＃xff0c;然后复制文件。 卸下数据库的命令&＃xff1a;Sp_detach_db 数据库名 连接数据库的命令&＃xff1a;Sp_attach_db或者sp_attach_single_file_db s_attach_db [&＃64;dbname &＃61;] ′dbname′, [&＃64;filename1 &＃61;] ′filename_n′ [,...16] sp_attach_single_file_db [&＃64;dbname &＃61;] ′dbname′, [&＃64;physname &＃61;] ′physical_name′ 使用此方法可以正确恢复SQL Sever7.0和SQL Server 2000的数据库文件&＃xff0c;要点是备份的时候一定要将mdf和ldf两个文件都备份下来&＃xff0c;mdf文件是数据库数据文件&＃xff0c;ldf是数据库日志文件。 例子&＃xff1a; 假设数据库为test&＃xff0c;其数据文件为test_data.mdf&＃xff0c;日志文件为test_log.ldf。下面我们讨论一下如何备份、恢复该数据库。 卸下数据库&＃xff1a;sp_detach_db &＃39;test&＃39; 连接数据库&＃xff1a;sp_attach_db &＃39;test&＃39;,&＃39;C:/Program Files/Microsoft SQL Server/MSSQL/Data/test_data.mdf&＃39;,&＃39;C:/Program Files/Microsoft SQL Server/MSSQL/Data/test_log.ldf&＃39; sp_attach_single_file_db &＃39;test&＃39;,&＃39;C:/Program Files/Microsoft SQL Server/MSSQL/Data/test_data.mdf&＃39; 2、只有mdf文件的恢复技术 由于种种原因&＃xff0c;我们如果当时仅仅备份了mdf文件&＃xff0c;那么恢复起来就是一件很麻烦的事情了。&＃xff08;此文章由飞客数据恢复中心搜集http://www.cellma.com.cn&＃xff09; 如果您的mdf文件是当前数据库产生的&＃xff0c;那么很侥幸&＃xff0c;也许你使用sp_attach_db或者sp_attach_single_file_db可以恢复数据库&＃xff0c;但是会出现类似下面的提示信息 设备激活错误。物理文件名 &＃39;C:/Program Files/Microsoft SQL Server/MSSQL/data/test_Log.LDF&＃39; 可能有误。 已创建名为 &＃39;C:/Program Files/Microsoft SQL Server/MSSQL/Data/test_log.LDF&＃39; 的新日志文件。 但是&＃xff0c;如果您的数据库文件是从其他计算机上复制过来的&＃xff0c;那么很不幸&＃xff0c;也许上述办法就行不通了。你也许会得到类似下面的错误信息 服务器: 消息 1813&＃xff0c;级别 16&＃xff0c;状态 2&＃xff0c;行 1 未能打开新数据库 &＃39;test&＃39;。CREATE DATABASE 将终止。 设备激活错误。物理文件名 &＃39;d:/test_log.LDF&＃39; 可能有误。 怎么办呢&＃xff1f;别着急&＃xff0c;下面我们举例说明恢复办法。 A&＃xff0e;我们使用默认方式建立一个供恢复使用的数据库(如test)。可以在SQL Server Enterprise Manager里面建立。 B&＃xff0e;停掉数据库服务器。 C&＃xff0e;将刚才生成的数据库的日志文件test_log.ldf删除&＃xff0c;用要恢复的数据库mdf文件覆盖刚才生成的数据库数据文件test_data.mdf。 D&＃xff0e;启动数据库服务器。此时会看到数据库test的状态为“置疑”。这时候不能对此数据库进行任何操作。 E&＃xff0e;设置数据库允许直接操作系统表。此操作可以在SQL Server Enterprise Manager里面选择数据库服务器&＃xff0c;按右键&＃xff0c;选择“属性”&＃xff0c;在“服务器设置”页面中将“允许对系统目录直接修改”一项选中。也可以使用如下语句来实现。 use master go sp_configure &＃39;allow updates&＃39;,1 go reconfigure with override go F&＃xff0e;设置test为紧急修复模式 update sysdatabases set status&＃61;-32768 where dbid&＃61;DB_ID(&＃39;test&＃39;) 此时可以在SQL Server Enterprise Manager里面看到该数据库处于“只读/置疑/脱机/紧急模式”可以看到数据库里面的表&＃xff0c;但是仅仅有系统表 G&＃xff0e;下面执行真正的恢复操作&＃xff0c;重建数据库日志文件 dbcc rebuild_log(&＃39;test&＃39;,&＃39;C:/Program Files/Microsoft SQL Server/MSSQL/Data/test_log.ldf&＃39;) 执行过程中&＃xff0c;如果遇到下列提示信息&＃xff1a; 服务器: 消息 5030&＃xff0c;级别 16&＃xff0c;状态 1&＃xff0c;行 1 未能排它地锁定数据库以执行该操作。 DBCC 执行完毕。如果 DBCC 输出了错误信息&＃xff0c;请与系统管理员联系。 说明您的其他程序正在使用该数据库&＃xff0c;如果刚才您在F步骤中使用SQL Server Enterprise Manager打开了test库的系统表&＃xff0c;那么退出SQL Server Enterprise Manager就可以了。 正确执行完成的提示应该类似于&＃xff1a;&＃xff08;此文章由飞客数据恢复中心搜集http://www.cellma.com.cn&＃xff09; 警告: 数据库 &＃39;test&＃39; 的日志已重建。已失去事务的一致性。应运行 DBCC CHECKDB 以验证物理一致性。将必须重置数据库选项&＃xff0c;并且可能需要删除多余的日志文件。 DBCC 执行完毕。如果 DBCC 输出了错误信息&＃xff0c;请与系统管理员联系。 此时打开在SQL Server Enterprise Manager里面会看到数据库的状态为“只供DBO使用”。此时可以访问数据库里面的用户表了。 H&＃xff0e;验证数据库一致性&＃xff08;可省略&＃xff09; dbcc checkdb(&＃39;test&＃39;) 一般执行结果如下&＃xff1a; CHECKDB 发现了 0 个分配错误和 0 个一致性错误&＃xff08;在数据库 &＃39;test&＃39; 中&＃xff09;。 DBCC 执行完毕。如果 DBCC 输出了错误信息&＃xff0c;请与系统管理员联系。 I&＃xff0e;设置数据库为正常状态 sp_dboption &＃39;test&＃39;,&＃39;dbo use only&＃39;,&＃39;false&＃39; 如果没有出错&＃xff0c;那么恭喜&＃xff0c;现在就可以正常的使用恢复后的数据库啦。 J&＃xff0e;最后一步&＃xff0c;我们要将步骤E中设置的“允许对系统目录直接修改”一项恢复。因为平时直接操作系统表是一件比较危险的事情。当然&＃xff0c;我们可以在SQL Server Enterprise Manager里面恢复&＃xff0c;也可以使用如下语句完成 sp_configure &＃39;allow updates&＃39;,0 go reconfigure with override go 方式二: 数据库置疑的处理方法 步骤1&＃xff1a; 创建一个新的数据库&＃xff0c;命名为原来数据库的名字。 步骤2&＃xff1a; 停止SQL Server 步骤3&＃xff1a; 把老数据库的MDF文件替换新数据库的相应的MDF文件&＃xff0c;并把LDF文件删除。 步骤4&＃xff1a; 重新启动SQL Server服务&＃xff0c;然后运行如下命令&＃xff1a; Use Master Go sp_configure &＃64;#allow updates&＃64;#, 1 reconfigure with override Go begin tran update sysdatabases set status &＃61; 32768 where name &＃61; &＃64;#db_name&＃64;# --Verify one row is updated before committing commit tran 步骤5&＃xff1a; 停止SQL然后重新启动SQL Server服务&＃xff0c;然后运行如下命令&＃xff1a; DBCC　TRACEON&＃xff08;3604&＃xff09; DBCC　REBUILD&＃xff3f;LOG&＃xff08;&＃64;#db_name&＃64;#,&＃64;#c:/mssql7/data/dbxxx_3.ldf&＃64;#&＃xff09; Go 步骤6&＃xff1a; 停止SQL然后重新启动SQL Server服务&＃xff0c;然后运行&＃xff1a; use master update sysdatabases set status &＃61; 8 where name &＃61; &＃64;#db_name&＃64;# Go sp_configue &＃64;#allow updates&＃64;#, 0 reconfigure with override Go 步骤7&＃xff1a; 运行dbcc checkdb(db_name) 检查数据库的完整性 注&＃xff1a;都要替换成真实的数据库名字。 方式三: 如果 SQL Server 因为磁盘可用空间不足&＃xff0c;而不能完成数据库的恢复&＃xff0c;那么 SQL Server 2000 会返回错误 1105 并且将 sysdatabases 中的 status 列设为置疑。 你可以看到在SQLSERVER 的ERROR LOG 和OS的应用程序日志中应该有1105的错误信息: SQL Server事务日志可能会被填满&＃xff0c;这会阻止之后的数据库操作&＃xff0c;包括UPDATE&＃xff0c; DELETE&＃xff0c; INSERT 和CHECKPOINT。 事务日志填满会导致1105错误: Can&＃39;t allocate space for object syslogs in database dbname because the logsegment is full。 If you ran out of space in syslogs&＃xff0c; dump the transaction log。 Otherwise use ALTER DATABASE or sp_extendsegment to increase the size of the segment。 这种现象可能出现于任何一个数据库中&＃xff0c;包括Master和TempDB。一些难以预见的因素可能消耗日志空间。 例如: 一个大型事务&＃xff0c; 尤其像批量数据更新、插入或删除。一个未提交的事务。检查点处理程序截除时所需的带宽过大。截除时超过阈值上述各种条件互相作用的结果。用于发布的标记事务没有被日志读取程序读走 下面是修复的步骤和收缩日志的步骤: 1.在命令提示符下运行以下命令启动 SQL Server&＃xff1a; SQLSERVER -f -m 备注&＃xff1a;-m 开关以单用户模式启动 SQL Server。在单用户模式下&＃xff0c;只能成功建立一个连接。 请注意是否有任何其他客户机或服务可能会在您通过 SQL Server 查询分析器 建立连接前使用那个连接。 2. 重置置疑数据库的状态。 sp_resetstatus &＃39;database_name&＃39; 下面是结果集&＃xff1a; Database&＃39;database_name&＃39;status reset! WARNING: You must reboot SQL Server prior to accessing this database! 3. 用 ALTER DATABASE 向数据库添加一个数据文件或日志文件: USE master GO CREATE DATABASE db_name ON ( NAME &＃61; dbname_dat1, FILENAME &＃61; &＃39;D:/MSSQL/Data/dbname_dat1.ndf&＃39;, SIZE &＃61; 1000MB, FILEGROWTH &＃61; 50MB ) GO --更改该数据库以添加一个 2GB 大小的新数据文件 ALTER DATABASE db_name ADD FILE ( NAME &＃61; dbname_dat2, FILENAME &＃61; &＃39;F:/MSSQL/DATA/dbname_dat2.ndf&＃39;, SIZE &＃61; 2000MB, FILEGROWTH &＃61; 50MB ) GO --更改该数据库以添加一个1GB 大小的新日志文件 ALTER DATABASE db_name ADD LOG FILE ( NAME &＃61; db_name_log2, FILENAME &＃61; &＃39;F:/MSSQL/Data/db_name_log2.ldf&＃39;, SIZE &＃61; 1000MB, FILEGROWTH &＃61; 20MB), GO 4. 停止并重新启动 SQL Server: 用新的数据文件或日志文件所提供的额外空间&＃xff0c;SQL Server 应该能完成数据库的恢复。 5. 释放磁盘空间并且重新运行恢复操作,按照下面的步骤收缩日志。 sp_resetstatus 关闭数据库的置疑标志&＃xff0c;但是原封不动地保持数据库的其它选项。 为从根本上解决这样的问题,你可以按下面的操作配置SQLSERVER 2000: a.如果不需要恢复到指定的时间点,你可以将数据库的恢复模式配置为简单,这样 UPDATE,DELETE,SELECT就不会记录日志,日志就不会增加的很大: USE MASTER GO ALTER DATABASE DB_NAME SET RECOVERY SIMPLE b.如果你的恢复模式是全部,你一定要配置日志字段收缩: USE MASTER GO sp_dboption &＃39;databasename&＃39;,&＃39;trunc. log on chkpt.&＃39;,true sp_dboption &＃39;databasename&＃39;,&＃39;autoshrink&＃39;,true c.通过每日备份将日志收缩: BACKUP DATABASE DATABASE_NAME TO BACKUP_DEVICES BACKUP LOG DATABASE_NAME TO LOG_DEVICES OR BACKUP LOG DATABASE_NAME with truncate_only **检查日志的容量:DBCC SQLPERF (LOGSPACE) 这时日志并没有收缩! d.每天在备份数据库完成之后,重新启动MS SQLSERVER SERVICE. USE DATABASE_NAME go DBCC SHRINKFILE(2,truncateonly) **检查日志的容量:DBCC SQLPERF (LOGSPACE) 这时日志已经收缩! e.手动快速收缩日志: / *run below script,you will shrink you database log files immediately, in my experience,you need to run the script for 3 or 4 minutes before stopping it manually */ use databasename dbcc shrinkfile(2,notruncate) dbcc shrinkfile(2,truncateonly) create table t1(char1 char(4000)) go declare &＃64;i int select &＃64;i&＃61;0 while(1&＃61;1) begin while(&＃64;i<100) begin INSERT INTO T1 VALUES (&＃39;A&＃39;) SELECT &＃64;I&＃61;&＃64;I&＃43;1 END TRUNCATE table T1 BACKUP LOG youdatabasename with truncate_only end GO 注意 只有在您的主要支持提供者指导下或有疑难解答建议的做法时&＃xff0c;才可以使用 sp_resetstatus。否则&＃xff0c;可能会损坏数据库。 由于该过程修改了系统表&＃xff0c;系统管理员必须在运行 sp_resetstatus这个过程前&＃xff0c;启用系统表更新。要启 用更新&＃xff0c;使用下面的过程&＃xff1a; USE master GO sp_configure &＃39;allow updates&＃39;, 1 GO RECONFIGURE WITH OVERRIDE GO 过程创建后&＃xff0c;立即禁用系统表更新&＃xff1a; sp_configure &＃39;allow updates&＃39;, 0 GO RECONFIGURE WITH OVERRIDE GO 只有系统管理员才能执行 sp_resetstatus。执行该过程后&＃xff0c;立即关闭 SQL Server。