osql backup

Kristian Tollefsen 1 Reputation point

I have a program that backs up its own database once a day and stores it for seven days.

Orgnial command it tryded to run was:
osql -S Server\Mydatabase -U$UN -P$PW -d$DB -Q "sp_backuprun $DB ,'c:\mssql\backup\$DB_$DAY.bak"'

Resault was:
[SQL Server Native Client 10.0]SQL Server Network Interfaces: Error Locating
Server/Instance Specified [xFFFFFFFF].
[SQL Server Native Client 10.0]Login timeout expired
[SQL Server Native Client 10.0]A network-related or instance-specific error
has occurred while establishing a connection to SQL Server. Server is not
found or not accessible. Check if instance name is correct and if SQL Server
is configured to allow remote connections. For more information see SQL Server
Books Online.
Part of the problem is was becuse of altered credical so i changed it to this.

osql -SMyserver -Usa -PMypassword -dMydatabase -Q "sp_backuprun Mydatabase ,'c:\mssql\backup\$DB_$DAY.bak"'
Now command works but it gives me a backupfil named $DB_$DAY.bak and not Mydatabase_Saturday.bak and so on whit the other day name.

Any ide


SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,232 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Bjoern Peters 8,766 Reputation points

    $DB and $DAY are variables in your cmd-script, which doesn't seem to be set or used correctly in your cmd-script.

    in your original cmdline those variables were used to specify the DatabaseName (and of cause the $DAY)
    osql -S Server\Mydatabase -U$UN -P$PW -d$DB -Q "sp_backuprun $DB ,'c:\mssql\backup\$DB_$DAY.bak"'

    Now your are setting these values "hardcoded"...
    osql -SMyserver -Usa -PMypassword -dMydatabase -Q "sp_backuprun Mydatabase ,'c:\mssql\backup\$DB_$DAY.bak"'

    For a better support within this Q&A forum, it would be helpful if you post the complete script (and the stored procedure)

    1 person found this answer helpful.
    0 comments No comments

  2. CathyJi-MSFT 21,106 Reputation points Microsoft Vendor

    Hi @Kristian Tollefsen ,

    Agree with Erland, could you share us the command of sp_backuprun?

    If you wat to save SQL backup with date in filename, you can also try to use the T-SQL from below blogs. Or refer to the T-SQL to adjust your SP.

    DECLARE @name NVARCHAR(256) -- database name    
    DECLARE @path NVARCHAR(512) -- path for backup files    
    DECLARE @fileName NVARCHAR(512) -- filename for backup    
    DECLARE @fileDate NVARCHAR(40) -- used for file name  
    -- specify database backup directory  
    SET @path = 'C:\test\'    
    -- specify filename format  
    SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(NVARCHAR(20),GETDATE(),108),':','')  
    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     
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'    
       BACKUP DATABASE @name TO DISK = @fileName    
       FETCH NEXT FROM db_cursor INTO @name     
    CLOSE db_cursor     
    DEALLOCATE db_cursor  

    How to Backup SQL Database with Date in File Name?
    Database Backup File Name Format DBname_YYYYMMDD.BAK

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    0 comments No comments

  3. Olaf Helper 38,711 Reputation points

    osql -S Server**Mydatabase**

    A database name is never part of a SQL Server (instance) name.

    gives me a backupfil named $DB_$DAY.bak and not Mydatabase_Saturday.bak

    Of course, because you named the backup file in the script that way.

    0 comments No comments