Backup using Snapshot API
-
I am using SQL 2000 running a couple of large (up to 70GB) databases. I am currently backing up to disk, which is working fine.
I now want to backup the databases using the Snapshot feature of my storage array. To do this however, I first need to put my databases (3 of them) into 'backup mode', so as to stop transactions being written to the database, and provide a consistent database to backup.
Has anyone had any experience with this feature? Microsoft provide a sample C++ program called snaphot.cpp that puts a single database into 'backup mode'. I am not particularly clued up on C++. Can multiple databases be put into this mode simultaneously? Is this particularly difficult to do?
Any help would be greatly appreciated.
-
Hi Marc,
You will need to confirm with your vendor what specific mode they want SQL Server put in. Generally, for snapshot backups you either have to take a database offline, or put it into read only mode.
To put the database into read-only:
ALTER DATABASE [yourdatabasename]
SET READ_ONLY
To change it back to read-write:
ALTER DATABASE [yourdatabasename]
SET READ_WRITE
To do this in Enterprise Manager, select your database, rick click and choose properties, select options, check/uncheck (Access) Read-Only.
To take a database offline:
ALTER DATABASE [yourdatabasename]
SET OFFLINE
To bring it back online:
ALTER DATABASE [yourdatabasename]
SET ONLINE
To do this in Enterprise Manager, select your database, right click and choose All Tasks, Take Offline/Bring Online.
If you want to do this across multiple databases simultaneously, you will need to create a script.
As a side note, I would be very wary of relying on backups taken with the snapshot method.
I would also find out from your storage vendor if the snapshot will be taken at a file level or at a volume level.
I have worked with SQL Server on SAN and NAS and have found that snapshot backup methods or backups directly to tape with a SQL snap-in are unreliable. If you decide to go down this path do lots and lots of testing. From my own experience, I would recommend you continue using the native SQL Server backup to disk and then take a snapshot of this, or back it up to tape. This prevents a lot of potential problems arising with the thrid party backup system and you don't have to change the state of your databases, which can only be a good thing!
Good luck
Angela
-
Thanks Angela, I will take that advice on board. I do agree about the native SQL backup to disk. It has been working very well for us.
However, my problem is further complicated by the database being constantly in use. ie. Application Servers have connections open to the database 24/7. So it is essential to use the Virtual Device Interface (I finally found out what the feature was called) to do this backup.
Marc
-
Marc,
You can do native SQL Server backups to disk while users are in your database, so this shouldn't affect you need to have 24/7 availability (it will just run a little slower)
If you put your database in Read Only mode, you can only do this when there are no users connected. Same for when you change back to Read-Write, no one can be connected and you are unable to kill connections.
With Offline/Online no one will be able to even see the database to make a connection and any existing connections will be terminated when you take a database offline.
The only way to provide 24/7 availability and be able to backup online in your situation from what you've described would be to:
a) Use SQL Server Backups as you have been
or b) Always have your databases in read-only mode - I'm not sure what your databases are used for. If they are for reporting this shouldn't be too much of a problem, but if they are transactional this won't work.
Cheers,
Angela
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply