15:17
Creating altering and dropping a database - Part 2
kudvenkat
Overview
This video explains how to create, alter (rename), and drop databases in SQL Server, covering both graphical methods using SQL Server Management Studio (SSMS) and query-based approaches. It details the underlying MDF (data) and LDF (log) files created for each database and discusses the importance of managing database usage, especially when dropping databases, by setting them to single-user mode with rollback options. The video also highlights that system databases cannot be dropped.
How was this?
Save this permanently with flashcards, quizzes, and AI chat
Chapters
- Databases can be created graphically in SSMS by right-clicking the 'Databases' folder, selecting 'New Database', and entering a name.
- Databases can also be created using the `CREATE DATABASE` SQL query, followed by the desired database name.
- Every database, whether system-defined or user-created, consists of two core files: a Master Data File (.mdf) for storing data and a Transaction Log File (.ldf) for recovery purposes.
Understanding how to create databases is fundamental for organizing and storing application data. Knowing about the MDF and LDF files provides insight into how data is persisted and protected.
Creating a database named 'sample1' graphically and another named 'sample2' using the `CREATE DATABASE sample2` query.
- User-defined databases (like 'sample1', 'sample2') are created by the user, while system databases (like 'master', 'model', 'msdb', 'tempdb') are essential for SQL Server's operation.
- The .mdf file is the primary data file that holds the actual data.
- The .ldf file is the transaction log file, crucial for database recovery in case of failures or errors.
Knowing the purpose of MDF and LDF files helps in understanding database storage, backup, and recovery strategies, which are critical for data integrity and availability.
Locating the 'sample1.mdf' and 'sample1_log.ldf' files in the SQL Server data directory after creating the 'sample1' database.
- Databases can be renamed graphically in SSMS by right-clicking the database and selecting 'Rename', similar to renaming files in Windows.
- Renaming can also be done using the `ALTER DATABASE` command with the `MODIFY NAME` option: `ALTER DATABASE old_name MODIFY NAME = new_name`.
- An alternative query method uses the system stored procedure `sp_renameDB`, passing the old and new database names as parameters: `sp_renameDB 'old_name', 'new_name'`.
The ability to rename databases is important for maintaining logical organization and clarity as projects evolve or naming conventions change.
Changing the name of 'sample2' to 'sample3' using `ALTER DATABASE sample2 MODIFY NAME = sample3` and then renaming 'sample3' to 'sample4' using `sp_renameDB 'sample3', 'sample4'`.
- Databases can be dropped graphically by right-clicking the database in SSMS and selecting 'Delete'.
- The `DROP DATABASE` SQL command is used to delete a database: `DROP DATABASE database_name`.
- Dropping a database also deletes its associated .mdf and .ldf files.
- System databases (like 'master') cannot be dropped as they are essential for SQL Server functionality.
Knowing how to drop databases is necessary for cleaning up unused databases, freeing up resources, and managing storage effectively.
Dropping the 'sample1' database either through the SSMS delete option or by executing `DROP DATABASE sample1`.
- A database cannot be dropped if it is currently in use by any user or process; an error message will indicate this.
- To drop a database that is in use, it must first be set to single-user mode using `ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE`.
- The `WITH ROLLBACK IMMEDIATE` option forcefully rolls back any pending transactions and closes active connections, allowing the database to be dropped.
- Graphically, this can be achieved by selecting the 'Close existing connections' option when prompted during the delete operation in SSMS.
This procedure is critical for safely removing databases that are actively being accessed, preventing data corruption and ensuring the drop operation can complete successfully.
Attempting to drop 'sample4' while another SSMS session is connected to it results in an error. Using `ALTER DATABASE sample4 SET SINGLE_USER WITH ROLLBACK IMMEDIATE` followed by `DROP DATABASE sample4` allows the deletion.
Key takeaways
- SQL Server databases can be managed (created, altered, dropped) using either the graphical interface of SQL Server Management Studio or by writing SQL queries.
- Every database comprises a data file (.mdf) and a transaction log file (.ldf), which are essential for storing data and enabling recovery.
- Renaming databases can be accomplished using the `ALTER DATABASE ... MODIFY NAME` command or the `sp_renameDB` stored procedure.
- The `DROP DATABASE` command and its graphical equivalent permanently delete a database and its associated files.
- Databases must not be in use to be dropped; if they are, they need to be put into single-user mode with immediate rollback of transactions.
- System databases are integral to SQL Server's operation and cannot be dropped.
Key terms
DatabaseSQL Server Management Studio (SSMS)CREATE DATABASEALTER DATABASEDROP DATABASEMaster Data File (.mdf)Transaction Log File (.ldf)System DatabasesUser-defined DatabasesStored Proceduresp_renameDBSINGLE_USERROLLBACK IMMEDIATE
Test your understanding
- What are the two primary methods for creating a database in SQL Server, and what are the two essential files generated for each database?
- How can you rename a database using SQL queries, and what is the purpose of the `sp_renameDB` stored procedure?
- Why is it sometimes impossible to drop a database directly, and what steps must be taken to overcome this limitation?
- What is the difference between user-defined databases and system databases, and which type cannot be dropped?
- Explain the function of the .mdf and .ldf files within a SQL Server database.