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.htmlselect name as dbfilename, type_desc as filetype, size/128.0 as filesizeinmb from sys.database_files where name='toppms'
