Voice of the DBA

Author: way0utwest

  • Two Types of Tail Log Backups

    In a recent thread I noted that a tail log backup is essentially a regular log backup, but made with the intention of restoring the database because something is wrong with your data file. Gail Shaw (blog | @SQLIntheWild) pointed out that that’s not quite true. There are two parameters that you need to add to the BACKUP LOG command. Thanks to Gail for the correction, and here’s a little more data.

    There are three options you have with a tail log backup are:

    • WITH NORECOVERY
    • WITH CONTINUE_AFTER_ERROR
    • WITH NO_TRUNCATE

    I covered the third one in doing some practice backing up of the tail log. The second one is noted in Books Online as one that you should use in the event that the database is offline and inaccessible. That will allow you to recover the last log backup (hopefully).

    The first one is recommended as the one you use when the database is going to be restored and you want the end of the log.

    As I mentioned in my previous post, this is a core DBA skill. It is what will allow you to recover a database with zero data loss.

  • #2 Skill – Performing a Restore

    This series of blog posts are related to my presentation, The Top Ten Skills You Need, which is scheduled for a few deliveries in 2011.

    You Will Restore a Database

    At some point you’ll need to restore a database. It might be a database on your local instance of SQL Server to correct a problem with a query or a patch, but you’ll need to restore data.

    This goes along with the first skill of backing up a database as the counterpart. A backup saves the data (and objects) and a restore brings that data back.

    Restores are fairly easy, but there are a couple of things you need to learn right away:

    Always use NORECOVERY

    By default the RESTORE command brings a database online by going through the recovery (redo and undo) processes. For a full database restore, this means you cannot restore additional logs. You might not to this time, but at some point you will.

    So always use WITH NORECOVERY.

    You need this in database mirroring, log shipping, and more scenarios. Always include this in your restore commands. To bring the database online when you are sure you are done restoring (even if this is only one restore), use the RESTORE command and WITH RECOVERY, as in:

    RESTORE DATABASE db1 WITH RECOVERY

    Learn to move files

    I find that many restores take place for practice, or on servers other than the original ones. In that case, the paths might not exist. Often the production servers, or the main servers you use, will have more drives than the test servers. In that case, having a file stored on the z: drive for a server doesn’t match up with a development server containing only a C: drive.

    The WITH MOVE option is used to move the existing logical files in your restore to a new location. Here is the sample command from Books Online.

    Script Restores

    It’s easy to make mistakes with the GUI in SSMS. Learn to script restores and run the scripts. Even if you use SSMS to setup the restore, don’t click OK. Instead click this:

    scriptrestore

  • The Window Is Shrinking

    Security is important, but perhaps doubly so in the cloud.

    There have been a number of issues with Dropbox and their encryption process for files stored on their systems. This highlights some of the issues with cloud services, as I’ve talked about as well. I use Dropbox, but for any files that have identity information, I encrypt them locally and only store the encrypted versions.

    There have been quite a few issues with cloud-type services related to security, and at this point, I think it’s good. The press about the Sony hacks, the RSA issues, and others should be scaring consumers and management in companies into demanding better security from vendors. Without a strong emphasis on security from clients, cloud vendors have no reason to spend more effort on security than they do now. I am actually hoping that insurance doesn’t cover the Sony issues, which will help force companies to consider purchasing insurance specifically for security issues. That will force insurance companies to demand better security as well.

    That means the window for throwing together a service without a well thought out security plan is shrinking, and that’s good. We should have security on the mind as we write code. Building that habit takes training, but it also takes practice and requires management to buy into the need to spend some time implementing security throughout our code, and testing for potential issues.

    I look forward to the time when strong security exists in all applications, not bolted on as an afterthought, but designed in from the very beginning.

    Steve Jones

    The Voice of the DBA Podcasts

  • Hardware Hacking

    How safe are our chips?

    One of the reasons that we have so many gadgets at low costs, with a regular re-fresh and re-tool of the internals is that many of our computing chips and circuit boards are built overseas in factories that are optimized for low costs and rapid evolution to new technologies. That works fine for consumers, but it presents a problem for a government concerned with security.

    Since the fabrication plants are under the control of a foreign government, that means that the military and governments that want to use the same chips that consumers use must vet the designs, and then the end product. This piece talks about the problems with the supply chain that can come from foreign suppliers that might have incentives to allow hardware hacks into the designs at the request of their own governments.

    The world continues to grow closer economically, but we still have our own national patriotism and loyalty . We want our own countries to grow and do well, and our companies to grow inside of our own borders. As the stakes raise, will foreign supplies think about introducing backdoors or other hardware hacks into more of our chips? Imagine the problems we might have if our x64 chips contained a way for foreign companies to access our data?

    Criminals certainly would love these hardware hacks and as they get more and more sophisticated, I wouldn’t be surprised if this becomes an attack vector in the future. We might find in the future that we will not only have choices between Intel and AMD, but also choices between domestic produced and foreign produced chips.

    Steve Jones

     

    The Voice of the DBA Podcasts