Creating an exact copy of the production database.

[tayorolls]

Hi,

I would like to create an exact copy of the existing database and rename it as HR_Staging.

I first created a new database - HR_Staging.

Then using the Import Data tool, I imported everything - tables and views from the original database HR into HR_Staging database.

However I see the views created as tables and saved within the Tables and not within the Views.

Also the default values as in the original databases when we create a new row (For instance, IsActive is a column of type "bit". It has a default value of 1. That is not being carried over to the new table of the new database. How can I ensure that all rules, integrety of data, foreign keys-primary keys relationships are carried over?

I have a backup file of HR database on my local drive. How can I build the HR_Staging off the backup file on my local drive.

If I am posting this question in the wrong forum, please pardon me.

Thanks.

 

[ESquared]

The best way to copy a database is to restore a backup.

Get out of ALL connections to the database. Including Query Analyzer/ Studio, etc. The task pane in Query Analyzer counts as a connection. If you can't figure out what's still connected, see sysprocesses or use enterprise manager to see what's still on.

restoring a database can be done with the wizards. The t-sql syntax is:

RESTORE DATABASE HR_Staging FROM DISK = 'C:\BackupFileName'

you will have to do a

WITH MOVE DataLogicalFilename TO 'C:\NewDataLiteralFilename',
WITH MOVE TlogLogicalFilename TO 'C:\NewTlogLiteralFilename'

and if you want to rename the logical file names for some reason, it's an

ALTER DATABASE HR_Staging ...

but you'll have to lookup the exact syntax for all of these as this is off the top of my head...

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]