How to copy sql server database from one to another server without any backups

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?

Answers


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


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.


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.


Need Your Help

What effect does using Action.async have, since Play uses Netty which is non-blocking

scala asynchronous playframework netty

Since Netty is a non-blocking server, what effect does changing an action to using .async?

Why are these permissions being refused?

android permissions

Just for the Heck of it, I requested all the permissions from my application (hello world), to see what are the types of permissions that are granted and what are those that are refused! To my amus...