Search code examples
sqlsql-server-2008sqlcmd

Drop DB but don't delete *.mdf / *.ldf

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?

Solution

  • 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.