Backup, Restore, and Move the SSIS Catalog

Backup, Restore, and Move the SSIS Catalog

For full details go through the web page

http://technet.microsoft.com/en-us/library/hh213291.aspx

Cannot drop database [SSISDB] because it is currently in use.

USE [master]
GO

/****** Object:  Database [SSISDB]    Script Date: --/--/20-- 12:53:55 PM ******/

alter database [SSISDB] set single_user with rollback immediate


 drop database [SSISDB]

Restore database [SSISDB] 

USE [master]
GO

/****** Object:  Database [SSISDB]    Script Date: 3/10/2014 12:53:46 PM ******/
CREATE DATABASE [SSISDB]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SSISDB.mdf' , SIZE = 40960KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 LOG ON
( NAME = N'log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SSISDB.ldf' , SIZE = 3456KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [SSISDB] SET COMPATIBILITY_LEVEL = 110
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [SSISDB].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [SSISDB] SET ANSI_NULL_DEFAULT OFF
GO

ALTER DATABASE [SSISDB] SET ANSI_NULLS OFF
GO

ALTER DATABASE [SSISDB] SET ANSI_PADDING OFF
GO

ALTER DATABASE [SSISDB] SET ANSI_WARNINGS OFF
GO

ALTER DATABASE [SSISDB] SET ARITHABORT OFF
GO

ALTER DATABASE [SSISDB] SET AUTO_CLOSE OFF
GO

ALTER DATABASE [SSISDB] SET AUTO_CREATE_STATISTICS ON
GO

ALTER DATABASE [SSISDB] SET AUTO_SHRINK OFF
GO

ALTER DATABASE [SSISDB] SET AUTO_UPDATE_STATISTICS ON
GO

ALTER DATABASE [SSISDB] SET CURSOR_CLOSE_ON_COMMIT OFF
GO

ALTER DATABASE [SSISDB] SET CURSOR_DEFAULT  GLOBAL
GO

ALTER DATABASE [SSISDB] SET CONCAT_NULL_YIELDS_NULL OFF
GO

ALTER DATABASE [SSISDB] SET NUMERIC_ROUNDABORT OFF
GO

ALTER DATABASE [SSISDB] SET QUOTED_IDENTIFIER OFF
GO

ALTER DATABASE [SSISDB] SET RECURSIVE_TRIGGERS OFF
GO

ALTER DATABASE [SSISDB] SET  DISABLE_BROKER
GO

ALTER DATABASE [SSISDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO

ALTER DATABASE [SSISDB] SET DATE_CORRELATION_OPTIMIZATION OFF
GO

ALTER DATABASE [SSISDB] SET TRUSTWORTHY OFF
GO

ALTER DATABASE [SSISDB] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO

ALTER DATABASE [SSISDB] SET PARAMETERIZATION SIMPLE
GO

ALTER DATABASE [SSISDB] SET READ_COMMITTED_SNAPSHOT OFF
GO

ALTER DATABASE [SSISDB] SET HONOR_BROKER_PRIORITY OFF
GO

ALTER DATABASE [SSISDB] SET RECOVERY FULL
GO

ALTER DATABASE [SSISDB] SET  MULTI_USER
GO

ALTER DATABASE [SSISDB] SET PAGE_VERIFY CHECKSUM
GO

ALTER DATABASE [SSISDB] SET DB_CHAINING OFF
GO

ALTER DATABASE [SSISDB] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO

ALTER DATABASE [SSISDB] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO

ALTER DATABASE [SSISDB] SET  READ_WRITE
GO