C.4 Scheduling Database Grooming

You can also schedule database grooming with Microsoft Windows Tasks Scheduler (previously called Scheduled Tasks). You can create a batch file on a computer that has the SQLCMD Utility installed to execute a SQL Server stored procedure, and then use the Microsoft Windows Tasks Scheduler to schedule the batch file.

Ensure the login account you use to connect to the SQL Server instance has db_owner privileges on the GPO_REPOSITORY database.

To execute the SQL Server stored procedure on a schedule:

  1. Create a text file to contain the SQL Server script that executes the SQL Server stored procedure as appropriate:

  2. Create a batch file that uses the SQLCMD utility to run the script by using the appropriate syntax:

    SQLCMD -s ServerName -d DatabaseName -i path\TextFileName (for Windows authentication)

    SQLCMD -U SQLLoginID -P Password -s ServerName -d DatabaseName -i path\TextFileName (for SQL authentication)

    For more information about the SQL Server syntax, see the Microsoft documentation.

  3. Use the Microsoft Windows Task Scheduler to schedule the batch file. For more information, see the Microsoft documentation.