Menu
I am experimenting and learning with Microsoft SQL Server 2008 R2 SP1. I have a database where I made many experiments. Now I would like to drop and recreate it. So I extract the creation script from database, I delete it and I use the script to recreate it. To my surprise, all the tables, keys etc are still there. How do I drop the database, so that I can rebuild the database from scratch?
657
Using SQL Server Management Studio In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance. Expand Databases, right-click the database to delete, and then click Delete. Confirm the correct database is selected, and then click OK.
If you are experienced with SQL Server commands, you can try the commands listed below to restore the deleted SQL database: DBCC CHECKDB (DATABASE_NAME): Check the entire database for repair in command line or query. REPAIR_ALLOW_DATA_LOSS: Repair data by allocating and deallocating rows.
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.
USE master IF EXISTS(select * from sys.databases where name='yourDBname') DROP DATABASE yourDBname CREATE DATABASE yourDBname +1 to AnandPhadke for his part of the code
This code will close all active connections to the database and then drop it
WHILE EXISTS(select NULL from sys.databases where name='YourDBName') BEGIN DECLARE @SQL varchar(max) SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';' FROM MASTER..SysProcesses WHERE DBId = DB_ID(N'YourDBName') AND SPId <> @@SPId EXEC(@SQL) DROP DATABASE [YourDBName] END GO CREATE DATABASE YourDBName GO If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
