Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

It is perhaps a bit stupid question, but I must admit I have very limited experience with MSSQL and yet has to accomplish this:

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

Help!!! :(

Additional info:

Origin DB server: SQL Server 2000

DB server on another machine: SQL Server 2005

share|improve this question
up vote 13 down vote accepted
  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.

share|improve this answer
Thank you David. How do I restore DB under a new name on another server though? – Alex N Sep 5 '09 at 14:03
Backing up the database produces a backup file (or extends an existing one). Transfer this file to the other server then use Management Studio (or a RESTORE command issued some other way) to restore a database from that file. The restore process offers you the opportunity to dictate a name for the database once it is restored. – David Spillett Sep 5 '09 at 23:32
Thank you David, it worked well! – Alex N Sep 8 '09 at 4:30

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
share|improve this answer
That's the quick way! Thanks – piertoni Jun 1 '15 at 8:28

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'


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


FROM DISK='c:\Test\clone.bak'
share|improve this answer

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.

share|improve this answer

protected by Community Nov 29 '15 at 20:59

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.