--获取每张表的行数 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 --清空日志文件 ALTER DATABASE {dbname} SET RECOVERY SIMPLE --简单模式 DBCC SHRINKFILE (N'{dbname}_log' , 11, TRUNCATEONLY) ALTER DATABASE {dbname} SET RECOVERY FULL --还原为完全模式 --删除备份文件 DECLARE @oldDate DATETIME SET @oldDate =dateadd(DAY,-5, GETDATE()) EXECUTE MASTER.dbo.xp_delete_file 0, --0: 备份文件,1: 维护计划文本报告 N'E:\', --文件路径 N'bak', --文件扩展名 @oldDate, --在此时间之前的文件一律删除 1 --删除子文件夹中的文件 -- 备份数据库命令 declare @name varchar(250) set @name='E:\{dbname}_'+ convert(varchar(50),getdate(),112)+cast(cast( rand()*1000 as int) as varchar)+'.bak' BACKUP DATABASE {dbname} TO DISK = @name WITH NOFORMAT, NOINIT, NAME = N'{dbname}_-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD ---------------自动还原数据库-------------- use master go ---声明变量 declare @dbName nvarchar(max)='{dbname}'; declare @dbFullName nvarchar(max)='E:\{dbname}.bak'; --1.1修改为单用模式 exec(N'ALTER DATABASE '+@dbName+' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'); --1.2结束链接进程 DECLARE @kid varchar(max) SET @kid='' SELECT @kid=@kid+'KILL '+CAST(spid as Varchar(10)) FROM master..sysprocesses WHERE dbid=DB_ID(@dbName) ; EXEC(@kid) ; --2.执行还原语句 restore database @dbName from disk=@dbFullName with replace --覆盖现有的数据库 --3.重置数据库为多用户模式 exec(N'ALTER DATABASE '+@dbName+' SET MULTI_USER WITH ROLLBACK IMMEDIATE');
如果您也喜欢它,动动您的小指点个赞吧