Cannot transfer/copy database in one step like I could with 2000.
Hi,
I would like to know if there will every be a clean way to transfer a database from a remote server to a local server (and back again). I've tried several different approaches, but they all currently have bugs/problems.
I've looked into database publishing wizard - but this creates a huge script that takes 10x longer to run than the old DTS transfer.
I've tried copying data, and objects separate, but this requires multiple steps, using DTS this was all done in one step.
I've tried using SSIS Transfer SQL Objects, but the defaults are not created, using DTS, the ENTIRE database was transfered, which is what I want.
I've tried using SSIS Transfer Database, but even when I select "Online" mode, I get an error asking me to select at least one source file, but there is nothing to select (no files appear in the pop up window).
I've tried copy database or backup database, but I do not have suffient permission on the remote server for these.
So many different ways to do it, but all fail!
PLEASE - make a simple way to transfer a database from a remote host and back again. I want to be able to select a database source and destination and have the database copied. PLEASE - at least restore the old way until other options are working first.
All Replies
mpavlik wrote: I've tried using SSIS Transfer Database, but even when I select "Online" mode, I get an error asking me to select at least one source file, but there is nothing to select (no files appear in the pop up window).
Offline:
Once you set SourceConnection and SourceDatabaseName, you should be able to set the network share for database files to be transferred by clicking on "..." in SourceDatabaseFiles value column.
Online:
Once you set SourceConnection and SourceDatabaseName, the value of source database files is automatically populated.
Do you see this behavior? If not, please report what you are observing and we might be able to help.Offline - I am not able to set the network share because I cannot browse (the window pops up, but there are no files to select). This may be a permission issue with the host server. I don't even know what the path would be to type it in.
Online - yes, the database name is auto-populated (or I can type it in), however when I try to run the package, validation fails because "at least one source must be selected"... Maybe I do not understand the error message, but I am assuming this is because the path to the database file is not selected ? ... even though it is greyed out. I don't know what else it could be, because everything else is populated.
Try looking at these:
http://manycounselors.com/taskdetail.jpg
http://manycounselors.com/taskerror.jpg
- In the image taskdetail.jpg, SourceDatabaseFiles value is not set. This property is set automatically after you select the database name. If you type an invalid database name, then the source database files property is not automatically populated. I think you typed the source database name instead of selecting it from the drop-down box. You might have entered an invalid database name and your error message could be due this reason. Try selecting the source database instead of typing. (You can see the list of available databases in the server by clicking on the button that appears in the right end of the property value column. This button appears when you click on the value column.)
I double checked it... I am selecting the database from the drop down list. The problem is that the database source file is not being automatically selected. It stays blank. Is this only happening for me? Seems like a bug.
- Can anyone confirm this as a bug, or tell me what I am doing wrong?
- I continue to experience this error. Can someone please help... This is a bug?!?
- I could not get to repro this in my machines? Could there be something wrong with your install? Is there another machine that you can use to repeat this behavior?
- I get exactly the same error, on a brand new install of SQL 2005 SP2 / Visual Studio 2005 SP1. I'm definitely selecting my source database from the dropdown (not typing it in) and the SourceDatabaseFiles file field is not autopopulated, whether the method is DatabaseOnline or DatabaseOffline. This seems doubly poor as I don't even *need* the source file specified to run a DatabaseOnline transfer, but nevertheless because the system fails to populate it, I can't run the package! It fails with the error message "At least one source file must be specified."
Like mpavlik above I'm left with no easy bug free way of transferring my database between servers which kind of sucks ... Im also getting the exact same issue on my end too.
I'm currently hosted at a 3rd party hosting company and need to move about 8 databases over, to no avail. I've tried just about everything mentioned by the above posters and I get the EXACT same error (no files found, etc)
it will not autopopulate the filename list nor can I even write the filenames in if I had them to write. I cannot use offline mode because im not the sysadmin, and even the hosting company is telling me that using ssis to transfer the database will definitely work. yeah! if you know the names of the files and you may need to know a little bit of magic too because it will not work. I think the problem is, these great software packages are only functional if you literally have the server ni front of you. The silly part is, if you had the server in front of you, you probably wouldnt need the tools in the first place!
I thought for the longest time that I must be crazy, now I know there's a few people out there who are in the same boat,.
If anyone knows of a fix or patch or cure, please let me know!
I found a great solution!! Use the Microsoft Database Publishing Wizard, On the front screen it'll ask you the usual.(server name, user/pass, etc)
I would skip that and just put in a connection string which you can specify your default database right from within there, You can script all of your objects and write it to an .sql file or directly transfer it to another DB.
I tested the scriping to file option and it worked beautifully!
I hope this info helps at least 1 person out there, this stuff can get pretty frustrating!
I am currently using MS DB Publishing Wizard as a work around - but it my opinion it is not a great solution because it creates huge files and takes so much longer than 2000 used to.
I am still hoping someone can explain how to get it to work.
Have you tried using the Copy Database Wizard?
- Yes - it doesn't work either.
- It's been 3 years now. I still have the same problem. Hasn't this been fixed yet? I am running into file size problems with DB Publishing wizard. Soon I won't be able to do anything because SSMS has out of memory errors with large scripts.
