ch04.sql

SQLServer2005基础教程,清华大学出版社

USE master
CREATE DATABASE ETravelCom 

USE master
CREATE DATABASE LCBCom
ON
(	NAME = LCBCom_DATA,
	FILENAME = 'D:\SQLServerDBFiles\LCBCom_DATA.mdf',
	SIZE = 6,
	MAXSIZE = 20,
	FILEGROWTH = 10%)
LOG ON
(	NAME = LCBCom_LOG,
	FILENAME = 'D:\SQLServerDBFiles\LCBCom_LOG.ldf',
	SIZE = 2MB,
	MAXSIZE = 10MB,
	FILEGROWTH = 1MB)
GO

USE master
CREATE DATABASE LCBSalesDB
ON PRIMARY
(   NAME = LCBSFile_dat,
    FILENAME = 'D:\SQLServerDBFiles\SFileDat.mdf',
    SIZE = 5,
    MAXSIZE = 50,
    FILEGROWTH = 10% ),
FILEGROUP SG
(   NAME = SGF1_dat,
    FILENAME = 'D:\SQLServerDBFiles\SGF1Dat.ndf',
    SIZE = 5,
    MAXSIZE = 50,
    FILEGROWTH = 5 ),
(   NAME = SGF2_dat,
    FILENAME = 'D:\SQLServerDBFiles\SGF2Dat.ndf',
    SIZE = 5,
    MAXSIZE = 50,
    FILEGROWTH = 5 )
LOG ON
(   NAME = LCBSFile_log,
    FILENAME = 'D:\SQLServerDBFiles\SDBlog.ldf',
    SIZE = 3MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 1MB )
GO


USE master
GO
ALTER DATABASE AdventureWorks
SET RECOVERY FULL,
PAGE_VERIFY CHECKSUM
GO

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Recovery')
GO

USE AdventureWorks
GO
sp_spaceused

sp_helpdb AdventureWorks

USE master
GO
ALTER DATABASE LCBCom
MODIFY NAME = LGCom

USE master
GO
ALTER DATABASE LGCom
ADD FILE
(	NAME = LGCom_02_DATA,
	FILENAME = 'D:\SQLServerDBFiles\LGCom_02_data.ldf',
	SIZE = 3,
	MAXSIZE = 10,
	FILEGROWTH = 10%)


USE master
GO
ALTER DATABASE LGCom
MODIFY FILE
(	NAME = LGCom_02_DATA,
	SIZE = 8)




USE master
GO
ALTER DATABASE LGCom
MODIFY FILE
(	NAME = LGCom_02_DATA,
	SIZE = 6)




ALTER DATABASE LGCom
ADD FILEGROUP FG1


USE master
GO
ALTER DATABASE LGCom
ADD FILE
(   NAME = LGF3,
    FILENAME = 'D:\SQLServerDBFiles\LGF3.mdf',
    SIZE = 5,
    MAXSIZE = 20,
    FILEGROWTH = 10%)
TO FILEGROUP FG1


USE master
GO
ALTER DATABASE LGCom
ADD FILE
(   NAME = LGF4,
    FILENAME = 'D:\SQLServerDBFiles\LGF4.mdf',
    SIZE = 5,
    MAXSIZE = 20,
    FILEGROWTH = 10%)
TO FILEGROUP FG2

USE master
GO
ALTER DATABASE LGCom
ADD FILEGROUP FG2 DEFAULT

USE master
GO
ALTER DATABASE LGCom 
MODIFY FILEGROUP FG1 READ_ONLY

USE master
GO
CREATE DATABASE AdventureWorks_snapshot_123456789
ON
(	NAME = AdventureWorks_Data,
	FILENAME = 'D:\SQLServerDBFiles\AdventureWorks_sp_12.snp')
AS SNAPSHOT OF AdventureWorks
GO

USE master
GO
sp_detach_db LGCom

USE master
GO
CREATE DATABASE LGCom
ON
(	FILENAME = 'D:\SQLServerDBFiles\LCBCom_DATA.mdf')
FOR ATTACH