1

I'm considering installing SQL Server Express Edition on a VPS.

My understanding is that, among the limitations of Express Edition, some of them involve features that facilitate backups.

Can someone explain some options for performing daily backups for this configuration. Is there a straight forward way, or do I need to buy additional software?

(Sorry if the question seems dumb. I'm a software developer and not at all a database or admin guy.)

5

Your best bet would be to use ola Hallengren's utilities

https://ola.hallengren.com/

from the faq

How do I get started with the SQL Server Maintenance Solution on SQL Server Express?

SQL Server Express has no SQL Server Agent. Therefore, the execution of the stored procedures must be scheduled by using cmd files and Windows Scheduled Tasks. Follow these steps.

    Download MaintenanceSolution.sql.
    Execute MaintenanceSolution.sql. This script creates the stored procedures that you need.
    Create cmd files to execute the stored procedures; for example:
    sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = N'C:\Backup', @BackupType = 'FULL'" -b -o C:\Log\DatabaseBackup.txt
    In Windows Scheduled Tasks, create tasks to call the cmd files.
    Schedule the tasks.
    Start the tasks and verify that they are completing successfully.

Another option would be to use the DBATools Powershell module and create and schedule powershell scripts.

0

One of the things that doesn't come with Express Edition is SQL Agent, which allows scheduling of tasks. Backups work the same way as with other editions, you just can't schedule them to run automatically. That's the big difference with Express and the other editions.

  • So, in asking how I would do that, are you saying I can't? – Jonathan Wood Dec 19 '19 at 19:45
  • I believe there are ways to schedule backups using Windows and either sqlcmd or powershell (check out dbatools.io too), but I've never tried it. My point is, you don't have SQL Agent to schedule backups. You can manually run backups, but you cannot schedule them with SQL Agent since Express doesn't come with SQL Agent. – SQL_Hacker Dec 19 '19 at 20:53
  • Yes, I got your point. But what I'm asking is how might I perform daily backups in my scenario. – Jonathan Wood Dec 19 '19 at 21:03
  • 2
    @JonathanWood Use Windows Task Scheduler to run PowerShell or batch scripts to run the backups. – Tony Hinkle Dec 19 '19 at 21:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.