分离附加数据库.sql

来自「简单的sql语句 数据与汇总。 高手多多指点。」· SQL 代码 · 共 69 行

create database sample
on
 primary(name=sampledata,
         filename='c:\program files\microsoft sql server\mssql\data\sample.mdf',
         size=10,
         maxsize=20,
         filegrowth=1)
log on
       (name=samplelog,
        filename='c:\program files\microsoft sql server\mssql\data\sample.ldf',
        size=10,
        maxsize=20,
        filegrowth=1)
go

drop database sample
go

USE master
GO
CREATE DATABASE Archive 
ON
PRIMARY ( NAME = Arch1,
      FILENAME = 'c:\program files\microsoft sql server\mssql\data\archdat1.mdf',
      SIZE = 100MB,
      MAXSIZE = 200,
      FILEGROWTH = 20),
( NAME = Arch2,
   FILENAME = 'c:\program files\microsoft sql server\mssql\data\archdat2.ndf',
   SIZE = 100MB,
   MAXSIZE = 200,
   FILEGROWTH = 20),
( NAME = Arch3,
   FILENAME = 'c:\program files\microsoft sql server\mssql\data\archdat3.ndf',
   SIZE = 100MB,
   MAXSIZE = 200,
   FILEGROWTH = 20)
LOG ON 
( NAME = Archlog1,
   FILENAME = 'c:\program files\microsoft sql server\mssql\data\archloga.ldf',
   SIZE = 100MB,
   MAXSIZE = 200,
   FILEGROWTH = 20),
( NAME = Archlog2,
   FILENAME = 'c:\program files\microsoft sql server\mssql\data\archlog2.ldf',
   SIZE = 100MB,
   MAXSIZE = 200,
   FILEGROWTH = 20)
GO
/*filename是物理名字，即就是路径名字。name则是属性里的文件名*/

use master

sp_detach_db archive
go
exec sp_attach_db 'archive',
'c:\program files\microsoft sql server\mssql\data\archdat1.mdf',
'c:\program files\microsoft sql server\mssql\data\archdat2.ndf',
'c:\program files\microsoft sql server\mssql\data\archdat3.ndf',
'c:\program files\microsoft sql server\mssql\data\archloga.ldf',
'c:\program files\microsoft sql server\mssql\data\archlog2.ldf'
go

/*
create database archive
on primary(FILENAME = 'c:\program files\microsoft sql server\mssql\data\archdat1.mdf')
for attach
*/
drop database archive