Copy Data Base

Question

Copy Data Base

thomashohner

SSCrazy Eights

Points: 9856
More actions
July 16, 2014 at 7:38 am

#291159

Hello,
I am currently trying to copy our test db from a virtual machine across the network to my local instance. I have been using the Copy Data Base Wizard. Everything goes fine until the very last part. Then it says "The job failed. Check the event log on the destination server for details"
I check the event log and all I get is this. Is there a better or easier way to copy a Database? I'm very new at this. Any guidance is appreciated.
Package "CDW_VISGWTEST02_THOMAS-PC_THOMASTEST2012_1" failed.
***SQL born on date Spring 2013:-)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 1

Unless I'm missing something major, I'd just take a backup, copy the file over to the other instance, then restore it. Have you tried that? It's probably the simplest way.

thomashohner SSCrazy Eights Points: 9856 More actions · Answer 2

I have not tried that. But I will give that a shot!

***SQL born on date Spring 2013:-)

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 3

If you back it up with the Compression option it will hopefully make things a bit quicker when you're doing the copying too because the .bak file should be smaller.

By the way when you restore it you don't need to create an empty database first. Some people think you do so I just mention it now :-).

thomashohner SSCrazy Eights Points: 9856 More actions · Answer 4

Thanks for the advice its appreciated. Going to give it a shot after hours today. Local instances make coding so much faster without network lag.

***SQL born on date Spring 2013:-)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

That's the approach I take when I want to copy a database to another server. It takes so many problems completely out of the equation. It was recommended by someone here on SSC several years ago, but I don't remember who.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

douglas.allison1 SSC Rookie Points: 41 More actions · Answer 6

In my humblest of opinions.

Have you thought of using the SSIS "Transfer SQL Server Object Task"?

I have used this option recently. It's slower that just doing a backup and restore option. However you can specify "NOT" to take SQL object you don't want.

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 7

Once you take the backup, please verify the backup using "RESTORE VERIFYONLY" command.

Recently my friend faced an issue - after coping the 8 GB backup file from network drive, he could not restore and found backup as corrupted.

Thanks

douglas.allison1 SSC Rookie Points: 41 More actions · Answer 8

With regards to doing .bak and restores for certain activities although I agree is a quick and fast option isn't always the way to go.

I concur that doing this you get everything.

If you doing it to actually replace a database on another system it does take a lot of stuff you may not want such as security information users roles and schemas etc when just doing a back up & restore.

Using the SSIS "Transfer SQL Objects Task" especially on a smallish DB you select the options to copy just the objects your require.

This may take a bit of extra time to set the course of what's going to be taken over but does leave the destination database in tact rather than just deleting and restoring from a different SQL instance.