1.最简单的办法是有数据库的全备份,然后恢复即可.
如果没有全备份,那就要用一些特殊的方法:
1.设置数据库为紧急模式
Use Master
go
sp_configure 'allow updates', 1
reconfigure with override
注:以上语句表示启用系统表更新,reconfigure with override 表示更新立即生效,否则要重启SQL后才生效。
UPDATE sysdatabases SET status = 32768 where name = 'WEWAY_JXC33'
注:32768指数据库壮态为紧急模式
2.停掉SQL Server服务:
NET STOP MSSQLSERVER
3.把原始数据库的数据文件WEWAY_JXC33.MDF,WEWAY_JXC33.LDF移走:
4.启动SQL Server服务:
NET START MSSQLSERVER
5.重新建立一个同名的数据库WEWAY_JXC33;
USE master
GO
CREATE DATABASE WEWAY_JXC33
ON
( NAME = WEWAY_JXC33,
FILENAME = 'C:\WeWaySoft\DataBase\WEWAY_JXC33.MDF',
SIZE = 10MB,
FILEGROWTH = 5MB )
LOG ON
( NAME = WEWAY_JXC33_LOG,
FILENAME = 'C:\WeWaySoft\DataBase\WEWAY_JXC33.LDF',
SIZE = 5MB,
FILEGROWTH = 5MB )
GO
6.设置数据库运行在单用户的模式:
USE MASTER
ALTER DATABASE WEWAY_JXC33 SET SINGLE_USER
或者
USE master
EXEC sp_dboption 'WEWAY_JXC33', 'single user', 'TRUE'
7.停掉SQL服务:
NET STOP MSSQLSERVER
8.把原来的数据文件再覆盖回来:
9.启动SQL Server服务:
NET START MSSQLSERVER
10.重新设置SQLSERVER的状态:
USE MASTER
EXEC sp_resetstatus "WEWAY_JXC33"
注:关闭数据库置疑状态
11.数据库完整性检测:
DBCC CHECKDB('WEWAY_JXC33')
12.恢复数据库为多用户模式:
USE MASTER
ALTER DATABASE WEWAY_JXC33 SET MULTI_USER
或者
USE master
EXEC sp_dboption 'WEWAY_JXC33', 'single user', 'FALSE'
13.恢复WEWAY_JXC33数据库到正常模式:
USE MASTER
UPDATE sysdatabases SET status = 4194320 where name = 'WEWAY_JXC33'
14.配置SQLSERVER不允许更新系统表:
USE MASTER
GO
sp_configure 'allow updates', 0
reconfigure with override
GO
15.重新启动MSSQLSERVER服务: