How to copy sql server database from one to anothe

Question

I have two machines, A and B. In my machine A, I have SQL server with some database. I need to copy the database from Machine A to B without taking any backups. How do I do that?

Answer 1

You can use the Copy Database functionality in SQL Server Management Studio.

Right-click the database, select "Tasks", "Copy database". If you can take the database offline, you can also choose to detach it, copy the files to the new server, and then reattach the database after.

See also the Microsoft Documentation: http://technet.microsoft.com/en-us/library/ms188664.aspx

Answer 2

Here is one more method that you can use only if you have direct access to both the machines.

In Source Machine:
1. Open Sql Server Management Studio(SSMS)
2. Right Click on Database you want to copy
3. Goto Properties
4. Select Files in Left Hand Menu
5. Scroll to Right Most columns you will find the filename and path of database file (note this path and filename of all the entries in the list)
6. Close the properties window
7. Again Right Click on Database
8. Click on Detach
9. Goto the path that you noted on Step 5
10. Copy all the files that you noted in step 5
11. Paste it in a folder in Target macine
12. Again Goto SSMS and Right Click on Database folder
13. Click on Attach
14. Click on Add and give the path of .mdf file you noted in Step 5

On Target Machine
1. Goto SSMS and Right Click on Database folder
2. Click on Attach
3. Click on Add and give the path of .mdf file you Copied from source Machine to Target machine.

EDIT
You can create a linked server (refer:http://msdn.microsoft.com/en-us/library/aa213778%28SQL.80%29.aspx) and use Cross server query to insert data into target database e.g

Select * Into Servername.DatabaseName.SchemaName.TableName From SourceSchemaName.Tablename

you don't need to perform this for each and every object, you can use cursors to do this for all the objects present in your database.

Answer 3

Just used a free tool from http://dbcopytool.codeplex.com/ to copy a live database from SQL 2005 to SQL 2012. After inputing all the info in the 2nd tab page, the 1st tab page can be configured and it all worked without issue.