Voice of the DBA

Tag: sql server

  • Striping Backups in SQL Backup Pro

    SQL_68x68_BackupThere are times you really do want to stripe backups across multiple files. If you have a short backup window, or a large data set, if you can write the backup to multiple files at the same time, you can drastically reduce your backup window. I think I heard one person say they had a client that was backing up a 2TB database in around 30 minutes with striping. Now that’s cooking with gas!

    My company, Red Gate Software, makes a nice backup product called SQL Backup Pro, which offers a number of features, compression and encryption being the two most notable ones. However I noticed at one point that it supported striping as well, so I decided to play with it.

    I started the backups, picking a database and then getting to the backup file screen.

    sqlbackup1

    There’s a drop down in the upper left, which gives you a few choices. One of these is the backup and mirror and the other is a backup to multiple files.

    sqlbackup2

    I selected the “Split backup into multiple files”, which equates to striping, and then added a file to the file dialog.

    sqlbackup3

    However I wanted to add a second file. I clicked “Add” again.

    sqlbackup4

    It’s nice that the names are changed, but adding a second file to the same path isn’t likely to give me much more throughput on the backup. Both files would write to the same I/O device. I was thinking that “Add” might give me a dialog for a different drive/path, but it didn’t.

    Instead I needed to click on the ellipsis next to the folder (I’ve added the arrow in the image below):

    sqlbackup5

    This brought me to a dialog that shows me the drives my machine can see. I didn’t test UNC paths, but I assume any valid path would work. You can set a path, or you can pick one, and you can even create a new folder. Handy since this could be a remote machine. The SQL Backup Pro client is like SSMS, it’s a client view to a remote service.

    sqlbackup6

    I select the D: drive on my machine, which is actually a separate physical drive.

    sqlbackup7

    I get back to the main dialog, and this time when I click “Add”, I get the desired result. A second file on a different path.

    sqlbackup8

    I run the backup, and sure enough, I have files in two places, each a little over 1MB. There’s some overhead, so it’s not a completely even stripe.

    sqlbackup10

    sqlbackup11

    As a comparison, I’ve highlighted a regular backup I made after this, same db, with one file:

    sqlbackup9

    That seems a little cumbersome, but I’d anticipate that you would only set this up once for each set up databases, and since you can easily script a SQL Backup set of commands (on the last screen) or create template, this shouldn’t be too hard to manage. In fact, the command for multiple databases does support striping as well, as in this example:

    EXECUTE master..sqlbackup ‘-SQL “BACKUP DATABASES [AdventureWorks,AdventureWorks_SSC,AdventureWorks2008R2]

    TO DISK = ”F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\<AUTO>_2.sqb”,

    DISK = ”C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\<AUTO>_1.sqb”

    WITH DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10″‘

    Since striping is usually done with only very large databases, this might not even be necessary. Most databases won’t need striping, and you can set a template for them that backs up to whatever location you wish.

    SQL Backup Pro has some nice features, and allows you to easily manage lots of backups across lots of servers. The compression ratios are nice and it has some great features, like this striping of backups, that can really improve your backup performance.

  • Measurements

    Measurements are important to growth.

    “If you cannot measure it, you cannot improve it” – Lord Kelvin.

    That’s a quote that comes from this blog post, talking about ways in which you might measure your performance. Numbers are not the only important thing to measure, and you certainly don’t want to just pick one measure. The blog post talks about performance of employees, looking at different ways to not only measure the performance of one job, but also using different measures for different jobs.

    The idea of measurement is applicable to both people and systems. Having a constant way to measure performance, track those measurements, and perform analysis of the data is an important way to know if you are actually making strides toward building better performance and more efficient systems.

    However it seems that even though many people know these things, they don’t actually capture the measurements regularly. As I talk to people, asking them if they know things like the backup growth for their critical databases, they don’t. I’ve asked people if they know how much time they spend on blogging, or if they are reaching more people, or really for any metrics, and they often don’t have any idea.

    Numbers aren’t everything, but they ought to be a thumb-in-the-air measurement that guides you to find ways to improve over time. You can be on the schedule that works in your environment, and on the pace to improve things that fits the situation, but if you don’t measure yourself or your systems regularly, it’s hard to know if you’re actually moving forward or backward.

    Steve Jones

    The Voice of the DBA Podcasts

  • Recovering a Database

    So you’re restored a database, restored a few logs, all with NORECOVERY as expected and realize there are no more logs. You see this in Management Studio and wonder what do to:

    restoringdb

    I’ve run a bunch of code, restoring lots of files, but I’m done.

    RESTORE DATABASE db4 FROM DISK = 'db4_base.bak' WITH norecovery
RESTORE LOG db4 FROM DISK = 'db4_log1.trn' WITH norecovery
RESTORE LOG db4 FROM DISK = 'db4_log2.trn' WITH norecovery
RESTORE LOG db4 FROM DISK = 'db4_log3.trn' WITH norecovery
RESTORE LOG db4 FROM DISK = 'db4_log4.trn' WITH norecovery
....
RESTORE LOG db4 FROM DISK = 'db4_log42.trn' WITH norecovery

    You don’t need another log to bring things online. This simple command will fix things:

    RESTORE DATABASE db4 WITH recovery

    That will return:

    RESTORE DATABASE successfully processed 0 pages in 2.629 seconds (0.000 MB/sec).

    and your database will be ready to go:

    retoreddb

  • The Top Ten Skills You Need

    One of the things I’ve found is that so many people struggle to get started with SQL Server. There are regular questions about how to learn to work with SQL Server, so I built this quick talk that should help you get started.

    This talk is built on the 80/20 rule, the idea that 80% of your job needs 20% of the DBA skills, and vice versa. So these are a series of skills, presented quickly, that should get you through most of the things that you need to deal with in SQL Server as an administrator.

    Developers will find that spending a little time learning these skills will also let them easily setup a SQL Server instance that can maintain itself for the most part without them. And it will protect them in the event there are issues.

    The talk moves quickly, but there are lots of links and references in the deck.

    Slides

    The slides are on my SkyDrive.

    Length

    This talk is built for 60-75 minutes

    Related Blogs

    These are the blog posts related to this talk: