原文:
--作业定时压缩脚本 多库批量操作DECLARE @DatabaseName NVARCHAR(50)DECLARE @ExecuteSql NVARCHAR(MAX)SET @ExecuteSql=''DECLARE name_cursor CURSORFOR 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 CURSORFOR 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:\05_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 = 0BEGIN --遍历集合 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 @nameEND 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:\05_DataBase_bakup\' --数据库还原后的文件存放路径 SET @restorePath = 'E:\db2008\' --还原到的数据库服务器SET @dbServer = '192.168.0.7,1501\SQL2008' --还原到的数据库用户名 SET @dbUserName = 'netuser' --还原到的数据库用户密码 SET @dbUserPwd = 'netuser2008' DECLARE cursors CURSOR FOR --查询集合 --注意：这里将数据库文件的物理路径替换掉了一部分，只保留数据库名称 SELECT [name], REPLACE (REPLACE(FILENAME, 'E:\01.数据库\', ''), '.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 = 0BEGIN --遍历集合 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,@fileNameEND CLOSE cursors DEALLOCATE cursors