2018-05-25  294 views 评论

SQL Server 使用SQL文件导入还原数据库小技巧 解决大部分异常

SQL Server文件存储的格式是mdf,直接附件虽然好用,但是存在权限问题和版本问题,会比较麻烦。

所以实际过程中更多的是导出低版本的SQL文件,那样基本上所有高版本的都能兼容了。

但是导入SQL的时候也会有一些问题,现在教大家一些小经验:

1、SQL文件的格式大致如下,导出选项不同内容也不同,以下的sql脚本导出比较复杂:

USE [master]
GO
/****** Object:  Database [TableBatch]    Script Date: 2018-05-25 22:37:56 ******/
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'TableBatch')
BEGIN
CREATE DATABASE [TableBatch] ON  PRIMARY 
( NAME = N'TableBatch', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\TableBatch.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'TableBatch_log', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\TableBatch_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
END

GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [TableBatch].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [TableBatch] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [TableBatch] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [TableBatch] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [TableBatch] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [TableBatch] SET ARITHABORT OFF 
GO
ALTER DATABASE [TableBatch] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [TableBatch] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [TableBatch] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [TableBatch] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [TableBatch] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [TableBatch] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [TableBatch] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [TableBatch] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [TableBatch] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [TableBatch] SET  DISABLE_BROKER 
GO
ALTER DATABASE [TableBatch] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [TableBatch] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [TableBatch] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [TableBatch] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [TableBatch] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [TableBatch] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [TableBatch] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [TableBatch] SET RECOVERY FULL 
GO
ALTER DATABASE [TableBatch] SET  MULTI_USER 
GO
ALTER DATABASE [TableBatch] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [TableBatch] SET DB_CHAINING OFF 
GO
EXEC sys.sp_db_vardecimal_storage_format N'TableBatch', N'ON'
GO
USE [TableBatch]
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;
GO
USE [TableBatch]
GO
/****** Object:  Table [dbo].[TableData]    Script Date: 2018-05-25 22:37:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableData]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TableData](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [Name1] [varchar](64) NOT NULL,
  [Name2] [bit] NOT NULL,
  [Name3] [datetime] NOT NULL,
  [Name4] [varchar](64) NOT NULL,
  [Rate] [decimal](5, 0) NOT NULL,
  [Name6] [varchar](64) NOT NULL,
  [Name7] [varchar](64) NOT NULL,
  [CreateTime] [datetime] NULL,
  [Category] [varchar](12) NOT NULL,
  [Link] [varchar](64) NULL,
  [Days] [int] NULL,
 CONSTRAINT [PK__TableDat__3214EC277F60ED59] PRIMARY KEY CLUSTERED 
(
  [ID] 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].[TableData] ON 

GO
INSERT [dbo].[TableData] ([ID], [Name1], [Name2], [Name3], [Name4], [Rate], [Name6], [Name7], [CreateTime], [Category], [Link], [Days]) VALUES (1, N'ABC1', 1, CAST(N'2018-04-28T00:00:00.000' AS DateTime), N'Jony', CAST(10 AS Decimal(5, 0)), N'2', N'test', CAST(N'2018-05-25T22:21:11.493' AS DateTime), N'cat1', N'www', 0)
GO
INSERT [dbo].[TableData] ([ID], [Name1], [Name2], [Name3], [Name4], [Rate], [Name6], [Name7], [CreateTime], [Category], [Link], [Days]) VALUES (2, N'ABC2', 0, CAST(N'2018-05-25T00:00:00.000' AS DateTime), N'Sunny', CAST(1 AS Decimal(5, 0)), N'1', N'haha', CAST(N'2018-05-25T22:21:07.063' AS DateTime), N'cat1', N'www', 1)
GO
INSERT [dbo].[TableData] ([ID], [Name1], [Name2], [Name3], [Name4], [Rate], [Name6], [Name7], [CreateTime], [Category], [Link], [Days]) VALUES (3, N'ABC2', 0, CAST(N'2018-05-25T00:00:00.000' AS DateTime), N'Sunny', CAST(1 AS Decimal(5, 0)), N'1', N'haha', CAST(N'2018-05-25T22:21:10.903' AS DateTime), N'cat1', N'www', 1)
GO
INSERT [dbo].[TableData] ([ID], [Name1], [Name2], [Name3], [Name4], [Rate], [Name6], [Name7], [CreateTime], [Category], [Link], [Days]) VALUES (4, N'ABC2', 0, CAST(N'2018-05-25T00:00:00.000' AS DateTime), N'Sunny', CAST(1 AS Decimal(5, 0)), N'1', N'haha', CAST(N'2018-05-25T22:21:11.097' AS DateTime), N'cat1', N'www', 1)
GO
INSERT [dbo].[TableData] ([ID], [Name1], [Name2], [Name3], [Name4], [Rate], [Name6], [Name7], [CreateTime], [Category], [Link], [Days]) VALUES (5, N'ABC2', 0, CAST(N'2018-05-25T00:00:00.000' AS DateTime), N'Sunny', CAST(1 AS Decimal(5, 0)), N'1', N'haha', CAST(N'2018-05-25T22:21:11.303' AS DateTime), N'cat1', N'www', 1)
GO
INSERT [dbo].[TableData] ([ID], [Name1], [Name2], [Name3], [Name4], [Rate], [Name6], [Name7], [CreateTime], [Category], [Link], [Days]) VALUES (6, N'ABC2', 0, CAST(N'2018-05-25T00:00:00.000' AS DateTime), N'Sunny', CAST(1 AS Decimal(5, 0)), N'1', N'haha', CAST(N'2018-05-25T22:21:11.493' AS DateTime), N'cat1', N'www', 1)
GO
SET IDENTITY_INSERT [dbo].[TableData] OFF
GO
USE [master]
GO
ALTER DATABASE [TableBatch] SET  READ_WRITE 
GO

Use Master是选择使用Master数据库,在master数据库下执行

IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'TableBatch') 是判断当前是否存在要添加的数据库

CREATE DATABASE [TableBatch] ON PRIMARY ( NAME = N'TableBatch', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\TableBatch.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ) LOG ON ( NAME = N'TableBatch_log', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\TableBatch_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )

这一段是创建数据库,包含数据库名,存放的文件地址,文件的大小,增长值等

这一段脚本需要需要将 FILENAME 改为自己的路径

创建数据库后就是Use 创建的数据库,随后Create Table 创建表格

可能会遇到的异常处理:

可以删除大部分报错的内容,只要保留CREATE TABLE这部分内容和数据导入的部分就可以,如果创建数据库就出错了,可以在SQL Server Manager的视图界面下先创建对应的数据库,随后Use创建的数据库名,然后把创建数据库的脚本,参数设置脚本全部删除即可,通常导出的Create table在不同版本的数据库下应该不会出错。

给我留言

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: