Tape Backups

  • Hi All,

    I have a basic question. Will a Tape Backup job properly backup .MDF and .LDF files while the SQL Server is running on the server? Or do we need to stop the SQL Server while the tape backup is running?

    Regards,

    Murali Damera.

    .

  • No problems with users being in system while backups are running. There is no difference in system availability between tape and disk backups.

  • No. My question is different. Here is the scenario.

    I have a new server coming on board with SQL2K on WIN2K. If we run a TAPE BACKUP from NT (to backup the files & directories on NT - This is NOT SQL Server Backup), Can we recover SQL Server Database using .MDF and .LDF files on the table? Or Do we need to stop the SQL Server for the proper backing up of the SQL database files onto TAPE?

    .

  • Your backup software will not back them up while the service is running. You will need to stop the service prior to taking a backup.

    Best solution in my opinion is schedule SQL Server backups to disk and have your backup software grab those files off disk. Very reliable and no downtime.

    HTH

    David

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • You can recover a database by using the .mdf and .ldf files. This is actually the easiest way to RECOVER a database since you only need to do an attach command. However, it is the hardest way to BACKUP a database since as others stated, you need to stop the services and then backup the files.

    The easiest way overall is to do an SQL Server backup (to disk or tape) and then recover using SQL Server.

    -SQLBill

  • Thank You Bill. Thats the same way I am doing on the other servers. I jsut wanted to take extra coution and take Data directory backup as well atleast biweekly or monthly, during the sql server downtime.

    Is that a bad idea?

    .

  • It's never a bad idea to copy the .mdf and .ldf during down times. It gives you the best way to restore data. Just remember:

    If you restore using the .mdf/.ldf files, you can never restore current transaction log backups or differential backups to those files. (i.e. Sunday you copy the .mdf/.ldf files, Monday you do a differential or transaction log backup. Tuesday you restore using the .mdf/.ldf files, you cannot also restore the Monday diff or transaction logs).

    -SQLBill

  • One thought - you cannot restore to a point in time this way. I would let SQL backup to disk, then save disk to tape.

  • i think most DBAs do this: use SQL backup to backup the database and log to physical backup file and use NT Backup of third-party s/w to backup the backup files to tape. No down time. Easy to restore, and able to restore to a point in time. 🙂

  • Thank You. I am doing the same. But I thought I would take Monthly backup of .mdf and .ldf files.

    Thanks for your suggestion.

    .

  • as a side-note another GREAT reason to perform backups as you have all stated is:

    By allowing SQL to do the backups to specified location the 3rd party software will never lock-up your SQL server due to jobs abending.

    This is a problem with BackUp EXEC. It locks the database(s) performs the backup and releases. This is different than SQL which takes a snap-shot of the log(s) does the back-up and then checks to see if log(s) are the same.

    In the 1st case if the tape backup fails you have (at minimum) to stop/restart services. With the 2nd if the job fails it has no effect on the server still running.

    AJ Ahrens

    SQL DBA

    Revenue Assurance Management - AT&T



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • what are mdf. and wmf documents?

  • .mdf = the physical file that your database uses to store data

    .ldf = transaction log(s)

    AJ Ahrens

    SQL DBA

    Revenue Assurance Management - AT&T



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

Viewing 13 posts - 1 through 12 (of 12 total)

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