July 25, 2016 at 2:52 am
Hi everyone,
I'm just wondering is this script that I wrote safe? Or could it be improved? I want to restore an existing backedup database - to a new name and location. I then want to query a relevent tabel in it to make sure it successfult restored. Then drop the restored database.
Any suggestions/comments appreciated:
Here is what I have so far:
--1. Get the most recent backup of the datbase in question
DECLARE @physical_device_name varchar(1000)
SELECT @physical_device_name = physical_device_name
FROM msdb.dbo.backupmediafamily
WHERE media_set_id =
(
SELECT TOP 1 media_set_id
FROM msdb.dbo.backupset
WHERE database_name='My_Database'
AND type='D'--D means full-backup type
ORDER BY backup_start_date DESC
)
--2. Restore to a new name and fiel location
RESTORE DATABASE My_Database_NEW_NAME
FROM DISK = @physical_device_name
WITH MOVE 'TestRestoreDatabaseData' TO 'D:\SQL2014\Data\My_Database_NEW_NAME.mdf',
MOVE 'TestRestoreDatabaseLog' TO 'E:\SQL2014\Logs\My_Database_NEW_NAME.ldf',
REPLACE
--3. Query a table in the resoted databse
select top 1 * from My_Database_NEW_NAME.SomeTable
--4. Email with relevent message
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'a@b.com',
@body = 'The stored procedure finished successfully.',
@subject = 'Automated Success Message' ;
--5. Remove the restored database
DROP DATABASE My_Database_NEW_NAME;
July 25, 2016 at 8:09 am
Afraid I do not know the criteria used to define safe or improved, but the code example looks like a basic start (as do all code examples:-)). Are there additional criteria, such as "every database has two files defined by identical logical names", and "every database has no more than one backup device"? And why is it necessary to use REPLACE when "new" is postulated? Perhaps it would be safer for the code to fail when a database file or a log file already exists. Similarly, how will the code handle a situation where a proposed new database name already exists? For this code to be safer, it should TRY CATCH and when caught, handle the ERROR_NUMBER. And who will be notified about failures (and how will failures be detected)?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply