使用 c# 创建 SQL Server 备份文件 (.bak) 到任何位置
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/728658/
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
Creating SQL Server backup file (.bak) with c# to any location
提问by
I'm trying to write simple application in C# which will allow me to backup, zip and send over ftp my SQL Server database. One problem I have encountered is that I'm not able to create the backup file (.bak) if I try to do it in different location than "C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Backup" or "C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data" folder. I understand that this is a premission problem. Could someone point me to the resources or write here a short snippet how to programmatically add such a permission to any folder on my system.
我正在尝试用 C# 编写简单的应用程序，它允许我备份、压缩和通过 ftp 发送我的 SQL Server 数据库。我遇到的一个问题是，如果我尝试在与“C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Backup”不同的位置创建备份文件 (.bak)，我将无法创建它或“C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data”文件夹。我知道这是一个前提问题。有人可以指向我的资源或在这里写一个简短的片段如何以编程方式向我的系统上的任何文件夹添加这样的权限。
Regards Kris
问候克里斯
回答by Magnus Johansson
Take a look at this article.
看看这篇文章。
Remember to set the permissions for the account that the SQL Server instance is running with.
请记住为运行 SQL Server 实例的帐户设置权限。
回答by Marcus L
Although this may not answer your immediate question, I'd advice you to look into SQL Server Integration Services (SSIS). This looks like the exact thing SSIS was created for, and in the 2008 version there's the possibility to use C# code if needed, should the standard components not do what you need (earlier versions used VB.NET).
尽管这可能无法回答您的直接问题，但我建议您查看 SQL Server 集成服务 (SSIS)。这看起来就像是为 SSIS 创建的确切对象，并且在 2008 版本中，如果标准组件不能满足您的需要（早期版本使用 VB.NET），则可以在需要时使用 C# 代码。
MSDN SSIS Info Link 1
SSIS 2005 Tutorial Link 2
MSDN SSIS 信息链接 1
SSIS 2005 教程链接 2
Take a look at it.
看一看。
回答by Andreas Niedermair
i assume you are running your programm as a scheduled task ... did you give writing permissions to the target folder for the executing user of the task??
我假设您正在将您的程序作为计划任务运行......您是否为任务的执行用户授予了目标文件夹的写入权限？
edit:
with permissions you can have 2 scenarios:
编辑：
有权限，你可以有 2 个场景：
- windows authenification
- mixed authentification
- 窗口认证
- 混合认证
if you are using windows authentification, the read and write permissions of the windows user are taken. otherwise the permissions for the sql server service account.
如果您使用的是 windows 身份验证，则会获取 windows 用户的读写权限。否则 sql server 服务帐户的权限。
and this behaviour makes sense to me and maybe hits the nail in your scenario!
这种行为对我来说很有意义，并且可能在您的场景中大放异彩！
edit 2:
i don't want to encourage you to do so ... some admins may hate you when you mess up their acl's
but thismay do the trick
编辑2：
我不想鼓励你这样做......一些管理员可能会恨你，当你搞砸了他的ACL的，但是这可能做的伎俩
btw: Magnus Johansson already gaveyou a "try-this" link
顺便说一句：马格努斯·约翰逊已经给了你一个“试试这个”链接
no matter for which method you go - be sure to hand in the correct user (as descriped above!)
无论您采用哪种方法 - 请务必提交正确的用户（如上所述！）
(for full history)
...
side-note:
i know this is not the exact answer to your question, but i would recommend you smo to generate backups ...
（完整的历史）
...
旁注：
我知道这不是您问题的确切答案，但我建议您使用 smo 来生成备份...
like
喜欢
using Microsoft.SqlServer.Management.Smo;
var bdi = new BackupDeviceItem(/* your path inlcuding desired file */);
var backup = new Backup
{
Database = /* name of the database */,
Initialize = true
};
backup.Devices.Add(bdi);
var server = new Server(this.SqlServer);
try
{
backup.SqlBackup(server);
}
catch (Exception ex)
{
// * log or sth
}
you only have to care for the .dll's. take assemblies for the desired server version (some params/properties vary through different server versions)
more info here
你只需要关心 .dll 的。为所需的服务器版本获取程序集（一些参数/属性因不同的服务器版本而异）
更多信息在这里
回答by Andreas Niedermair
Ok Guys, Magnus and dittodhole! Thanks a lot for your help. I have combined Magnus'es link to the article on setting up permisions on the folder together with some more research and finally I've got it :). So reassuming, I'm using Smo, and to create a folder with proper permissions I have to look for the group instead of win32_Users. Here you go a short snippet if someone finds this post he can find it usefull:
好的，伙计们，马格努斯和混蛋！非常感谢你的帮助。我已经将 Magnus 的链接与在文件夹上设置权限的文章结合起来，并进行了一些更多的研究，最后我得到了它:)。所以重新假设，我正在使用 Smo，并且要创建一个具有适当权限的文件夹，我必须查找组而不是 win32_Users。如果有人发现这篇文章，他会发现它很有用，你可以看一个简短的片段：
string tempPath = Directory.CreateDirectory("C:\path_to_your_folder").FullName;
//set permissions
SelectQuery sQuery = new SelectQuery("Win32_Group",
"Domain='" +
System.Environment.UserDomainName.ToString() +
"'");
try
{
DirectoryInfo myDirectoryInfo = new DirectoryInfo("C:\path_to_your_folder");
DirectorySecurity myDirectorySecurity = myDirectoryInfo.GetAccessControl();
ManagementObjectSearcher mSearcher = new ManagementObjectSearcher(sQuery);
foreach (ManagementObject mObject in mSearcher.Get())
{
string User = System.Environment.UserDomainName + "\" + mObject["Name"];
if(User.StartsWith("your-machine-name\SQL"))
{
myDirectorySecurity.
AddAccessRule(new FileSystemAccessRule(User,
FileSystemRights.FullControl,
AccessControlType.Allow));
}
}
myDirectoryInfo.SetAccessControl(myDirectorySecurity);
}
catch (Exception ex)
{
Console.WriteLine(ex.StackTrace);
}
Again thanks everyone for your help! Stackoverflow rocks!
再次感谢大家的帮助！Stackoverflow 太棒了！
回答by Rahul Shinde
Here is a procedure is use for back up in C#.Hope it helps
这是一个用于在 C# 中备份的程序。希望它有帮助
public void BackupDatabase (string BackUpLocation, string BackUpFileName, string
DatabaseName, string ServerName )
{
DatabaseName = "[" + DatabaseName + "]";
string fileUNQ = DateTime.Now.Day.ToString() + "_" + DateTime.Now.Month.ToString() + "_" + DateTime.Now.Year.ToString() +"_"+ DateTime.Now.Hour.ToString()+ DateTime.Now .Minute .ToString () + "_" + DateTime .Now .Second .ToString () ;
BackUpFileName = BackUpFileName + fileUNQ + ".bak";
string SQLBackUp = @"BACKUP DATABASE " + DatabaseName + " TO DISK = N'" + BackUpLocation + @"\" + BackUpFileName + @"'";
string svr = "Server=" + ServerName + ";Database=master;Integrated Security=True";
SqlConnection cnBk = new SqlConnection(svr);
SqlCommand cmdBkUp = new SqlCommand(SQLBackUp, cnBk);
try
{
cnBk.Open();
cmdBkUp.ExecuteNonQuery();
Label1.Text = "Done";
Label2.Text = SQLBackUp + " ######## Server name " + ServerName + " Database " + DatabaseName + " successfully backed up to " + BackUpLocation + @"\" + BackUpFileName + "\n Back Up Date : " + DateTime.Now.ToString();
}
catch (Exception ex)
{
Label1.Text = ex.ToString();
Label2.Text = SQLBackUp + " ######## Server name " + ServerName + " Database " + DatabaseName + " successfully backed up to " + BackUpLocation + @"\" + BackUpFileName + "\n Back Up Date : " + DateTime.Now.ToString();
}
finally
{
if (cnBk.State == ConnectionState.Open)
{
cnBk .Close();
}
}
}
