5

I am trying to automate a process of detaching and dropping a database (via a VBS objshell.run) If I manually use SSMS to detach and drop I can then copy to database files to another location... however if I use:

sqlcmd -U sa -P MyPassword -S (local) -Q "ALTER DATABASE MyDB set single_user With rollback IMMEDIATE"

then

sqlcmd -U sa -P MyPassword -S (local) -Q "DROP DATABASE MyDB"

It detaches/drops and then deletes the files. How do I get the detach and drop without the delete?

Qazxswe
  • 139
  • 1
  • 4
  • 10

3 Answers3

8

The MSDN Documentation on DROP DATABASE has this to say about dropping the database without deleting the files (under General Remarks):

Dropping a database deletes the database from an instance of SQL Server and deletes the physical disk files used by the database. If the database or any one of its files is offline when it is dropped, the disk files are not deleted. These files can be deleted manually by using Windows Explorer. To remove a database from the current server without deleting the files from the file system, use sp_detach_db.

So in order for you to drop the database without having the files get deleted with sqlcmd you can change it to do this:

sqlcmd -U sa -P MyPassword -S (local) -Q "EXEC sp_detach_db 'MyDB', 'true'"

DISCLAIMER: I have honestly never used sqlcmd before but assuming from the syntax of how it's used I believe this should help you with your problem.

John Odom
  • 1,189
  • 2
  • 20
  • 35
3

Use SET OFFLINE instead of SET SINGLE_USER

ALTER DATABASE [DonaldTrump] SET OFFLINE WITH ROLLBACK IMMEDIATE; DROP DATABASE [DonaldTrump];
Aurel Havetta
  • 455
  • 4
  • 9
0

Might it be best to detach the database rather than drop it? If you drop the database, that implies delete

Note, however, that this will leave your hard disk cluttered with database files you no longer want - in a couple of years time your successor will be running out of space and wondering why the disk is full of MDF files he doesn't recognise

Jack Whittaker
  • 1
  • This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. – Johan Jun 07 '19 at 15:06