数据库异常维护操作？ [复制链接]

toppms数据库

USE [master]
GO
ALTER DATABASE toppms SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE toppms SET RECOVERY SIMPLE   --简单模式
GO
USE toppms
GO
DBCC SHRINKFILE (N'toppms_Log' , 1, TRUNCATEONLY)
GO
USE [master]
GO
ALTER DATABASE toppms SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE toppms SET RECOVERY FULL  --还原为完全模式
GO



toppms2数据库

USE [master]
GO
ALTER DATABASE toppms2 SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE toppms2 SET RECOVERY SIMPLE   --简单模式
GO
USE toppms2
GO
DBCC SHRINKFILE (N'toppms2_Log' , 1, TRUNCATEONLY)
GO
USE [master]
GO
ALTER DATABASE toppms2 SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE toppms2 SET RECOVERY FULL  --还原为完全模式
GO



USE [master]
GO
ALTER DATABASE topsms SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE topsms SET RECOVERY SIMPLE   --简单模式
GO
USE topsms
GO
DBCC SHRINKFILE (N'topsms_Log' , 1, TRUNCATEONLY)
GO
USE [master]
GO
ALTER DATABASE topsms SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE topsms SET RECOVERY FULL  --还原为完全模式
GO



如何消除数据库质疑？

ALTER DATABASE toppms SET EMERGENCY
go
ALTER DATABASE toppms SET SINGLE_USER  ----修改数据库为单用户模式
go
DBCC CheckDB (toppms,REPAIR_ALLOW_DATA_LOSS)   ----修复数据库
go
DBCC CheckDB (toppms,REPAIR_REBUILD)  ----重建
go
ALTER DATABASE toppms SET MULTI_USER   ----恢复多用户
go


数据库联机
ALTER DATABASE toppms SET ONLINE




USE MASTER
GO
SP_CONFIGURE 'ALLOW UPDATES',1 RECONFIGURE WITH OVERRIDE
GO
ALTER DATABASE toppms SET EMERGENCY
GO
sp_dboption 'toppms', 'single user', 'true'
GO
DBCC CHECKDB('toppms','REPAIR_ALLOW_DATA_LOSS')
GO
ALTER DATABASE toppms SET ONLINE
GO
sp_configure 'allow updates', 0 reconfigure with override
GO
sp_dboption 'toppms', 'single user', 'false'
GO






查询数据库当前连接数
SELECT * FROM
[Master].[dbo].[SYSPROCESSES] WHERE [DBID] IN ( SELECT
[DBID]
FROM
[Master].[dbo].[SYSDATABASES]
WHERE
NAME='toppms'
)


ALTER DATABASE toppms SET EMERGENCY
go
ALTER DATABASE toppms SET SINGLE_USER  ----修改数据库为单用户模式
go
dbcc checktable('SF_FEERECS',REPAIR_ALLOW_DATA_LOSS)
go
dbcc checktable('SF_FEERECS',REPAIR_REBUILD)
go
ALTER DATABASE toppms SET MULTI_USER   ----恢复多用户
go



重建索引
DBCC DBREINDEX（表名）




从bak文件恢复数据库

RESTORE DATABASE toppms
  
FROM DISK = 'c:\toppms20171005.bak'
  
WITH MOVE 'toppms' TO 'd:\拓扑物业管理系统\database\toppms.mdf',
  
MOVE 'toppms_log' TO 'd:\拓扑物业管理系统\database\toppms_log.ldf',
  
STATS = 10, REPLACE
  
GO


restore database EaseWe
from disk='H:\EaseWe.bak'
with move 'EaseWe'
to 'c:\Program Files\Microsoft SQL Server\MSSQL\data\EaseWe.mdf',
move 'EaseWe_log'
to 'c:\Program Files\Microsoft SQL Server\MSSQL\data\EaseWe_LOG.LDF'
go



断开数据库所有连接
USE master
GO
ALTER DATABASE [toppms] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
--查看是否还有用户连接
SELECT * FROM sys.[sysprocesses] WHERE DB_NAME([dbid])='toppms'
GO
ALTER DATABASE [toppms] SET MULTI_USER
GO






RESTORE DATABASE toppms
  
FROM DISK = 'D:\拓扑物业管理系统\四川嘉木物业管理有限公司\toppms2021-05-25 22.00.02.bak'
  
WITH MOVE 'toppms' TO 'D:\拓扑物业管理系统\四川嘉木物业管理有限公司\database\toppms.mdf',
  
MOVE 'toppms_log' TO 'D:\拓扑物业管理系统\四川嘉木物业管理有限公司\database\toppms_log.ldf',
  
STATS = 10, REPLACE
  
GO






创建清理日志的存储过程的方法
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE   PROCEDURE [dbo].[clearlog]
  AS
    SET  NOCOUNT  ON
ALTER DATABASE toppms SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE toppms SET RECOVERY SIMPLE   --简单模式
DBCC SHRINKFILE (N'toppms_Log' , 1, TRUNCATEONLY)
ALTER DATABASE toppms SET RECOVERY FULL WITH NO_WAIT
ALTER DATABASE toppms SET RECOVERY FULL  --还原为完全模式








使用命令行执行.sql文件
用微软自带的sqlcmd工具，可以导入执行。以SQL Server 2008R版本为例：

第一步：Win+R 键入：cmd 命令，开启命令行工具；

第二步：键入：cd D:\Program Files\Microsoft SQL Server\110\Tools\Binn （具体目录路径跟你安装的SQL位置有关）

cd D:\Program Files\Microsoft SQL Server\110\Tools\Binn

cd C:\Program Files\Microsoft SQL Server\120\Tools\Binn

cd D:\Program Files\Microsoft SQL Server\110\Tools\Binn

第三步：键入：sqlcmd -S .\SQL2005 -U sa -P 13251337616 -d toppms -i D:\SQL.sql

sqlcmd -S .\SQL2005 -U sa -P 13251337616 -d toppms -i D:\clearlog.sql

参数说明：-S 服务器地址 -U 用户名 -P 密码  -d 数据库名称 -i 脚本文件路径












https://www.cnblogs.com/daikeda/p/16281550.html





select name as dbfilename, type_desc as filetype, size/128.0 as filesizeinmb from sys.database_files where name='toppms'




update SYSUSERKEY set KEY_VALUE='1.25' where key_id='win_font_rate'

update SYSUSERKEY set KEY_VALUE='1' where key_id='browser_rate_bywin'

update SYSUSERKEY set KEY_VALUE='1' where key_id='print_exelb'