Automated SQL Server Express Database Backups

Here’s an easy way to do this:

Create a “Backup All Databases” stored procedure. (I ripped the query off of some site)

USE master
GO

CREATE PROCEDURE dbo.BACKUP_ALL_DB
AS
DECLARE @name VARCHAR(50)
DECLARE @path VARCHAR(256)
DECLARE @fileName VARCHAR(256)
DECLARE @fileDate VARCHAR(20)
SET @path = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @fileName = @path + @name + '_' + @fileDate + '.bak'
    BACKUP DATABASE @name TO DISK = @fileName
    FETCH NEXT FROM db_cursor INTO @name
  END
CLOSE db_cursor
DEALLOCATE db_cursor
GO

Create a simple text file (I called mine backup.txt) containing:

EXEC BACKUP_ALL_DB
GO
EXIT

Setup a scheduled task in Windows to run nightly (for example) that runs the following:

sqlcmd < backup.txt

Enjoy.