Schedule and automate backups of SQL Express databases in SQL Server Express 1

[JoPaBC]

Hello,

I tried to follow these instructions (see below) in order to backup SQL Express 2017 automatically in three steps (A - stored procedure, B - bat file, C - scheduled task) using T-SQL.

Link

The first step (Step A) went OK.

The next step, B, is failing if run as .bat (sqlcmd -U SQLLogin -P password -S .\SQLEXPRESS -Q "EXEC sp_BackupDatabases @backupLocation ='D:\SQLBackups', @BackupType='F'").
It works if run as a query directly from SSMS v18.2 "EXEC sp_BackupDatabases @backupLocation ='D:\SQLBackups', @BackupType='D'" on the server.

If run from a workstation it fails in both, .bat and SSMS v18.4, giving an error in SSMS - Cannot open backup device 'D:\xxx.BAK'. Operating system error 5(Access is denied.).

The best option would be to run it as .bat from a workstation; can you please advise what needs to be done to do it?

Thx

 

[fredericofonseca]

-S .\SQLEXPRESS - this means a SQLEexpress instance on the local pc so when you execute on your workstation it is trying to connect to your local instance and it is trying to write down to a local D: drive
you need to change it to be
-S servername\instancename

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[JoPaBC]

Yes, I tried to run the .bat with -S .\SQLEXPRES directly on the server

Server .bat
sqlcmd -U sa -P pswd -S .\SQLEXPRESS2 -Q "EXEC sp_BackupDatabases @backupLocation ='E:\SQLBackups', @BackupType='F'"

Workstation .bat
sqlcmd -U sa -P pswd -S DSDI\SQLEXPRESS2 -Q "EXEC sp_BackupDatabases @backupLocation ='E:\SQLBackups', @BackupType='F'"

Both options didn't do anything.

The only working case is if run as a query from SSMS on the server after adding the full control of the backup location to the MSSQL$SQLExpress user. On the workstation has the full control the NETWORK SERVICE user.

 

[JoPaBC]

I added the 'Everyone' user with the full copntrol of the 'D:' drive but I am still getting this error message

Cannot open backup device 'D:\SQLBackupsDB1_FULL_01242020_105232.BAK'. Operating system error 5(Access is denied.)

:-(

 

[gmmastros]

Access Denied error usually means that the "Log On" for the SQL Service does not have permissions.

First, be clear about the drive. When you run this command against the server's sql server service, the D and/or E drive would be the server's D and/or E drive. Please make sure that these drives are physical drives on the server. If they are mapped drives, you'll need to set the service's logon to something that has permissions to the mapped drive location. If the drives are local drives to the server, there could still be a permission problem.

To check this...

Log on to the server.
Open services control panel
Find the SQL Server service
Right Click -> Properties
Click the "Log On" tab.

Make sure that the logon has permissions to the D and/or E drive.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JoPaBC]

Yes, that was the problem, thanks!