osql backup

Question

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

Answer 1

$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)

Answer 2

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

Answer 3

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.