Home Tech How to Automate Backup of Analysis Services Database Using SQL Server Agent...

How to Automate Backup of Analysis Services Database Using SQL Server Agent Job

0

In this article, we will show how to automate backup of an Analysis Services database using a SQL Server Agent Job. 

We will first create a job and also then schedule it to run at a specific time. This way, the backup process will be automated and also you won’t have to remember to do it manually every time.

Creating the Job:

1. Open SQL Server Management Studio and also connect to your Analysis Services instance.

2. In the Object Explorer pane, expand the “Jobs” folder. Right-click on it and also select “New Job…” from the context menu.

3. Enter a name for the job in the “Name” field and also optionally provide a description in the “Description” field. Then click “OK” to continue.

4. In the “Steps” pane, click on the “New…” button.

5. In the “New Job Step” dialog box, enter a name for the step in the “Step name” field.

6. Select the “Analysis ServicesCommand” type from the “Type” drop-down list.

7. In the “Command text” field, enter the following command:

<Backup xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”>

<Object>

<DatabaseID>Adventure Works DW Standard Edition</DatabaseID>

</Object>

<File>D:\AdventureWorksDW\AdventureWorksDW.abf</File>

<AllowOverwrite>true</AllowOverwrite>

</Backup>

Replace the “Adventure Works DW Standard Edition” value with the name of your Analysis Services database. Also, make sure to use a valid path for the backup file.

8. Click “OK” to save the step and also close the dialog box.

9. In the “Job Steps” pane, click on the “New…” button again.

10. In the “New Job Step” dialog box, enter a name for the step in the “Step name” field.

11. Select the “T-SQL” type from the “Type” drop-down list.

12. In the “Command text” field, enter the following command:

BACKUP DATABASE AdventureWorksDW TO DISK = ‘D:\AdventureWorksDW\AdventureWorksDW.bak’ WITH FORMAT

Replace the “Adventure Works DW Standard Edition” value with the name of your Analysis Services database. Also, make sure to use a valid path for the backup file.

13. Click “OK” to save the step and also close the dialog box.

14. In the “Job Steps” pane, click on the “New…” button again.

15. In the “New Job Step” dialog box, enter a name for the step in the “Step name” field.

16. Select the “Analysis ServicesProcessing” type from the “Type” drop-down list.

17. In the “Command text” field, enter the following command:

<Process xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”>

<Object>

<DatabaseID>Adventure Works DW Standard Edition</DatabaseID>

</Object>

<Type>ProcessFull</Type>

<WriteBackTableCreation>UseExisting</WriteBackTableCreation>

</Process>

Replace the “Adventure Works DW Standard Edition” value with the name of your Analysis Services database. 

18. Click “OK” to save the step and also close the dialog box.

19. Click “OK” to save the job and also close the dialog box.

Scheduling the Job:

1. In the Object Explorer pane, expand the “Jobs” folder. Right-click on the job that you created in the previous section and also select “Properties” from the context menu.

2. In the “Job Properties” dialog box, go to the “Schedules” tab.

3. Click on the “New…” button to create a new schedule.

4. In the “New Job Schedule” dialog box, enter a name for the schedule in the “Name” field.

5. Select the frequency with which you want the job to run from the “Frequency” drop-down list.

6. Enter the start date and also time for the schedule in the “Start Date” and “Start Time” fields.

7. Optionally, you can also enter an end date and time for the schedule in the “End Date” and also “End Time” fields.

8. Click “OK” to save the schedule and also close the dialog box.

9. Click “OK” to save the job and also close the dialog box.

Your job is now scheduled to run at the specified time interval. You can also verify this by looking at the “Last Run Date” and also “Last Run Time” columns in the “Jobs” folder in Object Explorer.

You can also manually run the job at any time by right-clicking on it in the Object Explorer and also selecting “Start Job at Step…” from the context menu.

Conclusion:

In this article, you have learned how to create a SQL Server Analysis Services backup job using SQL Server Management Studio. You have also learned how to schedule the job to run at regular intervals.

This is useful if you want to take regular backups of your Analysis Services databases or if you want to automate the process of backing up and also processing your databases.

Exit mobile version