Voice of the DBA

Category: Blog

  • A Full Backup Includes Everything (with a caveat)

    Full database backups in SQL Server include all of the data, objects, tables, rows, functions, stored procedures, etc. If something is in the database when the data reading portion of the backup concludes, it’s in there.

    Note that “in there” means committed in a transaction.

    If someone tells you the backup missed a row, or a procedure, or something else, they’re almost always wrong. 99.9999% of the time they are wrong, and you should stand by that.

    The issue is that things must be committed in the backup. If they aren’t committed, they aren’t included. And that means that there’s a small chance that something added to the database while the backup is running isn’t in the backup.

    If you remember how backups work, there’s a data reading portion of the backup and a log writing portion of the backup. The log writing portion of the backup takes a physical amount of time. If someone were to finish a transaction during this time, the data or objects would not be in a restored database. This is because the transaction didn’t exist or wasn’t committed when the data reading portion of the backup completed.

    If the data writing portion of the backup takes a few minutes, and a change was in the last minute or so of the process, someone might think something completed before the timestamp on the backup file is included. It wouldn’t be.

    It’s a small chance, and it’s not likely to come down to this point, but it could happen. Can you figure out the transactionally consistent time of the backup? Perhaps, but I don’t know how. You’d need to get the time for the last LSN written in the backup and map that to a time. If you know how to do that, let me know.

    Make sure that your backups are transactionally consistent. Don’t export, don’t use open file managers, don’t use anything that doesn’t respect transactions. The native SQL Server backup process does this. If you want a few other features, my employer makes SQL Backup Pro, which also respects transactions.

  • Back to iOS from Android

    I pre-ordered a Samsung Galaxy S4 last spring, after listening to my wife talk about how much happier she was with an S3 than an iPhone 4. There were a few things I thought Android was doing better for smartphones and after spending some time in the store with various devices, I went with an S4.

    I had a bit of a learning curve, but the phone worked pretty well for me. After spending a bit of time with the S4, I wrote about some impressions of Android v iOS. These were based on the way I use the phone, and how the two mobile OSes worked for me. I dove in wholeheartedly, abandoning my iPhone 4S to a desk drawer and only using the S4. I didn’t even use my iPad much.

    However a few things were annoying me about Android. I’ll write about those, but it came to me one day as I was packing up my iPad to trade it in. Target was giving me $200 (an amazing deal) and my plan was to upgrade to a Nexus 7 or other small Android tablet. I primarily use the tablet while cooking for recipies or entertainment.  As I played with a few of them in various stores, I realized that I missed the ease and smooth operation of iOS. I decided to trade up for an iPad Mini instead of an Android and realized that I like iOS better.

    The original fine print I’d gotten from my carrier noted that I couldn’t switch devices on my account for 6 months with the S4, but the day after 6 months were up, I brought my fully charged iPhone 4S into a store and switched it back as the active device on my account.

    There was definitely an adjustment as I had to change the way I did a few things. After a couple weeks back on iOS, I still look for the “back” button at times, it’s a bit slower for some tasks, and the screen feels much smaller. Overall, however, I’m happier with the iOS device.

    I’m not implying that iOS is better than Android. That’s a highly subjective choice, but given the way I use my phone  (and tablet), I prefer iOS.

    These thoughts continue with these posts:

  • SQL in the City and SQL Saturday–Washington DC

    Actually, Maryland, but close enough. I’ll be in Chevy Chase this weekend, first for our SQL in the City Seminar on Deployment Friday, and then SQL Saturday #233 on Saturday. Both events are free, so if you can come to one or both this weekend, register and come. The registration is mostly so we can plan on the number of people, so please register or cancel if you can, or can’t, come.

    Both events are free. This is a great chance to learn something new. Friday is a look at database deployment, building from Version Control to Automated Build and Testing your database, to deployment. We’re presenting some of the things we’ve learned at Red Gate from our own internal work and our customers, and people this year have really enjoyed these sessions and learned a lot.

    Saturday is a chance to learn about all sorts of technologies. From BIML to Columnstore to Indexing. There are a ton of sessions. My session on Saturday is about branding yourself, and if you want a new job, a better job, or more money, come see me at 2:00pm.

    This trip closes out my year, and I’m glad for that. It’s been an up and down year for travel, but overall, I think this was the most travel I’ve ever done in one year. I managed well, but there were a few spots when I was definitely worn out.

    I would love to say I won’t do it next year, but I’m not sure that’s the case. I really enjoy many of these events, and I’ll probably do the same number, or perhaps more, in 2014, but I’ll try to spread them out a bit more.

  • Accidently Kicking a Database into the Restoring State

    I learn new things all the time. This was one that actually stunned me. Huge props to Gail Shaw for posting a note about this in a thread.

    Run this code: 

    CREATE DATABASE MyRestoreTest
;
GO
USE MyRestoreTest
go
BACKUP DATABASE MyRestoreTest TO DISK ='myrestoretest.bak';
GO
CREATE TABLE mytable( id INT)
;
GO
USE master
go
BACKUP LOG myrestoretest TO DISK = 'myrestoretest_log.trn' WITH norecovery

    You’ll see this in your Object Explorer

    backuplog

    Ugh.

    I haven’t started a restore. I’ve run a backup. Apparently this causes problems, as noted by Gail in the thread. Sure enough, it’s documented in the BACKUP command, in the Log-specific Options.

    I had never scheduled backups with this type of option, but you might have a job that does this if you were preparing for a failover. Having a script ready it a good idea, but if it executes unexpectedly, this could happen.

    The lesson: make sure you know the options when you run a command. Always test, and if something strange happens, search or ask what might have happened.