Repository files navigation

A quick and dirty Windows Service replacement forSQL ServerAgent.

I built this to monitor and automate database backups to local disk and cloudstorage for on-premise servers running SQL Server Express edition, which doesn'thave SQL Server Agent.

There areothersolutions out there but they seem verylimited(no reporting, no notification, no auditing, no integration and not to mention,very hard to maintain).

I plan to gradually add new functionality to this service over time. Check theTODO list below for more info.

1. Clone this repository using Git or download a zipped version from GitHub.

2. Install the.Net Core 3.1SDK if you don't haveit already.

3. Open a command line and navigate to the folder where the source code is andrun:

   $dotnet publish -r win-x64 -c Release

   If you don't like publishing the app with so many DLLs and other files in thesame folder, you can try publishing as a single file, which will zip all thedependencies together in the EXE and unzip it to a temp folder at runtime(initial startup time will be slower):

   $dotnet publish -r win-x64 -c Release /p:PublishSingleFile=true

4. Move the generated EXE (and optional dependencies) to your folder ofpreference. There is no installer yet.

5. Make sure you also create asql_commands.json file inthe same folder as the service EXE.

6. Set up SQL command jobs by editingsql_commands.json. There is an examplefile provided in this repository.

7. Customize theSmtpClientSettings section of the config in theappsettings.json file.AdminEmail is the email address to which to send notifications of errors and otherinformation that occurs outside of jobs (like when the service is started or stopped).

8. Install and start the service by running

   $bt_sql_backup_service.exe install$bt_sql_backup_service.exe start

9. Check Windows Event Viewer for log messages from the service and any errors.You can also run the service executable from the command line and see logmessages outputted directly to the console by just calling the executablewith no arguments.

More command line arguments are available. Read theTopshelf project documentation for more info.For example, you can install multiple instances of this service by passingcertain command line arguments when installing the service. Make sure youinstall the service from different folder if you do this, so they don't all loadthe same settings and jobs.

This file contains a list ofSqlCommandEntity. The service will readthis file on startup and load any commands defined there as jobs to schedule andexecute.

An example file is provided which should be self-explanatory:

[  {"name":"Full backup of user databases","description":"Performs full backups to local disk, at 3:00 AM, for all user databases, using Ola Hallengren's DatabaseBackup procedure. After it's finished, it deletes backups older than 24h.","connectionString":"Server=127.0.0.1,1443;Connection Timeout=10;Database=master;User Id=USERNAME;Password=PASSWORD","sqlCommand":"EXECUTE [master].[dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'D:\\sqlbackups', @BackupType = 'FULL', @CleanupTime = 24, @CopyOnly = 'N', @MaxTransferSize = 4194304, @BufferCount = 25, @Compress = 'N', @CheckSum = 'Y', @Verify = 'N', @LogToTable = 'Y';","commandTimeout":10800,"cron":"0 0 3 ? * * *"  },  {"name":"Diff backup of user databases","description":"Performs diff backups to local disk, every 2 hours from 10 AM to 10 PM, for all user databases, using Ola Hallengren's DatabaseBackup procedure. After it's finished, it deletes backups older than 24h.","connectionString":"Server=127.0.0.1,1443;Connection Timeout=10;Database=master;User Id=USERNAME;Password=PASSWORD","sqlCommand":"EXECUTE [master].[dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'D:\\sqlbackups', @BackupType = 'DIFF', @CleanupTime = 24, @CopyOnly = 'N', @MaxTransferSize = 4194304, @BufferCount = 25, @Compress = 'N', @CheckSum = 'Y', @Verify = 'N', @LogToTable = 'Y';","commandTimeout":10800,"cron":"0 0 10,12,14,16,18,20 ? * * *"  }]

In case you yet understand what this file does: this assumes you haveOla'sDatabaseBackup scriptinstalled on themaster database and a SQL Server instance installed andrunning on the same machine as the service.

What this configuration essentially does is schedule a job for a full backup ofall user databases everyday at 3 AM and a job for differential backups of alluser databases every 2 hours starting from 10 AM up until 8 PM including(assuming those are working hours, when the database gets new data added).

• Run SQL scripts on a CRON schedule.
• Email notifications.
• Option to backup to Azure Blob Storage directly (as opposed to SQL Serverdoing that).
• Option to sync local backups to Azure Blob Storage or Azure File Shares inorder to preserve backup LSN chain and not have to run the same backuptwice.

Pull requests are welcome. For major changes, please open an issue first todiscuss what you would like to change.

Please make sure to update tests as appropriate.

MIT