0

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?

Candide
  • 141
  • 5

3 Answers3

4

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.

Rob Moir
  • 31,884
  • 6
  • 58
  • 89
  • Yes, but your answer does not address my questions: `. How does it affect production systems? 2. What is the standard for doing database backups (using LTO tapes) for Sql Server? – Candide Jan 14 '13 at 12:15
  • 1
    1. It requires *some* local resources, obviously. Not a lot, though. 2. By learning about and using the BACKUP command suite. – adaptr Jan 14 '13 at 12:49
  • The effect of backing up a production system will be that the production system is copied to another storage media. What are you fishing for here? – Basil Jan 14 '13 at 15:36
  • MSSQL supports direct backup to tape, too; why are you skipping that option ? It's very simple to set up with a COPY_ONLY ..copy. – adaptr Jan 14 '13 at 16:14
  • @adaptr I'm not "skipping that option", I mention it in the second paragraph of my answer. In the fourth paragraph, which was my reply to part 2 of Candide's comment (a request for suggested standards), was a note on the standards I have *used/seen in production*. I had already noted the existence of direct to tape in the first part of my answer, but it's not something that I myself have seen in day-to-day use and therefore I'm not qualified to comment on it as a "standard". – Rob Moir Jan 14 '13 at 17:53
  • 1
    +q. It is funny how developers (and many incompetent admins) think that a backup is a file copy. ALL larger database handling Systems integrate into an API for this as you can nto take the System down for hours AND Must take a CONSISTENT backup. SQL Server can handle really hard backups easily - you loose some UI Budget, but hey, that is to be expected. – TomTom Jan 14 '13 at 20:00
  • 1
    Thanks Rob, I feel better about the prospects of the project now. – Candide Jan 15 '13 at 08:08
1

Here is an excellent link suited for a beginner to learn about MSSQL backups:

http://msdn.microsoft.com/en-us/library/ms187510.aspx

MSSQL uses the Windows Volume Shadow Copy Service (VSS) to gain snapshot-like access to the data files; at no point should you EVER attempt to copy data files manually - it just won't work.

adaptr
  • 16,576
  • 23
  • 34
  • I did go over that page. At no point it made any recommendations for addressing production systems. IF you have experience with setting up these things, I'd rather you were more specific. – Candide Jan 14 '13 at 12:12
  • The entire guide that he linked is for production systems... – Basil Jan 14 '13 at 15:39
-1

The answer to your question is "It Depends" but I'll try to give to enough information so you can decide.

When backing up to tape, you must take into account that tapes are much slower than disk drives, this means that the BACKUP/VERIFY/RESTORE will be slow, and if there is a lot of data (terabytes) that can take many hours. You can use the native sql server method to achieve this or use some kind of 3rd party software like Idera/Redgate/CommVault/etc.

The other option is to backup to a local disk, for example: cheap SAN or a different Raid array. The key here is DIFFERENT! What you do is use the native sql server method to backup to the dedicated backup partition/lun/raid set/drive ensure you have enough storage so you can rotate the backup files. The key is to have enough free space for N+1 days you want to keep locally. Then you setup your tape backup to backup these files to tape.

The main advantage here is that the backup job runs quicker, meaning it runs for less time on you SQL Server, and since the backups are on a different storage than your SQL data and log files (MDF, NDF, LDF) files, the impact on SQL Server is virtually nothing and is a non issue.

Another important advantage is that you have the latest backup at hand and ready for a restore, that restore will be a lot faster than restoring from tape, minimizing downtime in such an event.

The way I would set it up is with the second method.

  1. Perform a daily FULL backup.
  2. Perform hourly transaction log backups
  3. Remove old backup files older than 24 or 25 hours.
  4. Perform a daily backup to tape of the location where you stored the SQL backups.

If using transaction logs, make sure your recovery models set to FULL.

If you have SQL enterprise you can even compress the backups so they take less storage space. If not, there are 3rd party tools that can compress it for you from vendors like Idera or Red-Gate. There is also a great open source project which I use named MSSQLCOMPRESSED

I hope this answers your question.

Idan
  • 309
  • 1
  • 2
  • 7
  • 2
    I wouldn't get too hung up on the part about tapes being slower than disk. LTO-4 can give a lot of cruddy RAID-5 and RAID-6 arrays a run for their money. LTO-5 is even faster. – Evan Anderson Jan 14 '13 at 19:35
  • It's all a matter of being cost-effective and what you have at your disposal. You wouldn't upgrade to LTO5 just to make your backups a bit faster. Tapes as tapes, and are good for one thing, sequential data. If you have the backup files at hand you can do other useful things like mounting then as VDB's allowing you to restore just the data without restoring the entire DB. – Idan Jan 14 '13 at 20:04
  • Funny enough, one of my Customers just upgraded from LTO-3 to LTO-5 to make backups go faster... >smile< They had no capacity problems, but they wanted the window shortened. In general, I do agree that disk-to-disk-to-tape is the right way to go today, however. – Evan Anderson Jan 14 '13 at 23:59
  • However... When going to tapes you loose the ability to use Virtual Databases. I'm sure we both know that just backing up isn't enough, you must test the restore path. Opposed to a SQL restore, VDB can and will be a life-saver. – Idan Jan 15 '13 at 04:20