1_01_MSSQL课程_基础入门2

1.数据库的迁移方案
　　->分离 附加
　　->权限问题：
　　　　->启用Administrator账号
　　　　->把数据库文件放到默认的数据库文件存放目录。
　　　　->数据库文件兼容级别，设置成2005兼容
　　->脱机 附加
　　->备份 还原
　　->生成SQL脚本(最保险)  

　　　　设置脚本编写选项——保存到新建查询窗口；

　　　　高级——要编写脚本的数据的类型——架构和数据

  1 USE [master]
  2 GO
  3 /****** Object:  Database [LJK_SQLServerDB]    Script Date: 2019/11/1 9:33:50 ******/
  4 CREATE DATABASE [LJK_SQLServerDB]
  5  CONTAINMENT = NONE
  6  ON  PRIMARY 
  7 ( NAME = N'LJK_SQLServerDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\LJK_SQLServerDB.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
  8  LOG ON 
  9 ( NAME = N'LJK_SQLServerDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\LJK_SQLServerDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
 10 GO
 11 ALTER DATABASE [LJK_SQLServerDB] SET COMPATIBILITY_LEVEL = 110
 12 GO
 13 IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
 14 begin
 15 EXEC [LJK_SQLServerDB].[dbo].[sp_fulltext_database] @action = 'enable'
 16 end
 17 GO
 18 ALTER DATABASE [LJK_SQLServerDB] SET ANSI_NULL_DEFAULT OFF 
 19 GO
 20 ALTER DATABASE [LJK_SQLServerDB] SET ANSI_NULLS OFF 
 21 GO
 22 ALTER DATABASE [LJK_SQLServerDB] SET ANSI_PADDING OFF 
 23 GO
 24 ALTER DATABASE [LJK_SQLServerDB] SET ANSI_WARNINGS OFF 
 25 GO
 26 ALTER DATABASE [LJK_SQLServerDB] SET ARITHABORT OFF 
 27 GO
 28 ALTER DATABASE [LJK_SQLServerDB] SET AUTO_CLOSE OFF 
 29 GO
 30 ALTER DATABASE [LJK_SQLServerDB] SET AUTO_CREATE_STATISTICS ON 
 31 GO
 32 ALTER DATABASE [LJK_SQLServerDB] SET AUTO_SHRINK OFF 
 33 GO
 34 ALTER DATABASE [LJK_SQLServerDB] SET AUTO_UPDATE_STATISTICS ON 
 35 GO
 36 ALTER DATABASE [LJK_SQLServerDB] SET CURSOR_CLOSE_ON_COMMIT OFF 
 37 GO
 38 ALTER DATABASE [LJK_SQLServerDB] SET CURSOR_DEFAULT  GLOBAL 
 39 GO
 40 ALTER DATABASE [LJK_SQLServerDB] SET CONCAT_NULL_YIELDS_NULL OFF 
 41 GO
 42 ALTER DATABASE [LJK_SQLServerDB] SET NUMERIC_ROUNDABORT OFF 
 43 GO
 44 ALTER DATABASE [LJK_SQLServerDB] SET QUOTED_IDENTIFIER OFF 
 45 GO
 46 ALTER DATABASE [LJK_SQLServerDB] SET RECURSIVE_TRIGGERS OFF 
 47 GO
 48 ALTER DATABASE [LJK_SQLServerDB] SET  DISABLE_BROKER 
 49 GO
 50 ALTER DATABASE [LJK_SQLServerDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
 51 GO
 52 ALTER DATABASE [LJK_SQLServerDB] SET DATE_CORRELATION_OPTIMIZATION OFF 
 53 GO
 54 ALTER DATABASE [LJK_SQLServerDB] SET TRUSTWORTHY OFF 
 55 GO
 56 ALTER DATABASE [LJK_SQLServerDB] SET ALLOW_SNAPSHOT_ISOLATION OFF 
 57 GO
 58 ALTER DATABASE [LJK_SQLServerDB] SET PARAMETERIZATION SIMPLE 
 59 GO
 60 ALTER DATABASE [LJK_SQLServerDB] SET READ_COMMITTED_SNAPSHOT OFF 
 61 GO
 62 ALTER DATABASE [LJK_SQLServerDB] SET HONOR_BROKER_PRIORITY OFF 
 63 GO
 64 ALTER DATABASE [LJK_SQLServerDB] SET RECOVERY FULL 
 65 GO
 66 ALTER DATABASE [LJK_SQLServerDB] SET  MULTI_USER 
 67 GO
 68 ALTER DATABASE [LJK_SQLServerDB] SET PAGE_VERIFY CHECKSUM  
 69 GO
 70 ALTER DATABASE [LJK_SQLServerDB] SET DB_CHAINING OFF 
 71 GO
 72 ALTER DATABASE [LJK_SQLServerDB] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
 73 GO
 74 ALTER DATABASE [LJK_SQLServerDB] SET TARGET_RECOVERY_TIME = 0 SECONDS 
 75 GO
 76 USE [LJK_SQLServerDB]
 77 GO
 78 /****** Object:  Table [dbo].[User_Info]    Script Date: 2019/11/1 9:33:50 ******/
 79 SET ANSI_NULLS ON
 80 GO
 81 SET QUOTED_IDENTIFIER ON
 82 GO
 83 CREATE TABLE [dbo].[User_Info](
 84     [User_ID] [int] IDENTITY(1,1) NOT NULL,
 85     [User_Name] [nchar](10) NULL,
 86     [User_Gender] [bit] NULL,
 87     [User_Age] [int] NULL,
 88     [User_Address] [nvarchar](50) NULL,
 89     [User_Birthday] [datetime] NULL,
 90     [User_Remark] [nvarchar](max) NULL,
 91     [User_Money] [money] NULL,
 92     [User_GUIDDemo] [uniqueidentifier] NULL,
 93  CONSTRAINT [PK_User_Info] PRIMARY KEY CLUSTERED 
 94 (
 95     [User_ID] ASC
 96 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 97 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 98 
 99 GO
100 SET IDENTITY_INSERT [dbo].[User_Info] ON 
101 
102 GO
103 INSERT [dbo].[User_Info] ([User_ID], [User_Name], [User_Gender], [User_Age], [User_Address], [User_Birthday], [User_Remark], [User_Money], [User_GUIDDemo]) VALUES (1, N'li        ', 0, 1, NULL, CAST(0x00009F9800000000 AS DateTime), NULL, NULL, NULL)
104 GO
105 INSERT [dbo].[User_Info] ([User_ID], [User_Name], [User_Gender], [User_Age], [User_Address], [User_Birthday], [User_Remark], [User_Money], [User_GUIDDemo]) VALUES (2, N'koko      ', 1, 1, NULL, CAST(0x0000A04900000000 AS DateTime), NULL, NULL, NULL)
106 GO
107 INSERT [dbo].[User_Info] ([User_ID], [User_Name], [User_Gender], [User_Age], [User_Address], [User_Birthday], [User_Remark], [User_Money], [User_GUIDDemo]) VALUES (3, N'ali       ', 0, 1, NULL, NULL, NULL, NULL, NULL)
108 GO
109 INSERT [dbo].[User_Info] ([User_ID], [User_Name], [User_Gender], [User_Age], [User_Address], [User_Birthday], [User_Remark], [User_Money], [User_GUIDDemo]) VALUES (4, N'wang      ', 1, 1, NULL, NULL, NULL, NULL, NULL)
110 GO
111 INSERT [dbo].[User_Info] ([User_ID], [User_Name], [User_Gender], [User_Age], [User_Address], [User_Birthday], [User_Remark], [User_Money], [User_GUIDDemo]) VALUES (5, N'kong      ', 1, 1, NULL, NULL, NULL, NULL, NULL)
112 GO
113 INSERT [dbo].[User_Info] ([User_ID], [User_Name], [User_Gender], [User_Age], [User_Address], [User_Birthday], [User_Remark], [User_Money], [User_GUIDDemo]) VALUES (6, N'ii        ', 0, 0, NULL, NULL, NULL, NULL, NULL)
114 GO
115 INSERT [dbo].[User_Info] ([User_ID], [User_Name], [User_Gender], [User_Age], [User_Address], [User_Birthday], [User_Remark], [User_Money], [User_GUIDDemo]) VALUES (7, N'ii        ', 1, 0, NULL, NULL, NULL, NULL, NULL)
116 GO
117 INSERT [dbo].[User_Info] ([User_ID], [User_Name], [User_Gender], [User_Age], [User_Address], [User_Birthday], [User_Remark], [User_Money], [User_GUIDDemo]) VALUES (8, N'mm        ', 0, 0, NULL, NULL, NULL, NULL, NULL)
118 GO
119 INSERT [dbo].[User_Info] ([User_ID], [User_Name], [User_Gender], [User_Age], [User_Address], [User_Birthday], [User_Remark], [User_Money], [User_GUIDDemo]) VALUES (9, N'tt        ', 1, 0, NULL, NULL, NULL, NULL, NULL)
120 GO
121 INSERT [dbo].[User_Info] ([User_ID], [User_Name], [User_Gender], [User_Age], [User_Address], [User_Birthday], [User_Remark], [User_Money], [User_GUIDDemo]) VALUES (10, N'gg        ', 0, 0, NULL, NULL, NULL, NULL, NULL)
122 GO
123 INSERT [dbo].[User_Info] ([User_ID], [User_Name], [User_Gender], [User_Age], [User_Address], [User_Birthday], [User_Remark], [User_Money], [User_GUIDDemo]) VALUES (11, N'dd        ', 1, 0, NULL, NULL, NULL, NULL, NULL)
124 GO
125 INSERT [dbo].[User_Info] ([User_ID], [User_Name], [User_Gender], [User_Age], [User_Address], [User_Birthday], [User_Remark], [User_Money], [User_GUIDDemo]) VALUES (12, N'rr        ', 1, 0, NULL, NULL, NULL, NULL, NULL)
126 GO
127 SET IDENTITY_INSERT [dbo].[User_Info] OFF
128 GO
129 USE [master]
130 GO
131 ALTER DATABASE [LJK_SQLServerDB] SET  READ_WRITE 
132 GO

 

posted @ 2019-11-01 09:36  MR_L先生  阅读(202)  评论(0)  编辑  收藏  举报