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
No comments:
Post a Comment