The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 2,805 other followers

SQL Server 2005 script to restore .BAK file to a database in a specific directory

Posted by jpluimers on 2010/07/13

Recently I needed a way to automate some testing on SQL Server 2005.
The easiest way was to start with restoring some predetermined database state from a .BAK file.
Hence I wrote the script below.

Next to SQLCMD, this script uses SubInACL, which you can download here (don’t worry that version seems to be only for Windows 2000..2003: it still works in Windows 7).

Microsoft does not have much documentation (except the built-in by calling “SubInACL /help /full”.
But this SubInACL page has some more insight on how to use it.

Somehow SQL Server is picky about the rights it has on the .BAK file (and the directory it is in), but not on the location of the .MDF and .LDF files.
So I use SubInACL to make sure that the user “NETWORK SERVICE” has full access to the directory where the .BAK file is in.

Here is the script:

rem input parameters: %basename% (for database, datafile and logfile), %sourcedirectory% (for bakfile), %targetdirectory% (for datafile and logfile), %servername%
rem examples:
rem set servername=.\SQLEXPRESS
rem set targetdirectory=C:\dat-and-log-files\
rem set sourcedirectory=C:\bak-files\

set databasename=%basename%
set bakname=%sourcedirectory%%basename%\%basename%.bak
set dataname=%basename%
set logname=%basename%_log
set datafilename=%targetdirectory%%dataname%.mdf
set logfilename=%targetdirectory%%logname%.ldf

rem make sure that the user "NT AUTHORITY\NETWORK SERVICE" has rights in the directory where the .BAK file is:
SubInACL /subdirectories %sourcedirectory%\*.* /grant="NETWORK SERVICE"=F

"%ProgramFiles%\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S .\SQLEXPRESS -E -Q "RESTORE DATABASE [%databasename%] FROM DISK = N'%bakname%' WITH REPLACE, STATS = 10, MOVE N'%dataname%' to N'%datafilename%', MOVE N'%logname%' to N'%logfilename%' ;"

Basically the SQL is this:

RESTORE DATABASE [%databasename%] FROM DISK = N'%bakname%' WITH REPLACE, STATS = 10, MOVE N'%dataname%' to N'%datafilename%', MOVE N'%logname%' to N'%logfilename%' ;

Hope this helps a few people.

–jeroen

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
%d bloggers like this: