SQL Server的文件恢复技术
SQL Server有两种备份方式,一种是使用BACKUP DATABASE将数据库文件备份出去,另外一种就是直接拷贝数据库文件mdf和日志文件ldf的方式。本文中,我们将主要介绍一下后者的备份与恢复。(本文中假定您目前已经能够熟练使用Server企业管理器和SQL Server查询分
SQL Server有两种备份方式,一种是使用BACKUP DATABASE将数据库文件备份出去,另外一种就是直接拷贝数据库文件mdf和日志文件ldf的方式。本文中,我们将主要介绍一下后者的备份与恢复。(本文中假定您目前已经能够熟练使用Server企业管理器和SQL Server查询分析器)
1、正常的备份、恢复方式
正常方式下,我们要备份一个数据库,首先要先将该数据库从运行的数据服务器中断开,或者停掉整个数据库服务器,然后复制文件。
卸下数据库的命令:
Sp_detach_db 数据库名
连接数据库的命令:
Sp_attach_db或者sp_attach_single_file_db s_attach_db [@dbname =] ′dbname′, [@filename1 =] ′filename_n′ [,...16] sp_attach_single_file_db [@dbname =] ′dbname′, [@physname =] ′physical_name′
使用此方法可以正确恢复SQL Sever 7.0和SQL Server 2000的数据库文件,要点是备份的时候一定要将mdf和ldf两个文件都备份下来,mdf文件是数据库数据文件,ldf是数据库日志文件。
例子:
假设数据库为test,其数据文件为test_data.mdf,日志文件为test_log.ldf。下面我们讨论一下如何备份、恢复该数据库。
卸下数据库:
sp_detach_db ’test’
连接数据库:
sp_attach_db ’test’,’C:Program FilesMicrosoft SQL ServerMSSQLDatatest_data.mdf’,’C:Program FilesMicrosoft SQL ServerMSSQLDatatest_log.ldf’sp_attach_single_file_db ’test’,’C:Program FilesMicrosoft SQL ServerMSSQLDatatest_data.mdf’
2、只有mdf文件的恢复技术
由于种种原因,我们如果当时仅仅备份了mdf文件,那么恢复起来就是一件很麻烦的事情了。
如果您的mdf文件是当前数据库产生的,那么很侥幸,也许你使用sp_attach_db或者sp_attach_single_file_db可以恢复数据库,但是会出现类似下面的提示信息:
设备激活错误。物理文件名 ’C:Program FilesMicrosoft SQL ServerMSSQLdatatest_Log.LDF’可能有误。
已创建名为’C:Program FilesMicrosoft SQL ServerMSSQLDatatest_log.LDF’的新日志文件。
但是,如果您的数据库文件是从其他计算机上复制过来的,那么很不幸,也许上述办法就行不通了。你也许会得到类似下面的错误信息:
服务器: 消息 1813,级别 16,状态 2,行 1
未能打开新数据库 ’test’。CREATE DATABASE 将终止。
设备激活错误。物理文件名 ’d:test_log.LDF’ 可能有误。
应该怎么办呢?下面我们举例说明恢复办法。
A、我们使用默认方式建立一个供恢复使用的数据库(如test)。可以在SQL Server Enterprise Manager里面建立。
B、停掉数据库服务器。
C、将刚才生成的数据库的日志文件test_log.ldf删除,用要恢复的数据库mdf文件覆盖刚才生成的数据库数据文件test_data.mdf。
D、启动数据库服务器。此时会看到数据库test的状态为“置疑”。这时候不能对此数据库进行任何操作。
E、设置数据库允许直接操作系统表。此操作可以在SQL Server Enterprise Manager里面选择数据库服务器,按右键,选择“属性”,在“服务器设置”页面中将“允许对系统目录直接修改”一项选中。也可以使用如下语句来实现。
use master go sp_configure ’allow updates’,1 go reconfigure with override go
F、设置test为紧急修复模式
update sysdatabases set status=-32768 where dbid=DB_ID(’test’)
此时可以在SQL Server Enterprise Manager里面看到该数据库处于“只读置疑脱机紧急模式”可以看到数据库里面的表,但是仅仅有系统表。
G、下面执行真正的恢复操作,重建数据库日志文件
dbcc rebuild_log(’test’,’C:Program FilesMicrosoft SQL ServerMSSQLDatatest_log.ldf’)
执行过程中,如果遇到下列提示信息:
服务器: 消息 5030,级别 16,状态 1,行 1
未能排它地锁定数据库以执行该操作。
DBCC执行完毕。如果DBCC输出了错误信息,请与系统管理员联系。
说明您的其他程序正在使用该数据库,如果刚才您在F步骤中使用SQL Server Enterprise Manager打开了test库的系统表,那么退出SQL Server Enterprise Manager就可以了。
正确执行完成的提示应该类似于:
警告:数据库 ’test’的日志已重建。已失去事务的一致性。应运行DBCC CHECKDB以验证物理一致性。将必须重置数据库选项,并且可能需要删除多余的日志文件。
DBCC执行完毕。如果DBCC输出了错误信息,请与系统管理员联系。
此时打开在SQL Server Enterprise Manager里面会看到数据库的状态为“只供DBO使用”。此时可以访问数据库里面的用户表了。
H、验证数据库一致性(可省略)
dbcc checkdb(’test’)
一般执行结果如下:
CHECKDB发现了0个分配错误和0个一致性错误(在数据库 ’test’ 中)。
DBCC执行完毕。如果DBCC输出了错误信息,请与系统管理员联系。
I、设置数据库为正常状态
sp_dboption ’test’,’dbo use only’,’false’
假如没有出错,,现在你就可以正常的使用恢复后的数据库啦。
J、最后一步,我们要将步骤E中设置的“允许对系统目录直接修改”一项恢复。因为平时直接操作系统表是一件比较危险的事情。当然,我们可以在SQL Server Enterprise Manager里面恢复,也可以使用如下语句完成。
sp_configure ’allow updates’,0 go reconfigure with override go

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

On Douyin, a short video platform full of creativity and vitality, we can not only enjoy a variety of exciting content, but also have in-depth communications with like-minded friends. Among them, chat sparks are an important indicator of the intensity of interaction between the two parties, and they often inadvertently ignite the emotional bonds between us and our friends. However, sometimes due to some reasons, the chat spark may be disconnected. So what should we do if we want to restore the chat spark? This tutorial guide will bring you a detailed introduction to the content strategy, hoping to help everyone. How to restore the spark of Douyin chat? 1. Open the Douyin message page and select a friend to chat. 2. Send messages and chat to each other. 3. If you send messages continuously for 3 days, you can get the spark logo. On a 3-day basis, send pictures or videos to each other

When deleting or decompressing a folder on your computer, sometimes a prompt dialog box "Error 0x80004005: Unspecified Error" will pop up. How should you solve this situation? There are actually many reasons why the error code 0x80004005 is prompted, but most of them are caused by viruses. We can re-register the dll to solve the problem. Below, the editor will explain to you the experience of handling the 0x80004005 error code. Some users are prompted with error code 0X80004005 when using their computers. The 0x80004005 error is mainly caused by the computer not correctly registering certain dynamic link library files, or by a firewall that does not allow HTTPS connections between the computer and the Internet. So how about

Quark Netdisk and Baidu Netdisk are currently the most commonly used Netdisk software for storing files. If you want to save the files in Quark Netdisk to Baidu Netdisk, how do you do it? In this issue, the editor has compiled the tutorial steps for transferring files from Quark Network Disk computer to Baidu Network Disk. Let’s take a look at how to operate it. How to save Quark network disk files to Baidu network disk? To transfer files from Quark Network Disk to Baidu Network Disk, you first need to download the required files from Quark Network Disk, then select the target folder in the Baidu Network Disk client and open it. Then, drag and drop the files downloaded from Quark Cloud Disk into the folder opened by the Baidu Cloud Disk client, or use the upload function to add the files to Baidu Cloud Disk. Make sure to check whether the file was successfully transferred in Baidu Cloud Disk after the upload is completed. That's it

StableDiffusion3’s paper is finally here! This model was released two weeks ago and uses the same DiT (DiffusionTransformer) architecture as Sora. It caused quite a stir once it was released. Compared with the previous version, the quality of the images generated by StableDiffusion3 has been significantly improved. It now supports multi-theme prompts, and the text writing effect has also been improved, and garbled characters no longer appear. StabilityAI pointed out that StableDiffusion3 is a series of models with parameter sizes ranging from 800M to 8B. This parameter range means that the model can be run directly on many portable devices, significantly reducing the use of AI

Recently, many netizens have asked the editor, what is the file hiberfil.sys? Can hiberfil.sys take up a lot of C drive space and be deleted? The editor can tell you that the hiberfil.sys file can be deleted. Let’s take a look at the details below. hiberfil.sys is a hidden file in the Windows system and also a system hibernation file. It is usually stored in the root directory of the C drive, and its size is equivalent to the size of the system's installed memory. This file is used when the computer is hibernated and contains the memory data of the current system so that it can be quickly restored to the previous state during recovery. Since its size is equal to the memory capacity, it may take up a larger amount of hard drive space. hiber

This paper explores the problem of accurately detecting objects from different viewing angles (such as perspective and bird's-eye view) in autonomous driving, especially how to effectively transform features from perspective (PV) to bird's-eye view (BEV) space. Transformation is implemented via the Visual Transformation (VT) module. Existing methods are broadly divided into two strategies: 2D to 3D and 3D to 2D conversion. 2D-to-3D methods improve dense 2D features by predicting depth probabilities, but the inherent uncertainty of depth predictions, especially in distant regions, may introduce inaccuracies. While 3D to 2D methods usually use 3D queries to sample 2D features and learn the attention weights of the correspondence between 3D and 2D features through a Transformer, which increases the computational and deployment time.

WindowsServerBackup is a function that comes with the WindowsServer operating system, designed to help users protect important data and system configurations, and provide complete backup and recovery solutions for small, medium and enterprise-level enterprises. Only users running Server2022 and higher can use this feature. In this article, we will explain how to install, uninstall or reset WindowsServerBackup. How to Reset Windows Server Backup If you are experiencing problems with your server backup, the backup is taking too long, or you are unable to access stored files, then you may consider resetting your Windows Server backup settings. To reset Windows

Xiaohongshu is a popular social e-commerce platform where users can share shopping experiences, life details, etc. During use, some users may experience their comments being deleted. So, how to restore deleted comments on Xiaohongshu? 1. How to restore deleted comments on Xiaohongshu? If it is found that a comment has been deleted by mistake, users can choose to wait for the official Xiaohongshu team to restore it. In this case, it’s best to be patient and wait as the official team may automatically process and resume comments after a while. If you find that a comment has been deleted, consider republishing similar content. But when reposting, please make sure the content complies with Xiaohongshu’s community guidelines to avoid being removed again. 3. Contact Xiaohongshu customer service: If you think your comment has been mistakenly
