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?

Improve this question

3 Answers 3

Reset to default
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.

Improve this answer
7
  • 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?
    – Roland Mai
    Jan 14, 2013 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, 2013 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, 2013 at 15:36
  • 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, 2013 at 20:00
  • 1
    Thanks Rob, I feel better about the prospects of the project now.
    – Roland Mai
    Jan 15, 2013 at 8: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.

Improve this answer
2
  • 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.
    – Roland Mai
    Jan 14, 2013 at 12:12
  • The entire guide that he linked is for production systems...
    – Basil
    Jan 14, 2013 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.

Improve this answer
4
  • 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, 2013 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, 2013 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, 2013 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, 2013 at 4:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .