July 29, 2003 at 2:39 pm
We currently have the need to make an online snapshot backup of SQL Server 2000. This is a feature of our storage array (HP VA7400). I have been looking at a way to put the database in a consistent mode for when I do the backup without shutting it down. It seems that the Virtual Backup Device API is the way to do it. Anyone have any other suggestions? For Oracle we just put the database in online backup mode, do the snapshot and then take it out of online backup mode. Is there anything similar in SQL Server or would using the VDC commands be the only way?
Thanks,
Mark Dickhoff
August 1, 2003 at 9:52 am
I don't think there is a way to do this. Autoclose might work, but it will reopen if anyone accessses it. I think using the backup to disk is the best way to capture the state of the databases.
Steve Jones
August 1, 2003 at 11:46 am
I have been looking at this and the solution is the Virtual Backup Device API. It is possible to create a program that will issue freeze and thaw commands to allow a consistent snapshot of multiple databases. It also submits metadata to SQL Server so that this type of snapshot backup command shows information in the logs.
Mark
August 4, 2003 at 7:34 am
Windows Server 2003 has included this functionality, try their beta version.
Hope this helps
Sindy
August 4, 2003 at 12:41 pm
It should be possible, have not experimented with it so far. You need a SAN to do it since a snapshot is really a copy over of the file allocation map, not a true copy of the data. After that when the data changes the SAN is smart enough not to overwrite blocks that are allocated to a snapshot. The backup just needs the ability to quiesce writes for a few seconds to make work.
Andy
August 5, 2003 at 8:54 am
Here's a sample from the SQL Server log:
Database PRD: IO is frozen for snapshot
Database PRD: IO is thawed
Database backed up: Database: PRD, creation date(time): 2003/07/26(16:43:42), pages dumped: 1, first LSN: 252:209524:1, last LSN: 252:209609:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{FC838DB4-F64A-4CA4-BA4A-D06276AE5907}'}).
I was a bit suprised that this had to be done programmatically but we still got the job done. I guess most people who have a SAN spend the extra money to buy the special client software to do this task.
If anyone has any questions please let me know.
Mark
August 6, 2003 at 7:00 am
Mark, if you have time I'd be interested in an article that covers how you got it all working. Contact me directly if interested.
Andy
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply