How to duplicate MSSQL database on the same or another server?
Solution 1:
- backup the database
- restore the database under a new name and/or on another server
Both these operations can be performed from Enterprise Manager (SQL2000) or its replacement SQL Management Studio (SQL2005+), or the TSQL commands BACKUP and RESTORE.
Note: you can restore a database backed up from SQL2000 on SQL2005 with not issues in most cases, but not the other way around.
Solution 2:
Adding this as I stumbled upon this while following the steps above.
In SQL Server 2008 R2 at least you can skip a step for getting a backup local to the server.
- Right Click Databases
- Click Restore Database..
- Set the From database option to the database you are copying
- Set the To database option to the new name
- Click OK
Solution 3:
Probably this is the best solution:
1) On Source DB Server - Create backup to local file
DECLARE @fileName nvarchar(400);
DECLARE @DB_Name nvarchar(50);
SET @DB_Name = 'NameOfSourceDatabase'
SET @fileName = 'c:\Test\original.bak'
BACKUP DATABASE @DB_Name TO DISK = @fileName
2) Copy the Source File to Destination Server.
If both servers are on the same machine, you might want to create a copy of that file:
EXEC master.dbo.xp_cmdshell 'COPY c:\Test\original.bak c:\Test\clone.bak'
3) On Destination DB Server: Restore the database from backup file
RESTORE DATABASE CloneDB
FROM DISK='c:\Test\clone.bak'