image Anyone who's ever developed a web application in .NET has had to play with a database management system, most probably SQL Server or its free cousin, SQL Server Express.  One of the tasks I personally hate doing with our SQL Server Express 2005 databases is restoring them from a backup, using SQL Management Studio.  We sometimes restore the point of sale database used by our customers to track down various issues or to build reports using their data as our test set. The process is not that long when you restore a backup from your own machine (restoring the MDF and LDF files to their original directory). If you restore databases from foreign systems, the process is simple only if both systems stored their databases in the same directory, which is rarely the case.

For example, I use Windows Vista x64 and our dedicated server uses a 32-bit version of Windows 2003.  Our data is stored in the default SQL Server directory, which is in the Program Files folder.  However, when using a 64-bit operating system, the program files directory is different (C:\Program Files (x86)).  Since the location of the MDF and LDF files are encoded directly in the bak file generated by SQL Server, restoring them via the command line is especially challenging when you don't control the original locations of the MDF and LDF files, nor their Logical Names.

Our goal is to be able to restore a database by executing a simple command such as this:

restore.bat LavaBlast

This command would look for LavaBlast.bak in the current directory and would restore the LavaBlast database to a default location on your computer where you want to store your MDF and LDF files.

Here is the code for restore.bat:

sqlcmd -S .\SQLEXPRESS -i attachDB.sql -v database="%1" -v root="%CD%"

We are simply calling sqlcmd (added to our path) to connect to our local instance of SQL Server Express and we are executing an SQL file (attachDB.sql) which includes two variables: database and root (the current path).

Here is the code for attachDB.sql:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[$(database)]') AND type in (N'U'))
create table #backupInformation (LogicalName varchar(100),
PhysicalName varchar(100),
Type varchar(1),
FileGroupName varchar(50) ,
Size bigint ,
MaxSize bigint,
FileId int,
CreateLSN int,
DropLSN int,
UniqueId uniqueidentifier,
ReadOnlyLSN int,
ReadWriteLSN int,
BackupSizeInBytes int,
SourceBlockSize int,
FileGroupId int,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN bigint,
DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit, IsPresent bit )
insert into #backupInformation exec('restore filelistonly from disk = ''$(root)\$(database).bak''')
DECLARE @logicalNameD varchar(255);
DECLARE @logicalNameL varchar(255);
select top 1 @logicalNameD = LogicalName from #backupInformation where Type = 'D';
select top 1 @logicalNameL = LogicalName from #backupInformation where Type = 'L';
DROP TABLE #backupInformation 
FROM DISK = '$(root)\$(database).bak'
MOVE @logicalNameD TO 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\$(database).mdf',
MOVE @logicalNameL TO 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\$(database).ldf'

Simply put, we are extracting the logical names (and other metadata) from the .bak file into a temporary table. We then use those values to restore the MDF and LDF to the correct location, instead of the ones specified in the .bak file.

If you want to use this script, simply ensure you change the location of your SQL Server data files (the last lines in the SQL file) and you should be good to go. Please note that in its current form, the script only supports files with one MDF and one LDF file in the database backup. Furthermore, it assumes your .bak file has the same name as the database you want to import. We could also enhance the script by automatically adding permissions to the ASP.NET user after restoring the database. Feel free to post any enhancements you make in this post's comments and I hope you'll find this script useful! Enjoy.

kick it on