How to Do a Command Line Backup of Microsoft SQL Database
A secure backup of your Microsoft SQL database is crucial to ensure business continuity in the event of a disaster. Sadly, many companies still overlook this mission critical task. Backing up your important business data in case of a catastrophe is often a low priority issue, until the day you need to restore lost information.
This article explains how to backup your Microsoft SQL Database without using the Microsoft SQL Server Management Studio (SSMS) utility. We will explain how to perform a backup
from the command line.
Command Line Backup of Microsoft SQL Server Express Database
Backing up your Microsoft SQL Server Database from the command line needs to be done with care to avoid errors. Follow the steps outlined below to complete the task correctly.
1. On the server, open a Command Prompt window (preferably with Administrator Rights).
2. Make sure you know the location of your SQL Server installation folder before you begin.
3. Run the following command (replacing the paths with the details matching your environment):
sqlcmd -S YOUR_SERVER_NAME\SQLEXPRESS -E -Q “EXEC sp_BackupDatabases @backupLocation=’C:\SQL_Backup\’, @backupType=’F'” 1>c:\SQL_Backup\backup.log
In the example above, we instructed the SQL Server to backup all databases to C:\SQL_Backup and used the type FULL.
4. We used sqlcmd in this example, but you can also use osql – refer to Google for an explanation.
The next procedure explains how to automate the backup process by including the above command in a Scheduled Task and having it executed via the Windows scheduler.
Schedule the Microsoft SQL Backup
Use Microsoft’s built-in task scheduler to automate the command line backup, of your Microsoft SQL Server Database by following the steps listed below.
1. On the computer running SQL Server, click Start, point to All Programs, point to Accessories, point to System Tools, and then click Scheduled Tasks.
2. Double-click Add Scheduled Task.
3. In the Scheduled Task Wizard, click Next.
4. Click Browse, click the batch file that you created in step 2 above then click Open.
5. Type SQLBACKUP for the name of the task, click Daily and then click Next.
6. Specify information for a schedule to run the job, then click “Next.” (We recommend that you run this task at least once every day.)
7. In the “Enter the username field”, type a valid Windows Domain username and the password associated with that user in the password field.
8. Click Next, and then click Finish.
9. Execute the scheduled task at least once to make sure that the backup completes successfully.
The folder for the SQLCMD executable is generally in the Path variables for the server after SQL Server is installed, but if the Path variable does not list this folder, you can find it under \90\Tools\Binn (For example C:\Program Files\Microsoft SQL Server\90\Tools\Binn).
It is crucial to backup your Microsoft SQL database to ensure business continuity in the event of a disaster. Unfortunately, many companies overlook this mission critical task and experience system downtime when they lose valuable business data. Backing up your critical business information in the case of a catastrophe is often a low priority issue, until the day you need to restore lost information
Microsoft, 2015 Available: How to schedule and automate backups of SQL Server databases in SQL Server Express [June 2016]
Should you wish to know specific ways to Backup/Move and restore BMC Client Management, BMC FootPrints, BMC Track-It!, please complete the form below and we will get back to you.