C# 以编程方式分离 SQL Server 数据库以复制 mdf 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/757036/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Programmatically detach SQL Server database to copy mdf file
提问by Pete Michaud
I have a small SQL Server database that I need to copy on command -- I need to be able to take the mfd and ldf files at any given moment, copy them, zip them, and make them available to an end user.
我有一个小型 SQL Server 数据库，我需要根据命令进行复制——我需要能够在任何给定时刻获取 mfd 和 ldf 文件，复制它们，压缩它们，并使它们可供最终用户使用。
Right now this is possible by manually:
现在这可以通过手动实现：
1) Logging onto the SQL server via Remote Desktop
1）通过远程桌面登录SQL服务器
2) Detaching the database via SQL Management Studio. I have to fiddle around with a combination of setting the database to single_user and/or restarting the service so I can get it to detach since the app server is normally logged into it.
2) 通过 SQL Management Studio 分离数据库。我必须摆弄将数据库设置为 single_user 和/或重新启动服务的组合，以便我可以让它分离，因为应用程序服务器通常登录到它。
3) While detached I go through the file system and copy the mdf and ldf files.
3) 在分离时，我通过文件系统并复制 mdf 和 ldf 文件。
4) I re-attach the database via SQL Management Studio
4) 我通过 SQL Management Studio 重新附加数据库
5) I zip the copied files, and I move them to an FTP server so the people who need them can get them.
5) 我将复制的文件压缩，然后将它们移动到 FTP 服务器，以便需要它们的人可以获取它们。
It's a horrible, inefficient process. It's not just a matter of needing the schema, but rather a need for people to work with snapshots of real, production data on their own local machines for the purpose of destructive experimentation. Luckily the zipped database is very small -- maybe 30 megs with the log.
这是一个可怕的、低效的过程。这不仅仅是需要模式的问题，而是需要人们在他们自己的本地机器上处理真实生产数据的快照，以进行破坏性实验。幸运的是，压缩后的数据库非常小——日志可能只有 30 兆。
So ideally, I'd like to create a page in the ASP .NET web application that has a button the user can press to initiate the packaging of the current database into a zip file, and then I'd just provide the link to the file download.
因此，理想情况下，我想在 ASP .NET Web 应用程序中创建一个页面，该页面具有一个按钮，用户可以按下该按钮以启动将当前数据库打包为 zip 文件的过程，然后我只需提供指向文件下载。
采纳答案by Stefan
Why not make a ordinary backup (easy to do with sqlcommand) and add a feature for the users to easy restore that backupfile with a click on a button?
为什么不做一个普通的备份（用 sqlcommand 很容易做）并添加一个功能，让用户通过点击一个按钮来轻松恢复备份文件？
- You can backup the database with sql-commands
- You can shell out and zip the backupfile with sql-commands
- You can also shell out and ftp the backupfile automagically to an webserver if you want.
- 您可以使用 sql-commands 备份数据库
- 您可以使用 sql-commands 打包并压缩备份文件
- 如果需要，您还可以自动将备份文件 shell 和 ftp 传送到网络服务器。
What are the end users using to consume your db? A winform-program? Then it easy done to do everything with a button click for the user.
最终用户使用什么来使用您的数据库？一个winform程序？然后，用户只需单击按钮即可轻松完成所有操作。
Here are some example code for that:
以下是一些示例代码：
Declare @CustomerID int
declare @FileName nvarchar(40)
declare @ZipFileName nvarchar(40)
declare @ZipComand nvarchar(255)
set @CustomerID=20 --Get from database instead in real life application
SET @FileName='c:\backups\myback'+ cast(@customerID as nvarchar(10))+'.bak'
SET @ZipFileName='c:\backups\myback'+ cast(@customerID as nvarchar(10))+'.zip'
--Backup database northwind
backup database northwind to DISK=@FileName
--Zip the file, I got a commanddriven zip.exe from the net somewhere.
set @ZipComand= 'zip.exe -r '+@ZipFileName+' '+@FileName
EXEC xp_cmdshell @zipcomand,NO_output
--Execute the batfile that ftp:s the file to the server
exec xp_cmdshell 'c:\movetoftp.bat',no_output
--Done!
You have to have a movetoftp.bat that contains this (change ftp-server to your):
ftp -s:ftpcommands.txt ftp.myftp.net
您必须有一个包含此内容的 movetoftp.bat（将 ftp-server 更改为您的）：
ftp -s:ftpcommands.txt ftp.myftp.net
And you have to have a ftpcommands.txt that contains this (You can have this file created dnamically with just the right zip-file by sqlcommands too, but I let you do that yourself):
并且你必须有一个包含这个的 ftpcommands.txt（你也可以通过 sqlcommands 使用正确的 zip 文件动态创建这个文件，但我让你自己做）：
ftpusername
ftppassword
binary
prompt n
mput c:\backups\*.zip
quit
ftpusername
ftppassword
binary
prompt n
mput c:\backups\*.zip
quit
回答by marc_s
Easy - check into the "SQL management objects" SMO that come with SQL Server - nice C# / VB.NET classes and methods to do all of this.
简单 - 检查 SQL Server 附带的“SQL 管理对象”SMO - 很好的 C#/VB.NET 类和方法来完成所有这些。
See: SMO - manage your SQL Server!
or:
或者：
SQL Server 2005 Database Backup and Restore using C# and .NET 2.0
使用 C# 和 .NET 2.0 的 SQL Server 2005 数据库备份和还原
Marc
马克
回答by Richard
Look at the dialogues you use in SQL Management Studio, near the top of each is a button which will generate a scrip to perform the action. This is a quick way to discover how to do this in SQL which can be executed from a database connection.
看看您在 SQL Management Studio 中使用的对话框，每个对话框的顶部附近都有一个按钮，它将生成一个脚本来执行操作。这是一种快速了解如何在可从数据库连接执行的 SQL 中执行此操作的方法。
E.g. to detach database db1:
例如分离数据库 db1：
EXEC master.dbo.sp_detach_db @dbname = N'db1'
回答by Rad
Personally I'd generate backups of the database and zip those and send them to the users. Perhaps you could write a small script to restore.
我个人会生成数据库的备份并将它们压缩并发送给用户。也许你可以写一个小脚本来恢复。
Reason being detaching the database makes it unavailable for others.
分离数据库的原因是其他人无法使用它。
回答by Davorin
I'd use SQL Dumperconsole version and compress the sql dump.
我会使用SQL Dumper控制台版本并压缩 sql 转储。
IMHO it's always better to have a plain text copy instead of a binary file. If something goes wrong, at least you can rewrite your data by hand, because you can read it.
恕我直言，拥有纯文本副本而不是二进制文件总是更好。如果出现问题，至少您可以手动重写数据，因为您可以读取它。
回答by ali
First Connect to SQL Server With no Attach any DB File and with no use Database name.
首先连接到 SQL Server，没有附加任何数据库文件，也没有使用数据库名称。
ConnectionString = @"Data Source=XXX;Integrated Security=True;Connect Timeout=30";
Note: XXX = . OR .\SQLEXPRESS OR .\MSSQLSERVER OR (local)\SQLEXPRESS OR (localdb)\v11.0 &...
注：XXX = . OR .\SQLEXPRESS OR .\MSSQLSERVER OR (local)\SQLEXPRESS OR (localdb)\v11.0 &...
then by under Query Detach your DB file.
然后在查询下分离您的数据库文件。
"ALTER DATABASE [your DB] SET OFFLINE WITH ROLLBACK IMMEDIATE \n\r exec sp_detach_db @dbname = [your DB]";
Ok.
好的。
My Sample code:
我的示例代码：
sql_connect1.ConnectionString = @"Data Source=.\sqlexpress;Integrated Security=True;Connect Timeout=30";
sql_command.CommandText = "ALTER DATABASE [IRAN] SET OFFLINE WITH ROLLBACK IMMEDIATE \n\r exec sp_detach_db @dbname = [IRAN]";
sql_command.Connection = sql_connect1;
sql_connect1.Open();
sql_command.ExecuteNonQuery();
sql_connect1.Close();
