December 2, 2015 at 10:37 am
Hi
I’m trying to copy a SQL table schema and data from Server A to Server B. The SQL table is just a reference table which hasn’t populated for some reason on Server B. Can anyone advise how the entire table could be copied across please on SQL Server 2000/2005.
So far we’ve tried a long-winded approach by copying the .mdb and .ldf files from Server A to Server B with a plan to then copy the table across into the Server B database but we are having some difficulty re-attaching the database to Server B.
Please can anyone help?
Kind Regards
James
December 2, 2015 at 10:50 am
mastersql (12/2/2015)
HiI’m trying to copy a SQL table schema and data from Server A to Server B. The SQL table is just a reference table which hasn’t populated for some reason on Server B. Can anyone advise how the entire table could be copied across please on SQL Server 2000/2005.
So far we’ve tried a long-winded approach by copying the .mdb and .ldf files from Server A to Server B with a plan to then copy the table across into the Server B database but we are having some difficulty re-attaching the database to Server B.
Please can anyone help?
Kind Regards
James
Copying and reattaching the mdb and ldf files is not a good approach. You should instead create a backup and then restore on the other server. However, this is for the ENTIRE database and it sounds like you want only a single table? For this I would use the import/export data functionality in SSMS.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 2, 2015 at 10:57 am
I agree with Sean. Since you are using 2000 and 2005 the problem may the direction you are going. If the table exists in 2005 and you are trying to take it to 2000, you have to use the export/import process.
December 2, 2015 at 1:02 pm
Hi SSC
I've now found a YouTube clip which says you shouldn't use export and import for SQL database to SQL database.
It says use the Copy Database option with SSMS Tasks at 3 mins 3 seconds into the clip.
Will export and import as work just the same?
Interestingly on my home version of SQL Express, the Copy Database option does not exist - hopefully it will exist when I go into work tomorrow to give it a try 🙂
Kind Regards
James
December 2, 2015 at 1:14 pm
mastersql (12/2/2015)
Hi SSCI've now found a YouTube clip which says you shouldn't use export and import for SQL database to SQL database.
It says use the Copy Database option with SSMS Tasks at 3 mins 3 seconds into the clip.
Will export and import as work just the same?
Interestingly on my home version of SQL Express, the Copy Database option does not exist - hopefully it will exist when I go into work tomorrow to give it a try 🙂
Kind Regards
James
You need to read that more closely. It suggests that if you want to copy an ENTIRE database AND the objects you should use copy database. In your question it sounds like you want to copy only a single table, not the entire database. This goes back to my original comment, for an entire database I would recommend creating a backup and then restoring that backup on the other instance. For a single table, you should use the import or export data functionality.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 2, 2015 at 3:39 pm
SSC
No probs - I'll use Export and Import to copy the table.
I've looked into it a bit more and also found out that I can use the Generate Script... option as long as I go into Advanced and enable the Schema and Data setting.
Cheers
James
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply