Creating SQL Server backup file (.bak) with c# to any location

Question

Hello all!

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.

Regards Kris

Answer 1

Take a look at this article.

Remember to set the permissions for the account that the SQL Server instance is running with.

Answer 2

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

MSDN SSIS Info Link 1
SSIS 2005 Tutorial Link 2

Take a look at it.

Answer 3

Integration services looks like a new faculty I would have to learn, couldn't find there anything usefull, sorry Nilsson. Magnus, I was trying to use a solution of yours but it doesn't help either, lets say I can find in this way 3 users on my system: Administrator, Kris and Guest but when I set Read, Write and even Full Controll using your script I'm still not able to backup to this directory,still only to Data or Backup directories craeted by SQL Server. Anyone has an idea how to set a permissions on any folder which would be the same as 'Data' or 'Backup' belonging to SQL Sever? Any help would be highly appreciated!

Answer 4

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:

• 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.

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 this may do the trick

btw: Magnus Johansson already gave you 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 ...

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

Answer 5

Hello dittodhole!

Yeah, I'm using Smo, when I specify the location different than 'Backup' or 'Data' folders inside SQLServer installation location I get everytime an exception: 'FailedOperationException' - An exception occurred while executing a Transact-SQL statement or batch. And that's it, nothing about security here but I guess it must be it, as to the two mentioned locations I can do backup without problems. Tested on other machines (Vista, XP), same story! You can see my code snippets at my latest article here: http://www.softwarepassion.com/?p=80

Edit:

Yes dittodhole I would have to probably add a permission to the folder to SQLService account but then, I have no idea how to do it. How to add such a permission programatically in C# to a folder? Thanks!

Answer 6

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:

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!