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.

35 Comments

  • 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

  • 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....

  • 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.

  • 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.

  • 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.

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

  • Awesome script, thanks!

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

  • 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.

  • 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?

  • 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?

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

  • 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.

  • Nice script.
    Just what i needed to backup / restore SQL2000 DB from one server to another.

    Regards
    Sko

  • Fantastic beat ! I would like to apprentice whilst you amend
    your web site, how can i subscribe for a weblog website?
    The account helped me a applicable deal. I were a little bit acquainted of this your broadcast provided vibrant transparent idea

  • Excellent write-up. I absolutely appreciate this website.
    Keep it up!

  • Hi there mates, how is all, and what you wish for to say concerning this post,
    in my view its actually amazing in favor of me.

  • Hello to all, how is all, I think every one is getting more from this web site, and your views are
    good for new users.

  • Hi to all, how is everything, I think every one
    is getting more from this website, and your views are fastidious
    for new visitors.

  • This is my first time visit at here and i am in fact
    pleassant to read all at one place.

  • Aw, this was an extremely good post. Taking the time and actual effort
    to make a really good article… but what can I say… I procrastinate
    a whole lot and never manage to get anything done.

  • Aw, this was an extremely good post. Taking the time and actual effort
    to make a really good article… but what can I say… I procrastinate
    a whole lot and never manage to get anything done.

  • Magnificent web site. Plenty of useful info here. I'm sending it to a few buddies ans also sharing in delicious. And certainly, thanks to your sweat!

  • I'm amazed, I must say. Seldom do I encounter a blog that's both educative and entertaining, and without a doubt, you
    have hit the nail on the head. The problem is something too few people are speaking intelligently about.
    I am very happy that I found this in my search for something concerning this.

  • What's Going down i am new to this, I stumbled upon this I've discovered
    It absolutely helpful and it has aided me out loads.
    I'm hoping to contribute & aid other customers like its aided me. Great job.

  • Couldn’t agree additional! at your succeeder

  • you are actually a just right webmaster. The site loading pace is incredible.
    It kind of feels that you are doing any unique trick.

    Moreover, The contents are masterwork. you've done a excellent job on this subject!

  • Incredible! This blog looks just like my old one!
    It's on a entirely different subject but it has pretty much the same page layout and design. Great choice of colors!

  • Do you have a spam issue on this website; I also am
    a blogger, and I was wondering your situation; many
    of us have developed some nice practices and we are looking to swap strategies with
    others, be sure to shoot me an e-mail if interested.

  • Excellent blog right here! Also your web site rather a lot up very fast!

    What host are you the use of? Can I am getting your associate link on
    your host? I desire my web site loaded up as quickly as yours
    lol

  • What a stuff of un-ambiguity and preserveness of precious familiarity on the
    topic of unpredicted emotions.

  • My brother recommended I might like this blog. He was once entirely right.
    This submit truly made my day. You cann't consider just how so much time I had spent for this information! Thanks!

  • Hi to every one, it's truly a pleasant for me to pay a visit this web page, it contains precious Information.

  • What's up to every one, the contents existing at this web site are really remarkable for people knowledge, well, keep up the nice work fellows.

  • Wonderful goods from you, man. I have understand your stuff prior to and you
    are just too fantastic. I really like what you have received here, really like what you're stating and the way by which you are saying it. You make it entertaining and you still care for to keep it smart. I can not wait to learn far more from you. This is actually a wonderful website.

Comments have been disabled for this content.