LavaBlast Software Blog

Help your franchise business get to the next level.
AddThis Feed Button

SQL Server - Restore a database backup via the command line

clock October 14, 2008 12:45 by author EtienneT

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:

USE MASTER
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[$(database)]') AND type in (N'U'))
  ALTER DATABASE $(database) SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
 
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 
 
RESTORE DATABASE $(database)
FROM DISK = '$(root)\$(database).bak'
WITH REPLACE,
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'
GO

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 DotNetKicks.com


Scripting an ASP.NET installation in Win2k3

clock March 27, 2008 08:26 by author JKealey

A month ago, I posted an article on a few console commands for managing ASP.NET applications and IIS. In the weeks that followed, I was contacted by my old friends at iWeb Technologies to help them automate their ASP.NET setup. I spent a couple hours creating the required scripts and I thought I'd post my real world example here!

By the way, iWeb is an exceptional web hoster (1TB of space, unmetered traffic, unlimited domains, $3 a month, and excellent technical support; what more could a web developer want?). I've been with them for nine years already!

Scripting the creation of a new website and configuring it for ASP.NET

Task: Given a domain name (lavablast.com) and a network/local path (\\fileserver\hosting\www.lavablast.com\web\ or c:\inetpub\wwwroot\www.lavablast.com\web\), setup IIS so that ASP.NET works on the root of the domain.

  1. Create an application pool
  2. Create a web site and associate it to the application pool
  3. Ensure both that the www subdomain works (www.lavablast.com and lavablast.com should load this website)
  4. Enable ASP.NET v2.0 on this website.
  5. Give ASP.NET read/write access to the folder.
  6. Add default.aspx to the default documents.

 

I ended up using the ADSUTIL.VBS script for most of these tasks. I used the iisweb command to create the web application, but it doesn't support network paths. I create it using a temp local path and end up using adsutil to change it to a network path. 

My googling skills are what made this task so short. Here are some of my references:

Finally, the IISBatchUtils collection of scripts provided the most help. Here's why. 

I have created batch files to make adding new websites to the server very quick and easy. The only tricky part about setting up new websites from batch file (or the command line) is that Microsoft's ADSUTL utility does not correctly add host headers like it says it does- rather than appending the new headers, it blindly sticks them in and possibly covers existing host headers that might already be set up.

I used both the original adsutil, Josh's modified adsutil, and some of his code to extract the site id from the IIS Metabase by using the site name.

The script

See the actual script for the variable definitions.

:Create
REM Step 1 - Create Application Pool
CSCRIPT //nologo %IWEB_ADSUTIL% CREATE w3svc/AppPools/%IWEB_DOMAIN% IIsApplicationPool
 
REM Step 2 - Create WebServer
iisweb /create %TEMP% %IWEB_DOMAIN% /d %IWEB_DOMAIN% /ap %IWEB_DOMAIN%
 
REM Step 3- Find new SiteID for further scripting. 
cscript //nologo iisbatchutils/translate.js "%IWEB_DOMAIN%" > siteid.txt
for /f %%I in (siteid.txt) do SET IWEB_SITEID=%%I
 
REM Step 4 - Add www. to site URL - uses their own custom adsutil because of bug in the normal one. 
cscript %IWEB_ADSUTIL2% append w3svc/%IWEB_SITEID%/serverbindings ":80:www.%IWEB_DOMAIN%"
 
REM Step 5 - set various website permissions. 
cscript //nologo %IWEB_ADSUTIL% set w3svc/%IWEB_SITEID%/accessread "true"
cscript //nologo %IWEB_ADSUTIL% set w3svc/%IWEB_SITEID%/accesswrite "true"
cscript //nologo %IWEB_ADSUTIL% set w3svc/%IWEB_SITEID%/root/AppFriendlyName %IWEB_DOMAIN%
cscript //nologo %IWEB_ADSUTIL% set w3svc/%IWEB_SITEID%/root/path %IWEB_Path%
cscript //nologo %IWEB_ADSUTIL% set w3svc/%IWEB_SITEID%/root/DefaultDoc Default.htm,Default.asp,index.htm,Default.aspx,index.asp,index.html
 
REM Step 6 - Cleanup
del siteid.txt
 
GOTO End

 

What about setting up IIS to use ASP.NET v2.0?

I did find a link showing me how to change the ASP.NET version from v1.1 to v2.0 using regiis, but later discovered that this stopped & restarted all websites... something catastrophic to do in a production environment. All the hosted websites (not only the new one) would lose their session state, for example. Fortunately, I found that you can change the root website and any new websites created afterwards will inherit the default ASP.NET version. Run the following once:

@echo off
echo WARNING: THIS WILL CHANGE THE DEFAULT ASP.NET VERSION FOR ALL NEW SITES TO V2.0
pause
 
REM will propagate to new sites. 
%windir%\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis -sn W3SVC/
 
