当您在 microsoft windows azure sql database中部署应用程序时，可能需要将内部部署的 sql server 实例上的数据库迁移到 sql database 中。本主题演示如何通过使用 transact-sql 脚本将一个简单的数据库迁移到 sql database。windows azure sql database支持一部分 transact-sql 语言。在将数据库部署到 sql database 之前，您必须修改生成的脚本，使其仅包括支持的 transact-sql 语句。创建 school 示例数据库
在 sql server management studio 中的“文件”菜单上，指向“新建”，然后单击“数据库引擎查询”。在“连接到数据库引擎”对话框中，输入您本地 sql server 实例的信息，然后单击“连接”。在查询窗**粘贴本主题末尾处的 transact-sql 脚本，然后单击“执行”。创建 transact-sql 脚本
在“对象资源管理器”中，右键单击“school”数据库，指向“任务”，然后选择“生成脚本”。在“生成和发布脚本向导”对话框中，单击“下一步”进入“选择对象”步骤。选择“编写整个数据库及所有数据库对象的脚本”，然后单击“下一步”。在“设置脚本编写选项”中，设置以下选项：
将“输出类型”设置为“将脚本保存到特定位置”。选择“保存到文件”。单击“单个文件”。在“文件名”中键入文件名和位置。单击“高级”。在“高级脚本编写选项”中，将“数据库引擎类型的脚本”选项设置为“sql database”，将“将 uddt 转换为基类型”选项设置为“true”，并将“要编写脚本的数据的类型”选项设置为“架构和数据”。单击“确定”。单击“下一步”，再单击“下一步”，然后单击“完成”。在 sql database 上运行脚本
通过 windows azure 平台管理门户或通过执行以下 transact-sql 命令在 sql database 服务器上创建 school 数据库：
create database school
在 sql server management studio 中打开“查询”菜单，指向“连接”，然后选择“更改连接”。输入 sql database 服务器名称和您的凭据，然后单击“选项>>”。在“连接属性”选项卡上的“连接到数据库”下拉菜单中，键入 school，然后单击“连接”。右键单击 sql database 中的“school”数据库，然后选择“新建查询”。在 sql server management studio 中的“文件”菜单上，指向“打开”，然后单击“文件”。在“打开文件”窗**，从您之前在“生成和发布脚本向导”中指定的位置打开 school 脚本文件。按 f5 执行该脚本。school 数据库 transact-sql 定义
运行此脚本，在 sql server 的本地实例中创建 school 数据库。transact-sql
set ansi_**s on
go
set quoted_identifier on
go
use[master];go
if ex**ts(select*from sys.databases where name='school')
drop database school;go
create the school database.
create database school;go
specify a simple recovery model
to keep the log growth to a minimum.
alter database school
set recovery simple;go
use school;go
create the department table.
if not ex**ts(select*from sys.objects
where object_id=o**ect_id(n'[dbo].[department]')
and type in(n'u'))
begin
create table[dbo].[department]
[departmentid][int]not **,
[name][nvarchar](50)not **,
[budget][money]not **,
[startdate][datetime]not **,
[admin**trator][int]**,
constraint[pk_department]primary key clustered
[departmentid]asc
with(ignore_dup_key=off)on[primary]
on[primary]
end
go
create the person table.
if not ex**ts(select*from sys.objects
where object_id=o**ect_id(n'[dbo].[person]')
and type in(n'u'))
begin
create table[dbo].[person]
[personid][int]identity(1,1)not **,
[lastname][nvarchar](50)not **,
[firstname][nvarchar](50)not **,
[hiredate][datetime]**,
[enrollmentdate][datetime]**,
constraint[pk_school.student]primary key clustered
[personid]asc
with(ignore_dup_key=off)on[primary]
on[primary]
end
go
create the onsitecourse table.
if not ex**ts(select*from sys.objects
where object_id=o**ect_id(n'[dbo].[onsitecourse]')
and type in(n'u'))
begin
create table[dbo].[onsitecourse]
[courseid][int]not **,
[location][nvarchar](50)not **,
[days][nvarchar](50)not **,
[time][**alldatetime]not **,
constraint[pk_onsitecourse]primary key clustered
[courseid]asc
with(ignore_dup_key=off)on[primary]
on[primary]
end
go
create the onlinecourse table.
if not ex**ts(select*from sys.objects
where object_id=o**ect_id(n'[dbo].[onlinecourse]')
and type in(n'u'))
begin
create table[dbo].[onlinecourse]
[courseid][int]not **,
[url][nvarchar](100)not **,
constraint[pk_onlinecourse]primary key clustered
[courseid]asc
with(ignore_dup_key=off)on[primary]
on[primary]
end
go
create the studentgrade table.
if not ex**ts(select*from sys.objects
where object_id=o**ect_id(n'[dbo].[studentgrade]')
and type in(n'u'))
begin
create table[dbo].[studentgrade]
[enrollmentid][int]identity(1,1)not **,
[courseid][int]not **,
[studentid][int]not **,
[grade][decimal](3,2)**,
constraint[pk_studentgrade]primary key clustered
[enrollmentid]asc
with(ignore_dup_key=off)on[primary]
on[primary]
end
go
create the courseinstructor table.
if not ex**ts(select*from sys.objects
where object_id=o**ect_id(n'[dbo].[courseinstructor]')
and type in(n'u'))
begin
create table[dbo].[courseinstructor]
[courseid][int]not **,
[personid][int]not **,
constraint[pk_courseinstructor]primary key clustered
[courseid]asc,
[personid]asc
with(ignore_dup_key=off)on[primary]
on[primary]
end
go
create the course table.
if not ex**ts(select*from sys.objects
where object_id=o**ect_id(n'[dbo].[course]')
and type in(n'u'))
begin
create table[dbo].[course]
[courseid][int]not **,
[title][nvarchar](100)not **,
[credits][int]not **,
[departmentid][int]not **,
constraint[pk_school.course]primary key clustered
[courseid]asc
with(ignore_dup_key=off)on[primary]
on[primary]
end
go
create the officeassignment table.
if not ex**ts(select*from sys.objects
where object_id=o**ect_id(n'[dbo].[officeassignment]')
and type in(n'u'))
begin
create table[dbo].[officeassignment]
[instructorid][int]not **,
[location][nvarchar](50)not **,
[timestamp][timestamp]not **,
constraint[pk_officeassignment]primary key clustered
[instructorid]asc
with(ignore_dup_key=off)on[primary]
on[primary]
end
go
define the relationship between onsitecourse and course.
if not ex**ts(select*from sys.foreign_keys
where object_id=o**ect_id(n'[dbo].[fk_onsitecourse_course]')
and parent_object_id=o**ect_id(n'[dbo].[onsitecourse]'))
alter table[dbo].[onsitecourse]with check add
constraint[fk_onsitecourse_course]foreign key([courseid])
references[dbo].[course]([courseid])
go
alter table[dbo].[onsitecourse]check
constraint[fk_onsitecourse_course]
go
define the relationship between onlinecourse and course.
if not ex**ts(select*from sys.foreign_keys
where object_id=o**ect_id(n'[dbo].[fk_onlinecourse_course]')
and parent_object_id=o**ect_id(n'[dbo].[onlinecourse]'))
alter table[dbo].[onlinecourse]with check add
constraint[fk_onlinecourse_course]foreign key([courseid])
references[dbo].[course]([courseid])
go
alter table[dbo].[onlinecourse]check
constraint[fk_onlinecourse_course]
go
define the relationship between studentgrade and course.
if not ex**ts(select*from sys.foreign_keys
where object_id=o**ect_id(n'[dbo].[fk_studentgrade_course]')
and parent_object_id=o**ect_id(n'[dbo].[studentgrade]'))
alter table[dbo].[studentgrade]with check add
constraint[fk_studentgrade_course]foreign key([courseid])
references[dbo].[course]([courseid])
go
alter table[dbo].[studentgrade]check
constraint[fk_studentgrade_course]
go
define the relationship between studentgrade and student.
if not ex**ts(select*from sys.foreign_keys
where object_id=o**ect_id(n'[dbo].[fk_studentgrade_student]')
and parent_object_id=o**ect_id(n'[dbo].[studentgrade]'))
alter table[dbo].[studentgrade]with check add
constraint[fk_studentgrade_student]foreign key([studentid])
references[dbo].[person]([personid])
go
alter table[dbo].[studentgrade]check