How To maintain only specific backup files in SQL server

There are many business scenarios that cause to maintain backup files for all applications and its database. In some case the organization doesn't require all the backup files that it was taken more than some days before. They need only certain days backup files, for example, they need only one week of backup files. The remaining no need to maintain. In this scenario we have an option in the SQL server called Maintenance plan.

How to achieve this in the SQL server Maintenance Plan

Step 1

Go to Start >>All programs >> SQL server Management studio. Enter a user name and password to login to the server

Note: Make sure that the user has rights to take a backup in the SQL server.

Step 2

In the left side menu Go to Management >> MaintenancePlan , right click and select New Maintenance Plan.

Step 3

You will get a drawing surface area. In the drawing surface area, drag the backup database task to it.

Step 4

Double click the backup database task in the surface area and give input as you required. In this scenario, the task going to take a full backup of all Databases and store it the specified folder also the sub-folder will be created for each database. Click OK to save the task.

 Step 5

In the drawing surface area drag the Maintenance Cleanup Task as followed.

Step 6

Double click the Maintenance Cleanup Task and set-up the task as you required. In this scenario, it is going to delete the backup files which located in the following location and the files older than one week. Click OK to save the task.

Step 7

Now, the backup task and Clean up task ready.So these tasks needs to be scheduled. Click the button in the right side corner of Subplan window and schedule the task as you required. Click OK to save the schedule.

Step 8

Now, Save and close the maintenance plan. Make sure that working properly.

Apart from this, there are some other options available that you can use based on the scenario you are required for.

How to add new users in SSRS

            SSRS is a tool which allows to create a report using SQL script, Table and views that display the report as we required and it can be design easy and timely manner. In this tool, giving access to the users who can access the report, and who can modify the report is really important. Because, the report should be accessible by authorized users and can be modified by administrative users. So that, the organization or company data can be personal and will be secure. Otherwise, it can be easily accessible to any users who is not an authorized user. This may raise any security issue.

            To overcome this, SSRS has the option to give access to users who need to print the report. In SSRS, the security structure is as follows,

Two levels of security

·         Site Security  - To access the site where the SSRS report is deployed the user needs to be added in this list. By default the system administrator added in this list. and administrative role automatically assigned to that.

·         Report Security – To access the particular report the user needs to be added in this list.

Note: This will not give access to the report data (The data coming from the back end )

·         Report Data Security – To get the report data the user should have minimum rights (Select the data) in the back end.

Add user in Site Security:

Step 1: Open the SSRS Report server link in browser. In the right side corner, we can find site security Click the link. The new window will open

Step 2:  Click add new user button and choose the user or group that needs to be add and choose the     appropriate role for that user and click OK.

Step 3:  Verify that user is present in the users list.

Add user Report Security:

Step 1: Open the SSRS Report server link in browser. Right click the SSRS report that you want to give access. Choose the option security, the new window will open.

Step 2:  Click add new user button and choose the user or group that needs to be add and choose the appropriate role for that user and click OK.

Step 3:  Verify that user is present in the users list

Add user Report Data Security:

Step 1: Open the SQL server management studio, connect the SQL server instance that SSRS report is connected.

Step 2:  In the left side panel, go to security and click add new user. The new window will open, choose                 the user you need to add and choose the appropriate role for that users and click OK.

Step 3: For GP, we need to give the role DYNGRP to that user and check all the roles which start with                Rpt_. So that the report can be accessed.                                                                                 

Step 3:  Verify that user is present in the users list, and the report can be generated by the user.