Tuesday, April 28, 2009

Schedule daily backup for sql server 2005 database using sql agent

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.
  1. Open sql server 2005 Management Studio and connect a server
  2. Expand the tree (left navigation) ”Management->Maintenance Plans”
  3. 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).

kick it on DotNetKicks.com
Shout it

39 comments:

Dave said...

This is a great wee guide to backing up daily. I'm having one issue that maybe you could help me with. I would like to schedule a daily backup and have the file name followed by the day of the week, for example, db_monday.bak That way any previous files will be overwritten. The reason I am doing this is that i have signed up to Jungle disk so i have a backup of all my dev files scheduled every day and i would like to only back up the most recent db.

N!j! said...

That was really helpful at the rit time.. cheers !!!

JF said...

@Dave

You can do what you asked using this SQL script. Then you just call it from a scheduled task that runs the following command:
sqlcmd -i BackupDB.sql >>%date:~10,4%-%date:~4,2%-%date:~7,2%.log

---------------
--Backup all db
---------------
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE @filePath VARCHAR(30) -- used for file name

SELECT @filePath = cast(DATEPART(WEEKDAY, GETDATE()) as nvarchar(2)) + '_' + DATENAME(WEEKDAY, GETDATE())
SET @path = 'C:\backup\DB\' + @filePath + '\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
where dbid > 4

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName WITH NOFORMAT, INIT

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

Kiran said...

JF your script is brilliant..i want to use it to back up even the windows internal DB ...sharepoint content DB..i m not sure how to do it..any idea?

Kiran said...

i made all steps of daily back up using sql agent but after finish... sql out this message and i can not get backup

Kiran said...

Hi Semo,

Please verify whether you have Sql Server Integration Services (SSIS) installed on your machine. Because the maintenance plan wizard internally takes help of SSIS to create backup jobs. When you installed Sql Server, You must have found the optional installation of Integration Services.

Kiran said...

How can I set the output dir to a network share? The Wizzard let me only choose local hard disks as output for the bak files...

THX for the good article

Kiran said...

Hi Claudio,

The name of the DBs to be backed up are selected with this:

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
where dbid > 4

So you just need to tweak the select statement until it includes all the DBs you want to have backed up.

Cheers!

Kiran said...

Just put the UNC-Path into the destination

Kiran said...

Is there a way to overwrite or delete backups which are old, in my case like backup for a week and delete the old ones to save disk space. im having to do this manually, im using backup to disk at the moment.

Kiran said...

Nice blog

Kiran said...

to back up a database, do we need to select "Execute SQL Servant Agent Job" in step 8?

Kiran said...

Thank you. This is a really useful article. Keep up working!!!

Kiran said...

I created a scheduled backup of the database.Where will I find the scheduled job?

Kiran said...

200 pixels wide screenshots? this article is useless because I can't read the text on them.

Kiran said...

read the points you can understand, Grow up man, its just information here you should understand if you have some bit of knowledge, dont expect to post some blueray movie to teach you. Blog is good

Kiran said...

Very helpful guide, and well explained and pictured!! Thanks a lot!

Kiran said...

When I run backup it gives error below, and there is no backup.

TITLE: Start Jobs -
------------------------------

Execution of job 'FBM BKP.Subplan_1' failed. See the history log for details.
-------------------
and in log history it is " The Job was invoked by user"

Kiran said...

You seem to be a master in DB Administration.Please help me where can i learn DBA Course.

Kiran said...

hey...how to identify the class B IP address which can host 1000 to 1500

Kiran said...

@ken

Huh? What does that have to do with anything related to this post or comments.

Anyway, class B is 128.1.0.1 to 191.255.255.254 do your math again its 65,000 hosts per network (16,000)

Kiran said...

I am new at sql server 2005. can you please tell me where to write this script?

Kiran said...

Thank u so much for posting..

Kiran said...

Very Good explaination JF and one more thing is like

1)how to add another step into the job.......means goto job -->edit the steps?


2)why Shrinking Logs in job step after backup?










/****** Object: Step [shrink logs] Script Date: 06/07/2010 17:51:39 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'shrink logs',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'exec sp_shrink_LogFiles',
@database_name=N'Love',
@output_file_name=N'X:\SQL_Job_Execution_Log\Maintenance Error Logs\shrinklogs.txt',
@flags=0

Kiran said...

Thank Q, really helpfull

Kiran said...

Thanks. it helped a lot

Kiran said...

Nice Thanks keep helping those who are in need

Kiran said...

Nice Thanks keep helping those who are in need

Kiran said...

Thank you!!!
This is very usefull and good article.

Kiran said...

Please let me know if i need to run/start the SQL agent
Also, please let me know if the back up databases are overwritten or appended ?

Thanx

Kiran said...

Thanks. This article helped me.

Kiran said...

i made all steps back up using sql agent but after finish... sql out this error message ,in my system integration services are installed .

Kiran said...

man it helped me a lotzz... grateful..

Kiran said...

Hi i new on SQL and i need help about how to make backup to over write the previous same day example monday last week will be over write by present monday so it will not require big hard drive space and it will be backup every month to other external hard drive. any answer will helpfull.

regards
gede agus

Kiran said...

Nice article, but please fix a typo in the title (Should be 'database')

Kiran said...

Thank you very much for identifying the typo since it has been a very long time after it was published and didn't realize that there was a typo in my post title.

Kiran said...

Useful Article but is there any other commands or quries to do the same job....

Kiran said...

Thank you

snowresonance said...

Thanks so much!! This is much more useful and better than the official guide!!