从远程sql server导出blob并保存到sql server所在磁盘上的文件[C＃] [英] Export blob from remote sql server and save to file on disk where sql server is located [C#]

问题描述

我需要做什么：
我正在为我的公司提供备份和还原工具。备份远程SQL数据库完成没有任何问题。但是在恢复它们时我有问题。我正在远程SQL上创建一个临时表，数据库的二进制文件被保存。

What i Need to do: im working on a backup and restore tool for my Company. Backup remote SQL databases is done without any Problems. But i have Problems while restoring them. I'm currently creating a temporary table on the remote SQL where the binary of the database is saved.

现在我需要将此二进制字段从远程SQL Server导出到需要保存在远程磁盘上的文件。此工具要求在本地机器上启动，所以无法在远程服务器上运行。

Now i Need to Export this binary field from a remote SQL Server to a file that Needs to be saved on remote disk too. This tool has the requirement to get started on local machine so there is no way of running it on remote Server.

当前代码部分：

using (SqlConnection conn = new SqlConnection(sqlConnectionString))
            {
                System.Data.SqlClient.SqlCommand commandAdd = new SqlCommand("CREATE TABLE dbo.tempTable (filename char(50), blob image);", conn);
                conn.Open();

                if (commandAdd.ExecuteNonQuery() != 0)
                {
                    byte[] fileBytes = System.IO.File.ReadAllBytes(fileSource);
                    System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("INSERT INTO dbo.tempTable (blob,filename) values (@blob,@name)", conn);
                    command.Parameters.AddWithValue("blob", fileBytes);
                    command.Parameters.AddWithValue("name", dbName);

                    if (command.ExecuteNonQuery() != 0)
                    {
                        // This is just for testing if i get binary back
                        // string SQL = "SELECT blob FROM dbo.tempTable";
                        // SqlCommand cmd = new SqlCommand(SQL, conn);
                        // byte[] byt = (byte[])cmd.ExecuteScalar();

                        // I think Problem is within this 2 lines..
                        Server server = new Server(new ServerConnection(conn));
                        server.ConnectionContext.ExecuteReader("'bcp.exe \"SELECT blob FROM dbo.tempTable\" queryout " + destinationSource + " -T -c'");

                        Restore restore = new Restore();
                        restore.Database = "_" + dbName;
                        restore.Action = RestoreActionType.Database;
                        restore.ReplaceDatabase = true;
                        restore.Devices.AddDevice(destinationSource, DeviceType.File);
                        restore.ReplaceDatabase = true;
                        restore.NoRecovery = false;
                        Server sqlServer = new Server(new ServerConnection(conn));
                        restore.SqlRestore(sqlServer);
                    }
                }

                SqlCommand commandDelete = new SqlCommand("DROP TABLE dbo.tempTable", conn);
                commandDelete.ExecuteNonQuery();
                conn.Close();
            }

谢谢

推荐答案

好的，我再试一次。
看这里： https ：//www.simple-talk.com/sql/t-sql-programming/the-tsql-of-text-files/

OK, i try again. Look here: https://www.simple-talk.com/sql/t-sql-programming/the-tsql-of-text-files/

--then we execute the BCP to save the file
  SELECT  @Command = 'bcp "select BulkCol from ['
          + @MySpecialTempTable + ']'
          + '" queryout '
          + @Filename + ' '
         + CASE WHEN @Unicode=0 THEN '-c' ELSE '-w' END
          + ' -T -S' + @@servername
  EXECUTE @RESULT= MASTER..xp_cmdshell @command, NO_OUTPUT

从c＃代码，xp_cmdshell执行命令（并保存文件）在服务器上

You can call, from c# code, xp_cmdshell that execute the command (and save the file) on the server

这篇关于从远程sql server导出blob并保存到sql server所在磁盘上的文件[C＃]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！