SQL express doesn’t have the luxury of SQL maintenance plans, but you can still write a SQL script to dump a database to a particular location and run it via task scheduler on a daily basis.
Create SQL query and save it to a particular location… (NightlyBackup.sql)
DECLARE @pathName NVARCHAR(512)
SET @pathName = ‘D:\SQL Backup\database_‘ + Convert(varchar(8), GETDATE(), 112) + ‘.bak’
BACKUP DATABASE [database] TO DISK = @pathName WITH NOFORMAT, NOINIT, NAME = N’db_backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
Create cmd / bat and save to similar location to above… (Backup DB to disk.cmd)
sqlcmd -S servername\INSTANCENAME -U sqluser -P sqluserpassword -i “NightlyBackup.sql”
forfiles /p “C:\SQLBackupLocation” /m *.bak /s /d -2 /c “cmd /c del @file : date >= 7 days >NUL”
Create windows task schedule event to run above at 5:30pm everyday.