SQL Server/T-SQL Tutorial/Database/Alter Database

Alter a database and set the recovery to simple

ALTER DATABASE database_name
SET RECOVERY SIMPLE

ALTER DATABASE pubs SET RECOVERY FULL

ALTER DATABASE pubs SET RECOVERY FULL

Altering a Database

ALTER DATABASE db_name
  ADD FILE file_spec1 [TO FILEGROUP group_name1]
  |ADD LOG FILE file_spec2
  |REMOVE FILE "file_name"
  |MODIFY FILE (NAME = old_name, NEWNAME = new_name ...)
  |CREATE FILEGROUP group_name2 | DROP FILEGROUP filegroup_name3
  |SET option_specifications [WITH terminations]
13> create database projects;
14>
15> ALTER DATABASE projects
16>       ADD FILE (NAME=projects_dat1,
17>         FILENAME = "C:\DATA\projects1.mdf",
18>         SIZE = 10,
19>         MAXSIZE = 100,
20>         FILEGROWTH = 5)
21>
22> drop database projects;
23> GO

changes the user access option of the pubs database to SINGLE_USER and generates an error if any other connections to the pubs database exist:

ALTER DATABASE pubs SET SINGLE_USER WITH NO_WAIT

how to set a database"s updatability value to READ_ONLY and how to determine the updatability of a database:

ALTER DATABASE pubs SET READ_ONLY
SELECT DATABASEPROPERTYEX("pubs", "updateability")

increases the size of a database file:

13> USE master
14> GO
1> ALTER DATABASE Test1
2> MODIFY FILE
3> ( NAME = "test1dat3",
4> SIZE = 20MB)

OFFLINE | ONLINE

ALTER DATABASE pubs SET OFFLINE
SELECT DATABASEPROPERTYEX("pubs", "status")

SINGLE_USER | RESTRICTED_USER | MULTI_USER

ALTER DATABASE pubs SINGLE_USER