Long name in TSQL is creating errors

Question

Long name in TSQL is creating errors

jbeclapez

SSC-Addicted

Points: 405
More actions
July 24, 2019 at 9:50 am

#3665617

Hello,
This will be a simple problem to solve for many of you! I am sure of it 😉
I have a script created in SQL agent and when I run it with a simple path it works but not with the long name:
SET @path = 'C:\SQLBackup\'
SET @path = 'C:\Users\Administrator\OneDrive - My Company Name\SQLBackup\'
The error is coming from the space. A bit like we had in MSDOS with ~ used for longnames.
Can someone provide me this solution please?
Thanks for your time.

Viewing 11 posts - 1 through 10 (of 10 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Please will you post the whole script? I suspect that you just need to put double quotes at the beginning and end of the path variable when you use it, but without seeing the script I can't be sure.

John

jbeclapez SSC-Addicted Points: 405 More actions · Answer 2

Thank John Mitchell!

I tried with double quotes before with no success.

DECLARE @name VARCHAR(50) -- database name 
DECLARE @path VARCHAR(256) -- path for backup files 
DECLARE @fileName VARCHAR(256) -- filename for backup 
DECLARE @fileDate VARCHAR(20) -- used for file name

-- specify database backup directory
SET @path = 'C:\Users\Administrator\OneDrive - NAme of Company\SQLBackup\'

-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')

DECLARE db_cursor CURSOR READ_ONLY FOR 
SELECT name 
FROM master.sys.databases 
WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping

OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @name 

WHILE @@FETCH_STATUS = 0 
BEGIN 
SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
BACKUP DATABASE @name TO DISK = @fileName 

FETCH NEXT FROM db_cursor INTO @name 
END


CLOSE db_cursor 
DEALLOCATE db_cursor

This reply was modified 5 years, 5 months ago by jbeclapez.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

SET @fileName = '"' + @path + @name + '_' + @fileDate + '.BAK"'

John

Edit: PS - why not use Ola Hallengren's database backup and maintenance suite? There's no sense in reinventing the wheel!

This reply was modified 5 years, 5 months ago by John Mitchell-245523.

jbeclapez SSC-Addicted Points: 405 More actions · Answer 4

John, it is still not working when I replace my line with your line.

SET @fileName = '"' + @path + @name + '_' + @fileDate + '.BAK"'

PS: I will have a look at the OLA's tools later.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

What's the error message? I'm thinking it may have nothing to do with the spaces in the path, but more to do with trying to back up to C:\Users\Administrator, which the SQL Server service account doesn't (or at least shouldn't) have access to.

John

DesNorton SSC-Insane Points: 24416 More actions · Answer 6

I haven't done this for many years. But I recall needing double path separators

'C:\\Users\\Administrator\\OneDrive - NAme of Company\\SQLBackup\\'

How to post data/code on a forum to get the best help.

jbeclapez SSC-Addicted Points: 405 More actions · Answer 7

spot on John.

I gave acces read/write to everybody on this folder and the script is now working. The original one.

So, do you know how to give access to the SQLAgent to this folder in Read/Write? I tried to add Network Services but it did not work.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

I really would recommend that you back up to a different folder, rather than giving access to the Administrator area. Something on a different server would be best - provided that you're running SQL Server under a suitable account. It's SQL Server that does the backups, not SQL Server Agent, even if it's running in a job.

John

ScottPletcher SSC Guru Points: 101126 More actions · Answer 9

Just create a separate drive path for backups on that machine and, to keep it simple, make sure there are no spaces or other unexpected chars in the path name.

Btw, as soon as you possibly can, you should move those backups to a different physical server.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 10

John Mitchell-245523 wrote:

I really would recommend that you back up to a different folder, rather than giving access to the Administrator area. Something on a different server would be best - provided that you're running SQL Server under a suitable account. It's SQL Server that does the backups, not SQL Server Agent, even if it's running in a job. John

I almost totally agree but need to take it one step further. You did make the stiffer recommendation but I want to highlight it as being uber important.

So far as I'm concerned, it's an absolute taboo to store any backups on the server being backed up, never mind the C: drive. It's a stupid risk to take and, even if it wasn't, the C: drive of the server should be treated as sacred holy ground that should NEVER be tread upon.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)