February 19, 2008 at 12:57 pm
I was trying to transfer a whole DB from one SQL2k5 to another. I used export database. Every time it just copy data into tables with same name, but the settings in original table, like primary key, etc, are not copied to new table. How could I transfer a DB to a new server, with all old table's features? Thank you very much.
February 19, 2008 at 1:13 pm
Any reason not to backup/restore the database?
Tommy
Follow @sqlscribeFebruary 19, 2008 at 1:27 pm
How big is your database. I am assuming you are doing this just to do some development/testing for yourself? If that is the case, just an OLE db source to OLE DB destination would be enough. Otherwise, what Tommy said above make the same sense to me. Backup and restore are much more easier. I don't see a problem with that.
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
February 19, 2008 at 1:56 pm
Thanks a lot for reply. The source database is a remote database, and I only have database access. I couldn't get any backup files from that database server. I am not sure if I can backup it to netdrive. Even I could, I prefer not use this function since the db's size is a little big.
February 19, 2008 at 1:59 pm
SQL King (2/19/2008)
How big is your database. I am assuming you are doing this just to do some development/testing for yourself? If that is the case, just an OLE db source to OLE DB destination would be enough. Otherwise, what Tommy said above make the same sense to me. Backup and restore are much more easier. I don't see a problem with that.
You are right. That's the reason for transfering db. Let me try OLE db source to OLE DB destination. I've never used this to transfer all database. I am not sure it can bring things like primary key to local. Thanks a lot.
February 19, 2008 at 2:25 pm
By reading your replies, I can understand your problems. Here is two way to get out of this problem.
1. Do as what I have told you before.(OLE DB Source to an OLE DB destination) Hope you know how to use it. There should not be a problem with primary keys.
2. Since you already have access to your remote server, backup the database you want to and copy the address and paste it in run(Start>run) Now save that to your desktop. Believe it or not you have a backup of the whole database in your own system now. Now go to your management studio and right click database, restore.
You can choose either one of this steps. Both will take care of your needs for development! 😉
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
February 20, 2008 at 9:27 am
SQL King (2/19/2008)
By reading your replies, I can understand your problems. Here is two way to get out of this problem.1. Do as what I have told you before.(OLE DB Source to an OLE DB destination) Hope you know how to use it. There should not be a problem with primary keys.
2. Since you already have access to your remote server, backup the database you want to and copy the address and paste it in run(Start>run) Now save that to your desktop. Believe it or not you have a backup of the whole database in your own system now. Now go to your management studio and right click database, restore.
You can choose either one of this steps. Both will take care of your needs for development! 😉
Thanks a lot, man! The second way is really new to me. But I am not quite clear about that.
1. "Copy the address and paste it..."
what address it is? how to find this address?
2. "in run(Start>run)", you mean in my local or destination host, right? Although I have access to remote database, but I don't really have the access to server. I can only access db from SQL studio.
Does this method work in this situation?
Thank you very much for the help.
February 20, 2008 at 9:48 am
The second method is actually cool! Okay, here is how it goes. Do you have access to some share drive? If so, ask someone(preferably someone with authentication), to backup the original database and paste the path to this share drive. Then all you have to do is click Start>run(in you very own machine), paste the path there and click ok. Now you can save it on your own machine's desktop or somewhere you like. The reason I am asking you to save it to your machine is simply because you cannot restore from the share straight. (because of your level of authetication from DB side). but now, since you have it local, go create a db with the SAME name. Right click database in Management studio>>New DB. then hit refresh, and you will see the new DB there. Now right click again and hit restore. Find the file you saved on your desktop just now. Click OK. You now have a copy of the DB!!:D
Just a few more tips:
In case they have update the original DB today let say, and you want the latest copy, do the same thing as above but this time when you restore, go to the next step and be sure to click on Overwrite DB radio button.
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
February 20, 2008 at 9:56 am
Hi, actually, that someone who has the priviledge can also email the .bak file to you. Then you can do the same thing.
The address that I am talking about here is nothing new to all of us. It's just that we don't really give any attention to it. Here let me help you with that.
1. Double Click on My Computer.
2. Can you see address bar now? It will say My Computer there.
That's all. Easy isn't it? I know, people tend to think hard things when working and we can forget simple things like this.
Let me know of your progress.:)
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
July 17, 2008 at 9:43 am
Hi, Can you give a sample of how to transfer a db from one server to an another using "OLE DB Source to an OLE DB destination" method you suggested earlier ? I want to automate the backup of production SSAS database using SSIS package and transfer the backup files (.abf) to a local machine to save the space. Thanks!
July 17, 2008 at 9:53 am
You could use the copy database wizard in SSMS to copy the database, and then save the package and use this in SSIS.
July 17, 2008 at 2:19 pm
CDW doesn't seem to allow to save the SSIS package that was created. Also, I am not getting CDW while trying to copy SSAS database.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply