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
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
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)
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"
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.
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:
7条答案
按热度按时间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:
RESTORE:
gojuced72#
Seba Illingworth 's code, In case you need time in your file name (it gives 2014-02-21_1035)
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)
6gpjuf904#
if you need the batch file to schedule the backup, the SQL management tools have scheduled tasks built in...
093gszye5#
Combine Remove Old Backup files with above script then this can perform backup by a scheduler, keep last 10 backup files
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:
It's working just fine here!!
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:
Here is a script to backup one database, clean up (delete obsolete backups), and write a notification to the Windows Event Log:
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:
Now, for example,
Get-ChildItem
command can be used to query the list of databases. UseBackup-SqlDatabase
command to backup the databases. Here is the command to backup all databases in general:Here is an example:
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:
To start a backup, use the following command:
More about SqlBak-CLI