Custom Stored Procedure To Restore A Database!
Thursday, December 20th, 2007Here is a stored procedure that will restore your database without having to remember the SQL syntax of the RESTORE DATABASE command in SQL Server 2000. This stored procedure is to be compiled and run from MSDB. The stored procedure reads from the registry to figure out the data and log path of the database files. To execute this stored procedure run the following statement:
exec pr_restore_database_new ‘ database name’, ‘the path of the backup file’, ‘backup type’
The backup types are: B = Full Backup, D = Differential Backup, L = Log Backup
The caveat here is that the way the stored procedure is written the database backup needs to be in its own backup folder. So for example, D:\DBBackups\Test\Test.bak
Here is the script:
CREATE procedure pr_restore_database_new(
@bk_name nvarchar(50),
@bk_path nvarchar(120),
@bk_type char(1)
)
as
set nocount on
declare @bk_file_name nvarchar(128)
declare @sql nvarchar(1000)
declare @file int
declare @data_path nvarchar(128)
declare @log_path nvarchar(128)
declare @last_lsn numeric(25,0)
set @file = 1
set @bk_file_name = @bk_name
exec master.dbo.xp_instance_regread N‘HKEY_LOCAL_MACHINE’,N‘Software\Microsoft\MSSQLServer\MSSQLServer’,N‘DefaultData’, @data_path output
exec master.dbo.xp_instance_regread N‘HKEY_LOCAL_MACHINE’,N‘Software\Microsoft\MSSQLServer\MSSQLServer’,N‘DefaultLog’, @log_path output
set @bk_path = @bk_path + @bk_name + ‘\’
Create Table #HeaderOnly(
BackupName nvarchar(128),
BackupDescription nvarchar(255),
BackupType smallint,
ExpirationDate datetime,
Compressed tinyint,
Position smallint,
DeviceType tinyint,
UserName nvarchar(128),
ServerName nvarchar(128),
DatabaseName nvarchar(128),
DatabaseVersion int,
DatabaseCreateionDate datetime,
BackupSize numeric(20, 0),
FirstLSN numeric(25, 0),
LastLSN numeric(25, 0),
CheckpointLSN numeric(25, 0),
DatabaseBackupLSN numeric(25, 0),
BackupStartDate datetime,
BackupFinishDate datetime,
SortOrder smallint,
CodePage smallint,
UnicodeLocaleId int,
UnicodeComarisonStyle int,
CompatibilityLevel tinyint,
SoftwareVendorId int,
SoftwareVersionMajory int,
SoftwareVersionMinor int,
SoftwareVersionBuild int,
MachineName nvarchar(128),
Flags int,
BindingID uniqueidentifier,
RecoverForkID uniqueidentifier,
Collation nvarchar(128)
)
Create Table #FileListOnly(
LogicalName nvarchar(128),
PhysicalName nvarchar(128),
Type char(1),
FileGroupName nvarchar(128),
Size numeric(20,0),
MaxSize numeric(20,0)
)
if(@bk_type = ‘B’)
Begin
Declare @LogicalName nvarchar(128)
Declare @PhysicalName nvarchar(128)
Declare @Type char(1)
–*************************** Get The Logical Names from the Backup ****************************
set @bk_file_name = @bk_file_name + ‘_full’
set @sql = ‘Restore FileListOnly From disk = ”’ + @bk_path + @bk_file_name + ‘.bak”’
insert into #FileListOnly
execute sp_executesql @sql
set @sql = ‘Restore Database ‘ + @bk_name + char(13)
set @sql = @sql + ‘From Disk = ”’ + @bk_path + @bk_file_name + ‘.bak”’ + char(13)
set @sql = @sql + ‘With’ + char(13)
Declare c1 cursor for
select LogicalName, reverse((left(reverse(PhysicalName), charindex(’\', reverse(PhysicalName)) - 1))) as PhysicalName, Type
from #FileListOnly
order by Type
open c1
fetch next from c1
into @LogicalName, @PhysicalName, @Type
while @@fetch_status = 0
begin
if(@Type = ‘D’)
begin
set @sql = @sql + char(9) + ‘Move ”’ + @logicalName + ”’ to ”’ + @data_path + @PhysicalName + ”’,’ + char(13)
end
else
begin
set @sql = @sql + char(9) + ‘Move ”’ + @logicalName + ”’ to ”’+ @log_path + @PhysicalName + ”’,’ + char(13)
end
fetch next from c1
into @LogicalName, @PhysicalName, @Type
end
close c1
deallocate c1
set @sql = @sql + char(9) + ‘Standby = ”’ + @data_path + @bk_name + ‘.stb”’
–print @sql
execute sp_executesql @sql
End
else if(@bk_type = ‘D’)
begin
set @bk_file_name = @bk_file_name + ‘_’ + cast(datepart(dw, getdate()) as varchar)
set @sql = ‘Restore HeaderOnly From disk = ”’ + @bk_path + @bk_file_name + ‘_Diff.bak”’
insert into #HeaderOnly
execute sp_executesql @sql
set @file = (select max(position) from #HeaderOnly)
truncate table #HeaderOnly
if(@file is not null)
begin
set @sql = ‘Restore Database ‘ + @bk_name + char(13)
set @sql = @sql + ‘From Disk = ”’ + @bk_path + @bk_file_name + ‘_Diff.bak”’ + char(13)
set @sql = @sql + ‘With’ + char(13)
set @sql = @sql + char(9) + ‘File = ‘ + cast(@file as varchar) + ‘, ‘ + char(13)
set @sql = @sql + char(9) + ‘Standby = ”’ + @data_path + @bk_name + ‘.stb”’
–print @sql
execute sp_executesql @sql
end
end
else if(@bk_type = ‘L’)
begin
while(@file is not null)
begin
set @sql = ‘Restore HeaderOnly From disk = ”’ + @bk_path + @bk_name + ‘_Log.bak”’
set @last_lsn = (select last_lsn from backupset
where backup_set_id = (select backup_set_id
from restorehistory
where restore_history_id = (select max(restore_history_id)
from restorehistory
where destination_database_name = @bk_name)))
insert into #HeaderOnly
execute sp_executesql @sql
set @file = (select position
from #HeaderOnly
where(FirstLsn <= @last_lsn and LastLsn > @last_lsn))
truncate table #HeaderOnly
if(@file is not null)
begin
set @sql = ‘Restore Log ‘ + @bk_name + char(13)
set @sql = @sql + ‘From Disk = ”’ + @bk_path + @bk_name + ‘_Log.bak”’ + char(13)
set @sql = @sql + ‘With’ + char(13)
set @sql = @sql + char(9) + ‘File = ‘ + cast(@file as varchar) + ‘, ‘ + char(13)
set @sql = @sql + char(9) + ‘Standby = ”’ + @data_path + @bk_name + ‘.stb”’
– print @sql
execute sp_executesql @sql
end
end
end
else if(@bk_type = ‘R’)
begin
set @sql = ‘Restore Database ‘ + @bk_name + char(13)
set @sql = @sql + ‘With’ + char(13)
set @sql = @sql + char(9) + ‘Recovery’
print @sql
–execute sp_execute @sql
end
Drop Table #FileListOnly
Drop Table #HeaderOnly
GO
