September 20, 2006 at 12:53 pm
Hi,
I have a hosted SQL Server 2000 database which I copy locally as a backup.
Originally I was using Enterprise Manager and I imported the entire database and saved the import as a DTS package which I could then run whenever I liked.
Now with my new PC on which I have installed SQL Serer 2005 I am trying to accomplish the same thing however everything seems different.
1) I tried to import the database but the import option only seems to allow tables and views now ignoring the rest of the database.
2) I then tried to copy the database but the copy wizard failed when it tried to list the databased on my hosted server because my login only has access to my own database and the wizard seems to be trying to pull some information about all databases across.
Has anyone got any ideas about how I can effectively take a local copy of my hosted 2000 database using the SQL Server 2005 management tools?
Thanks,
Dale
September 20, 2006 at 5:25 pm
You should be able to detach it from 2000 and reattach on 2005.
Alternatively, you can make a backup in 2000 and restore from the backup on 2005. Then you'll have it in both places.
---------------------------------------
elsasoft.org
September 21, 2006 at 2:03 am
Hi - Due to the fact that it is hosted I do not have access to do either of those things.
September 21, 2006 at 4:36 am
Well, we don't know what access you have.
How about the import wizard?
September 21, 2006 at 4:58 am
As already mentioned the import wizard *appears* to only allow me to import tables & views.
September 21, 2006 at 7:58 am
Hi.
Well. Generate the Sql script of your database including tables,views,indexes, PK, FK, stored procedures, and what are the things you need. Run it in SQL Server 2005 Management Studio. Then import all data to your database using dts. Hope it helps
Prema
September 21, 2006 at 8:02 am
With copy database wizard you can
"Pick a source and destination server.
Select one or more databases to move or copy.
Specify the file location for the databases.
Create logins on the destination server.
Copy supporting objects, jobs, user-defined stored procedures, and error messages.
Schedule when to move or copy the databases. "
Seems to me your missing the supporting objects etc. step. Remember you can't copy the master or msdb databases, so you're 'just' getting the database.
Check out 'Using the Copy Database Wizard ' in BOL.
September 21, 2006 at 8:21 am
Thanks for your help, again as already mentioned in the original post when I try and use the copy wizard it fails because it tries to list the available databases on the hosting server and my login doesn't have sufficient access for it to complete that step. If only it would copy the database that I clicked copy on rather than trying to list all of them!
September 21, 2006 at 8:22 am
Any way to automate those 2 steps? And where are the DTS options in 2005?
September 21, 2006 at 10:40 am
The SSIS Import wizard in 2005 is only for tables and views. Why don't you just migrate your 2000 DTS to 2005 and use the exisiting package?
Joshua Perry
http://www.greenarrow.net
September 21, 2006 at 10:55 am
I'd love to - how do I do that?
September 22, 2006 at 3:18 am
"hen I try and use the copy wizard it fails because it tries to list the available databases on the hosting server and my login doesn't have sufficient access"
Right, if you don't have access to any of the other databases, there's no way you can get objects from master or msdb.
If I were you I'd request a backup from your hosting company - you are paying them after all - to get a starting point. From then on you'll only need to update the data anyway in most cases.
September 22, 2006 at 3:20 am
The package migration wizard is a starting point, you can also try the Execute DTS 2000 Package task, but there's no gaurantee it'll work as some functions have no mirror in integration services.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply