Table of Contents

Explanation of SQL

This stored procedure will

Risk of Data Corruption if Run Improperly

High. Running this query incorrectly could delete your existing database (if you restore a backup on top of it). Do not run this except under the direction of a Cyrious Technical Support staff member. Doing otherwise may result in lost or contaminated data. All data modifications done through direct SQL are permanent and non-reversable.

Sample Usage

-- Restore a backup from D:\SQL\Backups, 
-- name it StoreDataCopy, 
-- and put the data on D:\SQL\Data\   (note the trailing backslash)
EXEC Restore_Backup 'StoreDataCopy', 'D:\SQL\Backups\StoreData20091207.bak', 'D:\SQL\Data\', 0

SQL

--------------------------------------------------------------------------------------
-- Section: Restore_Backup Procedure
--
-- This section defines a stored procedure that restores a backup of a database
--
--------------------------------------------------------------------------------------
 
CREATE PROCEDURE Restore_Backup
        @DatabaseName VARCHAR(50),
        @BackupFilePath VARCHAR(255),
        @DatabaseFilePath VARCHAR(255),
        @DeleteIfExists bit = 0
AS
BEGIN
  DECLARE @S VARCHAR(MAX);
 
  IF EXISTS(SELECT * FROM sys.DATABASES WHERE name = @DatabaseName ) 
  BEGIN
    IF  ( @DeleteIfExists=1 )
    BEGIN
      SET @S =
           'ALTER DATABASE '+@DatabaseName+' SET single_user WITH rollback immediate; '+
           'DROP DATABASE '+@DatabaseName + '; ';
      EXECUTE(@S);
    END
    ELSE
      RETURN; -- Nothing to do
  END;
  SET @S =
      'restore DATABASE '+@DatabaseName + ' ' +
      'FROM DISK = '''+@BackupFilePath + '''  ' +
      'WITH '+
        'Recovery, '+
        'Move ''EmptyDataSet_Data'' TO '''+@DatabaseFilePath+@DatabaseName+'.MDF'', ' +
        'Move ''EmptyDataSet_Log''  TO '''+@DatabaseFilePath+@DatabaseName+'.LDF'' ';
  EXECUTE (@S);
END;
--------------------------------------------------------------------------------------

Version Information