/* ------------------------ My Meta Content Here SEO ------------------------ */

Pages

Main Menu

Wednesday, August 28, 2019

SQL Database Cloning (Clone an existing database to a new database) Create database replica

Duplicating SQL Database

I need to essentially copy all of these tables and the data and the constraints exactly from one database to another.

The simplest backup command would be:

BACKUP DATABASE [db-prod] TO DISK = 'D:\dbbackup\db-prod.bak' WITH INIT;
Now to restore this as a different database, you need to know the file names because it will try to put the same files in the same place. So if you run the following:

EXEC [db-prod].dbo.sp_helpfile;
You should see output that contains the names and paths of the data and log files. When you construct your restore, you'll need to use these, but replace the paths with the name of the new database, e.g.:

RESTORE DATABASE [db-local] FROM DISK = 'D:\dbbackup\db-prod.bak'

WITH MOVE 'db-prod' TO 'C:\Program Files\Microsoft SQL 

Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\db-local.mdf',

MOVE 'db-prod_log' TO 'C:\Program Files\Microsoft SQL 

Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\db-local_log.ldf';

You'll have to replace dbname and newname with your actual database names, and also some folder and C:\path_from_sp_helpfile_output\ with your actual paths. I can't get more specific in my answer unless I know what those are.

Of course if the clone target (in this case DB-B) already exists, you'll want to drop it:

USE [master];

GO

IF DB_ID('db-local') IS NOT NULL

BEGIN

ALTER DATABASE [db-local] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE [db-local];

END

GO


No comments:

Post a Comment

My Blog List

  • काश - काश मुझे भी पीने की आदत होती,मैं कब का मुर्दा हो गया होता। छुटकारा मिलता आज के आतंकवाद से, किसी संतान भूमि में सो गया होता। मेरा एतबार कौन करेगा, मैंने मुर...
    2 months ago
  • काश - काश मुझे भी पीने की आदत होती,मैं कब का मुर्दा हो गया होता। छुटकारा मिलता आज के आतंकवाद से, किसी शमशान भूमि में सो गया होता। मेरा एतबार कौन करेगा, मैंने मुर...
    2 months ago
  • Kumaon University Nainital B.Ed entrance exam test result 2012 - कुमाऊँ विश्वविधालय, नैनीताल (उत्तराखण्ड)
    10 years ago