Back up sql server task scheduler window

Running scheduled tasks in SQL Server Express has to be done differently due to the limitations of the Edition. As well as having a maximum database size of 10GB and 1GB consumable RAM, Express does include the SQL Server Agent. So, If you need a task to run on a time based schedule, you’ll have to look outside of SQL Server.

Windows Task Scheduler is where I’d steer to, running a PowerShell or DOS script which utilises sqlcmd.

The DOS approach is further explained within the following steps;
# Locate/install sqlcmd.
# Create .bat sqlcmd script.
# Create Scheduled Task.

The .bat script for this post will execute Ola Hallengren’s Index Maintenance, which I’ll set to run weekly on the Express instance of SQL. Ola’s index maintenance stored proc is assumed to be on the SQL Server for this task.

Locate/Install Sqlcmd

The sqlcmd utility is installed with SSMS, or you can download separate Windows & Linux clients.

If you already have SSMS installed, you can find the directory of sqlcmd using this Microsoft documentation link – search ‘sqlcmd’ after selecting your SQL Version when on the page.

sqlcmd Location

The only thing we need for now is to have it installed on the same machine we’re running the script from… But have a look at my other post for more information on sqlcmd if of interest.

Create .BAT Sqlcmd Script

Below you’ll see -Q being used and everything within the command is within quotes. The -o parameter is also being used to forward output messages to a text file.

sqlcmd -Q "EXEC master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_updatestats'; EXEC dbo.IndexOptimize @Databases = 'ALL_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 30, @FragmentationLevel2 = 50, @pagecountlevel = 1000" -o "c:\logs\index_maintenance.txt"

Copy this into notepad and save as a .bat file. Also note, it’ll break if you try format the code.

sqlcmd .bat index maintenance
sqlcmd bat file

Next create a new folder for the log output file.

c:\logs

Double-click and run the index_maintenance.bat file, and we should quickly be able to see it working by viewing the logfile.

c:\logs index maintenance
SQL Server Index Maintenance Log File

Create A Scheduled Task

Open Task Scheduler.

Open Task Scheduler
Windows Task Scheduler

Within here, right-click and select Create New Task…

Task Scheduler Create New Task

Within the General tab of the prompted window, I’m changing the following;
# Name / Description, more information the better for visibility.
# The user account who the task runs as to a new local service account I created for this. Remember, the chosen account requires Logon As Batch and MSSQL permissions (sysadmin for quickness here).
# Run whether user is logged on or not.

Task Scheduler New Task General Tab

Click into the Triggers tab and hit New.. to set the schedule. 

Task Scheduler New Task Trigger Tab

I’m configuring to run every Sunday at 02:00. 

Task Scheduler Set Schedule

Next up is the Actions tab.

Task Scheduler New Task Action Tab

Hit New… and add our .bat file as the start script.

Task Scheduler New Task Add .bat File

Over to the last note-worthy tab, Settings. Stop the task if it runs longer than you’re comfortable with.

Task Scheduler New Task Set Max Runtime

When ready, click OK to create the task and enter user password.  

Task Scheduler New Task Password Prompt

The scheduled task has been created and is awaiting it’s next run early Sunday morning.

Task Scheduler sqlcmd

If we run this now we can watch it working by running sp_whoisactive on the SQL Server – you should see stats being refreshed or indexes being rebuilt. For me it takes under a second to run as my indexes have been refreshed 64 times today… but I can verify by deleting the logfile and watch it be recreated, and also by looking at timestamps in the log file.

Index Maintenance Scheduled Task Verification


sqlcmd -S SCEPC01441\SQLEXPRESS -U ssa -P P@ssw0rd@# -d EHR_TCV -Q "dbo.usp_BackupFullData"

Comments

Post a Comment

Popular posts from this blog

Data Import Best Practices in Power BI

Image
  When you create a data model in Power BI, you should consider how to properly use naming convention and what columns to include, in order to improve usability and performance. This article provides a quick list of best practices valid for both Power BI and Power Pivot. APR 5, 2018 UPDATED Marco Russo DAX     POWER BI     POWER PIVOT     TABULAR    Not all of the suggestions described can be applied to all of your data models. You should adapt these best practices to your specific scenario, looking at how to achieve the goals that are the reason for a certain pattern more than barely apply it without considering the pros and cons of each choice. Even if the article mentions Power BI, all the best practices described are valid for Power Pivot and Analysis Services Tabular models, too. The demo file you can download (at the end of the article) contains a sample Power BI file and the corresponding views defined in a SQL file for ...
Read more

ASP.NET MVC + AdminLTE

Image
  ASP.NET MVC + AdminLTE March 31, 2018 00:14 ASP.NET MVC + AdminLTE ( edit ) ASP.NET MVC Application with a Custom Layout ( Login screen ) https://code.msdn.microsoft.com/ASPNET-MVC-Application-b4b0dc3f A NuGet package for integrating Admin LTE 2.0.4 into ASP.Net MVC 5 https://github.com/eralston/AdminLteMvc  (AdminLTE version 2.0.4) ASP.NET  Example Version of Beautiful AdminLTE Template https://github.com/go2ismail/AdminLTE-ASP-NET-MVC  (AdminLTE version 2.4.0) Tutorials & Samples on adding Charts into ASP.NET MVC Applications https://canvasjs.com/docs/charts/integration/asp-net-mvc-charts/  (Chart) CRUD Operations in ASP.NET MVC Using AJAX and Bootstrap https://dzone.com/articles/crud-operation-in-aspnet-mvc-using-ajax-and-bootst AdminLTE +  ASP.NET MVC 5 + Net Framework 4.8 https://github.com/DiomedesDominguez/AdminLTE.NET ASP.NET MVC 5 Scaffolding https://github.com/robinli/MVC5-Scaffolder ASP.NET MVC 5 SmartCode Scaffolding ( HAY HAY HA...
Read more

Build your first Azure Dara Factory Pipeline

Image
Overview We're going to build a pipeline using the Copy Data tool. This tool makes it easier for people starting out with ADF to create their first pipelines. Before we start, we need to make sure some prerequisites are met. Prerequisites If you haven't already, follow the steps of the  previous part of the tutorial  to set up ADF, a storage account with a blob container and an Azure SQL DB. In the Azure Portal, go to your storage account and then to the "data-input" container we created. Click on the  Upload  link. A pane will open where you can select a local file. Upload the  Customers.csv  file, which you can  download here . Click  Upload  to put the file in the blob container. Create the Pipeline Go to ADF Studio and click on the  Ingest  tile. This will open the  Copy Data tool . In the first step, we can choose to simply copy data from one location to another, or to create a more dynamic, metadata-driven task. We'll cho...
Read more