Sql Server 数据迁移问题的解决

时间：2010-12-21 来源：mikelij

　　下面说一下怎么做：找到数据库这一级，右键点该数据库，选“Tasks”，再“Generate Scripts”。

选数据库，

选择输出脚本的选项。上下看了一遍，稍微改几个就可以了。

“Include If NOT EXISTS”，“Script drop”都选了True。因为我们要复制数据，“Script data”也选了True，另外我们也需要索引，所以“Script Indexes”也选了True。据我们说知，这个数据库没有用触发器，所以有些选项就直接用默认的。然后是选择表，视图，存储过程，自定义函数等。（以下示例的图只有表）

再选择具体的表，视图，存储过程，自定义函数等。我们这里是全选。

将脚本产生到一个文件中去。

最后完成。检查一下产生的脚本。

USE [learn]
GO
/****** Object:  Table [dbo].[Student]    Script Date: 12/21/2010 21:42:46 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Student]') AND type in (N'U'))
DROP TABLE [dbo].[Student]
GO
/****** Object:  Table [dbo].[Student]    Script Date: 12/21/2010 21:42:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Student]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Student](
    [StudentID] [int] IDENTITY(1,1) NOT NULL,
    [StudentName] [nvarchar](50) NOT NULL,
    [Gender] [nchar](10) NOT NULL,
    [BirthDate] [date] NOT NULL,
    [Address] [nvarchar](100) NULL,
CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED 
(
    [StudentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET IDENTITY_INSERT [dbo].[Student] ON
INSERT [dbo].[Student] ([StudentID], [StudentName], [Gender], [BirthDate], [Address]) VALUES (1, N'王超', N'男         ', CAST(0x89F90A00 AS Date), N'北京东城区万台路1300号')
INSERT [dbo].[Student] ([StudentID], [StudentName], [Gender], [BirthDate], [Address]) VALUES (2, N'李然', N'男         ', CAST(0x0FFD0A00 AS Date), N'西安二碗区')
INSERT [dbo].[Student] ([StudentID], [StudentName], [Gender], [BirthDate], [Address]) VALUES (3, N'张三', N'男         ', CAST(0x7CF70A00 AS Date), N'廊坊店村区')
INSERT [dbo].[Student] ([StudentID], [StudentName], [Gender], [BirthDate], [Address]) VALUES (4, N'梅雨', N'女         ', CAST(0xD1040B00 AS Date), N'西村区')
INSERT [dbo].[Student] ([StudentID], [StudentName], [Gender], [BirthDate], [Address]) VALUES (5, N'陈骏', N'男         ', CAST(0x69090B00 AS Date), N'一号地区')
INSERT [dbo].[Student] ([StudentID], [StudentName], [Gender], [BirthDate], [Address]) VALUES (6, N'马欣', N'女         ', CAST(0xB3120B00 AS Date), N'石家庄端区仓防')
INSERT [dbo].[Student] ([StudentID], [StudentName], [Gender], [BirthDate], [Address]) VALUES (7, N'丁一若', N'男         ', CAST(0x01E60A00 AS Date), N'太原')
INSERT [dbo].[Student] ([StudentID], [StudentName], [Gender], [BirthDate], [Address]) VALUES (8, N'冷丁', N'男         ', CAST(0x30F30A00 AS Date), N'乌鲁木齐')
INSERT [dbo].[Student] ([StudentID], [StudentName], [Gender], [BirthDate], [Address]) VALUES (9, N'诸葛健文', N'女         ', CAST(0x7C150B00 AS Date), N'贵阳')
INSERT [dbo].[Student] ([StudentID], [StudentName], [Gender], [BirthDate], [Address]) VALUES (10, N'东格', N'男         ', CAST(0xCB040B00 AS Date), N'合肥')
INSERT [dbo].[Student] ([StudentID], [StudentName], [Gender], [BirthDate], [Address]) VALUES (11, N'马波涛', N'男         ', CAST(0xB30D0B00 AS Date), N'福州')
SET IDENTITY_INSERT [dbo].[Student] OFF

似乎满足我们的要求。再拿到测试环境一执行。好，测试环境就都有数据了，视图，存储过程等也都有了。问题解决。