SQLCMD database exists

Ms / Ms/e6550
January 26, 2009 at 23:50:46
Specs: DOS, Intel DUO

I am creating a dos based batch file for database backup & Restore.

Here I need to make the database to be "dbo use only" from the CLI
So before restoring I need to execute the following command

EXEC sp_dboption '%DB-NAME%','dbo use only','TRUE'

But here the issue is: If database already exists this line not makes any error.
Else if database was been dropped. Then it produces the following error:

Msg 15010, Level 16, State 1, Server MT-SERVER123, Procedure sp_dboption, Line 64
The database 'testDatabase' does not exist. Use sp_helpdb to show available databases.

Available databases:

So here I need to check whether a database exist and need to run the command:
EXEC sp_dboption '%DB-NAME%','dbo use only','TRUE'

This is the Script:
set UN=%1
set PWD=%2
set SER=%3
set DB=%4
sqlcmd -U %UN% -P %PWD% -S %SER% -Q "EXEC sp_dboption '%DB%','dbo use only','TRUE'"
sqlcmd -U %UN% -P %PWD% -S %SER% -Q "RESTORE DATABASE %DB% FROM DISK='%DB%.bak'"

Please help me out in this issue

See More: SQLCMD database exists

Report •

January 27, 2009 at 03:11:03
The solution is just outlined in the error message

Use sp_helpdb to show available databases.

I don't know your database application, but issuing the reported command before restoring seems to show a list of available DBs that can be filtered to be checked, so

...sp_helpdb | find /I "%DB-NAME%" > nul
if not errorlevel 1 (
EXEC sp_dboption '%DB-NAME%','dbo use only','TRUE'

Report •
Related Solutions

Ask Question