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

4 Answers 4

  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.

  • 1
    Thank you David. How do I restore DB under a new name on another server though?
    – Alex N
    Sep 5, 2009 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. Sep 5, 2009 at 23:32
  • 1
    Cannot replace the file it's already in use... cannot change the names in the dialog either.. How can this solution function properly ?
    – JB.
    Jul 3, 2020 at 14:31
  • 1
    @JB.WithMonica. - sounds like you are trying to restore the database with a file name/location that matches one already in use by a database (perhaps the original or an earlier copy of the original). You need to specify new filenames or locations in that case, either in the relevant part of the UI or using the WITH MOVE ... TO ... syntax in the restore command. WRT "cannot change the names in the dialog either" - that sounds like a different issue (a problem with the tool, not the overall restore process). You should raise a new question about that, with screenshots and other details. Jul 6, 2020 at 9:21

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

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'
  • Well the restore fails... miserably... RESTORE DATABASE XXX2 => ` Impossible de remplacer le fichier 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\xxx.mdf' car il est utilisé par la base de données 'xxx' `
    – JB.
    Jul 3, 2020 at 14:35

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .