{$page}

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)

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.

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

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

• Entered : 12/2009
• Version : All

• Backlinks include_pagepage_componentbacklinks