T-SQL入門攻略之修改數(shù)據(jù)庫alter database

    更改數(shù)據(jù)庫名
    (1)
    alter database database_name
    modify name=new_database_name
    (2)
    sp_renamedb olddbname,newdbname
    添加數(shù)據(jù)文件和文件組(擴(kuò)大)
    (1)添加數(shù)據(jù)文件
    USE master
    GO
    DECLARE @data_path nvarchar(256);
    -- 獲取主數(shù)據(jù)文件的存放位置
    SELECT @data_path=physical_name
    FROM MyDatabase.sys.database_files
    WHERE file_id=1;
    SET @data_path=SUBSTRING(@data_path, 1, CHARINDEX(LOWER('MyDatabase.mdf')， LOWER(@data_path)) - 1);
    -- 添加數(shù)據(jù)文件(放在主數(shù)據(jù)文件所在的目錄下)
    go
    ALTER DATABASE MyDatabase
    ADD FILE
    (
    NAME = LogicNameOfDataFile1_1,
    FILENAME = 'D:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/DataFile1_1.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
    )
    (2)添加日志文件
    USE master
    GO
    DECLARE @data_path nvarchar(256);
    --獲取主數(shù)據(jù)文件的存放位置
    SELECT @data_path=physical_name
    FROM MyDatabase.sys.database_files
    WHERE file_id=1;
    SET @data_path=SUBSTRING(@data_path, 1, CHARINDEX(LOWER('MyDatabase.mdf')， LOWER(@data_path)) - 1);
    --添加數(shù)據(jù)文件(放在主數(shù)據(jù)文件所在的目錄下)
    go
    ALTER DATABASE MyDatabase
    ADD LOG FILE
    (
    NAME = LogicNameOfLogFile1_1,
    FILENAME = 'D:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/LogFile1_1.ldf',
    SIZE = 2MB,
    MAXSIZE = 50MB,
    FILEGROWTH = 3MB
    )
    (3)添加文件及文件組
    USE master
    GO
    -- 創(chuàng)建文件組_1
    ALTER DATABASE MyDatabase
    ADD FILEGROUP UserFG1_1;
    GO
    DECLARE @data_path nvarchar(256);
    --獲取主數(shù)據(jù)文件的存放位置
    SELECT @data_path=physical_name
    FROM MyDatabase.sys.database_files
    WHERE file_id=1;
    SET @data_path=SUBSTRING(@data_path, 1, CHARINDEX(LOWER('MyDatabase.mdf')， LOWER(@data_path)) - 1);
    --添加數(shù)據(jù)文件(放在主數(shù)據(jù)文件所在的目錄下)
    Go
    ALTER DATABASE MyDatabase
    ADD FILE
    (
    NAME = LogicNameOfDataFile1_2,
    FILENAME = 'D:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/DataFile1_2.ndf',
    SIZE = 10MB,
    MAXSIZE = 50MB,
    FILEGROWTH = 5MB
    )，
    (
    NAME = LogicNameOfDataFile1_3,
    FILENAME = 'D:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/DataFile1_3.ndf',
    SIZE = 5MB,
    MAXSIZE = 50MB,
    FILEGROWTH = 2MB
    )
    TO FILEGROUP UserFG1_1
    注：以上添加數(shù)據(jù)文件時(shí)如果不指定所在文件組那么將被默認(rèn)添加到主文件組中
    更改數(shù)據(jù)文件和文件組
    (1)更改數(shù)據(jù)庫的數(shù)據(jù)文件
    USE master
    GO
    ALTER DATABASE MyDatabase2
    MODIFY FILE
    (
    NAME = LogicNameOfDataFile2,
    NEWNAME = newLogicNameOfDataFile2,
    FILENAME
    ='C:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/DATA/newDataFile2.mdf',
    SIZE = 25MB,
    MAXSIZE = 150MB,
    FILEGROWTH = 10MB
    )
    (2)更改數(shù)據(jù)庫的日志文件
    USE master
    GO
    ALTER DATABASE MyDatabase3
    MODIFY FILE
    (
    NAME = MyDatabase3_log,
    FILENAME
    ='C:/Program Files/Microsoft SQL Server/MSSQL10.MSSQLSERVER/MSSQL/DATA/LogFile3.LDF',
    SIZE = 10MB , -- 設(shè)置初始大小
    MAXSIZE = 50MB, -- 設(shè)置文件的最大存儲空間
    FILEGROWTH = 5MB -- 設(shè)置自動增長幅度
    )
    (3)更改文件組名稱
    USE master
    GO
    ALTER DATABASE MyDatabase6
    MODIFY FILEGROUP UserFG6_2 NAME = newUserFG6_2
    GO
    (4)更改默認(rèn)文件組
    USE master;
    GO
    ALTER DATABASE MyDatabase6
    MODIFY FILEGROUP newUserFG6_2 DEFAULT;
    GO
    ALTER DATABASE MyDatabase6
    MODIFY FILEGROUP [PRIMARY] DEFAULT;
    GO