Post reply

How to Backup and Restore a sql server2005 database

  • June 9, 2009 at 5:13 am

    #384737

    Hi I need to create a job which takes Backup of database every 7th day.Can some one please tell me how to do this using sql query and also how to restore this using sql query.

    one more thing what other precaution I will have to take while taking backup because database is continously running i.e while taking backup do I have to stop database because millions of records are being insertted to database every day and it is not possible me to stop database.so Please while telling solution keep in mind that my database is running continuously

    Thanks

  • June 9, 2009 at 5:25 am

    #1006585

    Backup and restore is a big subject, your best bet (honest) is to refer to backup database and restore database in books online and do some practicing on a test server to get comfortable with it.

    you can run a SQL backup whilst the database is active.

    Best way to schedule a backup is via a maintenance plan. Under management in SSMS right click on maintenance plans and follow the prompts the wizard that come up gives you. when in the plan choose backup database task from the toolbox bottom left.

    If your database is that busy backing it up only every 7 days does not sound frequent enough. Daily is the usual set up plus transaction log backups if the recovery mode is FULL or bulk logged

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

  • June 9, 2009 at 5:27 am

    #1006588

    Look up BACKUP DATABASE and RESTORE DATABASE in Books Online (the SQL help file). There's a good amount of info there.

    Backups are online operations, nothing to worry about if the DB's in use. All you'll see is higher IO load during the backup.

    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

  • June 9, 2009 at 5:30 am

    #1006592

    Hey Gail, I'm finally typing faster than you 🙂

    heres a thread might interest you and the gang:

    http://www.sqlservercentral.com/Forums/Topic731195-1291-1.aspx

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

  • June 9, 2009 at 6:01 am

    #1006608

    Thanks but Please tell me syntax of both backup and restore

  • June 9, 2009 at 6:08 am

    #1006615

    The complete syntax of BACKUP DATABASE with examples can be found at

    http://msdn.microsoft.com/en-us/library/ms186865.aspx

    and that of RESTORE DATABASE can be found at

    http://msdn.microsoft.com/en-us/library/ms186858.aspx


    Pradeep Singh

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

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