SQL Server command line backup statement

oo7oh9g9  于 2023-03-28  发布在  SQL Server
关注(0)|答案(7)|浏览(205)

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?

chhqkbe1

chhqkbe11#

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
gojuced7

gojuced72#

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
ibps3vxo

ibps3vxo3#

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)

6gpjuf90

6gpjuf904#

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

093gszye

093gszye5#

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

qnzebej06#

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

oiopk7p5

oiopk7p57#

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:

  1. 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.
  2. 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":"*******",
        "backup_items":{
            "item_types":[
                "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

相关问题