Post reply

what is the easiest & effective way to take the backup & restore it.

  • November 15, 2006 at 7:32 am

    #175360

    what is  the easiest & effective way to take the backup & restore it.

  • November 15, 2006 at 7:53 am

    #671960

    depends

    i use veritas and like it, a lot of people here backup to disk and then to tape. where i work they don't want to spend the money for disk and tape. there are also some disk to disk backup applications like Evault and Avamar (now EMC) that are very slick

  • November 15, 2006 at 9:42 am

    #672037

    "the easiest & [most] effective way" means a lot of different things to a lot of different DBA's in different environments...

    Questions to ask yourself...

    What am I backing up,

    How Often,

    what is my Recovery point objective (how much data can I lose, do I need to get back to this morning, or a point in time)

    what is my Recovery time objective (how fast do I need to get everything back up and running after a failure)

    Answering these questions will go along way into us being bale to suggest alternatives to you.

    If you're just backing up a small SQL server database, perhaps the native backup tools, maintenance plans etc will suit your needs.

    If you have a large database and you need your backups to happen quickly and be very compressed then perhaps you may want to look into a product that will help with those things.  Something like Litespeed from Quest may be of value to you.

    Basically "it depends", although not the answer you are looking for is the best answer I can give you with no more information to go on.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • November 15, 2006 at 1:52 pm

    #672129

    Detach, copy, attach.

  • November 15, 2006 at 1:55 pm

    #672132

    but then the db is offline while you are backing it up

  • September 18, 2007 at 5:57 am

    #734270

    MICROSOFT SQL SERVER

    Selecting a SQL Server backup model

    Greg Robidoux, Contributor

    04.12.2005

    Rating: -3.88- (out of 5)

    RSS FEEDS: 

    Expert advice on database administration

    In a previous tip we discussed the different recovery models SQL Server offers and decision points on which to base your recovery model selection. After you've selected the appropriate recovery model, you need to put in place the proper backup strategy to minimize data loss and downtime in case of a database failure.

    I have seen many database installations wherein the correct recovery model is in place to minimize data loss, but the correct backup options and plans are not in place. Often the Full Recovery model is selected, but the only backups occurring are full backups. Occasionally, the Simple Recovery model is selected, even though the customer base could not afford to lose all transactions since the last full backup.

    Creating a full backup once a day probably makes a lot of sense to most people, but that should only be the beginning of your backup strategy. We will take a look at some of the components to help define a backup strategy as well as the different backup options that are available.

    First, let's look at the backup options:

    Database (Full)

    • This option creates a full backup copy of the database at the time the backup occurs.
    • Available for all recovery models.

    Transaction

    • This option creates a copy of the active transaction log, which lists any transactions that have occurred since the last backup.
    • Available only for Full Recovery or Bulk-Logged Recovery models.

     

    Differential

    • This option copies only the database pages that have been modified after the last database backup.
    • Available for all recovery models.
    • When to use Differential backups.

    • If you issue several transaction log backups throughout the day, it is helpful to also use differential backups in conjunction with database and transaction backups. The use of differential backups minimizes the number of restores that need to occur in case of a failure.
    • Here is an example: If you issue one database backup at midnight and transaction backups every 15 minutes, by 9 a.m. you will have 37 backup files. If you need to restore your database to 9 a.m., you will have to restore all 37 files. If you also issue differentials every three hours, and you need to restore your database to 9 a.m., you will only need to restore two files (the full and the differential that occurred at 9 a.m.). You can see how this could greatly decrease the time it takes to do a restore of a database and, therefore, minimize downtime.

    Others

    • In addition to Database, Transaction and Differential backups, SQL Server also has options for File or Filegroup and Snapshot backups. These options will be addressed in future tips.

    Unfortunately, SQL Server does not have an option for simply setting up a backup model like it does for the recovery model. It would be nice if you could just select a backup model from a drop-down list. The closest thing to that is Maintenance Plans, but the options in Maintenance Plans do not give you the choice of doing Differential backups, and you still need to set the proper backup schedule.

    Based on the recovery model you have selected, you now have to choose which type of backups to run -- but more importantly, the time and frequency of the backups.

    So, what is the best backup model to use for your database?

    If you are using the Simple Recovery model:

    • Use only Database backups.
    • Depending on how frequently the data changes and how critical it is, you should issue at least one full backup a day.
    • This should occur during off-hours when there is minimal database use.

    If you are using the Full or Bulk-Logged Recovery model:

    • Option 1 -- use Database and Transaction.
    • Option 2 -- use Database, Differential and Transaction.
    • A full backup should be created at least once a day.
    • Transaction log backups should occur every 15 minutes.
    • Differential backups should occur every three hours.
    • The full backup should occur during off-hours when there is minimal database use.
    • The transaction and differential backups should be on a set schedule based on when your full backup occurs.

    How to implement your backup model

    You can run backups manually, but the best approach is to schedule backups using SQL Agent. Once you set up the backup job, let SQL Agent run the backups on a set schedule. This can be done one of three ways:

    • Enterprise Manager

    • Right click on the database name.
    • Select "All Tasks."
    • Select "Backup Database."
    • Once the options are set, you can then use the schedule option to create a job.

    <>

    • T-SQL

    • Using BACKUP commands, you can create the command and then use Enterprise Manager to create a job or use T-SQL to create the job.
    • You have the ability to create Database, Differential and Transaction backups using T-SQL.

    • Maintenance plans

    • SQL Server has a maintenance plan wizard that walks you through whether you want to do Database and/or Transaction backups. The wizard also allows you to set the backup schedule.
    • To access the wizard in Enterprise Manager, under Management right click Database Maintenance Plans and select New Maintenance Plan.
    • Differential backups are not an available.
    • The default for transaction log backups is once a day, which you should change to more frequent transaction backups.

    <>

    Summary

    The types of backups and the schedule you use will allow you to control how much data could be lost in case of a failure as well as the time it takes to do a recovery. Using Differential backups could dramatically decrease the time it takes to do a restore of your database and get your users up and running quicker. Using Maintenance Plans is also a place to start, but take the time to get familiar with all of the backup options and then select a plan that matches your business goals.

     

     

    MICROSOFT SQL SERVER

    Selecting a SQL Server recovery model

    Greg Robidoux

    03.22.2005

    Rating: -4.36- (out of 5)

    RSS FEEDS: 

    Expert advice on database administration

    SQL Server 2000 offers three recovery models for each database: full recovery, simple recovery and bulk-logged recovery. The recovery models determine how much data loss is acceptable in case of a failure and what types of backup and restore functions are allowed.

    Most people either select full or simple for all of their databases and just stick with the same option across the board. In most cases, selecting the full recovery model is the smartest option, because it gives you the greatest flexibility and minimizes data loss in the event a restore has to take place.

    Although using the full recovery model makes logical sense, there are reasons why the other two options are available. We will further define why there are three options and when you might want to use the different options to protect your databases. First, let's take a closer look at each model.

    Simple

    The simple recovery model allows you to recover data only to the most recent full database or differential backup. Transaction log backups are not available because the contents of the transaction log are truncated each time a checkpoint is issued for the database.

    Full

    The full recovery model uses database backups and transaction log backups to provide complete protection against failure. Along with being able to restore a full or differential backup, you can recover the database to the point of failure or to a specific point in time. All operations, including bulk operations such as SELECT INTO, CREATE INDEX and bulk-loading data, are fully logged and recoverable.

    Bulk-Logged

    The bulk-logged recovery model provides protection against failure combined with the best performance. In order to get better performance, the following operations are minimally logged and not fully recoverable: SELECT INTO, bulk-load operations, CREATE INDEX as well as text and image operations. Under the bulk-logged recovery model, a damaged data file can result in having to redo work manually based on the operations that are not fully logged. In addition, the bulk-logged recovery model only allows the database to be recovered to the end of a transaction log backup when the log backup contains bulk changes.

    So once again, based on the information above it looks like the Full Recovery model is the way to go. Given the flexibility of the full recovery model, why would you ever select any other model? The following factors will help you determine when another model could work for you:

    Select Simple if:

    • Your data is not critical.
    • Losing all transactions since the last full or differential backup is not an issue.
    • Data is derived from other data sources and is easily recreated.
    • Data is static and does not change often.
    • Space is limited to log transactions. (This may be a short-term reason, but not a good long-term reason.)

    Select Bulk-Logged if:

    • Data is critical, but logging large data loads bogs down the system.
    • Most bulk operations are done off hours and do not interfere with normal transaction processing.
    • You need to be able to recover to a point in time.

    Select Full if:

    • Data is critical and no data can be lost.
    • You always need the ability to do a point-in-time recovery.
    • Bulk-logged activities are intermixed with normal transaction processing.
    • You are using replication and need the ability to resynchronize all databases involved in replication to a specific point in time.

    Switching recovery models

    For some databases, you may need to use a combination of these recovery models. Let's say you have a critical system and you cannot afford to lose any data during daily operations; but during off hours there are maintenance tasks and data loads that use way too much transaction log space to log every transaction. In a case like this, you may want to switch recovery models prior to your maintenance tasks. This can be automated using T-SQL in the job that runs your maintenance or data load tasks. After the maintenance task is completed, the recovery model can be switched back again.

    Switching between full and bulk-logged models is probably the best scenario for changing recovery models and also the safest and easiest. You can switch from any recovery model to another recovery model, but prior to or after the switch, you may need to issue additional transaction log or full backups to ensure you have a complete backup set.

    How to change recovery models

    There are two options that can be used to switch recovery models.

    • Enterprise Manager

    • Right click on the database name, select Properties, select the Options tab and select recovery model from the drop-down list. Selecting OK will change the recovery model immediately.

    ·         T-SQL

    ALTER DATABASE Northwind SET RECOVERY FULL

    GO

     

    Summary

    As you can see, the three recovery models each have a different purpose. If you are still unsure which recovery model to select, I recommend using the full recovery model and issuing both full and transaction log backups throughout the day. It becomes a little more complex to do both full and transaction log restores, but in case there is a failure in the middle of the day, you will appreciate the flexibility of point-in-time recovery.

     

     

    N.B : http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1157131,00.html

     

    _____________________________________________________________________________________

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply