通用数据库备份还原脚本

--通用数据库还原脚本

 

--指定还原位置

DECLARE @targetFolder NVARCHAR(300)=N'd:\data\sql\MSSQL12.SQL2014\MSSQL\DATA'

DECLARE @backFolder NVARCHAR(300)=N'd:\temp'

DECLARE @srcFolder NVARCHAR(300)=N'e:\temp\debug\a'

 

--指定需要还原的数据库

DECLARE @dbNames VARCHAR(2000)='ZY_Cloud_Main,ZY_Cloud_FS,ZY_Cloud_Health,ZY_Cloud_BM'

 

SELECT *

INTO #t

FROM ( SELECT item dbName FROM dbo.f_split(@dbNames,',')

) t;

 

--先备份啊

DECLARE @subFolder NVARCHAR(300)

SET @subFolder=FORMAT(GETDATE(),'yyyy_MM_ddHHmmss_ffff')

SELECT dbo.f_makedir(@backFolder+'\'+@subFolder)

 

 

DECLARE @baksql NVARCHAR(max)=''

SELECT @baksql=@baksql+ dbo.f_format_s4('BACKUP DATABASE [{0}] TO DISK = N''{1}\{2}\{0}.bak'' WITH NOFORMAT, INIT, NAME = N''{0}-还原前的自动完整备份'', SKIP, NOREWIND, NOUNLOAD, STATS = 10',dbName,@backFolder,@subFolder,'') FROM #t

EXECute(@baksql)

 

CREATE TABLE #allFile

(

[DBName] NVARCHAR(100) ,

[LogicalName] NVARCHAR(128) ,

[PhysicalName] NVARCHAR(260)

);

 

CREATE TABLE #fileListTable

(

[LogicalName] 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) ,

[UniqueID] UNIQUEIDENTIFIER ,

[ReadOnlyLSN] NUMERIC(25, 0) ,

[ReadWriteLSN] NUMERIC(25, 0) ,

[BackupSizeInBytes] BIGINT ,

[SourceBlockSize] INT ,

[FileGroupID] INT ,

[LogGroupGUID] UNIQUEIDENTIFIER ,

[DifferentialBaseLSN] NUMERIC(25, 0) ,

[DifferentialBaseGUID] UNIQUEIDENTIFIER ,

[IsReadOnly] BIT ,

[IsPresent] BIT ,

[TDEThumbprint] VARBINARY(32) -- remove this column if using SQL 2005

     --,[SnapshotUrl] varchar(50) -- remove this column if useing sql 2012,2014 ,only 2016

);

 

 

 

DECLARE @sql NVARCHAR(MAX)= '';

DECLARE @dbName VARCHAR(200);

WHILE EXISTS ( SELECT 1

FROM #t )

BEGIN

SELECT TOP 1

@dbName = dbName

FROM #t;

SELECT @sql = dbo.f_format_s2('INSERT INTO #fileListTable EXEC

( ''restore filelistonly from disk=N''''{1}\{0}.bak'''' WITH FILE=1''

);    

        ', dbName, @srcFolder)

FROM #t

WHERE dbName = @dbName;

 

 

EXECUTE( @sql);

 

INSERT INTO #allFile

( DBName ,

LogicalName ,

PhysicalName

)

SELECT @dbName ,

LogicalName ,

PhysicalName

FROM #fileListTable;

SET @sql = '';

SELECT @sql = @sql + dbo.f_format_s4('MOVE N''{0}'' TO N''{2}\{1}'',',

LogicalName,

dbo.f_split_index(PhysicalName,

'\', -1),

@targetFolder,

'')

FROM #fileListTable;

 

 

SELECT @sql = dbo.f_format_s4(' EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N''{0}'';

            ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

            DROP DATABASE [{0}];

            RESTORE DATABASE [{0}] FROM DISK = N''{1}\{0}.bak'' WITH FILE = 1, {2} NOUNLOAD, STATS = 5;

', dbName, @srcFolder, @sql, '')

FROM #t

WHERE dbName = @dbName;

          

EXECUTE(@sql);

 

DELETE FROM #fileListTable;

DELETE FROM #t

WHERE dbName = @dbName;

 

END;

 

--SELECT *

--FROM #allFile;

 

 

 

DROP TABLE #t;

DROP TABLE #allFile;

DROP TABLE #fileListTable;

 

 

   

   

   

转载于:https://www.cnblogs.com/QinQouShui/p/7326327.html