Backup up SQL Servers Advice

Posted on 2011-10-05
Last Modified: 2012-06-21

I have two sql servers which are currently only backed up by a maintenance plan each night at 9.

The server now have numerous database instances on then and although we got the nightly backups, if we were to lose the server due to hardware failure then it would be painstaking process to restore all the database from the bak files.

Is it possible to backup the file structure with a suitable piece of software? If so, could someone recommend something?

Many thanks
Question by:daiwhyte
    LVL 21

    Expert Comment

    If you are referring to backing up the objects in SQL Server without backing up the data, I would recommend SQL Compare from Red Gate.  You can take a snapshot of the database schema and use that to compare to another database or populate a new database.



    Author Comment

    Greg, no Im after something to backup it all so if I did have a server failure. I could use the backup to restore all the databases in one go.

    LVL 21

    Accepted Solution

    Nothing is going to restore all the database at the same time.  To simplify it though, you could script the restore process, so that running the script will restore all of the databases.  The syntax would be something like this for each of your databases.

    RESTORE DATABASE [YourDatabase]
    FROM DISK = '[Path to your backup]\YourBackupFile.BAK'



    Author Closing Comment

    Thank you

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This tutorial will walk an individual through locating and launching the BEUtility application and how to execute it on the appropriate database. Log onto the server running the Backup Exec database. In a larger environment, this would generally be …
    This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now