THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Eric Johnson

My Thoughts on SQL Server and Database Related Technologies.

  • Backup Compression with SQL Server 2008

    SQL Server 2008 provides a feature, which, in my opinion, has been far overdue -- backup compression. For too long, if you wanted the benefit of compressed backups, you had to look at a third-party tool. Now, backup compression is built right into SQL Server 2008, and what's even better is that it's easy to use. All you have to do is append the WITH COMPRESSION option to your backup statements and you are off to the races. This bit of code will back up the AdventureWorks2008 database using compression:

    BACKUP DATABASE AdventureWorks2008
    TO AdWorksBackup

    Using compression with SSMS is just as easy. Simply set the compression option on the Options page of the Back up Database dialog.

    You may be asking yourself: How effective is this compression? The answer can be tricky because it depends on the structure of your database and the type of data being stored. When I backed up my copy of the AdventureWorks2008 database, which is using about 700 MB of disk space, I got a 147 MB compressed backup file. Compare that to the 636 MB file I got when not using compression.

    Here's what else is cool: You can change the default compression behavior of your entire server. On the Database Settings tab of the Server Properties dialog, you can select the option Compress Backup. Alternatively, you can run the following t-SQL code:

    EXEC sp_configure 'show advanced options', 1
    EXEC sp_configure 'backup compression default', 1

    The first command enables advanced options and the second will make compression the default for all backups. If you go this route, you won't have to change a thing about your backup scripts in order to take advantage of compression. Now, just a simple BACKUP DATABASE statement will use compression. To run a backup without compression when it's the server default, simply use the WITH NO COMPRESSION option.

    SQL Server 2008 finally offers backup compression natively and I hope you find it to be a useful feature. It's great for saving disk space and you no longer have to zip backup files before moving them over the network just to improve the copy time

  • Scripting SQL Server Logins

    You ever find yourself with the need to copy SQL Server logins from one server to another? Maybe you are setting up a failover site, building a replacement server, setting up a reporting instance, or maybe you just want to backup the logins just in case. If you are using Windows Logins, this is a simple matter of scripting the login and applying it to the other server. Copying SQL Server Logins from one box to another is a bit trickier because SQL Server stores and manages the password. So just how do you copy the login and preserve the password? I am glad you asked.

    Understanding Login Components

    To successfully copy a login from one server to another, you will need to ensure that the copy has the same SID and password. The link between database users and logins is done with the logins SID, if this is different on the new server than any databases you copy over will contain orphaned users. To ensure that both the SID and the password are the same, Microsoft has written a stored procedure to aid in our transfer.


    SP_HELP_REVLOGIN is a stored procedure that will return a complete list of the logins that exists on you SQL Server in a script that can be run to recreate them. This script does not exist on your SQL Server by default, you must create with the code provided by Microsoft in KB article 918992 here Once you have create the procedures you can easily generate the create statements that allow you to copy your logins.

    As an example, I created a new login on my local instance of SQL Server called SQLScript with a password of scriptme. Now I can run SP_HELP_REVLOGIN as follows:

    sp_help_revlogin 'SQLScript'


    /* sp_help_revlogin script

    ** Generated Oct 30 2007 9:23AM on laptop1 */

    -- Login: SQLScript


    As you can see, I now have the syntax I need to recreate the login on another box. The SID will be forced to the same value and the passwords will match by virtue of this statement providing the hashed version of the password. If you need to copy all the logins, SQL Server and Windows Logins, you can run SP_HELP_REVLOGIN with no parameters.

  • SQL Server LiveLesson

    I just started working on a LiveLesson DVD for Addison-Wesley tentatively titled "SQL Server Fundamentals for the Accidental DBA". The DVD will focus on the essential skills required to work with SQL Server for people that are not full-time DBAs. I will keep you posted as I have more details. For more information about the LiveLesson videos, check out


    Co-Host CS Techcast

  • New SQL Server 2008 Install Contains Some Useful Features

    The new SQL Server installation is so different, I thought I would take a second to write about it. For starters, the whole thing is now called the SQL Server Installation Center.



    This tool is broken down into seven sections which include the following:

    • Planning - This section gives you access to documentation and allows you to run the upgrade advisor.
    • Installation - This is where the rubber meets the road. You can install stand-alone instances, clusters, add-nodes to existing clusters, and even start upgrades from 2000 or 2005.
    • Maintenance - Here you can change your edition of SQL Server (that's right - change - did you install developer edition on production by accident, here you can change to to enterprise), repair an installation, or remove a node from a cluster.
    • Tools - Access to a few handy tools such as the System Configuration Checker, a report to tell you what SQL Server features are installed, and an SSIS upgrade wizard.
    • Resources - Links to helpful documentation on SQL Server
    • Advanced - This section lets you install SQL Server based on a configuration file, perform advanced cluster prep, or complete a cluster from already cluster-prepared SQL Servers.
    • Options - Allows you to specify the architecture (x86, x64, or ia64) and the location of the install media.

    Enough about the Installation Center, let's take a look at the stand alone installation. I am not going to walk through step-by-step, I just want to highlight some of the new features that I think are pretty cool.  This is the face of the new instillation wizard.


    The first few screens are just some checks and then the installation of the prerequisites. After that's done we get to the meat of the installation. Even here, the first few screens are pretty straight forward asking if you are installing a new instance or changing an existing instance and another screen allowing you to enter you product key. The first screen that grabbed my attention was the Instance Configuration. Here you not only specify and instance name, but you an also specify the instance ID. The instance ID is used in folder naming instead of random numbers. By default the ID will be the name of your instance.


    The next few screens are your standard drive space overview and service account set up, nothing really new here. Ah, but then we get to the Database Engine Configuration screen which abounds with new options for security, file locations, and the new FILESTREAM type. The first tab, shown below, lets you set the authentication mode and set up accounts that will be administrators of SQL Server. Gone are the days of BUILTIN\Administrators and here are the days of telling SQL Server who you want to be administrators.


    The second tab, Data Directories, allows you to set up the default locations of some common files used by SQL Server. This really gives you a level of control that was previous a registry hack, done manually, or done after the installation was complete.  You can set up individual locations for the following:

    • Data Root
    • User Database Files
    • User Database Log Files
    • Temp DB Data
    • Temp DB Log
    • Backup Files


    Last but not least, the third tab allows you to enabled FILESTREAM for T-SQL access. I won't get into FILESTREAM here, but it is a feature of SQL Server 2008 worth your further investigation.

    That's the bulk of the more check to look for problems that might cause the installation to fail and your off and running.

    I know the installation wizard isn't the sexiest new thing in SQL Server 2008, but there are some neat new things that made me happy so I wanted to share my rather geeky bliss.

  • New to

    Hi everybody, I am new to so I thought I would blog a little about who I am. My name is Eric Johnson, not the guitar player, though I would like his money :) I am the co-founder of Consortio Services (, and the primary Database Technologies Consultant. My background in Information Technology is diverse, ranging from operating systems and hardware to specialized applications and development and I have even done my fair share of work on networks. Since IT is really just a way to support business processes, I also acquired my MBA in 2004. All in all, I have more than 10 years of experience with IT, a great amount of that time has been spent working with Microsoft SQL Server. In October 07 I was awarded an MVP from Microsoft in SQL Server. I am also the President of the Colorado Springs SQL Server Users Group ( In addition to all the SQL Server work, I remain a general IT geek and I co-host CS Techcast, a weekly podcast for IT pros available from I look forward to being a member of the community and I hope you find my contributions worth while.


More Posts « Previous page
Privacy Statement