How to make a copy sql database in same server?

Question

i want to make a copy sql database in same server. for example: i have a database 'main2012' i want a copy of 'main2012' as 'main2013' in same server .

Sudhakar Chaudhary · Answer

this is not working.... also find it from there. http://weblogs.asp.net/mschwarz/archive/2004/08/26/220735.aspx http://stackoverflow.com/questions/443421/sql-2005-quick-way-to-quickly-duplicate-a-database-data make a backup of database , make a database with desire name restore backup in new blank database at the option 'overwrite the exiting database' thanks all of you.

Akkiraju Ivaturi · Answer

Also there is an excellent tool available in codeplex for the same: http://dbcopytool.codeplex.com/

Akkiraju Ivaturi · Answer

USE master GO -- the original database (use 'SET @DB = NULL' to disable backup) DECLARE @DB varchar(200) SET @DB = 'PcTopp' -- the backup filename DECLARE @BackupFile varchar(2000) SET @BackupFile = 'c:\pctopp\sqlserver\backup.dat' -- the new database name DECLARE @TestDB varchar(200) SET @TestDB = 'TestDB' -- the new database files without .mdf/.ldf DECLARE @RestoreFile varchar(2000) SET @RestoreFile = 'c:\pctopp\sqlserver\backup' -- **************************************************************** -- no change below this line -- **************************************************************** DECLARE @query varchar(2000) DECLARE @DataFile varchar(2000) SET @DataFile = @RestoreFile + '.mdf' DECLARE @LogFile varchar(2000) SET @LogFile = @RestoreFile + '.ldf' IF @DB IS NOT NULL BEGIN SET @query = 'BACKUP DATABASE ' + @DB + ' TO DISK = ' + QUOTENAME(@BackupFile, '''') EXEC (@query) END -- RESTORE FILELISTONLY FROM DISK = 'C:	emp\backup.dat' -- RESTORE HEADERONLY FROM DISK = 'C:	emp\backup.dat' -- RESTORE LABELONLY FROM DISK = 'C:	emp\backup.dat' -- RESTORE VERIFYONLY FROM DISK = 'C:	emp\backup.dat' IF EXISTS(SELECT * FROM sysdatabases WHERE name = @TestDB) BEGIN SET @query = 'DROP DATABASE ' + @TestDB EXEC (@query) END RESTORE HEADERONLY FROM DISK = @BackupFile DECLARE @File int SET @File = @@ROWCOUNT DECLARE @Data varchar(500) DECLARE @Log varchar(500) SET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@BackupFile , '''') CREATE TABLE #restoretemp ( LogicalName varchar(500), PhysicalName varchar(500), type varchar(10), FilegroupName varchar(200), size int, maxsize bigint ) INSERT #restoretemp EXEC (@query) SELECT @Data = LogicalName FROM #restoretemp WHERE type = 'D' SELECT @Log = LogicalName FROM #restoretemp WHERE type = 'L' PRINT @Data PRINT @Log TRUNCATE TABLE #restoretemp DROP TABLE #restoretemp IF @File > 0 BEGIN SET @query = 'RESTORE DATABASE ' + @TestDB + ' FROM DISK = ' + QUOTENAME(@BackupFile, '''') + ' WITH MOVE ' + QUOTENAME(@Data, '''') + ' TO ' + QUOTENAME(@DataFile, '''') + ', MOVE ' + QUOTENAME(@Log, '''') + ' TO ' + QUOTENAME(@LogFile, '''') + ', FILE = ' + CONVERT(varchar, @File) EXEC (@query) END GO

Hemant Kumar · Answer

Sudhakar , You need to Check this Links first http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/b2e78801-6e04-4b9b-b9c7-ee0c3703499a/ http://www.dbforums.com/microsoft-sql-server/977738-copy-database-same-sql-server.html

How to make a copy sql database in same server?

Answers (4)