Post reply

How to schedule a sql script in SQL Express?

  • June 26, 2018 at 8:40 pm

    #352280

    Hi All,

    We are using SQLExpress localdb in our environment and wanted to run Ola hallengren maintenance scripts on a schedule basis.
    However, SQL Express doesn't have SQL Agent service, what are the different ways I can schedule a sql script to run every night 12:05 AM?

    Thanks,

    Sam

  • June 26, 2018 at 10:30 pm

    #1995404

    You can schedule the execution of scripts using Windows Task Scheduler. 
    Simply follow the steps on his FAQ section:

    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.

    1. Download MaintenanceSolution.sql.
    2. Execute MaintenanceSolution.sql. This script creates the stored procedures that you need.
    3. 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
    4. In Windows Scheduled Tasks, create tasks to call the cmd files.
    5. Schedule the tasks.
    6. Start the tasks and verify that they are completing successfully.

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • June 27, 2018 at 1:37 am

    #1995409

    Henrico Bekker - Tuesday, June 26, 2018 10:30 PM

    You can schedule the execution of scripts using Windows Task Scheduler. 
    Simply follow the steps on his FAQ section:

    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.

    1. Download MaintenanceSolution.sql.
    2. Execute MaintenanceSolution.sql. This script creates the stored procedures that you need.
    3. 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
    4. In Windows Scheduled Tasks, create tasks to call the cmd files.
    5. Schedule the tasks.
    6. Start the tasks and verify that they are completing successfully.

    Thanks Henrico.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply