×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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

RE: Schedule and automate backups of SQL Express databases in SQL Server Express

-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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Schedule and automate backups of SQL Express databases in SQL Server Express

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

RE: Schedule and automate backups of SQL Express databases in SQL Server Express

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

sad

RE: Schedule and automate backups of SQL Express databases in SQL Server Express

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

RE: Schedule and automate backups of SQL Express databases in SQL Server Express

(OP)
Yes, that was the problem, thanks!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close