ch05 database.sql

SQL+Server实用简明教程电子教案和实例源文件

USE master
CREATE DATABASE ElecTravelCom 

 

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






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







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







SELECT N'数据库的恢复模式' 
             = DATABASEPROPERTYEX('AdventureWorks', 'Recovery')
GO

USE AdventureWorks
GO
sp_spaceused

sp_helpdb AdventureWorks
GO



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:\SQLServerDBData\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)






USE master
GO
ALTER DATABASE LGCom
ADD FILEGROUP FG1
GO
ALTER DATABASE LGCom
ADD FILE
(   NAME = LGF3,
    FILENAME = 'D:\SQLServerDBData\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_20091112
ON
(	NAME = AdventureWorks_Data,
	FILENAME = 'D:\SQLServerDBData\AdventureWorks_sp_081112.snp')
AS SNAPSHOT OF AdventureWorks
GO




USE master
GO
sp_detach_db LGCom






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