在SSMS中导出表并将其导入到其他服务器 [英] Export tables in SSMS and import them onto a different server

问题描述

我们有运行带有大型SQL Server数据库的特定软件的客户端，他们使用SSMS对其进行管理(我们不希望他们在服务器上安装任何其他软件).我们需要使用SSMS从服务器中取出许多表，以便它们可以保存它们(这必须是一个相对简单的过程，这将是IT经理，而不是由DBA或程序员来完成任务)，然后将它们发送给我们(在USB驱动器上.

We have clients that run a particular piece of software with a large SQL Server database and they use SSMS to manage it (we don't want to ask them to install any other software on their servers). We need to get a number of tables out of the server using SSMS so they can save them (this has to be a relatively easy process, it will be an IT manager not a DBA or programmer doing the task) and send them to us (on usb drive).

所以我已经尝试过使用generate script选项，但是在测试中我确实得到了200GB .sql文件，然后我需要编辑第一行(USE [database])以指定一个不同的文件.数据库复制回(或每个客户端将具有相同的数据库名称并覆盖其他客户端的数据).当然，在200GB文件中编辑行不是一件容易的事，因此我仍然必须使导入工作.

So I have experimented with the generate script option, but in the test I did we ended up with a 200GB .sql file which I would then need to edit the first line (the USE [database]) to specify a different database to copy back to (or each client would have the same database name and overwrite the data from other clients). Of course editing a line in a 200GB file is not an easy task, then I still have to get the import to work.

我当时在想，如果我使用示例数据库中的generate脚本制作表等，然后使用SSMS中的导出功能将数据导出到CSV，但是数据可能是清洁，这很容易导致CSV问题.我当时在考虑使用平面文件而不是CSV，但是我担心它们可能会在编码等方面造成麻烦(而且我不确定与CSV相比，平面文件中的数据混乱程度如何.)

I was thinking maybe if I use the generate script from a sample database to make the tables etc on my end, then use the export functionality in SSMS to export the data to a CSV, however, the data will likely be anything but clean, and this could easily cause issues with a CSV. I was thinking a flat file rather than a CSV, but I am worried they may stuff something up with the encoding etc (and I am not sure how messy data goes in a flat file compared to a CSV).

我当时在想是否可以创建一个具有某种描述的SQL脚本来输出文件，但这必须很简单，以便他们可以告诉代码中没有可疑对象，并且需要输出文件或一组文件，但在没有数据损坏可能性的情况下仍然存在如何保存的相同问题.

I was thinking maybe if I could create an SQL script of some description to output a file, but it would have to be something simple so that they can tell there is nothing suspect in the code, and would need to output a file or set of file, but would still have the same issue of how to save without the possibility of data corruption.

有什么想法吗?我们使用的是Windows Server 2012 R2，数据可能来自不同版本的SQL Server，具体取决于该公司最近的更新.

Any ideas? We are on Windows Server 2012 R2 and the data may be coming from various versions of SQL Server depending how recently that company updated.

推荐答案

在找到更好的答案之前，我将只剩下我们在这里所做的事情.

Until there is a better answer, I will just leave what we did here.

我们已经创建了一组指令和脚本，这些脚本和脚本将使客户端创建新数据库，然后使用该脚本将数据传输到新数据库，然后备份此新创建的数据库.

We have created a set of instructions and a script that will get the client to create a new database, then use the script to transfer the data over to the new database, and then back up this newly created database.

脚本(查询)有效地创建了一个循环，以遍历表并使用以下命令创建sql:

The script (query) effectively creates a loop to go through the tables and create the sql with:

SET @sql = 'SELECT * INTO [' + @toDB + '].' + @currTable + ' FROM [' + @fromDB + '].' + @currTable

，它将使用当前表名称(@currTable)，并将其从其主数据库(@fromDB)移至新创建的数据库(@toDB).

which takes the current table name (@currTable) and moves it from their main database (@fromDB) into the newly created database (@toDB).

这不是理想的选择，但是目前看来，这是处理大量数据的最简单选择.最好的是，如果他们在选择要包括的表的备份时有一个选择.

This is not ideal, but for now seems to be the simplest option for large amounts of data. What would be great is if they had an option when doing a backup of choosing which tables to include.

如果其他人需要执行类似操作，请参考以下脚本:

For reference if others need to do something like this, here is the script:

--before you run this script, check that the 2 variables at the top are set correctly
--the @toDB variable should be a database you have just created to temporarily store exported data
DECLARE @fromDB VARCHAR(max) = 'main_database' --this should be set to the name of the database you are copying from
DECLARE @toDB VARCHAR(max) = 'main_database_export' --this should be set to the name of the database you are copying to (the temporary one)

/* ------------------------------------------
---------Do not edit from here down---------
------------------------------------------- */
--declare variables to be used in different parts of the script
DECLARE @sql VARCHAR(max)
DECLARE @currPos INT = 1
DECLARE @currTable VARCHAR(max)
DECLARE @tableNames TABLE(id INT, name varchar(max))
--create a list of files that we want top copy to the new database, the id must be sequential and start at 1)
INSERT INTO @tableNames VALUES
    (1, '[dbo].[table1]'),
    (2, '[dbo].[table2]'),
    (3, '[dbo].[table3]'),
    (4, '[dbo].[table4]')

DECLARE @totalTables INT = 4 --this should always be the number of the last table to be copied, if you add more or take any away, update this

--loop through the tables and copy them across
WHILE (@currPos <= @totalTables)
BEGIN

  --get the table name of the table we are up to
    SELECT @currTable = name FROM @tableNames WHERE id = @currPos

  --create the sql that will copy from the old table into the new table (including the table structure), this table must not exist yet
    SET @sql = 'SELECT * INTO [' + @toDB + '].' + @currTable + ' FROM [' + @fromDB + '].' + @currTable

  --run the sql statement we just created, this will create the table and copy the content (and leave a message to say how many rows were copied)
    EXECUTE (@sql)

  --set the counter up one so we move onto the next table
    SET @currPos = @currPos+1

  --output the name of the table that was just processed (note that no messages will show until the entire script finishes)
    PRINT @currTable + ' Copied.'

END

请注意，此脚本旨在提供给客户端，请勿从此处向下编辑"是对它们的说明(您将需要编辑要复制的表名以及保存表总数的变量).

Note that this script is designed to give to the client, the "Do not edit from here down" is an instruction for them (you will need to edit the table names you are copying and the variable holding the total number of tables).

然后，我们向其发送一组有关如何创建新数据库，运行此脚本然后备份新数据库等的说明.

We then send this with a set of instructions on how to create the new database, run this script and then back up the new database etc.

这篇关于在SSMS中导出表并将其导入到其他服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！