Jon Galloway

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.

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.

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

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

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

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.

Great examples.  Thank you.  

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.

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

Nice script.

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

Regards

Sko

@John :Microsoft and OSQL: "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use the feature. Use sqlcmd instead. For more information, see sqlcmd Utility." (msdn.microsoft.com/.../ms162806%28v=sql.90%29.aspx)

But sqlcmd has an other result using  -w 888

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!

It's a pity you don't have a donate button! I'd definitely donate to this excellent blog! I suppose for now i'll settle for bookmarking

and adding your RSS feed to my Google account.

I look forward to fresh updates and will share this blog with my Facebook group.

Talk soon!

Attractive section of content. I just stumbled upon your website and in accession capital

to assert that I acquire in fact enjoyed account your

blog posts. Any way I'll be subscribing to your feeds and even I achievement you access consistently rapidly.

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.

I'm really enjoying the design and layout of your blog. It's

a very easy on the eyes which makes it much more enjoyable for me to come here and visit more often.

Did you hire out a designer to create your theme? Exceptional work!

Hey there, You've done a fantastic job. I'll definitely digg it and personally recommend to my friends.

I'm sure they'll be benefited from this site.

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.

I like the valuable information you provide in your articles.

I'll bookmark your blog and check again here regularly. I'm quite sure I'll learn lots of new stuff right here! Best of luck for the next!

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.