SQL Server数据库日常维护命令与脚本(2)

查看存储过程源代码：

-- 方法1 EXEC sp_helptext '<procedureName>' -- 方法2 SELECT * FROM SYS.SQL_MODULES WHERE object_id = OBJECT_ID(N'<procedureName>'); -- 方法3 SELECT s.text AS ProcedureText, s.encrypted AS Encrypted, s.number AS number, CONVERT(NCHAR(2), o.xtype) AS xtype, DATALENGTH(s.text) AS ProcedureLen FROM dbo.syscomments s, dbo.sysobjects o WHERE o.id = s.id AND s.id = OBJECT_ID(N'procedureName') ORDER BY s.number, s.colid OPTION ( ROBUST PLAN );

检查数据库完整性

DBCC checkdb(<DBName>) -- Tablock选项提高速度 DBCC checkdb(<DBName>) with tablock

2. 查看服务器环境信息

查看数据库所在机器操作系统参数

EXEC master..xp_msver;

详解：xp_msver返回有关 Microsoft SQL Server 的版本信息。
xp_msver 还返回有关服务器的实际内部版本号的信息以及服务器环境的有关信息，例如处理器类型(不能获取具体型号)， RAM 的容量等等。

查看数据库服务器磁盘分区剩余空间。

EXEC master.dbo.xp_fixeddrives;

查看数据库服务器CPU/内存的信息

SELECT cpu_count AS [Logical CPU Count] , hyperthread_ratio AS [Hyperthread Ratio] , cpu_count / hyperthread_ratio AS [Physical CPU Count], physical_memory_kb / 1024 AS [Physical Memory (MB)] , sqlserver_start_time FROM sys.dm_os_sys_info OPTION ( RECOMPILE ) ;

3. 数据库备份

备份数据库

declare @sql varchar(255) set @sql = 'backup database <DBName> to disk = ''<File Path>' + rtrim(convert(varchar, getdate(), 112)) + '.bak'''; exec(@sql);

删除15天前备份文件

declare @sql varchar(255); set @sql = 'del <File Path>' + rtrim(convert(varchar, getdate()-15), 112)) + '.bak'''; exec master..xp_cmdshell @sql;

完全备份

USE Master GO declare @str varchar(100) set @str='<FilePath>'+replace(replace(replace(convert(varchar,getdate(),20),'-',''),'',''),':','')+'.bak' BACKUP DATABASE <DBName> TO DISK=@str WITH ( RETAINDAYS=15, NOFORMAT, NOINIT, NAME=N'Demo完整备份', SKIP, NOREWIND, NOUNLOAD, STATS=10 ) GO

差异备份

USE Master GO declare @str varchar(100) set @str='<FilePath>'+replace(replace(replace(convert(varchar,getdate(),20),'-',''),'',''),':','')+'.diff' BACKUP DATABASE <DBName> TO DISK=@str WITH ( DIFFERENTIAL, RETAINDAYS=8, NOFORMAT, NOINIT, NAME=N'Demo差异备份', SKIP, NOREWIND, NOUNLOAD, STATS=10 ) GO

日志备份

USE Master GO declare @str varchar(100) set @str='<FilePath>'+replace(replace(replace(convert(varchar,getdate(),20),'-',''),'',''),':','')+'.trn' BACKUP LOG <DBName> TO DISK=@str WITH ( RETAINDAYS=3, NOFORMAT, NOINIT, NAME=N'Demo 日志备份', SKIP, NOREWIND, NOUNLOAD, STATS=10 ) GO

4. 数据库常用操作

数据库重命名、修改恢复模式、修改用户模式

-- 数据库重命名 ALTER DATABASE <DBName> MODIFY NAME = <NewDBName> --设置数据库为完整恢复模式 alter database <DBName> set recovery full --只允许一个用户访问数据库, 并指定10秒后回滚事务 alter database <DBName> set single_user with rollback after 10 seconds --只有sysadmin,dbcreator,db_owner角色的成员可以访问数据库 -- rollback immediate: 立即回滚事务 alter database <DBName> set restricted_user with rollback immediate --多用户模式 --不等待立即改变，如不能立即完成，那么会导致执行错误 alter database <DBName> set multi_user with no_wait

扩展数据库：增加文件组、增加文件、修改文件大小、修改文件的逻辑名称

--添加文件组 ALTER DATABASE <DBName> ADD FILEGROUP <FileGroup> --添加数据文件 ALTER DATABASE <DBName> ADD FILE ( NAME = <FileName>, FILENAME = '<FilePath>', SIZE = 1mb, MAXSIZE = 10mb, FILEGROWTH = 1mb ) TO FILEGROUP <FileGroup> --添加日志文件 ALTER DATABASE <DBName> ADD LOG FILE ( NAME = <LogName>, FILENAME = '<LogFilePath>', SIZE = 1MB, MAXSIZE = 10MB, FILEGROWTH = 100KB ) --修改数据文件的大小,增长大小,最大大小 ALTER DATABASE <DBName> MODIFY FILE ( NAME = <FileName>, SIZE = 2MB, --必须大于之前的大小,否则报错 MAXSIZE= 8MB, FILEGROWTH = 10% ) --修改数据文件或日志文件的逻辑名称 ALTER DATABASE <DBName> MODIFY FILE ( NAME = <FileName>, NEWNAME = <NewFileName> )