Post reply

Back up a Database?

  • March 20, 2009 at 4:28 am

    #201411

    Hi everybody,

    I am having some doubts regarding the backup of a DB.

    1)I create a Maintenence plan in SQL server so as to take the back up of the DB.But, i am unable to see the files where the backup is stored and the logfile is created?

    2)What happens to the DB backup if there is no required space in the Disk?Whats the solution to get rid of this?

    Thanks for all.

    Venu Gopal.K
    Software Engineer
    INDIA

  • March 20, 2009 at 4:49 am

    #963410

    But, i am unable to see the files where the backup is stored and the logfile is created?

    Backup location is by default in the installation path.You can change it to any other location.What do you mean by logfile creation?Please explain in detail.

    What happens to the DB backup if there is no required space in the Disk?Whats the solution to get rid of this?

    The backup job will fail.You can see the error details in the job history.

    Instead of appending the backups,give the overwrite option so that space is always available(if one recent backup is sufficient for you)

  • March 20, 2009 at 5:02 am

    #963412

    Hi Venu,

    Each Maintenance Plan creates Jobs with respect to the activities you specify in the plan definition.

    If you have properly created the Maintenance Plan for db backup, a associated job will be created and may run as per defined schedule. You can check the location of BAK file from the SQL Server Log also.

    If there is no enough disk space, the job will fail. Same you can check from the Job History.

    Hope this information helps you.

  • March 20, 2009 at 5:28 am

    #963424

    Thanks for all who give me u r valuable reply.

    I created the Maintenanceplan correctly and presently now it is running also adn i keep its backup timing for every 1 hour . One to say is there is no error report r messages regarding the failure of back up , it is giving the back us is successful.But i am unable to get where the files are stored?

    Venu Gopal.K
    Software Engineer
    INDIA

  • March 20, 2009 at 5:36 am

    #963427

    Hi Venu,

    There are ways to check the location of log files.

    From SQL Server Mgmt Studio, you go to Management --> SQL Server Logs --> Current Logs. You can see the Location of Backup file when you explore the Backup Job activity log.

    If you open the Maintenance Plan to modify, select the Backup plan from Management and Right Click --> Modify, the plan will be open in the new window. Now select the activity icon from left box and do a Right click and select Edit. The window will appear with the details of db backup and the location of backup file.

    Hope this will help you.

  • March 20, 2009 at 5:40 am

    #963428

    K thanks a lot,

    Let me know one more.

    What are the main causes for a Database backup failures? How can we get rid of those common failures?

    Thanks in advance

    Venu Gopal.K
    Software Engineer
    INDIA

  • March 21, 2009 at 1:41 am

    #963923

    First reason if there is no disk space - so careful palnning of backup strategy will help , Full , differential and transaction log backups and then moving it to tape for future purpose so disk will be free

    Second if the database is in suspect state due to data corruption - then try detach and attach the database

    Third -Modify Folders permissions does not exist for SQL server startup account in the backup folder

    Fourth - SQL server agent is in stopped state - Write a script to monitor it often

  • March 21, 2009 at 2:41 am

    #963936

    rajganesh.dba (3/21/2009)

    Second if the database is in suspect state due to data corruption - then try detach and attach the database

    Absolutely not! That is the worst possible advice regarding a suspect database. A suspect database once detached will often fail to attach.

    Take a look at this article. http://www.sqlservercentral.com/articles/65804/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 21, 2009 at 11:34 am

    #964004

    Hi Venu,

    The database backup fails mainly due to disk space issue. You must ensure that you must have almost 3 times of free disk space compare to size of your database.

    Second reason is the network issue when you are taking a backup over the network.

    Third reason could be the disk failure. You should have spare disk for this purpose. Use possible and feasible RAID level in your disk management.

Viewing 9 posts - 1 through 8 (of 8 total)

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