dynamic restore script dbmp backup file

[TWillard]

In sql 2000, I would like to write my own restore script. I am using the dbmp to administer the database and transaction log backup dumps. What I would like is a script ready for on demand use, that will query the user or system database appropriate to obtain the last *.bak dump file and restore it as a new user databas somewhere else.

My initial problem is that I can not find where the name of the backup files are stored. Can someone provide a table and column reference. I have looked in msdb's backupfile table and also msdb's sysdbmainplan* tables. The closest thing that I can find is the message column in the sysmaintplan_history table.

Is this value stored elsewhere without the message text and just the backup location filename? Additionally, does anyone have experience on creating a dynamic restore statement.

 

[mrdenny]

Backup information is stored in msdb.

The backup file is stored in backupmediafamily.
You'll also need the backupfile and backupmediaset tables.

The problems that I see will be:
1. How will your T/SQL script be connecting to the remote server to fire off the restore.
2. How will your T/SQL script know what drive(s) to do the restore to?

I'd recommend just writing restore commands as one of scripts. If you do restore a specific database often I'd just save the script on a network share for easy access.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[TWillard]

mrdenny,

Thanks for the assistance. You have some valid points. I still like the sysmaintence plan tables vs the bacupmediafamily, because I may throw the restore script as an extra step on the maintence plan job. It just makes sence to me.

1) I am not sure yet.

2) This is not an issue, because we use the same file and directory path for the data and log files of all our database in effort to mirror the envionrments between prod and test.

Here is a script to capture the backup file information and restore the database.
--This script would need to be modified for each database that would use this script/logic.
--It currently uses linked server to query the production database and server to get the the backup file name.
--It currently uses hardcoded data file and log file names and there disk locations on the restore statement.
--If this is a two servers environment, the second/restore server must have the sql server services running under a domain account and not the localsystem account. This user must have the permissions to read the production backup file from a unc path. Therefore, the restore will actually occur accross a unc path and network.

USE MSDB

-- Declare variables
DECLARE @backup varchar(200)
DECLARE @proddb varchar(50)
DECLARE @prodserver varchar(50)
DECLARE @testdb varchar(50)

-- Set parameters for script
set @proddb = 'prod_db_name'
set @prodserver = 'prod_server'
set @testdb = 'test_db_name'

-- Select the most recent backup file
select top 1 @backup = message
from SERVER_NAME.MSDB.DBO.SYSDBMAINTPLAN_HISTORY
where activity = 'Backup database'
and database_name = @proddb
and succeeded = 1
and start_time > GetDate() - 1
and end_time > getdate() - 1
order by end_time desc

-- Check to see that there is a valid backup
if (@backup is null) or (LEN(@backup) <= 0)
begin
print 'Could not find the latest backup file.'
end

-- Parse the backup file name into a qualified unc backup path and filename
print @backup
set @backup = right(@backup, len(@backup)- charindex('[',@backup))
set @backup = left(@backup, len(@backup) -1)
set @backup = replace(@backup, ':', '$')
set @backup = '\\' + @prodserver + '\' + @backup
print @backup

RESTORE DATABASE @testdb
FROM DISK = @backup
WITH MOVE 'datafilename' TO 'd:\MSSQL\data\testdb_is.mdf',
MOVE 'logfilename' TO 'd:\MSSQL\data\testdb_is_log.LDF',
STATS=1

 

[mrdenny]

Maintenance plan jobs should not be modified. If you do you can't modify the maintenance plan. If you do modify the maintenance plan the job step you added may be wipped out.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com