-- Code and scripts from Chapter 4: Databases and Database Files




-- If you have a burning need to “see” the contents of 
--   mssqlsystemresource, a couple of methods are available. 
---  The easiest, if you just want to see what’s there, 
--   is to stop SQL Server, make copies of the two files for 
--   the resource database, restart SQL Server, and then 
--   attach the copied files to create a database 
---  with a new name. You can do this by using 
--   Object Explorer in SQL Server 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\Data\mssqlsystemresource_COPY.mdf'),
       (NAME = log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource_COPY.ldf')
    FOR ATTACH;
GO


-- The following is a complete example of the CREATE DATABASE 
--   command, specifying three files and all the properties 
--   of each file:
CREATE DATABASE Archive
ON
PRIMARY 
( NAME = Arch1,
FILENAME = 
    'c:\program files\microsoft sql server\mssql.1\mssql\data\archdat1.mdf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Arch2,
FILENAME = 
    'c:\program files\microsoft sql server\mssql.1\mssql\data\archdat2.ndf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20)
LOG ON 
( NAME = Archlog1,
FILENAME = 
    'c:\program files\microsoft sql server\mssql.1\mssql\data\archlog1.ldf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20);
GO

-- This example creates a database named sales with three filegroups:
--   The primary filegroup with the files Spri1_dat and 
--      Spri2_dat. The FILEGROWTH increment for both of these 
--      files is specified as 15 percent.
--   A filegroup named SalesGroup1 with the files SGrp1Fi1 
--      and SGrp1Fi2.
--   A filegroup named SalesGroup2 with the files SGrp2Fi1 
--      and SGrp2Fi2.
CREATE DATABASE Sales
ON PRIMARY
( NAME = SPri1_dat,
FILENAME = 
    'c:\program files\microsoft sql server\mssql.1\mssql\data\SPri1dat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
( NAME = SPri2_dat,
FILENAME = 
    'c:\program files\microsoft sql server\mssql.1\mssql\data\SPri2dat.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
FILEGROUP SalesGroup1
( NAME = SGrp1Fi1_dat,
FILENAME = 
    'c:\program files\microsoft sql server\mssql.1\mssql\data\SG1Fi1dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
( NAME = SGrp1Fi2_dat,
FILENAME = 
    'c:\program files\microsoft sql server\mssql.1\mssql\data\SG1Fi2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
FILEGROUP SalesGroup2
( NAME = SGrp2Fi1_dat,
FILENAME = 
    'c:\program files\microsoft sql server\mssql.1\mssql\data\SG2Fi1dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
( NAME = SGrp2Fi2_dat,
FILENAME = 
    'c:\program files\microsoft sql server\mssql.1\mssql\data\SG2Fi2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = ‘Sales_log’,
FILENAME = 
    'c:\program files\microsoft sql server\mssql.1\mssql\data\saleslog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB );
GO

-- The code in the book starts altering a database called Test1
--   without showing the code to create it. You can create a simple
--   database called Test1 with the following code:

CREATE DATABASE test1;
GO

--The following example creates a new filegroup in a database, 
--   adds two 5-MB files to the filegroup, and makes the new 
--   filegroup the default filegroup. We need three 
--   ALTER DATABASE statements.

ALTER DATABASE Test1 
  ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE Test1 
ADD FILE 
( NAME = 'test1dat3',
  FILENAME = 
    'c:\program files\microsoft sql server\mssql.1\mssql\data\t1dat3.ndf',
  SIZE = 5MB,
  MAXSIZE = 100MB,
  FILEGROWTH = 5MB),
( NAME = 'test1dat4',
  FILENAME = 
    'c:\program files\microsoft sql server\mssql.1\mssql\data\t1dat4.ndf',
  SIZE = 5MB,
  MAXSIZE = 100MB,
  FILEGROWTH = 5MB)
TO FILEGROUP Test1FG1;
GO
ALTER DATABASE Test1
MODIFY FILEGROUP Test1FG1 DEFAULT;
GO

-- This example increases the size of a database file:
USE master
GO
ALTER DATABASE Test1 
MODIFY FILE
( NAME = 'test1dat3',
SIZE = 20MB);

-- 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;
GO


-- 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';
GO


--  It’s relatively easy to test emergency status value for a 
--   database on a test server. You can create a simple 
--   database with the three-word command 
--   CREATE DATABASE TESTDB
--   and then stop your SQL Server instance and rename 
--   (or remove) the log file. When you restart your instance,
--   check the status of the new database:
SELECT name, database_id, user_access_desc, state_desc
FROM sys.databases
WHERE name = 'testdb';
GO

-- The state_desc should show RECOVERY_PENDING, which you can 
--   now change to EMERGENCY:
ALTER DATABASE testdb SET EMERGENCY;
GO


-- 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;
GO
SELECT name, is_read_only FROM sys.databases
WHERE name = 'AdventureWorks';
GO

-- Here is the syntax to create a snapshot of the 
--   AdventureWorks database, putting the snapshot files 
--   in the SQL Server 2005 default data directory:
CREATE DATABASE AdventureWorks_snapshot ON 
( NAME = N'AdventureWorks_Data', 
   FILENAME = 
   N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AW_snapshot.mdf') 
AS SNAPSHOT OF AdventureWorks;


-- You can basically replace the source database with one of 
--   its snapshots by reverting the source database to the 
--   way it was when a snapshot was made. 
--   You do this by using the RESTORE command:
RESTORE DATABASE AdventureWorks  
FROM SNAPSHOT = AdventureWorks_snapshot;
GO


-- The following query shows the mapping of users in the 
--   AdventureWorks database to login names, and it also 
--   shows the default schema 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;
GO