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
-- 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
- Entered : 12/2009
- Version : All
You could leave a comment if you were logged in.