Moving the SQL Server Installation to a Different Drive

Following fishing regulations is very important.  We as a society are called to be responsible stewards of our natural resources, and that includes fish.  Overfishing, poaching, and spreading invasive species can all decimate a lake’s fish population, ruining it for everyone else.  I was disheartened to see a news article this week about a man caught with over 2,500 panfish in his freezer.  The legal limit is 50 per species, so he would have been allowed to possess 150 fish.  Hopefully the story of his guilt will dissuade other poachers, but given his rather light sentence, I doubt that will be the case.

I recently needed to install SQL Server Analysis Services (SSAS) on our test server to begin experimenting with it.  However, the C drive, where SQL Server was installed, had only a few hundred MBs of space left.  When installing SSAS on the existing instance of SQL Server, you are forced to use the same drive and I didn’t have enough space.  I decided to move the existing installation from the C drive to the D drive, which had plenty of available space.

There isn’t any way to move the existing installation, so I was forced to uninstall SQL Server on the C drive, then install it on the D drive.  Here are the steps I followed:

  1. Take a backup of all the databases, just in case.  This is always a good first step when making any significant changes to your environment.
  2. Run the Uninstall Wizard through Windows Control Panel to remove all SQL Server components.
  3. Reinstall SQL Server on the D drive.  I found I had to use an actual iso to do the install rather than the extracted contents of the iso.  When I tried to use the extracted contents I kept running into errors about missing msi files.
  4. Apply any service pack and patches to the installation so it is at least at the same version as the uninstalled instance.  If you skip this step you will not be able to restore/attach any of your existing databases to the new instance.
  5. At this point I expected to be able to move my existing master database file into the new default data folder, but I found my existing master database file had disappeared!  The uninstall must have deleted it.
  6. Instead, I started up SQL Server with the -m parameter in the SQL Server Configuration Manager’s SQL Server Advanced Properties.  This causes SQL Server to start up in single user mode, and only the master database comes online.
  7. Now restore the last backup of the master database:
    C:\> sqlcmd  
    1> RESTORE DATABASE master FROM DISK = 'Z:\SQLServerBackups\master.bak' WITH REPLACE;  
    2> GO
  8. When the restore is complete the service will stop.  Remove the -m parameter and start SQL back up.
  9. At this point everything came up as expected.  There were a few cleanup tasks to complete before I was finished:
  • Reconfigure Reporting Services.
    • During the install I had chosen to install but not configure so that I could plug into my existing SSRS databases.
  • Configure Powershell
    • The msdb.dbo.syssubsystems table contains information about Powershell that SQL Server uses when executing a PS script.  Mine was pointing to a subsystem dll and agent exe that were in the old installation location.  I updated this directly in the table with an UPDATE statement.

Once complete, SQL was ready to use, I had SSAS installed, and I opened up an additional 3 GB of hard drive space on the C drive, relieving the fear of crashing the OS.

Advertisements

Restore latest backup and all transaction logs

Last weekend was the 2015 Rhinelander Hodag Muskie Challenge, the first fishing tournament I’ve ever competed in. It was a cool experience, and although we didn’t record a fish, I plan to keep trying. My team included myself and my brother-in-law, Jason. We came up two days early to prefish the lakes and try to identify a pattern that would work once the tournament started. Jason caught two fish during the tournament, a 42″ on Friday and a huge 48″ on Thursday. The bigger one was an enormous fish, and neither words nor the picture below really do it justice.

Jason's 48

It was one of those things where you just had to be their understand the real size of the fish and the excitement of the moment. Unfortunately, once the tournament started on Friday morning we only managed to get follows, no biters. The winning team ended up landing three fish, including two legal (40″+) ones. Congrats to them, that is a great accomplishment. It was cool to see how the weather really affected the fishing. We were faced with high pressure on Saturday and Sunday, and by Sunday it really appeared to have slowed down the fish. There were about 30 fish caught on Saturday during the 10 hours of fishing and only 6 on Sunday during the 5 hours of fishing. 36 fish between 80 teams isn’t that many, and it helps to realize that even some of the best muskie fishermen out there struggle under tough conditions.

I recently set up a HA passive node for our production 2008 R2 SQL Server. It is recommended by Microsoft to only mirror at most 10 databases per server because the mirror process takes up processor threads. I mirrored our 10 most important databases, but in the event of an outage we would probably want some of the other databases to be current on the backup server as well. So I created a script to create the restore process for any other databases. This script finds the latest full backup and each transaction log backup after that full backup. It is important to note that each restore except the last should be WITH NORECOVERY while the last should be WITH RECOVERY. I prefer very frequent TLOG backups for minimum data loss, so the potentially large number of transaction logs to restore makes the script necessary to get the latest version of the databases without spending a ton of time setting up the restores. The script will place all the restore statements in the correct order in the RestoreStatements field, and the database_name field is just used for sorting and can be disregarded.  This script also requires that you restore msdb to the mirror and will only generate backup statements for tlog backups taken before the last msdb backup.  It will also work best if the backups are stored on a network location that is accessible from both the principal and mirrored servers.

WITH    latestfullbackups
          AS ( SELECT   bu.database_name ,
                        bu.backup_set_id ,
                        bmf.physical_device_name ,
                        bu.backup_start_date ,
                        ROW_NUMBER() OVER ( PARTITION BY bu.database_name ORDER BY bu.backup_set_id DESC ) rn
               FROM     msdb.dbo.backupset bu
                        INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bu.media_set_id
               WHERE    bu.database_name NOT IN ( 'master', 'msdb', 'model','anymirroreddbs')
                        AND bmf.device_type  7
                        AND bu.TYPE = 'D'
             ),
        alltlogsafterlastfull
          AS ( SELECT   bu.database_name ,
                        bu.backup_set_id ,
                        bmf.physical_device_name ,
                        ROW_NUMBER() OVER ( PARTITION BY bu.database_name ORDER BY bu.backup_set_id DESC ) rn
               FROM     msdb.dbo.backupset bu
                        INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bu.media_set_id
                        INNER JOIN latestfullbackups ON latestfullbackups.database_name = bu.database_name
               WHERE    bmf.device_type  7
                        AND bu.TYPE = 'L'
                        AND bu.backup_start_date > latestfullbackups.backup_start_date
                        AND latestfullbackups.rn = 1
             )
    --Latest full backup
    SELECT  'RESTORE DATABASE [' + latestfullbackups.database_name
            + '] FROM DISK = N''' + latestfullbackups.physical_device_name
            + ''' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5;'
            + CHAR(10) + 'GO' AS RestoreStatements ,
            latestfullbackups.database_name
    FROM    msdb.dbo.backupset bs
            INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
            INNER JOIN latestfullbackups ON latestfullbackups.backup_set_id = bs.backup_set_id
                                            AND latestfullbackups.database_name = bs.database_name
                                            AND latestfullbackups.rn = 1
    UNION
    
   --All tlog backups after latest full except the last one
    SELECT  'RESTORE LOG [' + alltlogsafterlastfull.database_name
            + '] FROM DISK = N''' + alltlogsafterlastfull.physical_device_name
            + ''' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5;'
            + CHAR(10) + 'GO' ,
            alltlogsafterlastfull.database_name
    FROM    alltlogsafterlastfull
    WHERE   alltlogsafterlastfull.rn  1
    UNION

   --Last tlog backup - recover the database
    SELECT  'RESTORE LOG [' + alltlogsafterlastfull.database_name
            + '] FROM DISK = N''' + alltlogsafterlastfull.physical_device_name
            + ''' WITH FILE = 1, RECOVERY, NOUNLOAD, REPLACE, STATS = 5;'
            + CHAR(10) + 'GO' ,
            alltlogsafterlastfull.database_name
    FROM    alltlogsafterlastfull
    WHERE   alltlogsafterlastfull.rn = 1
    ORDER BY 2 ,
            1

SQL Server Upgrade/Migration

Last week I was feeling burnt out. We’d been having numerous issues pop up at work and it was taking its toll on me. I felt constantly mentally fatigued and knew I needed some time to myself. My wife could sense I needed some relaxation, so she took the kids to her aunt’s house and let me have some solo time. I considered going down to Pewaukee to try and catch some muskies, but the water temp is peaking over 80 degrees, which is the max you should fish for muskies. Any warmer than that and they are unable to properly recover from the fight and could end up dying, even if it isn’t for a couple hours after they are released. Besides, I needed to just relax, and while muskie fishing is an awesome sport it really isn’t very relaxing. So I headed over to Little Cedar Lake to shoot for some walleye, largemouth bass, or northern pike. While I only managed one medium sized bass, it was great to just sit back and sip a few beers while enjoying the lake air and beautiful scenery. Since that night I have felt much more refreshed and ready to get back to work.


Evening Fishing
We have been doing some server upgrade/migrations the last several weeks. We purchased a new server and needed to move our production instance to it. We don’t have any kind of high availability, and were able to tolerate a system outage of four or less hours. The first thing I did was to practice extensively using our Test environment. I did the cutover in Test several times. I was confident going into the upgrade that I would be able to complete it successfully.  This upgrade was slightly more complicated in that in involved moving some databases to different drives during the upgrade process.  Not only was I going to move the system files from the C: drive to the data file and log file drives, but I also needed to move tempdb from the G: to the F: drive and move data and log files into differently named folders on the D: and E: drives.
I used the following procedure list to perform the upgrade:

  1. Stop the application services.  This will close existing connections to the user databases.
  2. Stop the data warehouse transfer process.  We use a third party tool to move data from our Production SQL Server instance to our Oracle data warehouse.
  3. Take full copy-only backups of each user database.  After taking each backup, take the database offline to prevent new connections and changing data.
  4. Take full backups of all system databases.  It is important to do them after the user databases so that msdb has records of the latest user database backups.
  5. Move all system database using SQL Script below.
  6. Create and restore all user databases using SQL Script demonstrated in other blog post.
  7. Shutdown new and old server.  System Admin changes the name of the old server to whatever, then changes the name of the new server to what the old server used to be.  He also updates the IP of the new to be what the old was.  This way we don’t have to change any application settings.
  8. Bring up Reporting Services on new server.  I will explain the intricacies of this step in a later post.
  9. Begin testing everything – application, SSIS, SSRS, and SQL Agent jobs.

Here are the SQL Scripts used to make the changes:

USE [master]
RESTORE DATABASE [model] FROM  DISK = N'\\BackupLocation\OldServer SQL Backups\SQLBackupFiles\OldServer\model\FULL\OldServer_model_FULL_20150724_201857.bak' 
WITH  FILE = 1,  
--MOVE N'modeldev' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\model.mdf',  
--MOVE N'modellog' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\modellog.ldf',  
NOUNLOAD,  REPLACE,  STATS = 5

GO

ALTER DATABASE model MODIFY FILE (NAME = 'modeldev', FILENAME = 'D:\DataFiles\model.mdf')
ALTER DATABASE model MODIFY FILE (NAME = 'modellog', FILENAME = 'E:\LogFiles\modellog.ldf')

GO

--Stop SQL Server Agent
USE [master]
RESTORE DATABASE [msdb] FROM  DISK = N'\\BackupLocation\OldServer SQL Backups\SQLBackupFiles\OldServer\msdb\FULL\OldServer_msdb_FULL_20150724_201858.bak' 
WITH  FILE = 1,  
--MOVE N'MSDBData' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf',  
--MOVE N'MSDBLog' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\MSDBLog.ldf',  
NOUNLOAD,  REPLACE,  STATS = 5

GO

ALTER DATABASE [msdb] MODIFY FILE (NAME = 'MSDBData', FILENAME = 'D:\DataFiles\MSDBData.mdf')
ALTER DATABASE [msdb] MODIFY FILE (NAME = 'MSDBLog', FILENAME = 'E:\LogFiles\MSDBLog.ldf')

GO
--Restart the SQL Service

--Stop the service
--Add -m to SQL Server startup parameters to start in single user mode
--Open command prompt
--sqlcmd
--Start the service
RESTORE DATABASE [master] 
FROM DISK = N'\\BackupLocation\OldServer SQL Backups\SQLBackupFiles\OldServer\master\FULL\OldServer_master_FULL_20150724_201856.bak' WITH REPLACE;

--Remove -m from SQL Server startup parameter
--Open command prompt
--Go to the SQL Server directory (on this machine, C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn)
--run SQLServr.exe -T3608 -T3609
--		This will just open Master database.
--Run SQLCMD from a different command prompt and run these commands
ALTER DATABASE model MODIFY FILE (NAME = 'modeldev', FILENAME = 'D:\DataFiles\model.mdf')
ALTER DATABASE model MODIFY FILE (NAME = 'modellog', FILENAME = 'E:\LogFiles\modellog.ldf')
ALTER DATABASE msdb MODIFY FILE (NAME = 'MSDBData', FILENAME = 'D:\DataFiles\MSDBData.mdf')
ALTER DATABASE msdb MODIFY FILE (NAME = 'MSDBLog', FILENAME = 'E:\LogFiles\MSDBLog.ldf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev01', FILENAME = 'F:\TempDB\tempdb.mdf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev02', FILENAME = 'F:\TempDB\tempdb2.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev03', FILENAME = 'F:\TempDB\tempdb3.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev04', FILENAME = 'F:\TempDB\tempdb4.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev05', FILENAME = 'F:\TempDB\tempdb5.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev06', FILENAME = 'F:\TempDB\tempdb6.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev07', FILENAME = 'F:\TempDB\tempdb7.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev08', FILENAME = 'F:\TempDB\tempdb8.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev09', FILENAME = 'F:\TempDB\tempdb9.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev10', FILENAME = 'F:\TempDB\tempdb10.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev11', FILENAME = 'F:\TempDB\tempdb11.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev12', FILENAME = 'F:\TempDB\tempdb12.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev13', FILENAME = 'F:\TempDB\tempdb13.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev14', FILENAME = 'F:\TempDB\tempdb14.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev15', FILENAME = 'F:\TempDB\tempdb15.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev16', FILENAME = 'F:\TempDB\tempdb16.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', FILENAME = 'F:\TempDB\templog.ldf')

--End SQL Service, then start the service normallyy.
--At this point all the system databases should be transfered to New Server.

As I said, the migration went pretty smoothly.  I did have a few hiccups that should have been tested weeks before the migration, but I missed them.  I was able to get them working within a day of the migration, and next time I’ll get them right away.  The first was the database mail, which took me an extra hour to get set up right after the migration.  The second was a linked server.  This was a bit tougher because I had to install the Oracle ODBC driver, and this required a SQL Server reboot.

Script to Restore Latest Full Backup with Move Files

Last weekend I met a couple buddies for some early morning muskie fishing at Pewaukee Lake. We arrived at about 6:15 AM, and there was a steady but not hard rain falling. It was dead calm and about 70 degrees out. I consider this fairly pleasant weather to fish in. I’d rather be rained on than baked by the sun on a hot, humid, windless day. In the first half hour of our trip we managed one follow-up, a 33 – 35 inch muskie that followed to the boat before turning around. Then at about 6:45 AM, we had a durastic weather change. The wind picked up to 20 – 25 mph and the temperature dropped about 15 degrees. The changed happened so abruptly we could actually see it blowing across the lake. I’ve never seen a weather change like that before. Fishing under a cold front is tough, and we didn’t see any more fish for the rest of the day.
As a DBA, using scripts that create TSQL is a great way to make large changes quickly. In practicing my database migration, I ran into a situation where, after restoring the system databases on the new server, all other databases are left in a Restore Pending status. This is because the master database has records of the user databases, but they haven’t been restored or moved yet. Since this is just practice, I don’t actually want to move the user database files, so instead I’ll just restore the latest full backup of each. However, I have several user databases to restore, and I don’t want to write out each script or run through the RESTORE wizard for each one. So I created the following tsql script that will create the restore script for each user database. One complication was that the folder structure on the new server was different than on the old server. I used the first common table expression to get all the full backups of each user database. I use the ROW_NUMBER window function to make sure I get the latest backup first. I used the second table expression to create the MOVE part of the script for each database file. I had to put them in a common delimited list (as explained here).

WITH    latestbackups
          AS ( SELECT   bu.database_name ,
                        bu.backup_set_id ,
                        bmf.physical_device_name ,
                        ROW_NUMBER() OVER ( PARTITION BY bu.database_name ORDER BY bu.backup_set_id DESC ) rn
               FROM     msdb.dbo.backupset bu
                        INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bu.media_set_id
               WHERE    bu.database_name NOT IN ( 'master', 'msdb', 'model' )
                        AND bmf.device_type <> 7
                        AND bu.TYPE = 'D'
             ),
        movefiles
          AS ( SELECT   database_id ,
                myText = STUFF(( SELECT ', ' + name
                                FROM   ( SELECT DISTINCT
                                                CASE
                                                    WHEN TYPE = 1
                                                    THEN ' MOVE N''' + name + ''' TO N''E:\LogFiles\' + RIGHT(physical_name, CHARINDEX('\', REVERSE(physical_name), 1) - 1) + ''''
                                                    ELSE ' MOVE N''' + name + ''' TO N''D:\DataFiles\' + RIGHT(physical_name, CHARINDEX('\', REVERSE(physical_name), 1) - 1) + ''''
                                                END name
                                        FROM      sys.master_files b
                                        WHERE     a.database_id = b.database_id
                                    ) x
                            FOR
                                XML PATH('')
                                ), 1, 2, '')
               FROM     sys.master_files a
               GROUP BY a.database_id
             )
    SELECT  'RESTORE DATABASE [' + latestbackups.database_name
            + '] FROM DISK = N''' + latestbackups.physical_device_name
            + ''' WITH FILE = 1, ' + movefiles.myText
            + ', NOUNLOAD, REPLACE, STATS = 5;' + CHAR(10) + 'GO' 
    FROM    msdb.dbo.backupset bs
            INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
            INNER JOIN latestbackups ON latestbackups.backup_set_id = bs.backup_set_id
                                        AND latestbackups.database_name = bs.database_name
                                        AND latestbackups.rn = 1
            INNER JOIN movefiles ON DB_NAME(movefiles.database_id) = latestbackups.database_name; 

Here are the results:

20150610 Restore Latest Full Backup With Move Script Results

Using this script I was able to significantly reduce time to set up my RESTORES and can now restore all the user databases with the latest FULL backup whenever I want with little effort.

Rebuilding System Databases

Sometimes in fishing you really want a do-over. Last weekend I was fishing a lake up in Onieda County for muskies (my favorite!). As we drifted over a shallow finger that extended into deep water, I hooked up with a solid muskie about 10 – 15 feet away from the boat. It appeared out of nowhere, and just grabbed the bait and held it. Not feeling a strong strike, I wasn’t even aware the fish was there until I felt some weight and looked over to see it. Then I made a series of mistakes that cost me a catch. First, I didn’t give a sharp hookset. I put a lot of weight against the fish by pulling hard, but did not drive the hooks in with a sharp tug. The second mistake was made a couple weeks before. I was using the lure, a shallowraider, when I caught my opening-day muskie, and I never re-sharpened the hooks. Due to the hard boney mouth of the muskie, it is incredibly important to keep your hooks razor sharp. Third, I didn’t stay in control of the fish. As he approached the boat straight on, I had nowhere to go. I also became distracted as I could feel him coming up for a jump. The fish leapt out of the water and shook his head viscously. When he landed back in the water, the lure popped out of his mouth and the fish was lost. I got a great look at him and he was definitely around 40 – 41″, a good sized muskie. Losing that fish will continue to gnaw at me until my next encounter with a muskie.
DBA’s can also really want a do-over. As I have been practicing my cutover for the server migration, I have made a lot of mistakes. My last one was to try and restore the Production server’s master database to the new server. This wouldn’t usually be a problem except the drive letters are different on the new server, as is the path of the system databases. By restoring the mater database, I ran into a situation where I was not able to get the service to come all the way back up. My only option is to start over fresh. I’m going to try to rebuild the system database. I use the instructions from Microsoft here.
First I Mount the SQL Server 2008 R2 installation media.
20150529 System Database Rebuild
Next I run this command in an elevanted command prompt.
20150529 System Database Rebuild Cmd
Upon completion there is no message in the command prompt.
20150529 System Database Rebuild Cmd Completed
Lastly, I am able to start the service.
20150529 System Database Rebuild Service Back Up
Use this only in extreme cases or in test environments as it will wipe out all the data in each of the system databases. The last step would be to restore each database from a backup, if possible.

Backup size and time – database total space versus used space

There is a rather rough rule that fisherman follow when looking for trophy fish. The rule goes like this: the bigger the lake, the bigger the fish. As I stated, this is a rough rule, and there are lots of exceptions, but in many cases it proves to be true. There is a very small lake here in southeastern Wisconsin called Random lake. This lake holds muskie, but very few, if any, above the 40″ mark. Contrast that with Green Bay, a huge amount of water where many 50″+ muskie are caught every year. Much of this is the results of the forage available and the density of the target fish, but either way, if I’m looking to catch a true trophy, I’m heading to large lakes.
At work I came across a situation that made me wonder about backup sizes. I had inherited a ~100 GB database, and after some initial benchmarking of the system, I decided to compress some of the largest tables. I ended up reducing the amount of used space down by almost 53 gigabytes, which reduced the space needed and increased performance (less I/O). However, I now have these large data files (this database has 10 files) that are about 2/3rds empty. I wondered to myself whether having large files, even if they are mostly empty, increases the backup or the restore time of the database. I decided to run a quick test on my Sandbox machine to see if there was any real difference.
The database started out with an initial size of 0.4 GB.

20150109 Backup Restore Size Small DB

I used this script to do the backup. The script backups up the database and also does a test restore to ensure the backup is usable. This is an important step in any backup strategy.

BACKUP DATABASE [Sandbox] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLSANDBOX\MSSQL\Backup\Sandbox.bak' WITH NOFORMAT, NOINIT,  NAME = N'Sandbox-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
DECLARE @backupSetId AS INT
SELECT  @backupSetId = position
FROM    msdb..backupset
WHERE   database_name = N'Sandbox'
        AND backup_set_id = ( SELECT    MAX(backup_set_id)
                              FROM      msdb..backupset
                              WHERE     database_name = N'Sandbox'
                            )
IF @backupSetId IS NULL
    BEGIN
        RAISERROR(N'Verify failed. Backup information for database ''Sandbox'' not found.', 16, 1)
    END
RESTORE VERIFYONLY FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLSANDBOX\MSSQL\Backup\Sandbox.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO

20150109 Backup Restore Size Small Backup

The backup took 16 seconds. Here is the backup in the directory. You can see the backup size is 373 MB.

20150109 Backup Restore Size Small Backup Size

Lastly, I’ll do a restore to check how long that takes. You can see in the screenshot below it took 23 seconds.

USE [master]
RESTORE DATABASE [Sandbox]   FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLSANDBOX\MSSQL\Backup\Sandbox.bak' WITH  FILE = 1,  
                           NOUNLOAD,  REPLACE,  STATS = 5

GO

20150109 Backup Restore Size Small Restore

Next I grow the database file. Since I’m using SQL Server Express, I’ll grow it to its max size, 10 GB.

20150109 Backup Restore Size Large DB

I use the same backup script and it now takes 17 seconds. This is a very slight difference considering the database file grew by 2400%. We’ll call it a negligible difference

20150109 Backup Restore Size Large Backup

The size of the backup has also grown by a very small amount, up to 383 MB.

20150109 Backup Restore Size Large Backup Size

The last thing to check is the restore time. This time the restore took 23 seconds. This is in line with what we saw from the restore with the small size database file.

20150109 Backup Restore Size Large Restore

So this experiment has shown that data file size makes a tiny, if any at all, difference in the amount of time to backup and restore and the size of the backups. Shrinking database data files almost always causes high fragmentation in the database, so unless you absolutely need more disk space on your drive, there is really no reason to shrink the database files, even if they have a lot of free space.