注意先使用存储脚本生成存储过程再调用存储过程生成语句。
无意在网上找到的,发现很好用,现转上来分享给大家。
---请先使用存储脚本生成存储过程---调用存储过程 USE [master] EXEC PROC_DBHY 'D:\databack\','D:\data\',2,2 GO --调用的时候,请根据实际需要进行修改: --第一个传入参数为指定备份文件所在路径,如D:\databack\ --第二个传入参数为数据文件存放路径,如D:\data\ --第三个传入参数:当需要直接取文件名称为数据库名称时,设置为2; --第四个传入参数:还原数据库为2,删除数据库为1(请慎重使用)--存储过程脚本USE [master]SET ANSI_NULLS ON goSET QUOTED_IDENTIFIER ON goCREATE PROCEDURE [dbo].[PROC_DBHY] (@DBWLLJ VARCHAR(MAX),@DBLJ VARCHAR(MAX),@j int=1 , @D INT = 1 ) --ALTER PROCEDURE [dbo].[PROC_DBHY] ( @DBWLLJ VARCHAR(MAX) , @DBLJ VARCHAR(MAX) , @J INT = 1 , @D INT = 1 ) AS DECLARE @DBNAME VARCHAR(MAX) , @CSQL VARCHAR(MAX) , @CSQL2 VARCHAR(MAX)PRINT '-------------------------------------------------------------------------------------'PRINT '**********************************开始生成还原语句***********************************'IF EXISTS ( SELECT id FROM tempdb..sysobjects WHERE id = OBJECT_ID('tempdb..#TB1') ) DROP TABLE #TB1 CREATE TABLE #TB1( DBMC VARCHAR(MAX) , DBJ1 BIT , DBJ2 INT )IF EXISTS ( SELECT id FROM tempdb..sysobjects WHERE id = OBJECT_ID('tempdb..#TB2') ) DROP TABLE #TB2 CREATE TABLE #TB2( NAME NVARCHAR(128) , PhysicalName NVARCHAR(260) , Type CHAR(1) , FileGroupName NVARCHAR(128) , Size NUMERIC(20, 0) , MaxSize NUMERIC(20, 0) , FileID BIGINT , CreateLSN NUMERIC(25, 0) , DropLSN NUMERIC(25, 0) NULL , UniqueID UNIQUEIDENTIFIER , ReadOnlyLSN NUMERIC(25, 0) NULL , ReadWriteLSN NUMERIC(25, 0) NULL , BackupSizeInBytes BIGINT , SourceBlockSize INT , FileGroupID INT , LogGroupGUID UNIQUEIDENTIFIER NULL , DifferentialBaseLSN NUMERIC(25, 0) NULL , DifferentialBaseGUID UNIQUEIDENTIFIER , IsReadOnly BIT , IsPresent BIT , TDEThumbprint VARBINARY(32), ) ---判断临时表是否存在,存在就删除SET @CSQL = ' INSERT INTO #TB1 exec master..xp_dirtree ''' + @DBWLLJ + ''',1,1 'EXEC (@CSQL) --PRINT @CSQLALTER TABLE #TB1 ADD KNAME VARCHAR(MAX), DS INT, DS1 INTDELETE #TB1WHERE DBJ2 = 0 OR DBMC NOT LIKE '%.BAK' IF @J = 1 BEGIN UPDATE #TB1 SET DS1 = CHARINDEX('_backup', DBMC) - 1 WHERE CHARINDEX('_backup', DBMC) > 1 UPDATE #TB1 SET KNAME = SUBSTRING(DBMC, 1, DS1) --PRINT 'CHU1' ENDIF @J = 2 BEGIN UPDATE #TB1 SET DS = CHARINDEX('.BAK', DBMC) - 1 WHERE CHARINDEX('.BAK', DBMC) > 1 UPDATE #TB1 SET KNAME = SUBSTRING(DBMC, 1, DS) --PRINT 'CHU2' --获取逻辑文件名 ENDALTER TABLE #TB1 ADD NAME1 VARCHAR(MAX),NAME2 VARCHAR(MAX) --SELECT * FROM #TB1DECLARE LJWJM CURSORFORSELECT DBMCFROM #TB1OPEN LJWJM FETCH NEXT FROM LJWJM INTO @DBNAMEWHILE ( @@FETCH_STATUS <> -1 ) BEGIN SET @CSQL = 'INSERT INTO #TB2 Exec(''RESTORE FILELISTONLY FROM DISK =''''' + @DBWLLJ + @DBNAME + ''''''') ' EXEC (@CSQL) --PRINT @CSQL SET @CSQL2 = 'UPDATE #TB1 SET NAME1=(SELECT name FROM #TB2 WHERE Type=''D'') ,NAME2=(SELECT name FROM #TB2 WHERE Type=''L'') WHERE DBMC=''' + @DBNAME + '''' --KNAME='''+@DBNAME+'''' EXEC (@CSQL2) --PRINT @CSQL2 EXEC ('DELETE FROM #TB2') FETCH NEXT FROM LJWJM INTO @DBNAME ENDCLOSE LJWJMDEALLOCATE LJWJM IF @D = 1 BEGIN SELECT 'DROP DATABASE ' + KNAME FROM #TB1 END ELSE BEGIN SELECT 'USE master' UNION ALL SELECT 'RESTORE DATABASE [' + KNAME + '] FROM DISK=N''' + @DBWLLJ + '' + DBMC + ''' WITH replace, MOVE N''' + NAME1 + ''' TO N''' + @DBLJ + '' + KNAME + '.mdf'', MOVE N''' + NAME2 + ''' TO N''' + @DBLJ + '' + KNAME + '_LOG.ldf'', NOUNLOAD , REPLACE GO ' FROM #TB1 ENDPRINT '-------------------------------------------------------------------------------------'PRINT '-----**********************************生成语句成功***********************************-----' GO