?
Solved

using BEGIN END with IF statement..

Posted on 2016-11-02
12
Medium Priority
?
78 Views
Last Modified: 2016-11-02
how can the below logic be made to work? (the goal is to delete a database only if it exists.. then this will be part of automated code.. the need for SINGLE_USER logic is to make sure the database is not in use when tried to be deleted..)

USE MASTER

IF
(
SELECT COUNT(*) FROM sys.databases WHERE name = 'db1'
)
=1
BEGIN
USE db1
ALTER DATABASE db1 SET SINGLE_USER WITH  ROLLBACK IMMEDIATE
USE master
DROP  DATABASE db1  
END

GO
0
Comment
Question by:25112
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 18

Expert Comment

by:Anil Golamari
ID: 41870394
Below code can be used to drop the database.
USE MASTER

IF  EXISTS 
(SELECT name FROM master.dbo.sysdatabases WHERE name = 'Sample')
BEGIN
USE Sample
ALTER DATABASE Sample SET SINGLE_USER WITH  ROLLBACK IMMEDIATE
USE master
DROP  DATABASE Sample 
END
ELSE
BEGIN
	SELECT 'database does not exist'
END 

Open in new window

0
 
LVL 5

Author Comment

by:25112
ID: 41870460
anil, your code produces this error:

Msg 911, Level 16, State 1, Line 6
Database 'Sample' does not exist. Make sure that the name is entered correctly.
0
 
LVL 18

Expert Comment

by:Anil Golamari
ID: 41870468
I have created a database 'Sample' on my local and then used above code to delete the Sample database. Now if I run above code on my machine I get same error message. So please try to replace the database name 'Sample' with you test database name.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 5

Author Comment

by:25112
ID: 41870488
if database does not exist, should not this line be run:
SELECT 'database does not exist' (line #13)
0
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 1000 total points
ID: 41870567
You don't need those nested USE statements - you don't need to switch context to run the ALTER DATABASE statement:
USE MASTER

IF  EXISTS 
(SELECT name FROM master.dbo.sysdatabases WHERE name = 'Sample')
BEGIN
        ALTER DATABASE Sample SET SINGLE_USER WITH  ROLLBACK IMMEDIATE
        DROP  DATABASE Sample 
END
ELSE
BEGIN
	SELECT 'database does not exist'
END 

Open in new window

0
 
LVL 38

Expert Comment

by:Pawan Kumar
ID: 41870598
Try..

USE MASTER
GO

IF  EXISTS ( SELECT TOP 1 1 FROM MASTER.DBO.SYSDATABASES WHERE name = 'db1')
BEGIN
               
		ALTER DATABASE db1 SET SINGLE_USER WITH  ROLLBACK IMMEDIATE		
		DROP  DATABASE db1 
END

Open in new window

0
 
LVL 5

Author Comment

by:25112
ID: 41870611
>>
        ALTER DATABASE Sample SET SINGLE_USER WITH  ROLLBACK IMMEDIATE            
ALTER DATABASE db1 SET SINGLE_USER WITH  ROLLBACK IMMEDIATE      
>>

if you do SINGLE_USER without using
[USE SAMPLE]
prior,
another process could take hold of database, right? then the drop wont work??
0
 
LVL 38

Accepted Solution

by:
Pawan Kumar earned 1000 total points
ID: 41870624
Are you on a prod server ?

Note - Please make sure that if you are on prod server then alter database should be used very very carefully.

No I think  If you SINGLE_USER  then other user cannot take hold of the DB.
0
 
LVL 5

Author Comment

by:25112
ID: 41870768
this is test server..

---
USE db1
ALTER DATABASE db1 SET SINGLE_USER WITH  ROLLBACK IMMEDIATE
---
will make sure you are the only user In the database..
then if you have the next line as
USE MASTER,
the database is likely to be ready to be dropped, if that is the process..

but, the below
---
USE master
ALTER DATABASE db1 SET SINGLE_USER WITH  ROLLBACK IMMEDIATE
---
how can that guarantee that all process has been kicked out of the database.. only one user can be in the database and it may not be you, because you are in master database, in this code segment.. not db1?
0
 
LVL 5

Author Comment

by:25112
ID: 41870770
>> you don't need to switch context to run the ALTER DATABASE statement:
carl, the context is not on ALTER, as much as keep the database free of all processes to be able to be dropped..
0
 
LVL 38

Expert Comment

by:Pawan Kumar
ID: 41870772
Hi 25112,
Whats the update?

Is it done?
0
 
LVL 5

Author Comment

by:25112
ID: 41870818
confirmed. thanks.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Screencast - Getting to Know the Pipeline

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question