backup fails in sql 2012

  • If I try to backup of database in sql 2012 with t-Sql as follows:

    Backup database db1

    to disk='c:\myfullbkup.bak' with init

    go

    This code works absolutely fine.

    But if I try to take backup through SSMS -Task -Backup in object explorer and gave the same path of c: drive or any other folder outside sql default "Backup" folder.It gives error as access denied.

    This was not the scene in Sql server 2008.

    Is there anything new that I am missing in Sql 2012.

    If anyone can highlight it.

  • It sounds like a security problem. The GUI works just fine in 2012 (not that I use it or advocate for it). Access denied is a very specific error suggesting that your SQL Server account that the instance is running under doesn't have appropriate permissions.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Since the T-SQL backup succeeded, that implies that the SQL Server service account has appropriate permissions. Could the GUI be using the user's credentials to confirm the existence of the target directory? That's the only variable that I can see here. (which should imply the dozen or so that I can not see.)

  • And I was thinking the opposite, that the T-SQL would be under the user account while the GUI would be otherwise.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • The permissions should be the same. The GUI, whether it issue PoSh or T-SQL, is just asking the services to perform a backup.

    Likely something is being done differently between the query window and OE. Either the entry in the GUI is incorrect, or perhaps the user has different connections.

  • This is the sort of thing that makes me put "Access Denied" at the very top of my most hated error messages list. Just once, I would love to know either 1) What access was denied on, or 2) Who access was denied to. To get a definitive answer in this case, you would likely need to run procmon on the client, but more likely the server.

  • I did login as computername\administrator and sql is in windows authentication mode.

    when I create folder in c drive and give that as my backup path then it works fine but otherwise directly c drive like 'c:\fullbkup.bak' then access denied error comes.

  • Could this be a User Access Control problem, then? Windows is starting to move away from allowing people to write files tot he root of drives.

  • Matt Crowley (8/22/2014)


    Could this be a User Access Control problem, then? Windows is starting to move away from allowing people to write files tot he root of drives.

    That's a good point, though I'm not sure why the T-SQL one works then.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply