DOS batch script to restore a SQL 2000 database and grant permission to ASPNET account

I've found the most reliable method of  moving SQL Server databases between servers is a simple backup / restore. The restore is a bit of a pain in the neck, though - it takes some unnecessary clicky clicky in the SQL Server Enterprise Manager (ugh).

Here's a simple DOS batch script that does away with most of the repetitive work. I put a copy of this in a \Project\Database directory, fill in the DBNAME variable, and from then on the restore is as simple as copying the SQL Backup file over and running the batch file. The granting permission to the ASPNET user thing is important if you're using trusted connections, which is a durn good idea. Barry Dorrans has some great info on that here.  

::SET VARIABLES
set DBNAME=NAMEOFDATABASEBEINGRESTORED
set DBDIRECTORY=C:\Program Files\Microsoft SQL Server\MSSQL\Data

TITLE Restoring %DBNAME% Database

::PUT DATABASE IN SINGLE USER MODE TO ALLOW RESTORE
osql -E -d master -Q "alter database %DBNAME% set single_user with rollback immediate"

::RESTORE DATABASE
osql -E -d master -Q "restore database %DBNAME% from disk='%~dp0\%DBNAME%.bak' WITH MOVE '%DBNAME%_Data' TO '%DBDIRECTORY%\%DBNAME%_Data.MDF',  MOVE '%DBNAME%_Log' TO '%DBDIRECTORY%\%DBNAME%_Log.LDF'"

::GRANT PERMISSION TO ASPNET USER
osql -E -d %DBNAME% -Q "sp_grantdbaccess '%COMPUTERNAME%\ASPNET'"
osql -E -d %DBNAME% -Q "sp_addrolemember 'db_owner', '%COMPUTERNAME%\ASPNET'"

::RESTORE TO MULTI USER
osql -E -d master -Q "alter database %DBNAME% set multi_user"

pause

Usage notes:

  1. Replace DBNAME variable with the actual database name.
  2. restore database osql command should all be on one line; it's just formatted as is to look pretty. (fixed)
  3. This assumes you're using some defaults - for instance, you're saving your SQL database backup as DBNAME.bak, your database file names are DBNAME_Data.MDF and DBNAME_Log.LDF, and your DBDIRECTORY is the default directory. If this isn't the case, it's pretty easy to change the script to suit your bizarre tastes.
  4. This is built to be run in the same directory as the backup file. If that's not the case, replace the %~dp0 silliness with the path to the backup file.
  5. You're running this on a server that can stand a SQL Server restart. I use this on my development server, so that's just fine. (fixed, see update note below)

UPDATE: Original version restarted SQL Server before performing the restore. Based on Sean's comment, I've changed this to use single user mode instead.

Published Friday, December 02, 2005 7:09 AM by Jon Galloway
Filed under: ,

Comments

# re: DOS batch script to restore a SQL 2000 database and grant permission to ASPNET account

Instead of stopping the server, it's less intrusive to boot out the users by setting the database into SingleUser mode.

-- kill all users, and rollback any pending transactions
USE Master
go
alter database <dbName> set single_user with rollback immediate
GO

-- Put it back
alter database <dbName>set multi_user

Friday, December 02, 2005 11:42 AM by Sean

# re: DOS batch script to restore a SQL 2000 database and grant permission to ASPNET account

Looks good. I always cut/paste a sql script to do this, so I'm glad you took the time to write a DOS file. This will come in handy for porting from dev to test on automated builds. Nice job....

Saturday, December 03, 2005 12:40 AM by Sean

# re: DOS batch script to restore a SQL 2000 database and grant permission to ASPNET account

I'm just a windows server system admin and trying to use this script. I modified the script to match my database location. I get this error,

C:\>osql -E -d master -Q "restore database mms_prod from disk='c:\mms_prod\mms_p

rod.bak' WITH MOVE 'mms_prod_data' TO 'c:\mms_prod\mms_prod_data.mdf',  MOVE 'mm

s_prod_log' TO 'c:\mms_prod\mms_prod_log.ldf'"

Msg 3234, Level 16, State 2, Server USA-APPSERVER, Line 1

Logical file 'mms_prod_data' is not part of database 'mms_prod'. Use RESTORE

FILELISTONLY to list the logical file names.

Msg 3013, Level 16, State 1, Server USA-APPSERVER, Line 1

RESTORE DATABASE is terminating abnormally.

What could be the problem?

Thanks

Ganesh.

Thursday, August 17, 2006 12:53 PM by Ganesh

# re: DOS batch script to restore a SQL 2000 database and grant permission to ASPNET account

It sounds like the logical name for the data file is incorrect. You can find out what that is in Enterprise Manager or SSMS by right clicking the database and checking the file name.

I would guess that it's mms_prod instead of mms_prod_data. You could try replacing that and seeing if it works.

Thursday, August 17, 2006 4:26 PM by Jon Galloway

# re: DOS batch script to restore a SQL 2000 database and grant permission to ASPNET account

You can use the RESTORE FILELISTONLY in your generic database restore script as follows:

SET FLOCMD=osql -E -Q restore filelistonly from disk='%FILE%'" -w 888

SET LPRI=

SET LLOG=

For /f "skip=2 delims=/" %%i in ('%FLOCMD%') do @CAll :GetNames "%%i"

Then do your restore with move '%LPRI%' to '%MDF%', move '%LLOG%' to '%LDF%', replace

And the :GetNam sub slooks like:

:Getnam

SET L1=%~1

If !==!%LPRI% set LPRI=%L1:~1,80%& goto :Eof

