How best to transfer SQL 2000 database to 2005 SQL server?
I have a SQL 2000 server that has a database that I need to migrate over to a SQL 2005 server. The database has a few tables that we don't want to migrate because of their size. How do I properly export the tables and maintain all of the indexes, schema information, keys and such so that it all shows up when I import them into the SQL 2005 server?
lcohan
The easiest way would be to backup on SQL 2000, restore to SQL 2005 and drop all tables you don't need after that.
Backup a database from SQL 2000
Restore a backup copy into SQL 2005
this will get all indexes, schema information, keys
Restore a backup copy into SQL 2005
this will get all indexes, schema information, keys
ASKER
What if in this case the database is too large to restore to its new environment? That it would be necessary to exclude certain large tables so that the DB will fit into the new environment? Is there a way to copy it over in this case?
"What if in this case the database is too large to restore to its new environment?"
Don't want to sound out of context but newer environments are usualy beter, faster, and bigger...
Of course you can transfer data but lot more work and chance of errors. You can create a DB on the new SQL 2005 without the tables you don't need and use SQL Export/Import to get data from old SQL 2000. Caution if you have triggers they better be disabled before data transfer, and any constraints that can cause errors (like FKeys for instance) dropped and recreated with check after the insert.
Good luck!
Don't want to sound out of context but newer environments are usualy beter, faster, and bigger...
Of course you can transfer data but lot more work and chance of errors. You can create a DB on the new SQL 2005 without the tables you don't need and use SQL Export/Import to get data from old SQL 2000. Caution if you have triggers they better be disabled before data transfer, and any constraints that can cause errors (like FKeys for instance) dropped and recreated with check after the insert.
Good luck!
ASKER
The newer environment in this case is virtual, and in trying to keep it manageable, I've allotted limited storage space. I understand that it's possible to export triggers, indexes, stored procedures and the rest out of the SQL 2000 environment, but I tried what I thought would do that and it didn't work at least for the indexes. What procedure should I follow to get all of that information out of the SQL 2000 database and then what procedure would I use to get the data that I need?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
I might be able to do something like that if I can somehow free enough space on one of the partitions on the SQL 2000 server. The problem is that the database I'm trying to copy over is huge, and at the moment there isn't enough room for me to backup/restore the database to the SQL 2000 server so that I can drop the tables. Is there a way to export just the tables I want as well as the schema, index, etc?
Yes, in the SSMS you should look at the Export Data wizard. It does allow you to specify the tables and views you want copied.
otherwise, spend $150 and get a 1TB external USB drive and do the restore on that device (it will be slow but there will be space).
otherwise, spend $150 and get a 1TB external USB drive and do the restore on that device (it will be slow but there will be space).
Can you check how much free space is in your database? It may be "huge" but with lots of free space and you can do that by looking in SSMS at Tasks -> Shrink Files option one by one will show you how much free space you have. also because you are on SQL 2000 it would be better if you can run a DBCC UPDATEUSAGE (your_db_name) before running the script below:
SELECT name,size/128.0 as SizeInMB ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
"I understand that it's possible to export triggers, indexes, stored procedures and the rest out of the SQL 2000 environment, but I tried what I thought would do that and it didn't work at least for the indexes."
Not sure what tool did you used but I suggest use SQL2005 SSMS to script all needed objects from your SQL 2000 DB including indexes.
Just right click the DB in SQL 2005 SSMS and under Task select "Generate Scripts" and make sure you select "Script Indexes = true" like in the attached picture (and anything else you may need) then follow the steps in the wizzard and select what objects you want from the list.
select what objects you want to script from the list.
Untitled.png
SELECT name,size/128.0 as SizeInMB ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
"I understand that it's possible to export triggers, indexes, stored procedures and the rest out of the SQL 2000 environment, but I tried what I thought would do that and it didn't work at least for the indexes."
Not sure what tool did you used but I suggest use SQL2005 SSMS to script all needed objects from your SQL 2000 DB including indexes.
Just right click the DB in SQL 2005 SSMS and under Task select "Generate Scripts" and make sure you select "Script Indexes = true" like in the attached picture (and anything else you may need) then follow the steps in the wizzard and select what objects you want from the list.
select what objects you want to script from the list.
Untitled.png
Please follow the below steps:
1) First copy the MDF and LDF of the SQL 2000 Database in to a tape drive r disk.
2) The copy the same files in to the server which you want to upgrade to SQL 2005 server.
3) Attach the files to SQL 2005 instance and check the Database settings.
4) The list down the tables which you don’t need. The delete the data in those particular listed tables. Before dropping the tables check the dependencies like indexes etc;
5) Once dropping the unwanted tables is done then shrink the Database.
6) If everything is done then check the Database stability by running few big queries by which you can confirm there is no unwanted data loss.
Let me know if have and doubts…
--Venkat
1) First copy the MDF and LDF of the SQL 2000 Database in to a tape drive r disk.
2) The copy the same files in to the server which you want to upgrade to SQL 2005 server.
3) Attach the files to SQL 2005 instance and check the Database settings.
4) The list down the tables which you don’t need. The delete the data in those particular listed tables. Before dropping the tables check the dependencies like indexes etc;
5) Once dropping the unwanted tables is done then shrink the Database.
6) If everything is done then check the Database stability by running few big queries by which you can confirm there is no unwanted data loss.
Let me know if have and doubts…
--Venkat
ASKER
I went this route after I was able to clear enough space on the server to accommodate a restored copy of the database. Thanks!