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

Alter a database and set the recovery to simple

   <source lang="sql">

ALTER DATABASE database_name SET RECOVERY SIMPLE</source>

ALTER DATABASE pubs SET RECOVERY FULL

   <source lang="sql">

ALTER DATABASE pubs SET RECOVERY FULL</source>

Altering a Database

   <source lang="sql">

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</source>

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:

   <source lang="sql">

ALTER DATABASE pubs SET SINGLE_USER WITH NO_WAIT</source>

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

   <source lang="sql">

ALTER DATABASE pubs SET READ_ONLY SELECT DATABASEPROPERTYEX("pubs", "updateability")</source>

increases the size of a database file:

   <source lang="sql">

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

OFFLINE | ONLINE

   <source lang="sql">

ALTER DATABASE pubs SET OFFLINE SELECT DATABASEPROPERTYEX("pubs", "status")</source>

SINGLE_USER | RESTRICTED_USER | MULTI_USER

   <source lang="sql">

ALTER DATABASE pubs SINGLE_USER</source>