Description
This article provides important information about automatically backing up the WMS database correctly.
As you may already be aware of, many system backups just take a snapshot (similar to copy/paste) of a whole drive or incremental (copy / paste of only those files that have changed since last backup). This method is not suitable for SQL databases.
There will be no error message when you select the files and press Copy (or Ctrl+C), but when you try to paste them, you'll get the 'File is open in SQL Server' error message. Similarly, a backup system will receive and error along the lines of file is in use. SQL databases are LOCKED while the SQL server instance is running with the Database attached and online.
When setting up your system backup, it is recommended you avoid backing up the actual database (.mdf and .ldf) paths to avoid backup failure.
You can achieve a backup via any method below:
Take the database offline and copy the files (some backup systems allow this functionality but sometimes the SQL instance fails to restart and users cannot use the WMS in the morning)
Stop the SQL Server and copy the files (Manual)
If you need only the database state, create a full database backup (Manual)
If you need the database state and all its transactions, create the full database backup and the transaction log backup (Manual)
SQL provides a couple of utilities to create auto backup of database files (.bak) which may then be copied/backed up via your normal backup procedure that has been put in place by your IT Administrator.
Detail Steps
Option 1 - SQLCMD Batch File for a Stored Procedure (most SQL Versions)
You may like to ask your Datapel Support member for assistance to setup this task.
Daily backups may also be done using the SQLCMD which triggers an SQL store Procedure.
Run attached .SQL file once via SQL Management Studio.
Setup a windows batch file to run SQLCMD.
Example Batch Contents (also attached below)
"sqlcmd" -U datapel -P datapelwms -S . -Q "EXEC sp_BackupDatabases @backupLocation='D:\WMSDB\Backup\', @databasename='DPL-WMSSQL', @backupType='F'"
(It's recommended that prior to first use, a manual backup is run in case of any errors in your .SQL file that may cause database issue. Also recommended to only be processed by Datapel Support staff or other qualified SQL user.)
-U - SQL Username
-P - SQL Password
-S - Instance name (PCName\SQLInstance)
-Q - Command to Run "EXEC sp_BackupDatabases @backupLocation='D:\WMSDB\Backup\', @databasename='DPL-WMSSQL', @backupType='F'"
@backupLocation - Windows path where backup should be stored
@databasename - Name of Database to Backup
If you have more than one database please setup batch file as per following contents.
"sqlcmd" -U datapel -P datapelwms -S . -Q "EXEC sp_BackupDatabases @backupLocation='D:\WMSDB\Backup\', @databasename='DPL-WMSSQLUSA', @backupType='F'"
"sqlcmd" -U datapel -P datapelwms -S . -Q "EXEC sp_BackupDatabases @backupLocation='D:\WMSDB\Backup\', @databasename='DPL-WMSSQLAU', @backupType='F'"
3. Setup windows scheduled task to run the batch file at required intervals (The user that runs the batch file should have "LOG ON AS BATCH" permissions and permissions to write to the @backupLocation).
How to Schedule Automatic Task
Double-click Add Scheduled Task to start the Scheduled Task Wizard, and then click Next in the first dialog box.
Click Browse, browse to Expressmaint.exe, and then click Open.
Type a name for the task e.g DAILY FULL BACKUP and then choose Daily from the scheduling options.
Click Next, specify the information about the time to run the task e.g. 00:00, and then click Next.
Click Start a program.
Click Next, Select the .bat file.
Type the name and password of the account that will execute this task. Make sure that you choose an account that is a sysadmin for your instance.
Click Next, select the checkbox to Open the Advanced Properties for this task and then click Finish.
Select options to run whether user is logged on or not and run with highest privileges.
Click OK. If prompted, supply the password for the account again.
Option 2 - Express Maint Utility (SQL 2005 and prior)
This procedure is only valid for SQL Server 2005 or prior.
We recommend ....ExpressMaint Utility
The ExpressMaint utility supports the following operations:
Full Database Backup
Differential Database Backup
Log Backup
Housekeeping of backup files
Database Integrity Checks
Database Index Rebuilds
Database Index Reorganization
Database Statistics Update
Report Creation
Please note that double quotes are required around any command line arguments that include spaces. All switches are case sensitive. For switches that take an argument, there should be a space between the switch and the argument. The argument column in the table below indicates whether a switch takes an argument or not.
-? Displays help for the ExpressMaint utility
-S The SQL Server for ExpressMaint to connect to
-U SQL Authentication Login. If not specified a trusted Windows connection is used. If specified a non-blank password must be supplied
-P SQL Authentication Password
-D The target database for the maintenance operation. Valid values are a single database name, ALL_USER which will process all user databases, ALL_SYSTEM which will process all system databases and ALL which will process all databases
-T The type of maintenance operation to be performed. Valid values are:
DB - Full Database Backup DIF - Differential Database Backup LOG - Log Backup CHECKDB - Database Integrity Check REINDEX - Rebuild all indexes REORG - Reorganize all indexes STATS - Update all statistics using default sampling STATSFULL - update all statics with fullscan
-B The base folder to write the backups to. Subfolders will be created for each database
-V Indicates whether to verify the backup file
-BU The unit of measure for the -BV argument. Valid values are minutes, hours, days, weeks. The combination of these two arguments determines how long old backup files are kept. Required if -B argument is specified.
-BV The time period of old backups to keep. Required if -B argument is specified.
-R The folder where maintenance reports are written to
-RU The unit of measure for the -RV argument. Valid values are minutes, hours, days, weeks. The combination of these two parameters determines for how long old reports are kept. Required if -R argument is specified.
-RV The time period or number of copies of old reports to keep. Required if -R argument is specified.
-A Indicates whether to check the archive bit on a backup file before deleting it. This is a safety check to prevent the deletion of files that have not been backed up onto tape.
-DS If this switch is specified then the timestamp for backup and report files is appended to the front of the filename (the default is to append to the end of the filename)
-TO This switch allows the setting of a statement timeout specified in minutes (e.g. -TO 10). If not specified, the default is 10 minutes.
-C This switch allows multi-database maintenance operations (ALL,ALL_USER,ALL_SYSTEM) to continue if an error is encountered with one or more databases
-BF This switch allows specifying custom backup filename formats. Accepted tokens are $(DB) for database name, $(DATE) for current date, $(TIME) for current time and $(OPTYPE) for backup type
Example Syntax
Note that the entire command should be on one line even though some examples span multiple lines for formatting purposes.
Full Database Backup of all user databases to c:\backups, verify the backups and report to c:\reports keeping backups for 1 day and reports for 1 week and continue processing other databases if an error is encountered baking up a database
expressmaint -S (local)\SQLExpress -D ALL_USER -T DB -R c:\reports -RU WEEKS -RV 1 -B c:\backups -BU DAYS -BV 1 -V -C
Full Database Backup of all system databases to c:\backups, verify the backups and report to c:\reports keeping backups for 1 week and reports for 1 week
expressmaint -S (local)\SQLExpress -D ALL_SYSTEM -T DB -R c:\reports -RU WEEKS -RV 1 -B c:\backups -BU DAYS -BV 1 -V
Log Backup of all user databases to c:\backups, don't verify the backups and report to c:\reports keeping backups for 1 day and reports for 1 day
expressmaint -S (local)\SQLExpress -D ALL_USER -T LOG -R c:\reports -RU DAYS -RV 1 -B c:\backups -BU DAYS -BV 1
Check the integrity of the AdventureWorks database and report to c:\reports keeping reports for 1 week
expressmaint -S (local)\SQLExpress -D AdventureWorks -T CHECKDB -R c:\reports -RU WEEKS -RV 1
A Full Backup of the AdventureWorks database specifiying a custom backup format to achieve a filename like AdventureWorks_foo_20091107.bak
expressmaint -S (local)\SQLExpress -D AdventureWorks -T DB -R c:\reports -RU DAYS -RV 1 -B c:\backups -BU DAYS -BV 1 -BF $(DB)_foo_$(DATE)
Automating backups using ExpressMaint
Since SQL Server 2005 Express Edition does not include SQL Agent, we need to rely on the Windows Task Scheduler to run our maintenance tasks. If you are not familiar with how to set up a scheduled task, it's worth reviewing the Microsoft Knowledge Base article below.
How to Schedule Tasks in Windows XP
Double-click Add Scheduled Task to start the Scheduled Task Wizard and then click Next in the first dialog box.
Click Browse, browse to Expressmaint.exe, and then click Open.
Type a name for the task e.g. DAILY FULL BACKUP and then choose Daily from the scheduling options.
Click Next, specify the information about the time to run the task e.g. 00:00, and then click Next
Type the name and password of the account that will execute this task. Make sure that you choose an account that is a sysadmin for your instance.
Click Next, select the checkbox to Open the Advanced Properties for this task and then click Finish.
In the Run text box, append the arguments you want to pass to the ExpressMaint utility.(You must leave a space after the existing contents)
Click OK. If prompted, supply the password for the account again.
Example BAT file to use with Scheduled Task
echo OFF cd c:\ echo .... STARTING BACKUP echo .... echo ....For the latest version of expressmaint see http://www.sqldbatips.com..... echo .... SET SQLSERVER=VMWINXPEN SET BACKUPPATH=C:\BACKUP echo ON "C:\expressmaint.exe" -S %SQLSERVER% -D DPL-WMSSQL -T DB -bu DAYS -bv 10 -B %BACKUPPATH%
Related Information
None