?
Solved

SQL Server 2005 - Copy Database on to same server

Posted on 2006-07-07
9
Medium Priority
?
695 Views
Last Modified: 2012-06-22
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
0
Comment
Question by:Jackass03
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
9 Comments
 
LVL 12

Expert Comment

by:Einstine98
ID: 17057270
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

0
 

Author Comment

by:Jackass03
ID: 17057355
How do I script the database?

thanks for your answer
0
 
LVL 12

Expert Comment

by:Einstine98
ID: 17057487
click on the database in Management studio, right click and you hae the option to script it
0
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 8

Expert Comment

by:Kobe_Lenjou
ID: 17057943
You can also make a backup of the DB, amd restore it as a new DB
0
 

Author Comment

by:Jackass03
ID: 17058504
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
0
 

Author Comment

by:Jackass03
ID: 17058601
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?
0
 
LVL 12

Accepted Solution

by:
Einstine98 earned 200 total points
ID: 17061571
If you restore the database under a different name you may get the SID of the database conflicting... coming from Oracle background I just wouldn't do it on the same server :-)

The script all objects option does fail (rarely would it work) and I have confirmed this as bug with Microsoft...

1. Script the database by selecting all the objects manually..
2. The scripts do give errors because of the stupid way it is ordered (dependency thing)...
3. Run the script two times on the same database, in the second time you should receive no errors (other than the object already exists).... if so, then you are 100% it's done...
4. Alternatively (and that's what I do for clients on 2005) work through the script and handle the dependencies (which table should be created before which view!)
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question