SQLserver修复数据库相关

//更新系统表
sp_configure 'allow updates',1 reconfigure with override
sp_configure 'allow updates',0 reconfigure with override

//紧急模式
update sysdatabases set status =32768 where name='dbname'
update sysdatabases set status =28 where name='dbname'

//单用户模式
exec sp_dboption 'dbname','single','true'
exec sp_dboption 'dbname','single','false'
//检查与修复
dbcc checkdb(dbname)
dbcc checktable(tablename)

//sql2008紧急和正常
ALTER DATABASE 库名 SET EMERGENCY
ALTER DATABASE 库名 SET ONLINE

//获取有自增字段的表
SELECT table_name FROM INFORMATION_SCHEMA.COLUMNS  where (Select OBJECTPROPERTY(OBJECT_ID(Table_Name),'TableHasIdentity'))=1 group by table_name

//2获取有自增字段的表
select name from Sysobjects where xtype='U' and (select objectproperty(object_id(name),'tablehasidentity'))=1

//修复Seed的值 
Dbcc checkident(表名)

//重建日志
DBcc rebuild_log('库名','日志完整路径')

//查库中所有表的记录数
SELECT a.name, b.rows FROM sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id WHERE (a.type = 'u') AND (b.indid IN (0, 1)) ORDER BY b.rows DESC

//普通质疑修复步骤

--exec sp_configure 'allow updates',1 reconfigure with override
--update sysdatabases set status=32768 where name=‘数据库名称'
--dbcc rebuild_log('数据库名称','C:\Program Files (x86)\Microsoft SQL Server\MSSQL\Data\db_log.ldf')
--update sysdatabases set status=0 where name='数据库名称'
--restore database 数据库名称 with recovery
--exec sp_configure 'allow updates',0 reconfigure with override