I'm looking for a way to create a monthly recurring backup of a SQL server database using sqlcmd.

I've seen scripts for SQL Express, but they only execute once. I'm looking for a sql script that will use agent or another means to launch the backup on a periodically.


  • Thanks for the reply and the questions. I'm trying to stay away from the task scheduler due to being a locked down/secure system. I'm looking for a tsql script to implementing msdb.dbo.sp_add_job, msdb.dbo.sp_add_jobstep, and msdb.dbo.sp_add_jobschedule. At least that's what I think I need to do, since I posted the question.
    – garym
    Aug 10 '16 at 23:46
  • Ahh yes, 2012 and 2008r2 on secure 2008R2 server. No Express versions allowed. I'm the dba/machine admin. sa access but no domain admin privileges. The task scheduler belongs to the domain guys. I'm writing something (an installer) now. I'll see how it goes. I'll post it as soon as I get it working. I also need to pass most the variables via the command line
    – garym
    Aug 11 '16 at 0:59
  • It may make best sense to just create backup devices via TSQL with SSMS and then create a SQL Agent job with each step having logic in it to backup those DBs to the backup devices one-by-one with all having static names such as DBName1.bak so each time it's run, the existing file is overwritten and then just let SQL Agent take care of the scheduling. The SQL Agent job would likley be more powerful that SQLCMD and if you truly needed something that takes multiple parameters, you could create it to a dynamic SP and then pass in the arguments from there. Aug 11 '16 at 1:30
  • I do this all the time as the DBA of multiple instances of SQL Server and it's a very reliable method and you could also set it up so that each step moves onto the next step regardless of failure or success, and the very last step could then check the status of all previous steps and if any had failure, etc. then you could have it shoot out an email to indicate there's an issue and you should check logs, etc. Aug 11 '16 at 1:34
  • Last thought as well, so even though you're not a server admin, technically you are if you are sa of the SQL Server instance. Enabling and using cmdexec you can use most all commands from the server OS level with local admin permissions. I also wanted to share this with you ola.hallengren.com/sql-server-backup.html as I've heard nothing but good about this tool but I've not used it. If you're interested in the SQL backup devices and SQL Agent job to backup via those, I can write something up later tomorrow sometime. Check out dba.stackexchange.com too. Aug 11 '16 at 2:36

some steps as you asked about sqlcmd:

  • run sql server profiler
  • perform the backup from Management Studio
  • see the sql commands in the profiler
  • eventually make some adjustments to the sql code (eg. appending the current date to the file name)
  • save the sql code into a file makedump.sql
  • create a scheduled task with a makedump.bat file which executes "sqlcmd ... -i makedump.sql"

Or use the easy way:

  • launch Management Studio
  • browse for your sql instance in object explorer
  • expand the server node and "maintenance plan"
  • right click for the wizard and follow instructions

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.