Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

3 Answers 3

up vote 10 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

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'
share|improve this answer

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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