Many of us come across a need to schedule periodic database backups for sql server. But many of us don't realize that this feature is readily available in sql server management studio and we purchase some 3rd party tools to create such scheduled tasks. I recommend to use Sql Server Management Studio to do such jobs rather than spending on unnecessary tools. But remember, this feature is not available in express releases of sql server. If you are using sql server express edition please ignore this post. And this post also assumes that you have SSIS (Sql Server Integration Services) installed and running on your machine. SSIS is the prerequisite to follow the below instructions to schedule the database periodic backups.
Scheduling a daily backup of sql server 2005 database using sql agent is very easy. By following below steps anyone can do that.
- Open sql server 2005 Management Studio and connect a server
- Expand the tree (left navigation) ”Management->Maintenance Plans”
- Right click on node Maintenance Plans and Click “Maintenance Plan Wizard” (As shown in figure below) which causes to open a wizard.
4. Follow the wizard
5. In 2nd step of the wizard, You can schedule the task as clicking the Button “Change” as shown in the following
6. Once you click the button “Change” you will get a new popup to enter shedule details as shown below.
7. Click OK. It will save the schedule info and close the popup. And click Next button to follow the next step.
8. In this step you will be asked to check the check list for all the tasks those can be added in mainteance plan. Select “Backup Datbase (Full)” as shown in the figure.
9. Follow the steps until you get following window. Once you get here Select one or more databases from the list of databases by clicking Dropdown for Databases.
10. From the above window, browse the folder location where to store the backup files.
11. Continue the the steps until you get to Finish step.
12. Click Finish. Once you click finish, the wizard will execute the steps to to create a job to schedule database. Immediately you will notice a job created in Sql Agent -> Jobs.
13. As per the schedule, the job runs (Repeatedly or One time) and it creates backup file with name DB_NameMMddyyyyHHmm.bak (Ex: NorthWind060420081912.bak) in the specified folder (Borwsed in the above step).
Step2
--
Create database maintenance plans in SQL Server 2005 using SSIS
In SQL Server 2005, database maintenance plans are almost entirely created using SQL Server 2005 Integration Services (SSIS). Microsoft provides a useful wizard to guide you through the creation process, but you are not required to use the wizard.
This article shows you how to create a database maintenance plan without the use of the wizard. The maintenance plan I create performs full backups on my user databases and rebuilds my indexes in those databases.
Create a database maintenance plan from scratch
You’ll find Maintenance Plans under the Management tab in the server object explorer window in SQL Server Management Studio. (Figure A) Right-clicking the folder gives you the option of creating a new maintenance plan from scratch or creating one via the wizard. Select the New Maintenance Plan option to create one from scratch. (Figure B)
Figure A
Figure B
Creating a new maintenance plan brings up a familiar looking SSIS interface with several maintenance plan tasks. These tasks are also available for use in regular SSIS projects.
As part of my maintenance plan, I want to create a full backup of all of my user databases on my database instance. The Backup Database task will be the task I will use to accomplish this.
The Backup Database task gives me the option to back up: all databases on the instance; all system databases; all user databases; or specific databases. For this maintenance plan, I want to back up all user databases. (Figure C)
Figure C
Below is the Backup Database properties window. My plan is to make a full backup of all user databases and place the .bak files on my C:\SQLServerBackups directory on my server. (Figures D and E)
Figure D
Figure E
If I click the View TSQL button, I can look at the TSQL that may execute if no other changes are made in the interface. I like to look at the TSQL executing on the server when I use Management Studio; it allows me to better understand how the interface is working, and it’s great for learning TSQL code. SQL Server Profiler is another great tool for this. Profiler runs traces on the SQL Server to capture SQL statements that are occurring on the server. This tool has helped me significantly in enhancing my TSQL skills over the years. (Figure F)
Figure F
After I back up my user databases, I want to rebuild the indexes in them. These tasks can be interchangeable in terms of the order in which they execute. I typically prefer to back up the databases before the rebuild so that I know I have a good working backup copy of the databases in case some tragic error occurs during the rebuild process.
Rebuilding indexes resorts and defragments indexes on database tables for views to improve their efficiency when sorting or searching. (Figure G)
Figure G
Double-clicking the Rebuild Index task gives me the Properties window, where I can set specifics for my index rebuild. If I am doing a large set of tables, I will typically reorganize the data in the indexes to leave the default amount of free space. If I am rebuilding specific tables, I will be a bit more granular with the amount of free space I leave. The free space is the amount of space left per data page for new data and data manipulations. The goal is to find a good number for the table in order to limit the number of page splits, which are resource intensive. Note: This free space is only set for when the indexes are rebuilt; once the rebuild is complete, DML transactions will cause this number to change.
Sort results in tempdb
For an index to retrieve results effectively, it must remain in sorted order. When an index is rebuilt, it must resort the data in the index. This resorting of data is typically done in the database in which the index resides. You now have the option to sort these indexes in the tempdb database; this has advantages and drawbacks. If the tempdb on my system is on a different set of disks than my user databases, it may be quicker to sort the index in the tempdb database; however, this requires the index rebuild to use more disk space. If space is not an issue for your system, it might not be a bad idea to play around with this option to see if it speeds your rebuild time.
Next I add a TSQL task to the maintenance plan flow. I will use this task to alert me if any problem occurs in my maintenance plan. To do this, open up the task and type in a procedure call to send a Database Mail task if any error occurs in the task. (Figure H)
Figure H
Here is the TSQL I used for the above task:
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'chapman.tim@gmail.com',
@body='DB Maintenance Failure',
@subject ='A DB Maintenance Failure has occurred.',
@profile_name ='DatabaseMailProfile';
Below is my almost complete maintenance plan. Notice the red failure precedence from the Backup Database Task and Rebuild Index Task to the Send Alert task. This precedence will cause my Send Alert task to execute only if one of the tasks encounters a failure. (Figures I and J)
Figure I
Figure J
All that is left to do is schedule my maintenance plan so that it will run. I want my backups to run daily at midnight, and I want the SQL Agent job to run daily at midnight. (I typically like to run full backups and rebuild indexes during off-peak hours of the day.)
In my next column, I will discuss how you can use the new SQL Server 2005 feature for rebuilding indexes online.
munu bhai where is the images
ReplyDelete