Automate Backups in SQL Express

joe_echavarria
joe_echavarria used Ask the Experts™
on
Hi,

  I need to automate backups for a SQL Express installation.     Some sort of  daily backup maintenance plan.    I been told that it's not as simple as setting up a job using SQL management studio in the full version.

  Please any tips, links, help , tutorial that will orient me on how to this.

  Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Express edition does not offer a way to automate backups. What I have done in the past while dealing with Express editions, I would create an exe (usually using .NET) or PowerShell script that would perform backup. Then I'd schedule that script or exe via Windows scheduler on the server
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
I am using http://expressmaint.codeplex.com/. It is a set of commands that you schedule through Windows Task Scheduler
Below are two code samples that use AdventureWorks database as an example:

PowerShell

$svr = New-Object 'Microsoft.SqlServer.Management.SMO.Server' $inst
$bdir = $svr.Settings.BackupDirectory
$db = $svr.Databases['AdventureWorks']
$dbname = $db.Name
$dt = get-date -format yyyyMMddHHmmss
$dbbk = new-object ('Microsoft.SqlServer.Management.Smo.Backup')
                              $dbbk.Action = 'Database'
                              $dbbk.BackupSetDescription = "Full backup of " + $dbname
                              $dbbk.BackupSetName = $dbname + " Backup"
                              $dbbk.Database = $dbname
                              $dbbk.MediaDescription = "Disk"
                              $dbbk.Devices.AddDevice($bdir + "\" + $dbname + "_db_" + $dt + ".bak", 'File')
                              $dbbk.SqlBackup($svr)

Open in new window


VB.NET

Dim conn As New ServerConnection(serverName, userName, passWd) 
Dim oSQLServer As New Server(conn) 'create the SMO server object using connection
Dim OrigBackupPath As String = oSQLServer.Information.MasterDBPath.Replace("\DATA", "\Backup\AdventureWorks.BAK") ' -- set the path where backup file will be stored
Dim bkDevItem As New BackupDeviceItem(OrigBackupPath, DeviceType.File) ' -- create SMO.Backupdevice object

With oBackup ' Set the backup object property 
         .Action = BackupActionType.Database
         .Database = "AdventureWorks"
         .Devices.Add(bkDevItem)
         .Initialize = True
         .Checksum = True
         .ContinueAfterError = True
         .Incremental = False
         .SqlBackup(oSQLServer) 
End With

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I think any solution will work but my preference is too save the backup commands to a .sql file and schedule it to run in the task scheduler.
Microsoft even wrote an article on how to do that. If you're interested you have the full process and code here.
HuaMin ChenProblem resolver

Commented:
Hi,
You can run Stored proc to do backup, using the codes like
DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name

 
-- specify database backup directory
SET @path = 'C:\Backup\'  

 
-- specify filename format
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')  -- exclude these databases

 
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

Open in new window


and you can can have scheduled job to call such Stored proc, periodically.
HuaMin ChenProblem resolver

Commented:
In SQL Management Studio, navigate to the server, then expand the SQL Server Agent item, and finally the Jobs folder to view, edit, add scheduled jobs.
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
HuaMinChen, the asker is using Express which doesn't come with the scheduler.
joe_echavarriaDatabase Administrator

Author

Commented:
Thanks guys for your support.

I have used an Power Shell scripts  to perform the backups.

Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial