SET = 'BACKUP DATABASE ' ' TO DISK = ''' WITH INIT, NAME= ''' NOSKIP, NOFORMAT' SET = +' log backup for '+ Generate the dynamic SQL command to be executed SET = +' differential backup for '+ = 'L' Provide the backup a name for storing in the media Create backup filename in path\filename.extension format for full,diff and log backups SET = '['+(SELECT DBNAME FROM WHERE ID = Set the current date and time n yyyyhhmmss format
Database Names have to be in format since some have - or _ in their name IF where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')ĮLSE IF where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks') Filter out databases which do not need to backed up If specific database is chosen to be backed up only pick that out from INTO (DBNAME) Pick out only databases which are online in case ALL databases are chosen to be backed up Parameter2: backupType F=full, D=differential, L=logĬREATE PROCEDURE sysname = nvarchar(200) Description: Backup Databases for SQLExpress This will add a stored procedure to your master database. Run the below script ( provided by Microsoft) on your SQL Express server (I assume at this point you have the SQL Server Management Studio installed). I will offer one of those options here, along with that stored procedure, and then I will also add in a line of batch file code that will allow you to delete older backups, giving you a true replacement for the SQL Agent / maintenance plan method of SQL database backups. Be sure to check out that article to gain a full understanding of all the options.
SQL 2012 EXPRESS BACKUP ALL DATABASES HOW TO
In the Microsoft Knowledge Base article ( 2019698, “ How to schedule and automate backups of SQL Server databases in SQL Server Express“), they offer us a stored procedure and several batch file scenarios.
Luckily for us they proceed to offer us that “different approach” they mention.