asked on

SQL Server 2005 - Copy Database on to same server

Hi,

I would like to make a copy of a sql server 2005 database and pop it on the same server. I can't use the detact / attach method as the source database must not be taken offline.

I have tried the Copy Database Wizard but it errors, this is the message in the event log.

SQL Server Scheduled Job 'CDW_HHB_HHB_1' (0xDD5CBC096305DA4FA761440F930281F4) - Status: Failed - Invoked on: 2006-07-07 10:51:29 - Message: The job failed. The Job was invoked by User HHB\Administrator. The last step to run was step 1 (CDW_HHB_HHB_1_Step).

In SQL Server 2000, i used to be able to copy objects in a DTS package (tables, views, stored procs).

I have tried creating a new database and exporting from the source to this but it only copys the tables and views, not the stored procs.

Has anyone else managed to take a full copy of a database and successfully create a new replication database on the same server using SQL Server 2005?

Thanks

Einstine98

Yes, many times...

1. Create a new database
2. Script your first database
3. Run the script on the new database.
4. Copy your data.... using DTS wizard

Jackass03

ASKER

How do I script the database?

thanks for your answer

Einstine98

click on the database in Management studio, right click and you hae the option to script it

Kobe_Lenjou

You can also make a backup of the DB, amd restore it as a new DB

Jackass03

ASKER

Hi

I have tried scripting the database and running the script in the new database. However it seems to produce some errors and not create all the stored procs. I have set the script to 'script all objects' but it doesnt seem to work ok.

Einstine98 - when you have done it in the past has your script produced errors when you have run it for your new database?

Thanks

Jackass03

ASKER

Kobe_Lenjou

If I try and restore the database from a backup as a new database it errors because of the difference in the database names.

Any more ideas?

ASKER CERTIFIED SOLUTION

Einstine98

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial