Advanced Search

SQL Rebuild Dev Database

/ Published in: SQL
Save to your folder(s)
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).

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  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

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

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.