Posted By

plugables on 09/06/11


Tagged

sql mssql backup restore


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

Beppoi


Restore MSSQL database from backup (.bak)


 / Published in: SQL
 

  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  

You need to login to post a comment.