Post reply

Long name in TSQL is creating errors

  • 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.

     

     

  • July 24, 2019 at 11:04 am

    #3665654

    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

  • July 24, 2019 at 12:27 pm

    #3665663

    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, 2 months ago by  jbeclapez.

  • July 24, 2019 at 1:29 pm

    #3665705 

    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!

  • July 24, 2019 at 2:21 pm

    #3665759

    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.

  • July 24, 2019 at 2:25 pm

    #3665760

    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

  • July 24, 2019 at 2:47 pm

    #3665773

    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.

  • July 24, 2019 at 3:37 pm

    #3665787

    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.

  • July 24, 2019 at 3:45 pm

    #3665790

    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

  • July 24, 2019 at 5:17 pm

    #3665815

    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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • July 24, 2019 at 8:14 pm

    #3665864

    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)
    Intro to Tally Tables and Functions

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

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