Modifying databases (Changes to existing DB)

Modifying a database is not a very common occurrence, if you have created your database to allocate disk space dynamically, you may never need to alter it. Nonetheless, there may be occasions when it is necessary to modify a database. In such cases, you use the ALTER DATABASE Transact-SQL statement:

For example, suppose you want to INCREASE the size of the transaction log assigned to database employee to four megabytes from one megabyte. If you used the default values when creating the database, the database will be stored in a file named "employee.mdf" and the transaction log will be stored in a file named "employee_Log.ldf." To INCREASE the size of the transaction log, you'd issue the following statement:

ALTER DATABASE employee 
MODIFY FILE (NAME = "employee_Log", SIZE = 4MB)

Note that when you use the Name parameter, you do not include the file extension .ldf. Also, the MB designation for megabytes is optional. Note: It is important to note that you cannot modify a database to a smaller size, only to a larger size.
In the next lesson, we will cover the simple task of deleting a database. It is time to begin the course project! Click the Exercise link below to write Transact-SQL statements that create and modify the database that we will build throughout the remainder of the course.