osql backup

Kristian Tollefsen 1 Reputation point
2022-07-13T19:06:19.237+00:00

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

Regards
Kristian

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
    2022-07-14T12:35:38.09+00:00

    $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
    2022-07-14T02:47:55.177+00:00

    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),':','')  
       
    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  
    

    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
    2022-07-14T05:21:14.44+00:00

    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