REM does not propagate
REM cscript %IWEB_ADSUTIL% set w3svc/accessread "true"
REM cscript %IWEB_ADSUTIL% set w3svc/accesswrite "true"

 

Scripting the deletion of a website

Deletion is much simpler, as you can see below.

 

:Delete
iisweb /delete %IWEB_DOMAIN%
CSCRIPT //nologo %IWEB_ADSUTIL% DELETE w3svc/AppPools/%IWEB_DOMAIN%
 
GOTO End

Conclusion

I had a fun time perfecting my scripting skills while creating a concrete example that solves someone's problem. I discovered that the devil is in the details, but that numerous people have worked on similar problems in the past. I ended up creating another script which runs this one numerous times, according to actions found in a local text file. Simply put, iWeb's PHP system appends operations to a file (create this site, delete that one, create this other site) and mine performs the operation and empties the task list. That way, the script can be run periodically and all the PHP coders need to do is append to a particular file.  

Download the code.

kick it on DotNetKicks.com



Common console commands for the typical ASP.NET developer

clock February 25, 2008 14:19 by author JKealey

iis As an ASP.NET web developer, there are a few tasks that I must perform often for which I am glad to be able to perform via the command line. GUIs are great, but there are some things that are simply faster to do via the command line. Although we do have Cygwin installed to enhance our tool belt with commands like grep, there are a few ASP.NET related commands that I wanted to share with you today. Some of these are more useful on Windows 2003 server (because you can run multiple worker processes), but I hope you will find them useful.

1) Restarting IIS

The iisreset command can be used to restart IIS easily from the command line. Self-explanatory.

Attempting stop...
Internet services successfully stopped
Attempting start...
Internet services successfully restarted

2) Listing all ASP.NET worker processes

You can use tasklist to get the running worker processes.

tasklist /FI "IMAGENAME eq w3wp.exe"

Image Name PID Session Name Session# Mem Usage
========================================================================
w3wp.exe 129504 Console 0 40,728 K

You can also use the following command if you have Cygwin installed (easier to remember)

 

tasklist | grep w3wp.exe

 

w3wp.exe 4456 Console 0 54,004 K
w3wp.exe 5144 Console 0 101,736 K
w3wp.exe 2912 Console 0 108,684 K
w3wp.exe 3212 Console 0 136,060 K
w3wp.exe 852 Console 0 133,616 K
w3wp.exe 352 Console 0 6,228 K
w3wp.exe 1556 Console 0 155,264 K
w3wp.exe 3480 Console 0 6,272 K

3) Associating a process ID with a particular application pool

Should you want to monitor memory usage for a particular worker process, the results shown above are not very useful. Use the iisapp command.

W3WP.exe PID: 4456 AppPoolId: .NET 1.1
W3WP.exe PID: 5144 AppPoolId: CustomerA
W3WP.exe PID: 2912 AppPoolId: CustomerB
W3WP.exe PID: 3212 AppPoolId: Blog
W3WP.exe PID: 852 AppPoolId: LavaBlast
W3WP.exe PID: 352 AppPoolId: CustomerC
W3WP.exe PID: 1556 AppPoolId: CustomerD
W3WP.exe PID: 3480 AppPoolId: DefaultAppPool

By using iisapp in conjunction with tasklist, you can know which task is your target for taskkill.

4) Creating a virtual directory

When new developers checkout your code for the first time (or when you upgrade your machine), you don’t want to spend hours configuring IIS. You could back up the metabase and restore it later on, but we simply use iisvdir. Assuming your root IIS has good default configuration settings for your project, you can create a virtual directory like so:

iisvdir /create “Default Web Site” franchiseblast c:\work\lavablast\franchiseblast\

 

5) Finding which folder contains the desired log files.

IIS saves its log files in %WINDOWS%\System32\LogFiles, but it creates a different subdirectory for each web application. Use iisweb /query to figure out which folder to go check out.

Connecting to server ...Done.
Site Name (Metabase Path) Status IP Port Host
==============================================================================

Default Web Site (W3SVC/1) STARTED ALL 80 N/A
port85 (W3SVC/858114812) STARTED ALL 85 N/A

6) Many more commands…

Take a peek at the following articles for more command-line tools that might be useful in your context:

http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/b8721f32-696b-4439-9140-7061933afa4b.mspx?mfr=true

http://www.tech-faq.com/using-iis-command-line-utilities-to-manage-iis.shtml

Conclusion

There are numerous command line tools distributed by Microsoft that help you manage your ASP.NET website. Obviously, the commands listed here are the tip of the iceberg! Although many developers know about these commands because they had to memorize them for some test, many are not even aware of their existence. Personally, I feel that if you write a single script that sets up IIS as you need it to develop, you’ll save time setting up new developers or when you re-install your operating system. Script it once and reap the rewards down the road.

kick it on DotNetKicks.com  



Month List

Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2017

Sign in