Solved

using BEGIN END with IF statement..

Posted on 2016-11-02
12
49 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
 
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 250 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 25

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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 25

Accepted Solution

by:
Pawan Kumar earned 250 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 25

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

861 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now