Posted By

thadwheeler on 05/17/09


Tagged

mssql duplicate


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

sulfurito


Duplicate SQL Server Database


 / Published in: SQL
 

replace paths and database names as needed MYDBHERE PATHONSERVER NEWDBNAME

  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  

You need to login to post a comment.