Advanced Search

Restore MSSQL database from backup (.bak)

/ Published in: SQL
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. DECLARE @BAK_FILE_PATH VARCHAR(300)
  2. DECLARE @DB_TO_RESTORE_TO VARCHAR(300)
  3. DECLARE @SQL_SERVER_DATA_FOLDER VARCHAR(300)
  4.  
  5. /* [BAK_FILE_PATH] : Path of backup file. e.g; D:\DatabaseBackup\MyOldDB.bak */
  6. SET @BAK_FILE_PATH = 'D:\DatabaseBackup\MyOldDB.bak'
  7.  
  8. /* [DB_TO_RESTORE_TO] : Database to restore to. Should already exist. */
  9. SET @DB_TO_RESTORE_TO = 'MyNewDB'
  10.  
  11. /* [SQL_SERVER_DATA_FOLDER] : Sql server data folder. Usually C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data */
  12. SET @SQL_SERVER_DATA_FOLDER = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data'
  13.  
  14.  
  15. /*
  16.   Nothing to edit below this line
  17. */
  18.  
  19. DECLARE @LOGICAL_NAME_Data VARCHAR(300)
  20. DECLARE @LOGICAL_NAME_Log VARCHAR(300)
  21. DECLARE @query VARCHAR(3000)
  22.  
  23. /* Step 1 : Get the logical file names */
  24. SET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@BAK_FILE_PATH , '''')
  25.  
  26. CREATE TABLE #restoretemp
  27. (
  28. LogicalName nvarchar(128)
  29. ,Old_PhysicalName nvarchar(128)
  30. ,[TYPE] CHAR(1)
  31. ,FileGroupName nvarchar(128)
  32. ,[SIZE] NUMERIC(20,0)
  33. ,[MaxSize] NUMERIC(20,0)
  34. ,FileID BIGINT
  35. ,CreateLSN NUMERIC(25,0)
  36. ,DropLSN NUMERIC(25,0) NULL
  37. ,UniqueID uniqueidentifier
  38. ,ReadOnlyLSN NUMERIC(25,0)
  39. ,ReadWriteLSN NUMERIC(25,0)
  40. ,BackupSizeInByte BIGINT
  41. ,SourceBlockSize INT
  42. ,FilegroupID INT
  43. ,LogGroupGUID uniqueidentifier NULL
  44. ,DifferentialBaseLSN NUMERIC(25,0)
  45. ,DifferentialbaseGUID uniqueidentifier
  46. ,IsReadOnly bit
  47. ,IsPresent bit
  48. )
  49.  
  50. INSERT #restoretemp EXEC (@query)
  51.  
  52. SELECT @LOGICAL_NAME_Data = LogicalName FROM #restoretemp WHERE [TYPE] = 'D'
  53. SELECT @LOGICAL_NAME_Log = LogicalName FROM #restoretemp WHERE [TYPE] = 'L'
  54.  
  55. PRINT @LOGICAL_NAME_Data
  56. PRINT @LOGICAL_NAME_Log
  57.  
  58. TRUNCATE TABLE #restoretemp
  59. DROP TABLE #restoretemp
  60.  
  61. /* Step 2 : Restore the database */
  62. SET @query = 'ALTER DATABASE ' + @DB_TO_RESTORE_TO + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
  63. EXEC (@query)
  64.  
  65. SET @query = 'RESTORE DATABASE ' + @DB_TO_RESTORE_TO 
  66. + ' FROM DISK = ' + QUOTENAME(@BAK_FILE_PATH , '''') 
  67. + ' WITH REPLACE, ' 
  68. + ' MOVE ' + QUOTENAME(@LOGICAL_NAME_Data, '''') + ' TO ' + QUOTENAME((@SQL_SERVER_DATA_FOLDER+'\'+@DB_TO_RESTORE_TO+'.mdf') , '''')
  69. + ', MOVE ' + QUOTENAME(@LOGICAL_NAME_Log, '''') + ' TO ' + QUOTENAME((@SQL_SERVER_DATA_FOLDER+'\'+@DB_TO_RESTORE_TO+'_log.ldf'), '''') 
  70. EXEC (@query)
  71.  
  72. SET @query = 'ALTER DATABASE ' + @DB_TO_RESTORE_TO + ' SET MULTI_USER'
  73. EXEC (@query)

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.