======  ======



===== Explanation of SQL =====



This stored procedure will 
  * Restore a Backup
  * Place the files in the path specified
  * DELETE the existing database with the same name (if that option is specified)



===== 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 =====



<code sql>
-- 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
</code>



===== SQL =====



<code 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;
--------------------------------------------------------------------------------------
</code>



===== Version Information =====
  * Entered : 12/2009
  * Version : All