1

On a physical Dell server, I am trying to setup a backup job to *.bak file on to failover server.

In the video, DBA Fundamentals - backup 3 - setting up maintenance plans @6:24 backing up to a local drive but need to be on failover server.

I created a file on to another server drive \\mssql\backup. I try to run a backup file on to another physical server and it will not backup file over .bak file to failover. Do you have any recommendations on how to do this?

I need a T-SQL script in my management jobs to create .bak file over to failover server so I do not have to copy and paste .bak file. Used in agent.

I am trying to test the disaster recovery .bak for mcafee epoilcy orchestrator with MSSQL database using the backup file.

Improve this question
4
  • Does this article help?
    – HandyD
    Commented Apr 27, 2021 at 5:33
  • Thanks for the article. It helps some. The video I was referring to was on youtube by Brent Ozar.
    – ricknut
    Commented Apr 27, 2021 at 16:46
  • "I try to run a backup file on to another physical server and it will not backup file over .bak file to failover." you mean SQL Server fails to backup to another location? possible lack of permissions?
    – Ronaldo
    Commented Apr 29, 2021 at 18:51
  • I ran into this using Ola Hallengrens backup scripts to a UNC path. In my case I changed the SQL Service login to use a domain account. When you're writing the backup it's the service creds that need write permission. Who is SQL running as?
    – Sir Swears-a-lot
    Commented Apr 30, 2021 at 4:43

1 Answer 1

Reset to default
2

It's "Most Likely" a Permissions Issue

If you're performing a network SQL Server backup to a file share you'll most likely need to do one or more of these three common (A,B,C) tasks below:

A.> SQL Server Service

If you're running your backup from a SSMS query window, you need to make sure that the SQL Server service is using an Active Directory account that has permissions to write to the share location you're writing to.

B.> SQL Server Agent Service

If you're running the backup from the SQL Agent with a schedule job, you need to make sure that the SQL Agent is using an AD account that has permissions to write to the target share.

C.> Trusted Connection with sqlcmd

If you're running sqlcmd you need to use the -E and make sure the account that's running sqlcmd (the account running the cmd window) has permissions to write to the target share.

SQL Server Configuration Manager

If you need to change your SQL Server services to use an AD account, you'll need to apply the AD account using SQL Server Configuration Manager (SSCM). SSCM configures other things on the Windows Server besides just adding the AD account. After you change the associated service account, you'll need to restart that service for the account to take affect.

Workgroups instead of AD

If you're using a Windows Workgroup environment instead of AD, you'll want to make sure that both the Windows SQL Server and the target Windows box, have separate but identically named accounts on them with the same passwords. Run the SQL service you're using for the backup with this account.

Target Server Permissions

If you're unsure that the SQL Service/Agent accounts has the right permissions, just recreate the share on the target Windows machine and give that account at least read/write access to the share location using the folder's properties --> Share tab --> Share ... button.

Note

All that being said, if you're configuring backups for an AlwaysOn configuration there are separate gotchas that apply depending on which SQL Server is primary and which one is secondary and how you have backups configured. But, based on the video you presented, I don't think you're having issues with AlwaysOn backups.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.