how to specify folder for backup command when using sqlcmd

Question

Sql Server 2019 express edition.

I am trying to setup a backup (temporarily) using task scheduler in windows 10.

I have sqlcmd as the command. I have -S mymachine\sqlexpress -U myuser -P myuserpass -i c:\mytools\backupdbcommand.sql

In the backupdbcommand.sql file I have backup database mydatabasename TO DISK = N'C:\backups\dbbak.bak'

Since I need the backup file name to be unique, I was wondering if I could just specify the folder and let SQL generate a unique backup filename as it normally would.

Again, this is express edition so no built in Sql backups.

Answer 1

Hi,

I was wondering if I could just specify the folder and let SQL generate a unique backup filename as it normally would.

This is not the "normally" way

SQL Server does not generate queries and in fact it has no GUI. It is "just" a set of services which you control using queries. The queries can be sent using different applications/utilities.

I need the backup file name to be unique

This is a very common request with a simple solution in the client side. You should use a parameter in the scrip you run

If you run the command using bat file, then all you need is to manage the name of the file in .bat file script like this:

set timehour=%time:~0,2%
sqlcmd -S INSTANCENAME -i C:\Users\ronen\Desktop\test.sql -o c:\Users\name\Desktop\name-%date:~-4,4%%date:~-10,2%%date:~-7,2%-%timehour: =0%%time:~3,2%.csv

If you are using SQL Server dynamic query procedure xp_cmdshell then you can use something like this:

DECLARE       @sqlCommand   VARCHAR(max)
DECLARE       @filePath     VARCHAR(100)
DECLARE       @fileName     VARCHAR(100)
 
SET    @filePath = 'C:\Users\ronen\Desktop\'
SET    @fileName = 'Output_' +
       + CONVERT(VARCHAR, GETDATE(), 112) + '_' +
         CAST(DATEPART(HOUR, GETDATE()) AS VARCHAR) + '_' +
         CAST(DATEPART(MINUTE,GETDATE()) AS VARCHAR) + '.csv'
SET    @sqlCommand =
       'SQLCMD -S server_name -E -d master -q "select @@servername" -o "' +
       @filePath + @fileName +
       '" -h-1'
 
PRINT       @sqlCommand
 
--EXEC   master..xp_cmdshell @sqlCommand
GO 

Answer 2

Welcome to Q&A Forum;

this is a great place to get support, answers, and tips.

Thank you for posting your question; I'll be more than glad to help you out.

An open-source solution is available for automatic SQL server backups, even from the command line, which dynamically creates those filenames.

You can find this script, that creates some stored procedures on your SQL Server, which then can be executed from the cmd/bat file... https://ola.hallengren.com

Both ways Ronen showed you above are widespread and easy solutions...

I hope my answer is helpful to you,

Your

Bjoern Peters

If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!