I am not a systems person or a DBA, and as I was reviewing a bid for a project, I started to wonder how the tape backup (LTO-5 3000) affects live production applications such as Sql Server Databases in Windows Server (2012).
From my experience, the database files are completely locked and cannot be copied. My first thought was that one would use SQL Server Replication to a secondary (virtual) server. Then, the server can be set to perform dumps/backups on a schedule. Is this the "prescribed" approach? If not, what's the general approach for backing up Sql Server databases?
Best Answer
By your talk of the database files being locked, I'm thinking you're going about backing up databases in entirely the wrong way if you're trying to copy the database files themselves and complaining that they are locked. Not only are they 'locked' as you've noticed, simply copying the files would be a great way to end up with a database backup that couldn't reliably be restored.
All the database server products I know of will have some kind of internal backup system that you should be using to backup the data. It will either be a full back up routine that lets you dump the data out to a file or tape directly, or it will be an API that 3rd party backups can connect to in order to trigger a backup that's fully supported by the product. MSSQL can use both these methods.
I'd suggest the thing to do is use SQL's internal backup to backup to a 'file device' on the hard disk and then you can back those files up to tape at your leisure.
edit To address your comment,
1) The impact of using a supported backup method should be minimal, though obviously there has to be some impact. I wouldn't like to just say "Oh it'll be x% slower" or anything like that: it's a 'test it and see' thing. I will say that it should not cause the database to be unavailable.
2) There are two "standards" - backing up to disk using the internal backup routine then backing the results of that up to tape/other nearline storage and/or backing up directly to tape via a database-aware backup agent. I wouldn't say that one method is better than the other (I've seen serious databases protected with both methods) but I tend to use either the SQL->Disk->Tape method, or SQL->Disk->Tape and SQL->backup agent->Tape.