How to duplicate MSSQL database on the same or another server?

Question

I am trying to accomplish the following:

1. Take a snapshot of the original database, create and exact a duplicate on another server?
2. Take a snapshot of the original database, create a duplicate on the same server under a different DB name

However, I cannot due to very limited experience with MSSQL. Any suggestions?

Additional info:

Origin DB server: SQL Server 2000

Destination DB server: SQL Server 2005

Answer 1

1. backup the database
2. 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.

Answer 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.

1. Right Click Databases
2. Click Restore Database..
3. Set the From database option to the database you are copying
4. Set the To database option to the new name
5. Click OK

Answer 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'

Answer 4

using MS SQLServer 2012, you need to perform 3 basic steps first, generate .sql file containing only the structure of the source DB => right click on the source DB and then Tasks then Generate Scripts => follow the wizard and u can save the .sql file locally Second, replace in .sql file the source db with the destination one => right click on the destination file and press New Query and Ctrl-H or (edit - find and replace - Quack replce) finally, populate with data => right click on the detination DB, then Tasks and then Import Data => Data source drop dow set to ".net framework data procider for sql server" + set connection string text field under DATA ex: Data Source=Mehdi\SQLEXPRESS;Initial Catalog=db_test;User ID=sa;Password=sqlrpwrd15 => Same thing to do with the destination => check the table you want to transfer or check box besides "source :....." to check all of them

you are done.