December 19, 2008 at 5:55 am
Hi friends,
Please i would need your help with this assignment on how can i copy a database from one sql server to another sql server, either within sql server of the same version or different version.
Thanks!
Saheed.
December 19, 2008 at 7:01 am
Backup the database
Copy the backup file to the other server
Restore the backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 19, 2008 at 7:50 am
thanks for that response. but what i really wanted is how to copy a table from one database on a particular server to another database on a different server. i really need this urgently. i'm sure it involves some sql codes, plz help out.
have a nice day!
December 19, 2008 at 7:59 am
Your original question asked how to copy a database.
Do you need to copy an entire database or a single table within a database?
If the latter, is there a linked server set up between the two servers? Does the destination database exist? Do you need to copy constraints, defaults and indexes, or just the data?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 19, 2008 at 8:42 am
once again, thank u very much. yes. the destination database exists, but i don't understand what you meant by the linked server. the situations are:
1. copy a table within a database in one server(sql 2000) to database in another server(sql 2005) within a machine(PC).
2. copy a table within a database in one server(sql 2000 on a machine) to database in another server(sql 2000 on another machine) within the same network.
i think my boss just want to punish me for this kind of assignment.
I shall expect your response soonest.
Thank you.
December 19, 2008 at 9:05 am
1. To copy data within two databases residing on same server, its rather easy...
something like
Select * into db1.dbo.DestinationTable from db2.dbo.SourceTable
Do keep in mind that only table structure and data will be copied. If the source table has dependent objects like contraints, indexes, Triggers etc will not be imported.
2. To copy data within two databases residing on different servers, you need to create a linked servers first. Refer to BOL about linked servers.
Once you have created the linked server, you can write similar query....
Select * into Db1.Dbo.DestinationTable from Server2.db2.dbo.SourceTable
Read BOL and refer to msdn for details 🙂
December 19, 2008 at 9:11 am
ibnzakariyyahh (12/19/2008)
i think my boss just want to punish me for this kind of assignment.
I wish I got this kind of punishment. This is dead simple and very quick.
You haven't answered my question on constraints, indexes or defaults. Assuming you need none of those, use the import/export wizard. From object explorer (management studio) right click on the source database, select Tasks and then Export Data. Go through the wizard selecting destination and appropriate options and then let it run. You can use that for both the 2005 and 2000 servers. Management studio can connect to SQL 2000.
As for linked server - http://msdn.microsoft.com/en-us/library/ms188279(SQL.90).aspx. It's just another way of doing this.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 19, 2008 at 2:11 pm
when you transfer a table from one server to another, consider import/export task of sql server 2000. (since it is from sql 2000 to 2005) beacuse I was told by one of my friends that the flow of data will be faster when you transfer data from 2000 to 2005.
December 22, 2008 at 10:24 am
Thanks Gail. The Import/Export data stuff works fine. But am not sure it would when i have tables that are full of constraints, indexes, e.t.c. Like you asked if those things needed to be copied too. Yeah, there is need to copy the constraints, indexes too. Can't thank you enough.
December 22, 2008 at 10:45 am
use the generate scripts option to script of the indexes, constraints, triggers,etc
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 23, 2008 at 5:03 am
thanks very much for your concern. by the way, what's BOL? can u give me a copy of it to read?
regards
December 23, 2008 at 5:14 am
BOL=Books ONLINE. Thats an online reference to SQL Server that u can have on ur machine.
download it from http://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00df-4220-b133-29c1e0b6585f&displaylang=en
December 23, 2008 at 5:31 am
generate scripts? pls xplain better. thanks.
December 23, 2008 at 10:16 am
From management studio, object explorer. Right click on the database, select Tasks, then Generate Scripts.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 23, 2008 at 10:35 am
GilaMonster, u are such a wonderful person. u are a darling. thanks, already done that.
best regards,
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply