SqlServer批量压缩数据库日志-多数据库批量作业,批量备份还原

SqlServer批量压缩数据库日志-多数据库批量作业,批量备份还原--作业定时压缩脚本 多库批量操作 DECLARE @DatabaseName NVARCHAR(50) DECLARE @ExecuteSql NVARCHAR(MAX) SET @ExecuteSql='' DECLARE name_cursor CURSOR FOR SELECT name FROM master..sysdatabases WHERE name NOT IN ( 'master', 'model', 'msdb', 'tempdb', 'northwind','pubs' ) OPEN name_cursor; FETCH NEXT FROM name_cursor INTO @DatabaseName; WHILE @@FETCH_STATUS = 0 BEGIN SET @ExecuteSql ='' SET @ExecuteSql +=' USE ['+@DatabaseName+']; DECLARE @Error INT SET @Error=(SELECT TOP 1 size/128.0 - CAST(FILEPROPERTY([NAME], ''SpaceUsed'') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files ORDER BY [NAME] DESC) --PRINT @Error IF(@Error>1) BEGIN ALTER DATABASE ['+@DatabaseName+']  --数据库名字 SET RECOVERY SIMPLE;  --设置简单恢复模式 DBCC SHRINKFILE ([YiDianTongV2], 1);  --(M)不能小于1M, DBCC SHRINKFILE ([YiDianTongV2_log], 1);  --(M)不能小于1M ALTER DATABASE ['+@DatabaseName+'] SET RECOVERY FULL;  --恢复为原来完整模式 END ' PRINT @ExecuteSql; --打印 EXEC(@ExecuteSql) --执行 FETCH NEXT FROM name_cursor INTO @DatabaseName; END; CLOSE name_cursor; DEALLOCATE name_cursor; DECLARE @DatabaseName NVARCHAR(50) DECLARE @ExecuteSql NVARCHAR(MAX) SET @ExecuteSql='' DECLARE name_cursor CURSOR FOR SELECT name FROM master..sysdatabases WHERE name NOT IN ( 'master', 'model', 'msdb', 'tempdb', 'northwind','pubs') AND name LIKE '%mh%' OPEN name_cursor; FETCH NEXT FROM name_cursor INTO @DatabaseName; WHILE @@FETCH_STATUS = 0 BEGIN SET @ExecuteSql ='' SET @ExecuteSql ='USE ['+@DatabaseName+'];' SET @ExecuteSql +='这里换成要执行的sql语句' PRINT @ExecuteSql; --打印 --EXEC(@ExecuteSql) --执行 FETCH NEXT FROM name_cursor INTO @DatabaseName; END; CLOSE name_cursor; DEALLOCATE name_cursor; /************************************************************ * 数据备份脚本: * 1.运行前,请根据实际配置修改相应账户信息; * 2.请将脚本执行结果另存为批处理文件,执行批处理文件即可; ************************************************************/ DECLARE @name NVARCHAR(200) DECLARE @path NVARCHAR(500) DECLARE @dbServer NVARCHAR(500) DECLARE @dbUserName NVARCHAR(500) DECLARE @dbUserPwd NVARCHAR(500) --备份文件存放路径 SET @path = 'E:5_DataBase_bakup' --数据库服务器 SET @dbServer = '.' --数据库用户名 SET @dbUserName = 'sa' --数据库用户密码 SET @dbUserPwd = '123' DECLARE cursors CURSOR FOR --查询集合 SELECT [name] FROM [sysdatabases] WHERE NAME NOT IN ('master', 'tempdb', 'model', 'msdb') OPEN cursors FETCH NEXT FROM cursors INTO @name WHILE @@fetch_status = 0 BEGIN --遍历集合 PRINT 'osql -S ' + @dbServer + ' -U ' + @dbUserName + ' -P ' + @dbUserPwd + ' -Q "BACKUP DATABASE [' + @name + '] TO DISK = ''' + @path + '[%date:~0,4%%date:~5,2%%date:~8,2%]' + @name + '.bak''"' FETCH NEXT FROM cursors INTO @name END CLOSE cursors DEALLOCATE cursors/************************************************************ * 生成批量还原数据库脚本 ************************************************************/ DECLARE @name NVARCHAR(200) DECLARE @fileName NVARCHAR(200) DECLARE @path NVARCHAR(500) DECLARE @restorePath NVARCHAR(500) DECLARE @dbServer NVARCHAR(500) DECLARE @dbUserName NVARCHAR(500) DECLARE @dbUserPwd NVARCHAR(500) --数据库备份文件存放路径 SET @path = 'E:5_DataBase_bakup' --数据库还原后的文件存放路径 SET @restorePath = 'E:db2008' --还原到的数据库服务器 SET @dbServer = '192.168.0.7,1501SQL2008' --还原到的数据库用户名 SET @dbUserName = 'netuser' --还原到的数据库用户密码 SET @dbUserPwd = 'netuser2008' DECLARE cursors CURSOR FOR --查询集合 --注意:这里将数据库文件的物理路径替换掉了一部分,只保留数据库名称 SELECT [name], REPLACE (REPLACE(FILENAME, 'E:1.数据库', ''), '.mdf', '') AS [fileName] FROM [sysdatabases] WHERE NAME NOT IN ('master', 'tempdb', 'model', 'msdb') OPEN cursors FETCH NEXT FROM cursors INTO @name,@fileName WHILE @@fetch_status = 0 BEGIN --遍历集合 PRINT 'osql -S ' + @dbServer + ' -U ' + @dbUserName + ' -P ' + @dbUserPwd + ' -Q "RESTORE DATABASE [' + @name + '] FROM DISK = ''' + @path + @name + '.bak'' WITH FILE = 1, MOVE N''' + @fileName + ''' TO N''' + @restorePath + @name + '.mdf'', MOVE N''' + @fileName + '_log'' TO N''' + @restorePath + @name + '.LDF'', NOUNLOAD, REPLACE,STATS = 10' FETCH NEXT FROM cursors INTO @name,@fileName END CLOSE cursors DEALLOCATE cursors

相关内容推荐