DTS question 1

[OldSkoolSkater]

Hi all

I would like to take a copy of a live database to enable me to test with it.
I have created a test DB and used the DTS import wizard to copy data into it, but when I look at the DB size in Enterprise Manager it is smaller than the source DB from which information has been taken:
The source DB is showing as 219MB with 15Mb of free space.
The new DB is showing as 190MB with 18MB of free space.
Is this because the import process has filtered out all the unused space, etc. from the source and streamlined it for the destination?
Or am I guessing wildly??

Cheers

Steve

 

[MikePP]

Hi

I think you are correct in your assumptions about the changes in file sizes.

To confirm this you could:

Check the number of tables/Stored Procedures etc. match in the two databases.

Check the number of records match in a number of sample tables.

If you are happy with the checks, I think you can safely assume that you have performed the copy correctly.

Regards

Sadcow

 

[tlbroadbent]

Missing indexes could account for the size difference. Do the new tables have the same indexes and keys? Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[OldSkoolSkater]

SadCow(!)

I compared the DBs and found that the new one was missing one table and all of the stored procs. I can't understand why these weren't moved over with the DTS import, and can't see a way to import the SPs.
Can I do a simple copy and paste for them?

Cheers

Steve

 

[sunila7]

Hi,

To move SP's and Views u can script out the Stored procedure from the old DB and run the Script on the new server...

To do that go to Enterprise manager right click go to all task and select generate script..... and select SP views and all the object u want script out and save the script...


and open this script in query analyzer for the new DB and run it there..... that should create all the objects(SP views) in that DB...

Hope it helps

Sunil

 

[anilcognos]

You can copy the .mdf file and .ldf file
AND Then do

EXEC sp_attach_db @dbname = dbname,
@filename1 = path\pubs.mdf',
@filename2 = path\pubs_log.ldf'

it will create the whole copy of database including stored procedures and DTS

 

[anilcognos]

sorry wont copy the DTS for copying DTS you need to copy msdb database, but this will copy all the tables and stored procedures
anil

 

[miq]

hi,

sunila7 and anilcognos have provided you the right options. easiest is the anilcognos one. simply detach database copy it to another location. rename that copy to something else and attach database using enterprise manager or sp_detach_db and sp_attach_db.


bye
miq