Here 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