我有我需要复制的命令小SQL Server数据库 - 我需要能够采取MFD和LDF文件在任何时刻，将它们复制，压缩它们，使它们最终用户可用

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.

现在这是可能的手动方式：

Right now this is possible by manually:

1）登录到通过远程桌面SQL服务器

1) Logging onto the SQL server via Remote Desktop

2）拆下通过SQL Management Studio中的数据库。我不得不反复折腾设置数据库的组合为SINGLE和/或重新启动服务，所以我可以把它分离，因为应用服务器通常记录了进去。

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.

3）虽然脱离我经过文件系统和复制的MDF和LDF文件。

3) While detached I go through the file system and copy the mdf and ldf files.

4）我重新连接通过SQL Management Studio中的数据库

4) I re-attach the database via SQL Management Studio

5）我压缩复制的文件，我将它们移动到一个FTP服务器，这样谁需要它们的人能够得到他们。

5) I zip the copied files, and I move them to an FTP server so the people who need them can get them.

这是一个可怕的，低效的过程。这不只是一个问题需要的模式，而是需要人们与实际生产数据的快照工作，对自己的本地机器进行破坏性实验的目的。幸运的是，压缩数据库是非常小的 - 也许30兆的日志

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.

那么理想，我想创建有一个按钮，用户可以preSS启动当前数据库打包成一个zip文件中的ASP .NET Web应用程序的网页，然后我会只是提供链接到文件下载。

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.

推荐答案

为什么不能做一个普通的备份（容易做到的SqlCommand），并添加了一个功能，用户能够轻松恢复backupfile上点击一个按钮？

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?

什么是使用消耗你的数据库的最终用户？一个WinForm程序？然后很容易做尽一切与点击一个按钮，为用户。

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.

下面是一些例子code为：

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!

您必须有包含此（其他城市的ftp服务器到你的）一个movetoftp.bat： FTP -s：ftpcommands.txt ftp.myftp.net

You have to have a movetoftp.bat that contains this (change ftp-server to your): ftp -s:ftpcommands.txt ftp.myftp.net

和你必须有一个包含这一个ftpcommands.txt（你可以有恰到好处的zip文件由sqlcommands太创建dnamically这个文件，但是我让你做你自己）：

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):

ftpusername ftppassword 二进制 提示ñ mput的C：\备份\ *拉链 退出

ftpusername ftppassword binary prompt n mput c:\backups\*.zip quit