Post reply

backup SQL2K using disk snapshots

  • March 10, 2004 at 9:04 am

    #162062

    Hello Experts,

    please help me on how to place SQL2K DB into(and back from) "Suspended/Frozen" state ( when new transactions not commited in DB but stored in cache ) from the command line? I'm absolutely new to this RDBMS and still have

    no idea how to do this. I need this to organize backup using disk snapshot which require a small amount of time when DB stays in consistent but not offline state.

    Any inputs are greatly appreciated

    Thanks in advance, Andrey.

  • March 10, 2004 at 12:37 pm

    #498173

    You backup databases with the BACKUP DATABASE Command and you can backup logs with the BACKUP LOG command.  The Backup Log command backs up changes made since the last BACKUP DATABASE command was executed.    Both of these commands can be done while users are connected to the database and doing work.   SQL Server takes care of commiting transactions, there aren't any timing issues issues you need to worry about with respect to backups. 

    There are many reasons why SQL Server backups are better than disk snapshots.

    1.  Are all your databases (including system databases on the same physical) disk?  If they are they when you recove are you recovering all disk files or just the databases files?

    2.  Do you have multiple databases?  If so are they on the same disk?

    3.  Are all componants of a databases on the same disk?  I keep logs separate from the database and the system databases are on a separate drive from the user databases.

    SQL backup allow you to do point in time recovery.  Can you do this with disk backups?

    Francis

  • March 11, 2004 at 4:09 pm

    #498406

    Andrey,

    I had this same discussion with a particular SAN vendor just last week.  From your post, I'm guessing you are trying to use these disk snapshots on a SAN.

    In my case, the vendor kept describing putting SQL Server in a "frozen" state.  Basically this was vendor jargon and can't be done on SQL Server, though I believe there is something called 'hotbackup mode' on Oracle, which is simalar.

    After putting our databases into production on this SAN and having a our test restores fail miserably, it turns out that the so-called database/file snapshots, were in fact volume snapshots and all databases residing on the same volume have to be offline for the backup AND the restore process.

    Also, as far as I know, snapshots can't restore to a point in time like a SQL transaction log backup can.

    We have gone back to using SQL Server backups, which puts us back in control and we know they are reliable.  I would recommend you forget about the snapshot idea and do the same.

    Good luck,

    Angela

     

  • March 15, 2004 at 5:19 am

    #498725

    Hello, thanks to all for replies.

    I didn't find any clear way to make backup using disk snapshot for MS SQL using my own backup scripts.

    The solution is to purchase third-party integrations, like EMC SIMS for Clariion, all of these integrations use special API which called VDI ( virtual device interface ) to interact MS SQL.

    Andrey

  • March 15, 2004 at 1:53 pm

    #498826

    Angela is correct.  SQL Server backups are trustworthy and reliable.

    As for the third party products, I believe they use SQL Server API's because they can NOT be reliable or trustworthy without doing so!!

    GaryA

Viewing 5 posts - 1 through 4 (of 4 total)

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