SQL Server command line backup statement

Question

Does any one know if there is a way to script out SQL Server backup in to a batch file  so that it could be executed from a command line

User · Answer

You can use sqlcmd to run a backup  or any other T-SQL script  You can find the detailed instructions and examples on various useful sqlcmd switches in this article  Working with the SQL Server command line  sqlcmd

User · Answer

Here s an example you can run as a batch script  copy-paste into a  bat file   using the SQLCMD utility in Sql Server client tools   BACKUP    echo off cls echo -- BACKUP DATABASE -- set  p DATABASENAME Enter database name      filename format Name-Date  eg MyDatabase-2009 5 19 bak  set DATESTAMP  DATE  -4   DATE  7 2   DATE  4 2  set BACKUPFILENAME  CD   DATABASENAME - DATESTAMP  bak set SERVERNAME your server name here echo   sqlcmd -E -S  SERVERNAME  -d master -Q  BACKUP DATABASE   DATABASENAME   TO DISK   N  BACKUPFILENAME   WITH INIT   NOUNLOAD   NAME   N  DATABASENAME  backup   NOSKIP   STATS   10  NOFORMAT  echo  pause   RESTORE   echo off cls echo -- RESTORE DATABASE -- set  p BACKUPFILENAME Enter backup file name  CD   set  p DATABASENAME Enter database name  set SERVERNAME your server name here sqlcmd -E -S  SERVERNAME  -d master -Q  ALTER DATABASE   DATABASENAME   SET SINGLE USER WITH ROLLBACK IMMEDIATE      WARNING - delete the database  suits me    sqlcmd -E -S  SERVERNAME  -d master -Q  IF EXISTS  SELECT   FROM sysdatabases WHERE name N  DATABASENAME     DROP DATABASE   DATABASENAME       sqlcmd -E -S  SERVERNAME  -d master -Q  CREATE DATABASE   DATABASENAME        restore sqlcmd -E -S  SERVERNAME  -d master -Q  RESTORE DATABASE   DATABASENAME   FROM DISK   N  CD   BACKUPFILENAME   WITH REPLACE      remap user login  http   msdn microsoft com en-us library ms174378 aspx  sqlcmd -E -S  SERVERNAME  -d  DATABASENAME  -Q  sp change users login  Update One    login-name    user-name   sqlcmd -E -S  SERVERNAME  -d master -Q  ALTER DATABASE   DATABASENAME   SET MULTI USER  echo  pause

User · Answer

I am using SQL Server 2005 Express  and I had to enable Named Pipes connection to be able to backup from the Windows Command  My final script is this    echo off set DB NAME Your DB Name set BK FILE D  DB Backups  DB NAME  bak set DB HOSTNAME Your DB Hostname echo  echo  echo Backing up  DB NAME  to  BK FILE     echo  echo  sqlcmd -E -S np    DB HOSTNAME  pipe MSSQL SQLEXPRESS sql query -d master -Q  BACKUP DATABASE   DB NAME   TO DISK   N  BK FILE   WITH INIT   NOUNLOAD   NAME   N  DB NAME  backup   NOSKIP   STATS   10  NOFORMAT  echo  echo Done  echo    It s working just fine here

User · Answer

Seba Illingworth s code  In case you need time in your file name  it gives 2014-02-21 1035   echo off cls echo -- BACKUP DATABASE -- set  p DATABASENAME Enter database name  For  f  tokens 2-4 delims       a in   date  t   do  set mydate   c-  a-  b  For  f  tokens 1-2 delims       a in    TIME    do  set mytime   a  b      filename format Name-Date  eg MyDatabase-2009 5 19 bak  set DATESTAMP  mydate   mytime  set BACKUPFILENAME  CD   DATABASENAME - DATESTAMP  bak set SERVERNAME   echo   sqlcmd -E -S  SERVERNAME  -d master -Q  BACKUP DATABASE   DATABASENAME   TO DISK   N  BACKUPFILENAME   WITH INIT   NOUNLOAD   NAME   N  DATABASENAME  backup   NOSKIP   STATS   10  NOFORMAT  echo  pause

User · Answer

Combine Remove Old Backup files with above script then this can perform backup by a scheduler  keep last 10 backup files  echo off    set folder to save backup files ex  BACKUPPATH c  backup set BACKUPPATH  lt  lt back up folder here gt  gt      set Sql Server location ex  set SERVERNAME localhost SQLEXPRESS set SERVERNAME  lt  lt sql host here gt  gt      set Database name to backup set DATABASENAME  lt  lt db name here gt  gt      filename format Name-Date  eg MyDatabase-2009-5-19 1700 bak  For  f  tokens 2-4 delims       a in   date  t   do  set mydate   c-  a-  b  For  f  tokens 1-2 delims       a in    TIME    do  set mytime   a  b   set DATESTAMP  mydate   mytime  set BACKUPFILENAME  BACKUPPATH   DATABASENAME - DATESTAMP  bak echo   sqlcmd -E -S  SERVERNAME  -d master -Q  BACKUP DATABASE   DATABASENAME   TO DISK   N  BACKUPFILENAME   WITH INIT   NOUNLOAD   NAME   N  DATABASENAME  backup   NOSKIP   STATS   10  NOFORMAT  echo      In this case  we are choosing to keep the most recent 10 files    Also  the files we are looking for have a  bak  extension for  f  skip 10 delims     F in   dir  BACKUPPATH    bak  s b o-d a-d   do del    F

User · Answer

if you need the batch file to schedule the backup  the SQL management tools have scheduled tasks built in

[sql-server] SQL Server command line backup statement

Examples related to sql-server

Examples related to command-line

Examples related to backup