Posted By

DaveChild on 09/11/08


Tagged

database rebuild


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

nublaii


SQL Rebuild Dev Database


 / Published in: SQL
 

URL: http://weblogs.asp.net/mschwarz/archive/2004/08/26/220735.aspx

This script should be run from the "master" database. It will destroy the dev copy of a database and restore from the live copy.

Code slightly modified from original (linked).

  1. CREATE PROCEDURE RebuildDevDatabase AS
  2.  
  3. -- the original database (use 'SET @DB = NULL' to disable backup)
  4. DECLARE @DB varchar(200)
  5. SET @DB = 'LiveDatabase'
  6.  
  7. -- the backup filename
  8. DECLARE @BackupFile varchar(2000)
  9. SET @BackupFile = 'C:\Backups\SQL\RebuildDevDatabase\db_backup.dat'
  10.  
  11. -- the new database name
  12. DECLARE @TestDB varchar(200)
  13. SET @TestDB = 'DevDatabase'
  14.  
  15. -- the new database files without .mdf/.ldf
  16. DECLARE @RestoreFile varchar(2000)
  17. SET @RestoreFile = 'C:\Backups\SQL\RebuildDevDatabase\db_backup'
  18.  
  19.  
  20. -- ****************************************************************
  21. -- no change below this line
  22. -- ****************************************************************
  23.  
  24.  
  25. DECLARE @query varchar(2000)
  26.  
  27. DECLARE @DataFile varchar(2000)
  28. SET @DataFile = @RestoreFile + '.mdf'
  29.  
  30. DECLARE @LogFile varchar(2000)
  31. SET @LogFile = @RestoreFile + '.ldf'
  32.  
  33. IF @DB IS NOT NULL
  34. BEGIN
  35. SET @query = 'BACKUP DATABASE ' + @DB + ' TO DISK = ' + QUOTENAME(@BackupFile, '''') + ' WITH INIT, SKIP'
  36. SELECT @query
  37. EXEC (@query)
  38. END
  39.  
  40. -- RESTORE FILELISTONLY FROM DISK = 'C:\temp\backup.dat'
  41. -- RESTORE HEADERONLY FROM DISK = 'C:\temp\backup.dat'
  42. -- RESTORE LABELONLY FROM DISK = 'C:\temp\backup.dat'
  43. -- RESTORE VERIFYONLY FROM DISK = 'C:\temp\backup.dat'
  44.  
  45. IF EXISTS(SELECT * FROM sysdatabases WHERE name = @TestDB)
  46. BEGIN
  47. SET @query = 'ALTER DATABASE ' + @TestDB + ' SET SINGLE_USER with ROLLBACK IMMEDIATE'
  48. EXEC (@query)
  49. SET @query = 'DROP DATABASE ' + @TestDB
  50. EXEC (@query)
  51. END
  52.  
  53. RESTORE HEADERONLY FROM DISK = @BackupFile
  54. DECLARE @File int
  55. SET @File = @@ROWCOUNT
  56.  
  57. DECLARE @DATA varchar(500)
  58. DECLARE @Log varchar(500)
  59.  
  60. SET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@BackupFile , '''')
  61.  
  62. CREATE TABLE #restoretemp
  63. (
  64. LogicalName varchar(500),
  65. PhysicalName varchar(500),
  66. type varchar(10),
  67. FilegroupName varchar(200),
  68. size int,
  69. maxsize bigint
  70. )
  71. INSERT #restoretemp EXEC (@query)
  72.  
  73. SELECT @DATA = LogicalName FROM #restoretemp WHERE type = 'D'
  74. SELECT @Log = LogicalName FROM #restoretemp WHERE type = 'L'
  75.  
  76. PRINT @DATA
  77. PRINT @Log
  78.  
  79. TRUNCATE TABLE #restoretemp
  80. DROP TABLE #restoretemp
  81.  
  82. IF @File > 0
  83. BEGIN
  84. SET @query = 'RESTORE DATABASE ' + @TestDB + ' FROM DISK = ' + QUOTENAME(@BackupFile, '''') +
  85. ' WITH MOVE ' + QUOTENAME(@DATA, '''') + ' TO ' + QUOTENAME(@DataFile, '''') + ', MOVE ' +
  86. QUOTENAME(@Log, '''') + ' TO ' + QUOTENAME(@LogFile, '''') + ', FILE = ' + CONVERT(varchar, @File)
  87. EXEC (@query)
  88. END
  89. GO

Report this snippet  

You need to login to post a comment.