How to schedule automatic database backup in Microsoft SQL Server Express without using SQL Server Agent Jobs.
If you are using the free version of Microsoft SQL Server Express there is no way you can schedule backup jobs because it does not come with the SQL Server Agent which is used to schedule jobs. So in this tutorial I am going to share with you how to schedule automatic database backup in Microsoft SQL Server Express without using SQL Server Agent Jobs.
How to schedule automatic backups in SQL Express using a batch script
For us to be able to achieve this, we need the following.
- SQL Script
- Batch Script
- Windows Task Scheduler
In the script below the SQL database name that I want to backup is called ‘Logistics’.
Copy the SQL Query below. Make sure to change all the occurrences of ‘Logistics’ to match your database name. Paste in a text editor and Save As with the .SQL extension.
N is the path where the .bak SQL file will be dumped.
BACKUP DATABASE Logistics TO DISK = N'C:\Users\Abdalla\Desktop\My Backups\Logistics.bak' WITH NOFORMAT, INIT, NAME = N'Logistics Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO
This batch script is going to execute the SQL Script above. So we need to give it credentials for the script to be able to long in to the SQL Server.
-S – SQL Server instance name. Mine is SMARTTECH
-U – Username able to login to the SQL Server. Mine is sa
-P – Password of the username above. Mine is MyPassword
-i – The full path of where you saved you .sql script
Copy the script below, paste in a text editor and Save As .bat
"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S SMARTTECH -U sa -P MyPassword -i E:\EDMS\Scripts\SQLServer\SQLBackupScript\SQLExpressBackupsScript.sql @echo on
When the Batch Script is save it should look like below:
Make sure to test your scripts before you schedule the batch script.
Windows Task Scheduler
Now that we have the two script we need to schedule the batch script to make the backup automatic. See this post to learn how to schedule the batch file in windows scheduler.