?
Solved

How to create individual differential backup files in SQL 2005

Posted on 2010-01-05
9
Medium Priority
?
202 Views
Last Modified: 2012-06-22
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.
0
Comment
Question by:goodforit
  • 3
  • 2
  • 2
7 Comments
 
LVL 5

Expert Comment

by:dbidba
ID: 26188223
BACKUP DATABASE <<DbName>> TO DISK = '<<Path\FileName.bak>>' WITH DIFFERENTIAL

Pass a different file name to the backup each time. I append the date-time into the filename.
i.e. d:/SqlBackup/SomeInstance/SomeDatabase/Diff/DbName-diff-20100106-032722.bak

0
 
LVL 6

Accepted Solution

by:
aymoon80 earned 1000 total points
ID: 26189109
Check this article:
http://www.devx.com/getHelpOn/10MinuteSolution/16507/1954

you can use SSIS to schedule executing of the backup command
0
 
LVL 6

Expert Comment

by:aymoon80
ID: 26189156
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



0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:goodforit
ID: 26190958
Thanks for the help so far.  How do I "append the date-time into the filename"?
0
 
LVL 5

Expert Comment

by:dbidba
ID: 26191998
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), getdate(),108),':','')

This assumes that you are creating the backup command in code (in a scheduled job) and dynamically executing it.
0
 

Author Comment

by:goodforit
ID: 26195899
Is there a way to do it through the Studio Manager and not through code?
0
 
LVL 5

Assisted Solution

by:dbidba
dbidba earned 1000 total points
ID: 26196368
I have not found a way to create a new file for each new backup using SSMS Maintenance Plans, which I now understand you are using. This type of limitationis why I encourage coding them instead. You may create a SQL Agent job (also in SSMS) using the following code to do the backups on whatever schedule you choose to setup. Separate jobs may be modified to also do your full backups and transaction log backups. If you need help creating the jobs, I can script that also.

..dbi


-- ============================================================================
-- Example script to do a differential database backup.
-- Place this in a SQL Agent job and schedule as desired.
-- ============================================================================
-- ----------------------------------------------------------------------------
-- Declare Variables.
-- ----------------------------------------------------------------------------
declare
   @Cmd      nvarchar(4000)   -- Temporary Command holder.
  ,@Msg      varchar(8000)    -- Temporary Messate holder.
  ,@DbNa     sysname          -- Name of Database to backup.
  ,@BkuPthNa varchar(1000)    -- Backup Path Name.
  ,@BkuFilNa varchar(1000)    -- Fully Qualified Backup File Name.

-- ----------------------------------------------------------------------------
-- Assign Constants.
-- ----------------------------------------------------------------------------
select
   @BkuPthNa = 'D:\SqlBackup\SomeInstanceName\'
  ,@DbNa = 'SomeDatabase'

-- ----------------------------------------------------------------------------
-- Build the backup file name.
-- ----------------------------------------------------------------------------
select
  @BkuFilNa = @BkuPthNa
  + @DbNa
  + '\'
  + @DbNa
  + '-Diff-'
  + convert(varchar(20), getdate(),112)
  +'-'
  + replace(convert(varchar(20), getdate(),108),':','')
  + '.bak'

-- ----------------------------------------------------------------------------
-- Create the base backup command.
-- ----------------------------------------------------------------------------
select @Cmd = 'backup database @DbNa to disk = @BkuFilNa with differential'

-- ----------------------------------------------------------------------------
-- Print the command to be executed.
-- ----------------------------------------------------------------------------
select @Msg = replace(@Cmd,'@DbNa',@DbNa)
select @Msg = replace(@Msg,'@BkuFilNa',''''+@BkuFilNa+'''')
select @Msg = 'Command = ' + @Msg
print @Msg

-- ----------------------------------------------------------------------------
-- Execute the command using the runtime values.
-- ----------------------------------------------------------------------------
exec sp_executesql
   @Cmd
  ,N'@DbNa sysname,@BkuFilNa varchar(1000)'
  ,@DbNa
  ,@BkuFilNa

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question