Is this safe?

  • 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;

  • 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