April 9, 2024 at 7:14 pm
Am trying to set up backups to a storage container on Azure government.
If I backup a database using Tasks -> Backup database I am able to backup successfully; it generates the following script which works:
BACKUP DATABASE [BLAH]
TO URL = N'https://myURL.net/backup-container/BLAH_backup.bak' WITH NOFORMAT,
NOINIT, NAME = N'BLAH-Full Database Backup', NOSKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
However, when I try to create the backup via Maintenance Plan, I am getting an error. The script it is trying to run is:
BACKUP DATABASE [BLAH]
TO URL = N'https://myURL.net/backup-container/BLAH_backup.bak'
WITH CREDENTIAL = N'https://myURL.usgovcloudapi.net/backup-container',
NOFORMAT, NOINIT, NAME = N'BLAH_backup', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
The actual error message I get if I try to run the script is:
Msg 3225, Level 16, State 1, Line 1
Use of WITH CREDENTIAL syntax is not valid for credentials containing a Shared Access Signature.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
It looks like I just need to remove the "WITH CREDENTIAL" but I don't see any way to do that via the Maintenance Plan wizard. The credential it is trying to use does exist - and it's valid, manual backup is working successfully - but it appears to not need to be explicitly called in the script.
I could just write a script myself and run it via a scheduled job, but prefer using maintenance plans if possible to keep things orderly.
April 9, 2024 at 7:32 pm
Why not just schedule the TSQL statement in a sqlagent job?
or use a powershell jobstep to create the backup using sqlagent ( or scheduled tasks if for express edition )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 9, 2024 at 9:29 pm
My preference is to NOT use maintenance plans unless it is the only option or required by the business for some reason. The reason being - configuration. Maintenance plans give you a LOT fewer options when configuring them. For example, if you do index maintenance on all indexes, it will fail if you have disabled page locks on the index. No clue why you'd do that, but I had a vendor tool do just that and broke the maintenance plan.
Then scenarios like yours - maintenance plan doesn't like it.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 11, 2024 at 6:31 pm
Ola Hallengren's solution supports backup to URL - it might be a better option. This gist helped me get started with it - though it sounds like you already have the pieces in place for it. The managed backup options might also be worth a look if it meets your RPO and backup retention needs.
I don't have any advice for the maintenance plan issue - it's possible it's not supported or it could be a bug.
DBA Dash - Free, open source monitoring for SQL Server
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply