Jan 5, 2015

Script to restore MS SQL database from a physical path

In case we want to automate the deployment of a MS SQL database, here is the script.

It will restore a database from a backup file, and store into the folder of your choice.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Dio Phung
-- Create date: 05/01/2015
-- Description: Restore DB from a physical path
-- =============================================
USE [master]
IF EXISTS(SELECT * from sys.[objects] AS o WHERE o.[type]='P' AND o.[name]='sp_RestoreDbFromPath')
BEGIN
DROP PROCEDURE sp_RestoreDbFromPath
END

GO

CREATE PROCEDURE sp_RestoreDbFromPath(
@db_name nvarchar(max), -- The name of the db
@db_source_fullpath NVARCHAR(max), -- path to the backup
@db_dest_folder nvarchar(max) -- folder to store db)
AS
BEGIN

SET NOCOUNT ON;
DECLARE @Table TABLE
(
LogicalName VARCHAR(128)
,[PhysicalName] VARCHAR(128)
,[Type] VARCHAR
,[FileGroupName] VARCHAR(128)
,[Size] VARCHAR(128)
,[MaxSize] VARCHAR(128)
,[FileId] VARCHAR(128)
,[CreateLSN] VARCHAR(128)
,[DropLSN] VARCHAR(128)
,[UniqueId] VARCHAR(128)
,[ReadOnlyLSN] VARCHAR(128)
,[ReadWriteLSN] VARCHAR(128)
,[BackupSizeInBytes] VARCHAR(128)
,[SourceBlockSize] VARCHAR(128)
,[FileGroupId] VARCHAR(128)
,[LogGroupGUID] VARCHAR(128)
,[DifferentialBaseLSN] VARCHAR(128)
,[DifferentialBaseGUID] VARCHAR(128)
,[IsReadOnly] VARCHAR(128)
,[IsPresent] VARCHAR(128)
,[TDEThumbprint] VARCHAR(128)
)

DECLARE @LogicalNameData VARCHAR(128),@LogicalNameLog VARCHAR(128)
INSERT INTO @table EXEC ('RESTORE FILELISTONLY FROM DISK=''' + @db_source_fullpath + ''' ')

SET @LogicalNameData = ( SELECT LogicalName FROM @Table WHERE Type = 'D' )
SET @LogicalNameLog = ( SELECT LogicalName FROM @Table WHERE Type = 'L')

DECLARE @RestoreSql nvarchar(max)
SET @RestoreSql = (
'RESTORE DATABASE [' + @db_name +'] FROM DISK = N'''+ @db_source_fullpath+ '''
WITH FILE = 1,
MOVE N'
''+ @LogicalNameData+ ''' TO N'''+ @db_dest_folder + '\'+@db_name+'.mdf'',
MOVE N'
''+ @LogicalNameLog+ ''' TO N'''+ @db_dest_folder + '\'+@db_name+'_log.ldf'',
NOUNLOAD, REPLACE, STATS = 10
'
)

SELECT @RestoreSql
EXEC(@RestoreSql)
END
GO

No comments:

Post a Comment