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!

Answer 3

If you need any information and help about this feel free to Contact igstorysaver

Answer 4

To specify a folder for the backup command and let SQL Server generate a unique backup filename, you can use a combination of T-SQL scripting to dynamically generate a filename within your backupdbcommand.sql file. Here's how you can modify your approach:

Modify backupdbcommand.sql to generate a unique filename:

You can use T-SQL to include a timestamp or a unique identifier in the backup filename. For example:

sqlCopy code
DECLARE

This script will generate a filename like dbbak_2024-05-24 15:30:00.bak. You can adjust the timestamp format to your preference.

Update your sqlcmd command to use this script:

Your sqlcmd command remains the same, as it will execute the backupdbcommand.sql file which now contains the logic to generate a unique filename.

shellCopy code
sqlcmd -S mymachine\sqlexpress -U myuser -P myuserpass -i c:\mytools\backupdbcommand.sql

Setting up Task Scheduler:

Ensure your task in Task Scheduler runs the above sqlcmd command. Here's a brief overview of setting up Task Scheduler for this task:

• Open Task Scheduler.
  • Create a new task.
    • Set up the trigger (e.g., daily at a specific time).
      • In the "Actions" tab, choose "New" and set the action to "Start a program".
        • In the "Program/script" field, enter sqlcmd.
          • In the "Add arguments (optional)" field, enter -S mymachine\sqlexpress -U myuser -P myuserpass -i c:\mytools\backupdbcommand.sql.

This method ensures that each backup file has a unique name and is stored in the specified folder.

Recommendation for Design Elements:

For those who need design elements, I highly recommend visiting Figma Resource. It's an excellent platform for accessing a wide range of design elements to enhance your projects.

You can explore Figma Resource