SQL server back up

  • bailifei2011 (10/14/2011)


    For database, it is safer to choose one reliable tool to protect it. Trust backup software on the market, such as todo backup, nova, clonezilla, Acronis. Personally, I recommend todo backup for price.

    It also can provide advanced backup scheduler and one-click to recover system to dissimilar hardware, incremental backup, differential backup, backup SQL server/ Exchange server, Backup to tape for 2003/2008/2005 servers.

    another company employee maybe ? 🙂

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • If your relational databases are large, complex, or valuable enough to warrant a full time DBA, then as a general rule of thumb, that DBA or team of DBA's should be responsible for the backup and recovery of those databases due to that being in their core competencies.

    There is unnecessary risk to moving that responsibility over to network administrators who are unlikely to know the nuances of transaction logs, log shipping, differentials, transaction logging levels, the performance impact of various database consistency checks, the impact of index rebuilds, how the backups fit into the larger maintenance plans, and more.

  • I backup all my databases (and the tranlogs) to the actual server they reside on. I wrote a program that copies all the new backup files every hour to a Data Domain deduplication system that is 1/2 mile away. I keep 2 days' worth of backups on the actual server to make restores easier, since I won't have to copy them from the Data Domain (but could if the backup was needed from before two days ago).

    I personally resist the use of 3rd party tools for backups. The two biggest reasons are:

    1) KISS - keep it simple.

    2) Disaster Recovery - in a DR situation, where I have to get a new database server up and running and restore all the databases that are needed, I don't want to have to worry about installing the 3rd party tools, dealing with app activation keys, etc. I just want to copy my databases, and restore them, so the users can access them.

    Not to mention, data retention policies. If several years go by, and you have an old copy of the database, but you no longer have a copy of the 3rd party tool you used back then (or licenses, etc.), you won't be able to restore the database.

    I would only use 3rd party tools if there was a business requirement that the native tools could not handle, then I would ask management to sign off on its use, after taking into account the issues above.

    Just my way of doing things. Every situation is different.

  • The mature third party backup tools provide utilities, for free, without license, that convert their compressed/encrypted backups to native formats. You can get those for SQL Backup (Red Gate) and Litespeed.

    Disclosure: I work for Red Gate Software

  • Disclosure: I work for Red Gate Software

    I would recommend it as a best practise to add 'Disclosure' on such matters. Nice start-up Steve.

  • I prefer to use the native backup tool too.

    But one problem I often comes with how to select to backup all user databases but to exclude one or more specific databases.

    I don't like to choose to backup specific databases, because if someone later created a new database, you have to remember to come to the maintenance plan to add it. But if there is a feature of exclude, it will automatically include the new database.

  • I had a member of our ops department come in the other day and ask why he couldn't restore a backup he had of the accounting database (for some reason DBAs do not maintain this). The first thing that I saw was that the file was an mdf file. Hmm... I asked how he was backing up the files and he said that they were using a 3rd party application and simply backing up the file.

    Bottom line is that any disaster recovery plan should include regular restores of the data. Have you tested this solution and restored a backup from it? What value does it add to your current strategy to use this? Simply because it is there? If you have no reason to use it, then don't. If it offers something above and beyond what you can do with scripts, then test it and base your decision from that.

    Jared
    CE - Microsoft

  • bailifei2011 (10/14/2011)


    For database, it is safer to choose one reliable tool to protect it. Trust backup software on the market, such as todo backup, nova, clonezilla, Acronis. Personally, I recommend todo backup for price.

    It also can provide advanced backup scheduler and one-click to recover system to dissimilar hardware, incremental backup, differential backup, backup SQL server/ Exchange server, Backup to tape for 2003/2008/2005 servers.

    Either that or someone trolling. Putting in a contrary response just to start a flame war.

    Leo

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • :w00t: :hehe:

    I use SQLBackup from Red-Gate, price is nice and functionality is better than the price. I backup about 500 DBs and I can schedule the jobs, compress the files, protect the files with password, sent to a centralized location and from there, the Windows team backup the centralized location using their own backup to tape tool.

    The other advantage is the time running the backups, for 300 GB it runs in about 15 minutes and the compressed size (which you can set to a desire percentage) is about 19 GB. Great tool.

    :w00t: :hehe:

  • Steve Jones - SSC Editor (10/17/2011)


    The mature third party backup tools provide utilities, for free, without license, that convert their compressed/encrypted backups to native formats. You can get those for SQL Backup (Red Gate) and Litespeed.

    Disclosure: I work for Red Gate Software

    I didn't look for it at a previous company when I was told that there was no utility to convert a Quest Litespeed backup to a native backup. When a native backup of a database was requested we would restore a copy of the database to test/development server and run a native backup.

    Always thought this stupid as I knew that Hyperbac had such a tool for their backups when I ussed their product (prior to RedGate buying them) at another employer. Why won't Quest have such a tool?

  • sqlfriends (10/17/2011)


    I prefer to use the native backup tool too.

    But one problem I often comes with how to select to backup all user databases but to exclude one or more specific databases.

    I don't like to choose to backup specific databases, because if someone later created a new database, you have to remember to come to the maintenance plan to add it. But if there is a feature of exclude, it will automatically include the new database.

    And then you have those of us on the other end of the spectrum. I prefer to have individual backup processes. Gives me better control of when the backups occur. Also, I don't use the Maintenance Plans, as I prefer to use a custom process so I can control the different database maintenance activities.

  • AFAIK, the tool comes with the Litespeed install. Unless things are changed, there was a converter to move compressed->native that was a .exe in the folder where you installed it. DBAssociates (inventor of Litespeed) and then Imceda provided it. Not sure about Quest.

  • Steve Jones - SSC Editor (7/20/2012)


    AFAIK, the tool comes with the Litespeed install. Unless things are changed, there was a converter to move compressed->native that was a .exe in the folder where you installed it. DBAssociates (inventor of Litespeed) and then Imceda provided it. Not sure about Quest.

    I can only go off what I was told. As a contractor there I wasn't going to argue. Just didn't make any sense to me that Quest would not provide a tool convert their backup files to a native backup format. Just makes sense from a DR perspective.

  • And then you have those of us on the other end of the spectrum. I prefer to have individual backup processes. Gives me better control of when the backups occur. Also, I don't use the Maintenance Plans, as I prefer to use a custom process so I can control the different database maintenance activities.

    I would be interested how many of DBAs use maintenance plan, and how many percent use individual backup process:

    For most of the our servers, we have a standard for maintenance plan,

    we created subplans like below:

    1. Full backup on Sunday for all dbs.

    2. Differential daily

    3. Transaction log every 1 hour daily

    4. Saturday check integrity, update statistics, reindex...

    5. Clean up history

    when you say individual process, do you mean for each above, you are using script to do it?

    Thanks

  • I have been in charge of small SQL shops <30dbs and <1 terabyte of info. I have been using Ola Hallengren backup scripts for about 4 years now backup up to both local and network drives. So far I have been really pleased with the results.

Viewing 15 posts - 16 through 30 (of 32 total)

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