That is expected and documented behavior:
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 why are you taking your database offline first? Just set it into SINGLE_USER
mode and then drop it as documented on SQL Server Books Online.-
USE master;
ALTER DATABASE [databasename] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [databasename] ;
Note, database backups will not be deleted as part of the process documented above.
Lets take your questions one by one :
Is it possible to do the following:Have a copy of the database on the same SQL Server Instance
Yes it is possible. You just have to restore the database main-db.bak
with move
.
e.g. Provided you dont have secondary data files, below will work
-- find logical names
restore filelistonly from disk = 'path\main-db.bak'
go
restore database main_db_copy
from disk = 'path\main-db.bak'
with recovery, stats = 10,
-- below logical data and log file names will be found from the filelistonly mentioned above.
move 'logicalDataFileName' to 'PhysicalPath\main-db_data.mdf',
move 'logicalLogFileName' to 'PhysicalPath\main-db_log.ldf'
Create a new empty database
No need to first create an empty database, as the restore with move
will create it.
Restore the database from main-db.bak file and not overrite anything else that may be using the same data MDF and LDF files?
Same as above. As restore with move
will not over write unless you use restore with REPLACE
explicitly to overwrite.
Or would it be easier to reproduce this database from a live running database copy so that it is not needed to restore if from backup?
Cannot understand what you mean by this ! If you want to create a new database or copy of the current database , you have to take a backup and then use restore database
to restore it.
Is it mandatory to rename the LDF and MDF files when the database is restored, can I script to automate the majority of the work?
If you want to overwrite the existing one, then no. If not and you want to create a copy of existing one, then Yes - you have to rename the mdf and ldf files.
You can fully automate the restore process. Some examples can be found here and here.
Best Answer
Let's say you have a database named wildlife, kick the users off and detach the database.
This can be done via the SSMS GUI, right click, under all tasks.
-- Kick off users, roll back current work
ALTER DATABASE [WILDLIFE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
-- Detach database
sp_detach_db 'WILDLIFE'
GO
However, the *.mdf and *.ldf files are still there. Shift delete them so that they do not go to the recycle bin.
Good luck!
PS: Check out my blog for more information.
http://craftydba.com/?p=1753