If !==!%LLOG% set LLOG=%L1:~1,80%& goto :Eof

Goto :Eof

The skip=2 skips teh first two lines from the output, the w=888 prevents too many lines, and the delms=/ is necessary because the LPRI and LLOG names may contain spaces.

Wednesday, November 15, 2006 7:32 PM by John

# re: DOS batch script to restore a SQL 2000 database and grant permission to ASPNET account

can you modify this to work with a named sql server instance please.

Thursday, February 15, 2007 2:56 AM by Saeed

# Hi nice site look my - <a href="http://seducelebrityhairstyles.wordpress.com/">Sedu Celebrity HairStyles</a>,WoW your best

Hi nice site look my - <a href="http://seducelebrityhairstyles.wordpress.com/">Sedu Celebrity HairStyles</a>

Monday, May 14, 2007 7:15 AM by Sedu Celebrity HairStyles,Sedu Celebrity HairStyles,Hi nice site look my - <a href="http://seducelebrityhairstyles.wordpress.com/">Sedu Celebrity HairStyles</a>

# re: DOS batch script to restore a SQL 2000 database and grant permission to ASPNET account

Absolutely GREAT find. Thank you for this site and thanks to the poster of the DOS logic. Exactly what I needed...

Thursday, July 19, 2007 10:11 PM by Phil

# re: DOS batch script to restore a SQL 2000 database and grant permission to ASPNET account

nice piece of script, saved me lot of time. couldnt thank you enough. :)

Thursday, July 26, 2007 9:28 AM by uttam

# re: DOS batch script to restore a SQL 2000 database and grant permission to ASPNET account

Really Good Job. I made some adds for SQLEXPRESS (and to replace the database if exists). Cheers.

::SET VARIABLES

set DBNAME=DBNAME

set DBDIRECTORY=%PROGRAMFILES%\Microsoft SQL Server\MSSQL.1\MSSQL\Data

TITLE Restoring %DBNAME% Database

::PUT DATABASE IN SINGLE USER MODE TO ALLOW RESTORE

osql -S localhost\SQLEXPRESS -E -d master -Q "alter database %DBNAME% set single_user with rollback immediate"

::RESTORE DATABASE

osql -S localhost\SQLEXPRESS -E -d master -Q "restore database %DBNAME% from disk='%~dp0\%DBNAME%.bak' WITH MOVE '%DBNAME%' TO '%DBDIRECTORY%\%DBNAME%.MDF',  MOVE '%DBNAME%_Log' TO '%DBDIRECTORY%\%DBNAME%_Log.LDF', REPLACE "

::GRANT PERMISSION TO ASPNET USER

osql -S localhost\SQLEXPRESS -E -d %DBNAME% -Q "sp_grantdbaccess '%COMPUTERNAME%\ASPNET'"

osql -S localhost\SQLEXPRESS -E -d %DBNAME% -Q "sp_addrolemember 'db_owner', '%COMPUTERNAME%\ASPNET'"

::RESTORE TO MULTI USER

osql -S localhost\SQLEXPRESS -E -d master -Q "alter database %DBNAME% set multi_user"

PAUSE

exit

Wednesday, August 08, 2007 11:54 AM by Pablo Heredia

# re: DOS batch script to restore a SQL 2000 database and grant permission to ASPNET account

Awesome script, thanks!

Thursday, August 23, 2007 1:23 PM by Daniel Flippance

# re: DOS batch script to restore a SQL 2000 database and grant permission to ASPNET account

how  can I combime the restore filelistonly of the dos restore script?

Tuesday, March 04, 2008 1:29 AM by Jeboy

# re: DOS batch script to restore a SQL 2000 database and grant permission to ASPNET account

Thanks, this entry helped me write a systematic script to backup, copy, and restore a number of databases from an old, to-be-retired server to the new device.

Saturday, March 29, 2008 8:27 AM by Fred

# re: DOS batch script to restore a SQL 2000 database and grant permission to ASPNET account

When I try to put the master Database in Single-User Mode I get the message:

Option 'SINGLE_USER' cannot be set in database 'MASTER'

How can I RESTORE the MASTER Database with a Batch script?

Wednesday, May 21, 2008 10:01 AM by André Fampa

# re: DOS batch script to restore a SQL 2000 database and grant permission to ASPNET account

To be awkward I'd like to restore a database backed up from database1 over one called database2 and the restored over one still be called database2.

Is that possible?

Friday, May 23, 2008 8:10 AM by RR

# re: DOS batch script to restore a SQL 2000 database and grant permission to ASPNET account

Thanks for your post! - This came in real handy for keeping my production db copied to another backup server for running reports/dr purposes.

Friday, October 10, 2008 7:25 PM by Slade Hornick

# re: DOS batch script to restore a SQL 2000 database and grant permission to ASPNET account

Great examples.  Thank you.  

Monday, November 03, 2008 8:19 AM by Tony

# re: DOS batch script to restore a SQL 2000 database and grant permission to ASPNET account

Hi,

I need to be able to do the following:

1) put a database in single user mode

2) restore the LATEST backup from a directory which contains many .bak's

3) put database back into multi users mode

Can anyone help please. Thanks.

Monday, April 20, 2009 4:37 AM by John

# how to backup and restore sql 2005 data base with bat file - RarityGuide.com Forums

Pingback from  how to backup and restore sql 2005 data base with bat file - RarityGuide.com Forums

Thursday, December 03, 2009 2:24 AM by how to backup and restore sql 2005 data base with bat file - RarityGuide.com Forums

Leave a Comment

(required) 
(required) 
(optional)
(required)