将数据库从一个SQL Server导出到另一个SQL Server

I have a test database that I need to export into our client's test environment.

我有一个测试数据库，我需要将其导出到客户端的测试环境中。

This will be a one time only job.

这将是一次性的工作。

I'm using SQL Server 2005 (My test db is SQL Server 2005 Express)

我正在使用SQL Server 2005（我的测试数据库是SQL Server 2005 Express）

What is the best way to do this?

做这个的最好方式是什么？

2 个解决方案

#1

Easiest Way

Backup the database in SSMS, and then restore the database on the target machine.

在SSMS中备份数据库，然后在目标计算机上还原数据库。

To do this in SSMS (SQL Server Management Studio), right click the database you want to backup select Tasks->Backup, note the type of backup and the path of the .bak file. Then grab that file (the .bak) and go to the target database server / machine. Right click databases and do a "Restore".

要在SSMS（SQL Server Management Studio）中执行此操作，请右键单击要备份的数据库，选择任务 - >备份，记下备份类型和.bak文件的路径。然后获取该文件（.bak）并转到目标数据库服务器/机器。右键单击数据库并执行“还原”。

Here you can tell it the path of the .bak file, and the database will be created on your clients machine with the name you specify.

在这里，您可以告诉它.bak文件的路径，并使用您指定的名称在客户端计算机上创建数据库。

Harder But Reusable Way

If you really feel geeky you can write some T-SQL to backup and restore the database as well. Let me know if you feel real geeky and we can go this route as well...but it appears you are only doing this once so scripting is probably some overkill. But just in case anyone needs to backup a database, you can throw this in a procedure if you want:

如果你真的觉得很讨厌，你也可以写一些T-SQL来备份和恢复数据库。让我知道，如果你觉得真的很怪异，我们也可以走这条路......但是看起来你只是这样做了一次，所以脚本可能有点矫枉过正。但是，如果有人需要备份数据库，您可以在程序中将其抛出：

DECLARE @strRootPath varchar(50)
DECLARE @BackupFile varchar(100)
DECLARE @strDB varchar(25)

SELECT @strRootPath = 'C:\SQL_BACKUPS\MyDBFolder\'
SELECT @strDB = db_name()

SELECT @BackupFile = 
      @strRootPath
    + db_name()
    + '_'
    + CONVERT(varchar(8), GetDate(), 112)               -- yyyymmdd
    + '_'
    + REPLACE(LEFT(CONVERT(varchar(8), GetDate(), 108), 5), ':', '')    -- hh:mm:ss
    + '.BAK'

BACKUP DATABASE @strDB TO  DISK =@BackupFile WITH RETAINDAYS = 10, NAME = N'MyDB_DATA-Full Database Backup', STATS = 10

BACKUP LOG MyDB
   TO MyDB_Log;

#2

or maybe you can use SQL Server Management Studio (SSMS) then right click on database that you want to export so you will get new window for export-import database. fill any information for database connection and follow the steps until you success export it :)

或者您可以使用SQL Server Management Studio（SSMS），然后右键单击要导出的数据库，这样您将获得export-import数据库的新窗口。填写数据库连接的任何信息，并按照步骤，直到您成功导出它:)