How to create individual differential backup files in SQL 2005
I'm fairly new to backing up SQL Databases and need detailed instructions to perform a differential backup. I have it backing up, but it just creates on file and keeps adding to it every 2 hours. I need to basically have one large full backup and just have small differential backups every two hours in the form of a separate file. This is on SQL 2005 and I'm using the Studio Manager to setup the backups.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
From Management Studio:
- Managements --> right click Maintenance Plan --> New Maintenance Plan
- From tool box drag and drop "Backup Database Task"
- Right click on Backup Database Task you just created and Click Edit
- Select backup type to be Diffrential
- Choose the database you want to backup
- select the folder of backup files destination
- press Ok
- Press Schedule
- Select Occurs Daily and Check Occure every 2 Hours
- Starting at "your start time" and "ending at your end time" of your working time.
--- make the same for the full backup but specify weekly
save
- Managements --> right click Maintenance Plan --> New Maintenance Plan
- From tool box drag and drop "Backup Database Task"
- Right click on Backup Database Task you just created and Click Edit
- Select backup type to be Diffrential
- Choose the database you want to backup
- select the folder of backup files destination
- press Ok
- Press Schedule
- Select Occurs Daily and Check Occure every 2 Hours
- Starting at "your start time" and "ending at your end time" of your working time.
--- make the same for the full backup but specify weekly
save
ASKER
Thanks for the help so far. How do I "append the date-time into the filename"?
Something like this:
select
@BkuFilNa = @BkuPthNa
+ @pDbNa
+ '-'
+ case
when ( @pBkuTyp = 'F' )
then 'Full'
when ( @pBkuTyp = 'D' )
then 'Diff'
when ( @pBkuTyp = 'T' )
then 'Tran'
end
+ '-'
+ convert(varchar(20), getdate(),112)
+'-'
+ replace(convert(varchar(20
This assumes that you are creating the backup command in code (in a scheduled job) and dynamically executing it.
select
@BkuFilNa = @BkuPthNa
+ @pDbNa
+ '-'
+ case
when ( @pBkuTyp = 'F' )
then 'Full'
when ( @pBkuTyp = 'D' )
then 'Diff'
when ( @pBkuTyp = 'T' )
then 'Tran'
end
+ '-'
+ convert(varchar(20), getdate(),112)
+'-'
+ replace(convert(varchar(20
This assumes that you are creating the backup command in code (in a scheduled job) and dynamically executing it.
ASKER
Is there a way to do it through the Studio Manager and not through code?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Pass a different file name to the backup each time. I append the date-time into the filename.
i.e. d:/SqlBackup/SomeInstance/