Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

While working on making an automated Delete and Restore script for my backups I ran into a snag. I can not delete the database; however, the delete button works exactly how I need it to. Can anyone tell me the script for that delete button?

SQL Delete button

I have also tried another query to do this task, however it is not working like I would like. Here is the code:

> USE CENTRAL
ALTER DATABASE DATABASE REMOVE FILE DATABASE_1.LDF
ALTER DATABASE DATABASE REMOVE FILE DATABASE_2.LDF
ALTER DATABASE DATABASE REMOVE FILE DATABASE.MDF
ALTER DATABASE DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE DATABASE SET OFFLINE
EXEC sp_detach_db DATABASE, true;
DROP DATABASE DATABASE
GO

The error that I get when I run the code is:

Executed as user: NT AUTHORITY\SYSTEM. Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102)  Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102)  Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102).  The step failed.

I am not sure what I am doing wrong, can someone please tell me?

share|improve this question

2 Answers 2

/****** Object:  Database [DatabaseName] Script Date: 03/06/2014 10:32:06 AM ******/
DROP DATABASE [DatabaseName]
GO

However, you should probably just use the RESTORE DATABASE command and use the REPLACE option.

REPLACE Specifies that SQL Server should create the specified database and its related files even if another database already exists with the same name. In such a case, the existing database is deleted.

For example:

RESTORE DATABASE [DatabaseName] FROM  DISK = N'D:\SQL\BackupName.bak' WITH  FILE = 1,  MOVE N'KERP' TO N'D:\SQL\BackupName.mdf',  MOVE N'KERP_log' TO N'L:\SQL\BackupName_log.ldf',  NOUNLOAD,  REPLACE,  STATS = 5
GO
share|improve this answer
    
I have tried using the Restore Database command. It is not working. that is why I am trying to work around it. I am sorry I should have said that. I apologize I am new to SQL. –  Derron R Mar 6 at 15:48
    
I get the following error:Executed as user: NT AUTHORITY\SYSTEM. To restore the master database, the server must be running in single-user mode. For information on starting in single-user mode, see "How to: Start an Instance of SQL Server (sqlservr.exe)" in Books Online. [SQLSTATE 42000] (Error 3108) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed. –  Derron R Mar 6 at 15:55
1  
I modified my code above. See if that works better for you. If you have problems, reply with the statement you're trying to run. Verify your file names and locations by using EXECUTE sp_helpfile –  drumatix Mar 6 at 16:14
    
HOLD UP!!! @DerronR If you note in your error it states "master database". You cannot just drop or restore that database. You have to start SQL Server service (database engine) up in single-user mode. You are not really trying to do this in an automated fashion are you? –  Shawn Melton Mar 7 at 6:23
    
Yes I am trying to set up an automatic restore. It was working before some programs were installed on the machine, however the scheduled backup no long successfully backs up the server. Every back up Fails. –  Derron R Mar 10 at 15:26

If you click on the 'Script' in the execute box that pops up from the delete button, it reveals that the script actually uses a DROP command:

USE [DATABASE]
GO
/****** Object:  Table [dbo].[TABLE]    Script Date: 3/6/2014 9:29:39 AM ******/
DROP TABLE [dbo].[TABLE]
GO
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.