转自:http://blog.csdn.net/claro/article/details/6449824
三步10分钟搞定数据库版本的降迁 (将SQL2008R2降为SQL2005版本)
转载原文,并注明出处!虽无多少技术含量,毕竟是作者心血原创,希望理解。
前思后想仍觉得实战数据库版本的降迁 一文中的方式不仅老土而且低效,故有了下文三步搞定数据库从MSSQL2008R2 高版本降迁至SQL2005低版本。 整个过程如果思路清晰,数据量小,不过就是10分钟的事,效率提高很多。
我们这里仍然用上一篇的案例,即Discuz!NT 3.6.519 RC的后台数据库,这里需要利用一个免费的数据库对象同步工具,实际上整个操作过程中,这一步是最耗时低效,检查发现用OpenDBDiff和SQL-DBDiff可以高效解决这个问题。
首先,MSSQL2008R2有源数据库dnt2_db ,需要转到MSSQL2005目标数据库dnt2_dbNew中。
第一步 在低版本数据库中新建目标库。
案例中SQL2005新建数据库T-SQL:
- ----/****** Object: Database [dnt2_dbNew] ******/
- CREATE DATABASE [dnt2_dbNew] ON PRIMARY
- ( NAME = N'dnt2_db', FILENAME = N'D:/Program Files/bbs/bbs3.6/dnt2_db .mdf' , SIZE = 15552KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
- LOG ON
- ( NAME = N'dnt2_db _log', FILENAME = N'D:/Program Files/bbs/bbs3.6/dnt2_db _log.ldf' , SIZE = 530112KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
- GO
- EXEC dbo.sp_dbcmptlevel @dbname=N'dnt2_dbNew', @new_cmptlevel=90
- GO
- IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
- begin
- EXEC [dnt2_dbNew].[dbo].[sp_fulltext_database] @action = 'enable'
- end
- GO
- ALTER DATABASE [dnt2_dbNew] SET ANSI_NULL_DEFAULT OFF
- GO
- ALTER DATABASE [dnt2_dbNew] SET ANSI_NULLS OFF
- GO
- ALTER DATABASE [dnt2_dbNew] SET ANSI_PADDING OFF
- GO
- ALTER DATABASE [dnt2_dbNew] SET ANSI_WARNINGS OFF
- GO
- ALTER DATABASE [dnt2_dbNew] SET ARITHABORT OFF
- GO
- ALTER DATABASE [dnt2_dbNew] SET AUTO_CLOSE OFF
- GO
- ALTER DATABASE [dnt2_dbNew] SET AUTO_CREATE_STATISTICS ON
- GO
- ALTER DATABASE [dnt2_dbNew] SET AUTO_SHRINK OFF
- GO
- ALTER DATABASE [dnt2_dbNew] SET AUTO_UPDATE_STATISTICS ON
- GO
- ALTER DATABASE [dnt2_dbNew] SET CURSOR_CLOSE_ON_COMMIT OFF
- GO
- ALTER DATABASE [dnt2_dbNew] SET CURSOR_DEFAULT GLOBAL
- GO
- ALTER DATABASE [dnt2_dbNew] SET CONCAT_NULL_YIELDS_NULL OFF
- GO
- ALTER DATABASE [dnt2_dbNew] SET NUMERIC_ROUNDABORT OFF
- GO
- ALTER DATABASE [dnt2_dbNew] SET QUOTED_IDENTIFIER OFF
- GO
- ALTER DATABASE [dnt2_dbNew] SET RECURSIVE_TRIGGERS OFF
- GO
- ALTER DATABASE [dnt2_dbNew] SET DISABLE_BROKER
- GO
- ALTER DATABASE [dnt2_dbNew] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
- GO
- ALTER DATABASE [dnt2_dbNew] SET DATE_CORRELATION_OPTIMIZATION OFF
- GO
- ALTER DATABASE [dnt2_dbNew] SET TRUSTWORTHY OFF
- GO
- ALTER DATABASE [dnt2_dbNew] SET ALLOW_SNAPSHOT_ISOLATION OFF
- GO
- ALTER DATABASE [dnt2_dbNew] SET PARAMETERIZATION SIMPLE
- GO