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
The Wiert Corner - irregular stream of stuff 
Leave a comment