-- You can do this by using Object Explorer in Management Studio or by using the
-- CREATE DATABASE FOR ATTACH syntax to create a clone database, as shown here:
CREATE DATABASE resource_COPY ON (NAME = data, FILENAME =
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\mssqlsystemresource_COPY.mdf'),
(NAME = log, FILENAME =
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\mssqlsystemresource_COPY.ldf')
FOR ATTACH;
-- A CREATE DATABASE Example
CREATE DATABASE Archive
ON
PRIMARY
( NAME = Arch1,
FILENAME =
'c:\program files\microsoft sql server\mssql.1\mssql\data\archdat1.mdf',
SIZE = 100MB,
MAXSIZE = 200MB,
FILEGROWTH = 20MB),
( NAME = Arch2,
FILENAME =
'c:\program files\microsoft sql server\mssql.1\mssql\data\archdat2.ndf',
SIZE = 10GB,
MAXSIZE = 50GB,
FILEGROWTH = 250MB)
LOG ON
( NAME = Archlog1,
FILENAME =
'c:\program files\microsoft sql server\mssql.1\mssql\data\archlog1.ldf',
SIZE = 2GB,
MAXSIZE = 10GB,
FILEGROWTH = 100MB);
-- A FILEGROUP CREATION Example
CREATE DATABASE Sales
ON PRIMARY
( NAME = salesPrimary1,
FILENAME =
'c:\program files\microsoft sql server\mssql.1\mssql\data\salesPrimary1.mdf',
SIZE = 100,
MAXSIZE = 500,
FILEGROWTH = 100 ),
( NAME = salesPrimary2,
FILENAME =
'c:\program files\microsoft sql server\mssql.1\mssql\data\salesPrimary2.ndf',
SIZE = 100,
MAXSIZE = 500,
FILEGROWTH = 100 ),
FILEGROUP SalesGroup1
( NAME = salesGrp1Fi1e1,
FILENAME =
'c:\program files\microsoft sql server\mssql.1\mssql\data\salesGrp1Fi1e1.ndf',
SIZE = 500,
MAXSIZE = 3000,
FILEGROWTH = 500 ),
( NAME = salesGrp1Fi1e2,
FILENAME =
'c:\program files\microsoft sql server\mssql.1\mssql\data\salesGrp1Fi1e2.ndf',
SIZE = 500,
MAXSIZE = 3000,
FILEGROWTH = 500 ),
FILEGROUP SalesGroup2
( NAME = salesGrp2Fi1e1,
FILENAME =
'c:\program files\microsoft sql server\mssql.1\mssql\data\salesGrp2Fi1e1.ndf',
SIZE = 100,
MAXSIZE = 5000,
FILEGROWTH = 500 ),
( NAME = salesGrp2Fi1e2,
FILENAME =
'c:\program files\microsoft sql server\mssql.1\mssql\data\salesGrp2Fi1e2.ndf',
SIZE = 100,
MAXSIZE = 5000,
FILEGROWTH = 500 )
LOG ON
( NAME = 'Sales_log',
FILENAME =
'c:\program files\microsoft sql server\mssql.1\mssql\data\saleslog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB );
-- The file and filegroup will grow as data is added to tables that have been created with
-- filestream columns:
CREATE DATABASE MyMovieReviews
ON
PRIMARY
( NAME = Reviews_data,
FILENAME = 'c:\data\Reviews_data.mdf'),
FILEGROUP MovieReviewsFSGroup1 CONTAINS FILESTREAM
( NAME = Reviews_FS,
FILENAME = 'c:\data\Reviews_FS')
LOG ON ( NAME = Reviews_log,
FILENAME = 'c:\data\Reviews_log.ldf');
GO
-- ALTER DATABASE Examples
-- This example increases the size of a database file:
USE master
GO
ALTER DATABASE Test1
MODIFY FILE
( NAME = 'test1dat3',
SIZE = 2000MB);
-- You need three ALTER DATABASE statements:
ALTER DATABASE Test1
ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE Test1
ADD FILE
( NAME = 'test1dat4',
FILENAME =
'c:\program files\microsoft sql server\mssql.1\mssql\data\t1dat4.ndf',
SIZE = 500MB,
MAXSIZE = 1000MB,
FILEGROWTH = 50MB),
( NAME = 'test1dat5',
FILENAME =
'c:\program files\microsoft sql server\mssql.1\mssql\data\t1dat5.ndf',
SIZE = 500MB,
MAXSIZE = 1000MB,
FILEGROWTH = 50MB)
TO FILEGROUP Test1FG1;
GO
ALTER DATABASE Test1
MODIFY FILEGROUP Test1FG1 DEFAULT;
GO
-- The following query retrieves some of the most important columns from sys.databases for
-- the four databases that exist on a new default installation of SQL Server:
SELECT name, database_id, suser_sname(owner_sid) as owner,
create_date, user_access_desc, state_desc
FROM sys.databases
WHERE database_id <= 4;
-- For example, to set the AdventureWorks database to single-user mode, use the following code:
ALTER DATABASE AdventureWorks SET SINGLE_USER;
-- To determine which user access value is set for a database, you can examine the
-- sys.databases catalog view, as shown here:
SELECT USER_ACCESS_DESC FROM sys.databases
WHERE name = '<name of database>';
-- The following code examples show how to set a database’s status value to OFFLINE and
-- how to determine the status of a database:
ALTER DATABASE AdventureWorks SET OFFLINE;
SELECT state_desc from sys.databases
WHERE name = 'AdventureWorks';
-- The following code shows how to set a database’s updatability value to READ_ONLY and
-- how to determine the updatability of a database:
ALTER DATABASE AdventureWorks SET READ_ONLY;
SELECT name, is_read_only FROM sys.databases
WHERE name = 'AdventureWorks';
-- The following command changes the user access option of the AdventureWorks database to SINGLE_USER
-- and generates an error if any other connections to the AdventureWorks database exist:
ALTER DATABASE AdventureWorks SET SINGLE_USER WITH NO_WAIT;
-- Here is the syntax to create a snapshot of the AdventureWorks database, putting the snapshot files
-- in the SQL Server 2008 default data directory:
CREATE DATABASE AdventureWorks_snapshot ON
( NAME = N'AdventureWorks_Data',
FILENAME =
N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\AW_data_snapshot.mdf')
AS SNAPSHOT OF AdventureWorks;
-- You do this by using the RESTORE command:
RESTORE DATABASE AdventureWorks
FROM DATABASE_SNAPSHOT = 'AdventureWorks_snapshot';
-- The following query shows the mapping of users in the AdventureWorks database to login names,
-- and it also shows the default schema (which I will discuss shortly) for each database user:
SELECT s.name as [Login Name], d.name as [User Name],
default_schema_name as [Default Schema]
FROM sys.server_principals s
JOIN sys.database_principals d
ON d.sid = s.sid;
-- The statement GRANT CREATE TABLE TO sue refers to the user sue. Let’s say sue then creates a table,
-- as follows:
CREATE TABLE mytable (col1 varchar(20));
-- If another user wants to retrieve data from this table, he can issue this statement:
SELECT col1 FROM sue.mytable;
-- Here is the command to detach a database:
EXEC sp_detach_db <name of database>;
-- Verify the change by running the following query:
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'<database_name>');
-- Start the instance of SQL Server in master-only recovery mode (by specifying traceflag 3608)
-- by entering one of the following commands at the command prompt.
-- If the instance is the default instance:
NET START MSSQLSERVER /f /T3608
-- For a named instance:
NET START MSSQL$instancename /f /T3608
-- Verify the change by running the following query:
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'<database_name>');
-- Verify the file change for the master database by running the following query.
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('master');
--you should first verify that your database is in 100 compatibility mode by executing this command:
SELECT compatibility_level FROM sys.databases
WHERE name = '<database name>';
-- To change to a different compatibility level, use the ALTER DATABASE command:
ALTER DATABASE <database name>
SET COMPATIBILITY_LEVEL = <compatibility-level>;
