SQL Server command line backup statement

Question

Is there a way to script out SQL Server backup in to a batch file, so that it could be executed from a command line?

stackoverflow.com/questions/122690/…

David d C e Freitas
– David d C e Freitas

2014-03-11 05:25:13 +00:00
Commented Mar 11, 2014 at 5:25 — David d C e Freitas
– David d C e Freitas, Commented Mar 11, 2014 at 5:25

Seb · Accepted Answer · 2012-05-03 03:18:43Z

59

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

edited May 3, 2012 at 3:18

answered May 19, 2009 at 0:50

Seb

5,8624 gold badges29 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Hamish Grubijan Over a year ago

How about restoring database from file?

Hamish Grubijan Over a year ago

Also, adding a reference to the original manual would be awesome. Thanks!

Seb Over a year ago

@hamish-grubijan - I added Restore to the answer

oarrivi Over a year ago

@Ramesh, You can use -U and -P options to set username and password.

Community · Accepted Answer · 2017-05-23 12:26:12Z

3

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

edited May 23, 2017 at 12:26

CommunityBot

11 silver badge

answered Feb 21, 2014 at 15:42

David Duman

6,6567 gold badges47 silver badges55 bronze badges

Comments

Aksel L · Accepted Answer · 2017-12-04 09:39:18Z

3

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)

answered Dec 4, 2017 at 9:39

Aksel L

1311 silver badge3 bronze badges

Comments

Ali Shafai · Accepted Answer · 2009-05-19 00:50:57Z

2

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

answered May 19, 2009 at 0:50

Ali Shafai

5,1618 gold badges37 silver badges50 bronze badges

Comments

Supawat Pusavanno · Accepted Answer · 2015-04-27 10:53:12Z

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=<<back up folder here>>

:: set Sql Server location ex. set SERVERNAME=localhost\SQLEXPRESS
set SERVERNAME=<<sql host here>>

:: set Database name to backup
set DATABASENAME=<<db name here>>

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

Guilherme Costa · Accepted Answer · 2009-10-12 09:52:07Z

1

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!!

answered Oct 12, 2009 at 9:52

Guilherme Costa

111 bronze badge

Comments

Ivan · Accepted Answer · 2023-05-12 11:54:47Z

batch + `sqlcmd`

As a rule, a command line backup script is needed in order to run it automatically. In addition to the backup itself, the script must do two more things:

Delete obsolete backups - if they are not deleted, then over time the hard disk space will run out, and storing backups indefinitely is not necessary.
Leave at least somewhere a notification about the result of the backup. - the easiest way is to display the notification in the Windows Event Log. In this case, the error can be found in Server Manager > Dashboard.

Here is a script to backup one database, clean up (delete obsolete backups), and write a notification to the Windows Event Log:

set DB_NAME=MyDatabaseName
set BACKUP_DIR=C:\Backups
set DAYS_TO_KEEP=0
echo "Starting backup of %DB_NAME% database..."
sqlcmd -E -S . -Q "BACKUP DATABASE %DB_NAME% TO DISK='%BACKUP_DIR%\%DB_NAME%_%date:/=-%_%time::=-%.bak' WITH INIT, COMPRESSION"
if %ERRORLEVEL% neq 0 (
    echo "Backup failed with error code %ERRORLEVEL%."
    eventcreate /T ERROR /L APPLICATION /ID 100 /D "SQL Server backup failed with error code %ERRORLEVEL%."
    goto end
)
echo "Deleting old backup files older than %DAYS_TO_KEEP% days in directory %BACKUP_DIR% ..."
forfiles /P "%BACKUP_DIR%" /M "%DB_NAME%*.bak" /D -%DAYS_TO_KEEP% /C 2>nul "cmd /c if @ISDIR==FALSE del @PATH"
echo Old backup files deleted successfully.
:end
echo "Script complete."

powershell + `Backup-SqlDatabase`

Batch scripts are quite limited. It is much more efficient to use PowerShell scripts. Moreover, it has modules for interacting with SQL Server directly.

Installing the SqlServer module:

Install-Module -Name SqlServer

Now, for example, Get-ChildItem command can be used to query the list of databases. Use Backup-SqlDatabase command to backup the databases. Here is the command to backup all databases in general:

Get-ChildItem "SQLSERVER:\SQL\[server-name]\[instance-name]\Databases" | Backup-SqlDatabase -BackupContainer "[backup-path]"

Here is an example:

Get-ChildItem "SQLSERVER:\SQL\MSI\DEFAULT\Databases" | Backup-SqlDatabase -BackupContainer "c:\Backups2\"

In principle, a very complex script can be written in PowerShell, which will be limited only by your needs.

Here is a sample script to send a backup to shared folder.

Learn more about Backup-SqlDatabase

Third-party command line backup utilities

Instead of writing a script yourself, a third-party backup utility can be used. For example SqlBak-CLI. An example of sending a backup to FTP.

Create a JSON file with settings for where to get the backup and where to send it:

{
    "source":
    {
        "type":"mssql",
        "source_name":"source-name",
        "data_source":".",
        "trust_server_certificate":true,
        "is_integrated_security":false,
        "user_name":"sa",
        "user_password":"*******",
        "databases":[ "mssql_custom" ]
    },
    "storages":[
        {
            "type":"ftp",
            "server_name":"backup-storage.com",
            "user_name":"ftp-user",
            "user_password":"*************",
            "path":"backups"
        }
    ]
}

To start a backup, use the following command:

sqlbak-cli run-backup --job-settings=backup-settings.json

More about SqlBak-CLI

Collectives™ on Stack Overflow

SQL Server command line backup statement

7 Answers 7

4 Comments

Comments

Comments

Comments

Comments

Comments

batch + `sqlcmd`

powershell + `Backup-SqlDatabase`

Third-party command line backup utilities

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

4 Comments

Comments

Comments

Comments

Comments

Comments

batch + sqlcmd

powershell + Backup-SqlDatabase

Third-party command line backup utilities

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

batch + `sqlcmd`

powershell + `Backup-SqlDatabase`