Create and Alter Databases
Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.
To create or alter database
-
Select the Databases node in the Object Explorer.
- Select one of the following:
- Click
to create a database.
- Select a database and click
to create a database like the selected one.
- Select a database and click
to alter it.
Note: Databases marked with
icon are CONTAINED. Such databases are available in MS SQL SERVER 2012 and above. By default Contained Databases are turned off on the server. To enable Contained Database authentication select Tools | Administer | Server Properties | Advanced tab.
- Click
-
Complete the General page. Review the following for additional information:
Containment Description Containment
Note: According to the contained databases concept all metadata, connection and configuration settings are stored inside database that provides opportunity to easily move it between SQL Server instances.
Partial contained databases are introduced in SQL Server 2012 and provide limited containment functionality, but also support uncontained features available before. See the Microsoft® Developer Network for more information on partial contained databases. All databases created in previous versions of SQL Server are non-contained.
Select one of the following:
- None—Select to create non-contained database.
- Partial—Select to create partially contained database.
Tranform noise word enabled
Select True to ignore the words included in the stopwords list and to process full-text query. Two digit year cutoff
Set four digit year that will be treated as a cutoff.
The century of the two digit year is interpreted based on the last two digits of the cutoff year. If a two digit year is less or equals to it, it is considered to be of the same century as the cutoff year. If two digit year exceeds last two digits of the cutoff year, than the two digit year is considered to precede the century of the cutoff year.
-
Complete the Filegroup page. Review the following for additional information:
General options Description Files
Displays the number of files associated with this filegroup.
Read-only
Select this checkbox to make the filegroup read-only. If selected, you cannot modify data files included in this filegroup.
Default
Select this checkbox to automatically assign files to this filegroup if a filegroup is not specified.
Notes:
-
You cannot move files to a different filegroup if the files have been added to the database.
-
You cannot delete the PRIMARY filegroup.
Non-transacted access options Description Non-transacted access
Specifies whether the filetables can be created in the current database.
Select one of the following:
- Off—Select to disallow filetables in the current database.
- Read-only—Select to allow read-only access to the filetables in the current database.
- Full—Select to allow read and write access to the filetables in the current database.
Directory name
Specify unique directory name where the filetables will be stored. See the Microsoft® Developer Network for more information on filetables and directory options. -
- Complete remaining pages of the dialog.
Note: Right-click the object to see additional actions and tasks available for it.
Edit Database Permissions
This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.To set permissions for a database
-
Select a database under the Databases node in the Object Explorer.
- Right-click a database and select
Edit Permissions.
- Click Add on the Permissions page.
-
Complete the fields as necessary. Review the following for additional information:
User or role
Select the user or role for which you want to edit permissions.
Permissions
Select whether you want to allow or deny permissions for each type listed.
Note: You can revoke all permissions for an object by clicking Revoke.
Working with Databases
Manage Database Storage and Shrink Databases
You can manage database storage, see free disk space, change database location and shrink databases, which contain both transaction and log files; however, you can only shrink databases to their original size. If you need to shrink the database to a size smaller than the original, use the shrink database file procedure instead. This allows you to shrink individual transaction or log files to a custom size, regardless of their original size.
Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.
To manage database storage
| » | Select Tools | Administer | Storage Management. Select one of the following tabs. Review the following for additional information: |
- Drives tab—To see information on free disk space and to move databases to another disk.
- Databases tab—To review space allocation for each database, alter and shrink databases, shrink files.
- Objects tab—To review a list of objects available in the selected database.
Tip: Right-click a database (or any object) in the list to see additional actions and tasks available for it.
To move database to another drive
-
Select Tools | Administer | Storage Management.
- Select the Drives tab.
- Select a database and drag it to another drive.
- Specify new location in the dialog.
- Follow instructions in the generated script:
- Click Make database offline.
- Move database files using Windows Explorer or xp_cmdshell.
- Click Modify files to update database metadata.
- Click Make database online.
Note: Right-click a database and select Cancel changes to move database back to its original location.
-
Select Tools | Administer | Storage Management.
-
Select Databases tab.
-
Select a database and click
.
Note: You can also right-click a database from the Databases node in the Object Explorer and select Tasks | Shrink Database.
-
Complete the fields as necessary. Review the following for additional information:
Reorganize files
Select True to move pages from the end of a data file to unallocated pages at the beginning of the data file. If selected, free space is not returned to the operating system.
To shrink individual database files
-
Select Tools | Administer | Storage Management.
- Select Databases tab.
-
Select a transaction or log file and click Shrink.
Note: You can also right-click a database from the Databases node in the Object Explorer and select View Details | Files.
- Complete the fields as necessary. Review the following for additional information:
Shrink action
Select one of the following:
- Release unused space—Releases any free space at the end of the data file to the operating system.
- Reorganize pages—Moves pages from the end of a data file to unallocated pages at the beginning of the data file. If selected, free space is not returned to the operating system.
- Empty files by Migrating the data—Moves data from the selected file to other data files in the filegroup.