Thursday, 14 November 2013

Automated Script to restore SQL Server Database



SQL Server restore process is restoring given database from specified backup file. It is always recommended to use the data and log file location that are defined in the Server settings while creating new databases.

In some cases, we may have to create the user defined test databases in different location from Server settings. In this case while restoring, if we don’t specify the actual location of the physical database files (data and log], SQL Server restores it into default location which are defined in the SQL Server settings. We need to set the file location in the restore wizard.

Below mentioned automated script helps to restore it into the actual location of database files. Here we are passing only 2 parameters that are <Restore Database Name> and <Backup file location>

Restore Database Name:  TEST_OLD
Backup file location: D:\Backup\TEST_NEW.bak

Execution Step

EXEC USP_RESTORE_DATABASE ‘TEST_OLD', 'D:\Backup\TEST_NEW.bak'

Script

CREATE PROCEDURE USP_RESTORE_DATABASE
(
@Restore_Database_Name VARCHAR(100),
@BackupFilePath_WithFileName VARCHAR(1000)
)
AS
BEGIN

DECLARE @Sql VARCHAR(MAX),
                                                @Local_ServerName VARCHAR(100),
                                                @Data_File VARCHAR(1000),
                                                @Log_File VARCHAR(1000),
                                                @Data_LogicalName VARCHAR(100),
                                                @Log_LogicalName VARCHAR(100)

DECLARE @Backup_File_Parameter AS TABLE
                                                            (
                                                            LogicalName varchar(255),
                                                            PhysicalName varchar(255),
                                                            Type char(1),
                                                            FileGroupName varchar(50),
                                                            Size bigint,
                                                            MaxSize bigint,
                                                            FileId int,
                                                            CreateLSN numeric(30,2),
                                                            DropLSN numeric(30,2),
                                                            UniqueId uniqueidentifier,
                                                            ReadOnlyLSN numeric(30,2),
                                                            ReadWriteLSN numeric(30,2),
                                                            BackupSizeInBytes bigint,
                                                            SourceBlockSize int,
                                                            FileGroupId int,
                                                            LogGroupGUID uniqueidentifier,
                                                            DifferentialBaseLSN numeric(30,2),
                                                            DifferentialBaseGUID uniqueidentifier,
                                                            IsReadOnly int,
                                                            IsPresent int,
                                                            TDEThumbprint varchar(10)
                                              )

SELECT @Local_ServerName = @@SERVERNAME
                                            
SELECT
                                             @Data_File      = physical_name
FROM
                                             sys.master_files
WHERE
                                             physical_name LIKE '%' + @Restore_Database_Name + '%.mdf'

SELECT
                                             @Log_File        = physical_name
FROM
                                             sys.master_files
WHERE
                                             physical_name LIKE '%' + @Restore_Database_Name + '%.ldf'

INSERT INTO @Backup_File_Parameter
EXECUTE ( 'RESTORE FILELISTONLY FROM DISK = ''' + @BackupFilePath_WithFileName  + '''')

 SELECT @Data_LogicalName = [LogicalName] FROM @Backup_File_Parameter WHERE [Type] = 'D'

 SELECT @Log_LogicalName = [LogicalName] FROM @Backup_File_Parameter WHERE [Type] = 'L'

 SELECT @Sql =
               'USE [master]
               ALTER DATABASE [' + @Restore_Database_Name + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
               RESTORE DATABASE [' + @Restore_Database_Name + '] FROM DISK = ''' + @BackupFilePath_WithFileName  + '''
               WITH MOVE ''' + @Data_LogicalName + ''' TO ''' + @Data_File + ''',
                                                            MOVE ''' + @Log_LogicalName  + ''' TO ''' +  @Log_File  + ''',
               REPLACE
               ALTER DATABASE [' + @Restore_Database_Name + '] SET MULTI_USER'

EXEC(@Sql)

END