Table of Contents

{$page}

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

code_formatsql

– 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_formatsql

– 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