Schedule Database Backup in SQL Server Express Version by using Task Scheduler

User need to Save the script inside a notepad, rename it to Backup.sql, and save into drive 1st.

DECLARE @MyFileName varchar(1000)

SELECT @MyFileName = (SELECT 'D:\MyDB_' + convert(varchar(500),GetDate(),112) +right('0' + CONVERT(varchar(2), datepart(hour, getdate())),2)+

right('0' + CONVERT(varchar(2), datepart(minute, getdate())),2)+ '.bak') 

BACKUP DATABASE [SampleDB2023001C4] TO DISK=@MyFileName

Use Task Scheduler to Schedule Database Backup in SQL Server 2012 Express

Step 1. Go to Start > Administrator Tools and select Task Scheduler. Then, you will see the Task Scheduler window, select Create Basic Task... to start this process.

Step 3. In the Create Basic Task Wizard, type a name and description for this task and click Next.

Step 4. Select the trigger to start a backup, daily, for example, and click Next.

Step 5. Then, you will be asked to set the start date and time, backup interval, set it and click Next.

Step 6. Select Start a program and click Next.

Step 7. Click Browse in the Program/script window and select sqlcmd.exe in the SQL installation location.

"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE"

Step 7. Then, type-U SA -P QnE123!@# -S PC358\SQL2017DEV -i "D:\backup.sql" in the Add arguments (optional) window. Click Next.

Notes: 

• -S tells the task which instance of SQL server to backup
• -\sqlexpress is the default SQL server instance name during the setup process.
• -i tells it which input file to run on the server and point the location of .sql script file created before. 

Step 8. Tick Open the Properties dialog for this task when I click Finish and click Finish.

Step 9. Then, in its Properties window, tick Run with highest privileges and Hidden under the General tab.

Step 10. Go to Settings option, uncheck Stop the task if it runs longer than and click OK.

That’s all the process of schedule database backup in SQL Server Express Version. It only allows you to backup one database every time, that’s to say, you need to repeat all the configuration steps above multiple times if you need to backup multiple instances.

** Learn more about Azure Cloud

** Learn more about QNE Hybrid Cloud Software