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?
我有两台机器，A和b。在我的机器A中，我有SQL server和一些数据库。我需要将数据库从机器A复制到B，而不需要做任何备份。我该怎么做呢?
3 个解决方案
#1
14
You can use the Copy Database functionality in SQL Server Management Studio.
您可以在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.
右键单击数据库，选择“Tasks”、“Copy database”。如果可以将数据库脱机，还可以选择分离它，将文件复制到新服务器，然后在之后重新附加数据库。
See also the Microsoft Documentation: http://technet.microsoft.com/en-us/library/ms188664.aspx
请参阅Microsoft文档:http://technet.microsoft.com/en-us/library/ms188664.aspx
#2
3
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.
这里还有一个方法，只有在您可以直接访问这两台机器时才可以使用。在源机:1。打开Sql Server Management Studio(SSMS) 2。右键单击要复制的数据库3。转到属性4。选择文件在左手菜单5。向右滚动大多数列，您将找到数据库文件的文件名和路径(注意列表中所有条目的路径和文件名)6。关闭属性窗口7。再次右键单击数据库8。点击分离9。走你在第5步所注意到的路径。复制您在步骤5 11中注意到的所有文件。将它粘贴到目标macine 12的文件夹中。再次访问SSMS并右键单击数据库文件夹13。点击附加14。单击Add，给出您在目标机器1的步骤5中注意到的.mdf文件的路径。Goto SSMS和右键单击数据库文件夹2。点击附加3。单击Add，并给出从源机复制到目标机的.mdf文件的路径。
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
编辑您可以创建一个链接服务器(参见:http://msdn.microsoft.com/en- us/library/aa213778.28% sql .80%29.aspx)，并使用跨服务器查询将数据插入目标数据库，例如
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.
您不需要为每个对象执行此操作，您可以使用游标来为数据库中的所有对象执行此操作。
#3
2
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.
只是使用了http://dbcopytool.codeplex.com/上的一个免费工具将一个实时数据库从SQL 2005复制到SQL 2012。在第二个选项卡页面中输入所有信息后，可以配置第一个选项卡页面，并且没有问题。
#1
14
You can use the Copy Database functionality in SQL Server Management Studio.
您可以在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.
右键单击数据库，选择“Tasks”、“Copy database”。如果可以将数据库脱机，还可以选择分离它，将文件复制到新服务器，然后在之后重新附加数据库。
See also the Microsoft Documentation: http://technet.microsoft.com/en-us/library/ms188664.aspx
请参阅Microsoft文档:http://technet.microsoft.com/en-us/library/ms188664.aspx
#2
3
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.
这里还有一个方法，只有在您可以直接访问这两台机器时才可以使用。在源机:1。打开Sql Server Management Studio(SSMS) 2。右键单击要复制的数据库3。转到属性4。选择文件在左手菜单5。向右滚动大多数列，您将找到数据库文件的文件名和路径(注意列表中所有条目的路径和文件名)6。关闭属性窗口7。再次右键单击数据库8。点击分离9。走你在第5步所注意到的路径。复制您在步骤5 11中注意到的所有文件。将它粘贴到目标macine 12的文件夹中。再次访问SSMS并右键单击数据库文件夹13。点击附加14。单击Add，给出您在目标机器1的步骤5中注意到的.mdf文件的路径。Goto SSMS和右键单击数据库文件夹2。点击附加3。单击Add，并给出从源机复制到目标机的.mdf文件的路径。
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
编辑您可以创建一个链接服务器(参见:http://msdn.microsoft.com/en- us/library/aa213778.28% sql .80%29.aspx)，并使用跨服务器查询将数据插入目标数据库，例如
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.
您不需要为每个对象执行此操作，您可以使用游标来为数据库中的所有对象执行此操作。
#3
2
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.
只是使用了http://dbcopytool.codeplex.com/上的一个免费工具将一个实时数据库从SQL 2005复制到SQL 2012。在第二个选项卡页面中输入所有信息后，可以配置第一个选项卡页面，并且没有问题。