Hi folks!
We have a client who uses GoodSync to make file/folder backups on his server. He has the backup program set up to make copies of the SQL databases without first stopping the SQL Server service, so the databases are active and live when the backup is performed. Question: can this be done or is this simply not a good idea? It seems to me that any software which would attempt to grab the databases without coordinating such operation with the SQL server process might interfere with said process or other processes which are actively using the databases.... but what do I know.
Thanks!
hopefully this article might shed some light
please google search using right terms .. plenty of links
Not only could it interfere with/impact SQL Server's operation, the backups may not be restorable in case they're needed. Almost every third-party backup software does not properly back up SQL Server files in a way that can be restored to a transactionally consistent state.
A better option for your client is for them to make regular database backups, full and transaction logs, using the BACKUP command in T-SQL. They can then use GoodSync to copy those backup files.
2 Likes
MS has code that vendors can use to do SQL Server backups correctly. We use a 3rd party tool and it works fine. But it does database backups, not file backups.
I don't know of any way to backup individual files and have such backups be usable by SQL Server. I'd expect massive failures if you tried to restore files -- rather than entire dbs -- for SQL Server to use.
1 Like
Use the VDI - an external backup can either perform native backups to virtual devices or freeze/thaw the databases to perform file level backups.
Generally - freeze/thaw is used with storage level snapshots that perform a snap of the files, unfreezes the database and then copies (backs up) the snapshot.
1 Like
Whoa! Jeff, your post just raised my eyebrow. That seems pretty draconian! Seems to me that all other running processes which try to write to the database while it is frozen will likely crash or time out or, worse, impair the database.
That would only occur if the freeze is extended - but this is a normal operation when performing snapshot backups and has been around for a very long time. Storage backup solutions all utilize the freeze/unfreeze methodology to perform backups.
I am using SPP on my servers now - and the freeze/unfreeze process takes less than one second.
To give you a better idea - my 3TB+ database was frozen at 19:09:42, resumed at 19:09:42 and 'completed' the backup in SQL Server at 19:09:44. The actual process of backing up the data takes much longer - but since that is on a vSnap volume separate from the regular volumes it has no impact on the system.
BTW - the idea of freeze/thaw has been around forever. We had a similar process in place on our VMS systems where we froze the databases, split the mirrored drive, unfroze the databases - then proceeded to backup from the mirror.
Once the backups were completed - we added the mirror back and let the drives synchronize the mirror disk.
For redundancy - we used a 3-mirrored disk setup so we could remove one of the mirrors and still have 2 mirrored disks available.
