CODEWITHSUNDEEP
sql-server
suresh j
suresh j

Reputation: 1

duplicating database

how to duplicate whole database in sql

Upvotes: 0

Views: 92

Answers (4)

Nagesh
Nagesh

Reputation: 1308

You can do with the following options:

  1. If you want to copy the database to another server with the same name, take full backup and restore this backup.
  2. Script the entire database and execute this script in new database. Now use SQL Server import/export wizard to import the data to new database. Please ensure that you have executed the script before you import, otherwise relationships and keys will be ignored.
  3. You can restore one database backup to another database through SQL Script: Here is the script:

RESTORE DATABASE [yourExistingDB] FROM DISK = 'C:\Backup\yourFullBackup.bak' WITH FILE = 1, MOVE 'newDB' TO 'C:\newDB.mdf', MOVE 'newDB_log' TO 'C:\newDB_log.ldf', NOUNLOAD, REPLACE, STATS = 10

GO

Upvotes: 0

Campey
Campey

Reputation: 390

The backup is the simplest, and the first thing that comes to mind.

Alternatively you can use the Schema and Data compare tools from RedGate. Or even the packager, depending on your need.

Run them comparing your current database to an empty database. They'll generate lovely scripts, and it's repeatable for differential copies in future.

We use them here, they're great.

Upvotes: 0

StuartLC
StuartLC

Reputation: 107387

Agree - backup and restore (under a different name if on the same server / instance).

If you need to keep them totally in synch after restoration, look at mirroring or log shipping (depending on what you need to do with the DB copy). Partial or conditional synchronisation can be done using replication.

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453910

The easiest way is to take a COPY_ONLY backup then restore it (optionally under a different name)

Upvotes: 4

Related Questions