Advanced Search

Duplicate SQL Server Database

/ Published in: SQL
Save to your folder(s)
replace paths and database names as needed
MY_DB_HERE
PATH_ON_SERVER
NEW_DB_NAME

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. USE master
  2. GO
  3.  
  4. -- the original database (use 'SET @DB = NULL' to disable backup)
  5. DECLARE @DB VARCHAR(200)
  6. SET @DB = 'MY_DB_NAME'
  7.  
  8. -- the backup filename
  9. DECLARE @BackupFile VARCHAR(2000)
  10. SET @BackupFile = 'PATH_ON_SERVER'
  11.  
  12. -- the new database name
  13. DECLARE @TestDB VARCHAR(200)
  14. SET @TestDB = 'NEW_DB_NAME'
  15.  
  16. -- the new database files without .mdf/.ldf
  17. DECLARE @RestoreFile VARCHAR(2000)
  18. SET @RestoreFile = 'PATH_ON_SERVER'
  19.  
  20. -- ****************************************************************
  21. -- no change below this line
  22. -- ****************************************************************
  23.  
  24. DECLARE @query VARCHAR(2000)
  25.  
  26. DECLARE @DataFile VARCHAR(2000)
  27. SET @DataFile = @RestoreFile + '.mdf'
  28.  
  29. DECLARE @LogFile VARCHAR(2000)
  30. SET @LogFile = @RestoreFile + '.ldf'
  31.  
  32. IF @DB IS NOT NULL
  33. BEGIN
  34.  
  35. SET @query = 'BACKUP DATABASE ' + @DB + ' TO DISK = ' + QUOTENAME(@BackupFile, '''')
  36. EXEC (@query)
  37.  
  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.  
  48. SET @query = 'DROP DATABASE ' + @TestDB
  49. EXEC (@query)
  50.  
  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.  
  72. INSERT #restoretemp EXEC (@query)
  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.  
  88. EXEC (@query)
  89.  
  90. END
  91.  
  92. GO

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.