原文: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:\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 = 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:\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 = 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