locked
Help needed for backup RRS feed

  • Question

  • Hi All

    I have to create backup of a database. For that the conditions are

    1) Backup must be performed  weekly at 7a on Sundays.

    2)  DB backup job hold 8 weeks and overwrite from week 9.

    for the 1st one i will create a job that will perform backup on Sundays.

    for the 2nd condition there are 2 options are available.

    I want to know how to overwrite the existing backup.

     1st one through vbscript i can do it.

    2nd through stored procedures can do it.

    Please let me know which one is the best and how i can achieve.

    Finally my doubt is that every week is it required to take the fullbackup or shall i go with differential backup along with log.

    Currently my DB size is 55GB

    Daily if i take  the DB backup it will come around 30 GB.

    Waiting for valuable replies.

    Tuesday, July 27, 2010 3:29 AM

Answers

  • In SQL Server 2008 you can create a new Maintenance Plan (located in SQL server management studio under Management node). Her you can specify how often and how long the database backup should "live".

    We use two Maintainance plans, one for transactionlog backup 4 times a day and one for full backup each night. This is a big production system with 7-8000 users, appox 150Gb backups daily. This solution works fine for us and have been running for several year. Each production database has Recovery model set to FULL.

    We store each backup for each database in a separate file and keep the files locally on a separate disk for 3 days (incl. all transaction logs).

    When you create an new maintainance plan, you can drag in various "tasks" for the cleanup etc.

    • Marked as answer by Kalman Toth Tuesday, July 27, 2010 10:19 AM
    Tuesday, July 27, 2010 10:15 AM

All replies

  • It depends on your recovery requirements. Generally differential backups suffice. If you are using SQL Server 2008, then you may use backup with compression option to save a lot of memory space.
    Phani Note: Please mark the post as answered if it answers your question.
    Tuesday, July 27, 2010 4:16 AM
  • Sorry i didn;t mentioned the version we are using. We are using SQL Server 2005 enterprise edition
    Tuesday, July 27, 2010 4:24 AM
  • Using the Maintenance Plan will be the easiest approach

    Pradeep Adiga http://www.sqldbadiaries.com
    Tuesday, July 27, 2010 4:34 AM
  • I prefer rhe version to do it with stored procedures and scheduled with SQL Server Agent.
    This works fine in our Company.

    With maintenance plans we hat some issues with the clranup.

    Tuesday, July 27, 2010 6:08 AM
  • Hi Christa

    Can u provide some examples

     

    Tuesday, July 27, 2010 6:54 AM
  • The Cleanup didn't do the cleanup. All older backups stay in the folder, so I create my own Cleanup.

    Maintenance Plans in SQL 2000 worked fine, but in SQL 2005 not really, so I prefer to all with procedures.

    Tuesday, July 27, 2010 7:22 AM
  • can u provide examples of  u r applied procedures

    Tuesday, July 27, 2010 7:27 AM
  • Oh sorry, I misunderstood your post ;-)

    here an example for backup:

    declare @pfad varchar(100);
    set @Pfad = 'G:\Bandsicherung\RC_ArtikelDB\RC_Artikel_DB_' + convert(varchar(8),getdate(),112) +
    replace(convert(varchar(12),getdate(),108),':','') + '.bak';
    BACKUP DATABASE [RC_ArtikelDB] TO  DISK = @pfad WITH NOFORMAT, NOINIT,  NAME = N'RC_Artikel_DB_backup', SKIP, REWIND, NOUNLOAD

    this is the first step in my Job. On sucess got to "cleanup" on failure goto "Mail send"

    Example for cleanup:

    declare @Datum varchar(25)
    set @Datum = replace(convert(varchar(25),dateadd(hh,-23,getdate()),120),' ', 'T')
    --select @datum
    EXECUTE master.dbo.xp_delete_file 0,N'G:\Bandsicherung\RC_ArtikelDB',N'bak',@Datum

    Tuesday, July 27, 2010 7:44 AM
  • Currently my DB size is 55GB


    How long the full backup takes?

    Do you need recovery capability in the middle of day (as opposed to recover to last night backup)?


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, July 27, 2010 9:21 AM
  • full backup takes 14-16 mins

    transaction log backup 5 mins

    when we are taking daily backup it comes around more than 30GB.

    Currently there is no need of recovery capability. But in case of failure desperately we need it. So priority will be ecovery capability is needed.

    Waiting for your's valuable  suggestions

    We are using SQL Server 2005 enterprise edition

    Tuesday, July 27, 2010 10:01 AM
  • In SQL Server 2008 you can create a new Maintenance Plan (located in SQL server management studio under Management node). Her you can specify how often and how long the database backup should "live".

    We use two Maintainance plans, one for transactionlog backup 4 times a day and one for full backup each night. This is a big production system with 7-8000 users, appox 150Gb backups daily. This solution works fine for us and have been running for several year. Each production database has Recovery model set to FULL.

    We store each backup for each database in a separate file and keep the files locally on a separate disk for 3 days (incl. all transaction logs).

    When you create an new maintainance plan, you can drag in various "tasks" for the cleanup etc.

    • Marked as answer by Kalman Toth Tuesday, July 27, 2010 10:19 AM
    Tuesday, July 27, 2010 10:15 AM
  • When you create an new maintainance plan, you can drag in various "tasks" for the cleanup etc.

    That is when you use the Designer.

    Using the Database Maintenance Plan Wizard, no dragging, rather checkmarking.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, July 27, 2010 10:20 AM
  • Hi SQLUSA

    Any suggestions for the above mentioned options(My Thread)

    Tuesday, July 27, 2010 10:22 AM
  • full backup takes 14-16 mins

    transaction log backup 5 mins


    There is no need to use differential backup. Full backup nightly and transaction log backup during business hours like every 1/2 hour. The Database Maintenance Plan Wizard can set it up for you in a few minutes. Make sure you test it.
    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, July 27, 2010 10:23 AM
  • This article will get you "going" http://technet.microsoft.com/en-us/library/ms189953.aspx

    Here is another article with pictures etc http://www.databasedesign-resource.com/sql-server-maintenance-plan.html If this does not look exactly like your wizard the version is different but the content is approx. the same.

    Good luck:)

    Tuesday, July 27, 2010 12:42 PM