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).
14 comments:
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.
That was really helpful at the rit time.. cheers !!!
@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
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?
i made all steps of daily back up using sql agent but after finish... sql out this message and i can not get backup
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.
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
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!
Just put the UNC-Path into the destination
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.
Nice blog
to back up a database, do we need to select "Execute SQL Servant Agent Job" in step 8?
Thank you. This is a really useful article. Keep up working!!!
I created a scheduled backup of the database.Where will I find the scheduled job?
Post a Comment