Tự động backup database đối với SQL Server Express

 

Create the Backup Job

Run SQL Server Management Studio Express.

1. In the tree view, expand Server Objects => New Backup Device.
   
2. The Backup Device dialog opens
   
   1. For Device Name, type in a name for your new backup job.
   2. For Destination, select the path to store the backups. In most cases, it is best to use an location on another device.
   3. Press OK.
3. Right click on the new backup device that you just created and select the option called "Backup Database".
   
   1. Select the backup type (most often this is "Full").
      
4. On the left side, select Backup Options and set the following:
   
   1. Name: Create a name for the backup job.
   2. Description (optional).
   3. Press OK.
5. SQL Server runs the backup job to test it. If successful, it confirms this with the following message:
   
6. To verify the backup output yourself, check the destination folder to ensure that the backup file exists. The backup of the database is in the form of a .bak file.

The setup of the backup job is now complete. Repeat these steps for each database you wish to backup.

Create a Batch Command to Run the Job

A batch file can be used to run the SQL Server backup job that you just created. Here's an example (remember to save this file with a "BAT" file extension):

--- < Begin Code Batch Script > ---
sqlcmd -S MYSERVER\OFFICESERVERS -E -Q 
"BACKUP DATABASE MASTER TO TESTBACKUP"
--- < End Code Batch Script > -----

..where "MYSERVER" is the name of the SQL Server physical machine.
..where "OFFICESERVERS" is the name of the SQL Server.
..where "TESTBACKUP" is the name of the backup job.
..where "MASTER" is the name of the database.

Schedule the Job

For this task to run automatically, it must be assigned to a scheduler program. Here are a few options:

1. The built in Microsoft Windows Scheduler and assign the batch file created above to a scheduled event.
2. FMS offers a program Total Visual Agent, which includes the ability to execute and run a specified batch command on the schedule that best suits your needs and maintain an audit log.

---------------------------------------------------------------------------------------------------

ví dụ: 

Tạo file .bat như sau: 

echo off

:: set folder to save backup files ex. BACKUPPATH=c:\backup

set BACKUPPATH=E:\123

:: set Sql Server location ex. set SERVERNAME=localhost\SQLEXPRESS

set SERVERNAME=LAMLE\SQLSERVER_2014

:: set Database name to backup

set DATABASENAME=test

set DATESTAMP=%date:~6,4%_%date:~3,2%_%date:~0,2%_%time:~0,2%%time:~3,2%

set BACKUPFILENAME=%BACKUPPATH%\%DATABASENAME%_%DATESTAMP%.bak

set zipfilename=%BACKUPPATH%\%DATABASENAME%_%DATESTAMP%.zip

echo.

sqlcmd -U sa -P luakchuong -S %SERVERNAME% -d %DATABASENAME% -Q "BACKUP DATABASE [%DATABASENAME%] TO DISK = N'%BACKUPFILENAME%' WITH INIT , NOUNLOAD , NAME = N'%DATABASENAME% backup', NOSKIP , STATS = 10, NOFORMAT"

echo.

echo running command: creating bak file....

%cmdStr%

set cmdStr="C:\Program Files\WinRAR\winrar.exe" a -afzip "%zipfilename%" "%BACKUPFILENAME%"

echo.

echo running command: %cmdStr%

%cmdStr%

set cmdStr=DEL /q /f "%BACKUPFILENAME%"

echo.

echo running command: %cmdStr%

%cmdStr%

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=2 delims=" %%F in ('dir %BACKUPPATH%\*.zip /s/b/o-d/a-d') do del "%%F"

xcopy /y /f "E:\123\*.zip" "D:\Backup" /c

for /f "skip=2 delims=" %%F in ('dir D:\Backup\*.zip /s/b/o-d/a-d') do del "%%F"

echo.

exit