T-SQL ALTER DATABASE Examples

Here are various code examples for altering a database with Transact-SQL (T-SQL).

Change the Name of a Database

The following code changes the name of a database from Problems to Solutions.

However, note that the above statement doesn't rename the data files and log files. When you rename a database, you will probably also want to rename its data files and log files (both the logical names, and the physical file names). Each of these requires its own statement.

Here's a script that combines all statements (including changing the database name):

Simply change the database names, logical file names, and physical file paths to your own.

Note that the above file paths use Linux/Mac syntax. If you use Windows, use backslashes (\) instead of forward slashes (/), as well as the drive letter at the start (e.g. C:\).

Change the Collation of a Database

The following code changes the collation of the Solutions database to French_CI_AI.

Add a Data File to a Database

The following code adds a data file to the Solutions database. This example assumes a Windows operating system (see below for a Linux and Mac example).

The file doesn't need to go into the same location as specified here — this is just an example. You can specify whatever location you need to put the log file in.

Here's an explanation of the arguments provided in this example:

NAME: Specifies the logical file name. This is the logical name used in an instance of SQL Server when referencing the file.
FILENAME: This is the full physical path to the file (i.e. the operating system's path to the file).
SIZE: Specifies the file size. Can be specified in KB, MB, GB, or TB.
MAXSIZE: The maximum file size to which the file can grow. Can be specified inÂ KB, MB, GB, TB, or UNLIMITED.
FILEGROWTH: The automatic growth increment of the file (the amount of space added to the file every time new space is required). Can be specified inÂ KB, MB, GB, TB, or %.

The default is MB (for megabytes).

Add a Data File on Linux or Mac Systems

The file location in the above example assumes a Windows operating system. If you use Linux or Mac, the path will look different (you'll need to use forward slashes (/) instead of backslashes (\).

Here's an example of adding a data file when running SQL Server for Linux on a Mac (via a Docker container):

It doesn't need to go into the same location as specified here — this is just an example. You can specify whatever location you need to put the log file in.

Add a Filegroup to a Database

The following code adds a filegroup to the Solutions database, then adds two data files to the filegroup.

It doesn't need to go into the same location as specified here — this is just an example. You can specify whatever location you need to put the log file in.

Add a Log File to a Database

The following code adds a log file to the Solutions database. This example uses Windows file path syntax.

It doesn't need to go into the same location as specified here — this is just an example. You can specify whatever location you need to put the log file in.

Increase the File Size

The following code increases the file size of the Solutions_dat_2 data file.

Using ALTER DATABASE with the MODIFY FILE command can only be used to increase the file size. If you need to decrease the file size, use DBCC SHRINKFILE (below).

Shrink the File Size

Use DBCC SHRINKFILE to shrink a database file. This example shrinks the data file to 5MB:

Remove a File from a Database

You can remove a database file if required. This example removes the data file Solutions_dat_2 (created in a previous example).

File Cannot be Removed?

If you get an error stating that the file ...cannot be removed because it is not empty, try running the following first:

Once you've run that code, try removing the file again.

Be sure to replace Solutions with your database name and Solutions_dat_2 with the logical file name that you're trying to remove.

DBCC SHRINKFILE shrinks the size of the file, or empties the file by moving the data to other files in the same filegroup, and therefore allowing the file to be removed from the database. EMPTYFILE explicitly migrates the data from the specified file to other files in the same filegroup.

Check the Location of all Data Files and Log Files

You can check that the file was created by running the following statement:

This queries the sys.database_files system catalog view. This view returns all data files and log files for the specified database (in our case the Solutions database). This includes the logical file name along with the full physical path to each file.

Alternatively, you can get a list of all data files and log files on the SQL Server instance

For each of these, you can also use an asterisk (*) to return all colums from the respective views.