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.

Create Basic Task

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.

Daily Trigger

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

Start Date Time

Step 6. Select Start a program and click Next.

Start a Program

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.

Add SQLCMD Commands

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.

Open Properties window

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

Highest Privilege

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

Uncheck Stop the Task

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