Trying to convert from 2000 to 2005
-
This probably falls into the category of "dumb newbie question", but I am trying to use the Import tool through the Management Studio (right-mouse on database, All Tasks, Import Data) to import data from a 2000 database into my 2005 database. However, it only imports the tables, not the stored procedures or anything else. There doesn't seem to be an "advanced" button or anything like that where you can exactly specify the objects you want to import. It just imports the tables, that's it.
Obviously I am going about this wrong. What would the correct way be?
Thanks!
Cynthia
-
It looks like the Import tool only brings data. When I try to import a table using it, I lose the primary keys, defaults, etc. Do you need to convert the entire database from 2000 to 2005? If so, you could just restore from a backup. Otherwise, you may want to use the scripting tool (right click on any object -> All tasks -> Generate SQL Script), to create your table, procs and other objects and play the scripts in the 2005 database. Make sure you look at all options on the script dialog box. After your objects are created, import the data.
There is also a Copy Database Wizard found by right clicking a database -> Copy Database.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Thanks Kathi.
I can't restore from backup because it says the backup was made on a different server (something like that -- I forget exactly) .. which makes sense, that you could not restore a 2005 database from a 2000 backup.
Plus the Copy Database -- that would just copy to another 2000 database, right?
So that leaves the Generate Scripts method. I did generate scripts for my 2000 DB .. but the problem seems to be that the scripts regenerate exactly the same objects on the same server.
For example the first command:
CREATE
DATABASE [GlobalData] ON (NAME = N'GlobalData_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\GlobalData_Data.MDF' , SIZE = 55, FILEGROWTH = 10%) LOG ON (NAME = N'GlobalData_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\GlobalData_Log.LDF' , SIZE = 176, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
"C:\Program Files\Microsoft SQL Server\MSSQL\data\GlobalData_Data.MDF" -- that's my 2000 database. When I ran the script, it deleted my 2000 database! (Fortunately I am fiddling around with a copy of the real database!)
Is the answer as simple as tweaking this statement so it points to the 2005 section of Program Files?
I don't get it -- this is surely something that DBAs all over Sql Server-dom are having to do, right? Why wouldn't Microsoft provide a tool or something to do this?
-
You definately should be able to restore to SQL 2005 from a SQL 2000 backup, but not the other way around. Make sure you understand how to do a restore, it is possible that you are not doing that correctly. Sometimes you have to modify where the files will be restored to (the WITH MOVE command if you are scripting the restore).
Where did you run the script, on the 2000 server? You need to run the script on the 2005 server. Also, you might want to leave out the CREATE DATABASE option and manually create your new database first.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Here are the steps to do a restore and move the file destinations:
In SQL Server Management Studio
Right Click on Databases and choose "Restore Database"
Type the name of the database that you want to restore to
Under "Source for restore", choose "From Device"
Click the ellipsis button to open the "Specify backup" dialog box.
Click "Add"
Browse to the backup file that you want to restore and click "OK"
You should now see a list of the backups in the file
Choose the backup that you want to restore
On the left side, click "Options"
You'll see a list of files to be restored
Under "Restore As" modify the location so that your 2005 files do not overwrite your 2000 files
Click "OK" to do the restore
See if that works for you. Another option is detaching a database, copying the files and then attaching on the new instance.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
It worked!!
Thank you thank you thank you!The key was being able to change the destination -- I could not figure out how.
Thanks for your time and attention, I really appreciate it!
-
Yeah! Good luck with your upgrade.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Another way is detach the data base from SQL 2000 and attach the same in SQK 2995
V.Kadal Amutham
-
Since you're migrating, you should use a script.
ex.
RESTORE DATABASE "ITIS"
FROM DISK = 'd:\backups\full\bu_ITIS.bak'
WITH MOVE 'ITIS_Data' TO 'D:\SQL Server 2005\MSSQL.1\MSSQL\DATA\ITIS.MDF',
MOVE 'ITIS_Log' TO 'E:\SQL Server 2005\MSSQL.1\MSSQL\LOG\ITIS_Log.MDF'
GO
-
I tried the methods listed here on the same task. I get:
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'PRODUCTS' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
-
You Guys could also use SSIS which is easier. But to do it you must have SSIS knowledge first! Read from the SS2K5 Integration forum here.
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
-
When i open 2005 and try to go to the drive where my back up is MSSQL it will not let me see in it to choose the BAK file? I tried copying the file to the MSSQL.1 folder and i can see it there but it kicks me a permissions error when i try to restore.
-
Please post new questions in a new thread. Thank you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply