I have a lot of records that are stored into multiple tables in a database , for example ContosoUniversity .
I want to export all the records into a file , and restore it any time that I want .
Any idea how to do that in SQL SERVER 2012 ?
Please note that I don't want the code of creating the DB , meaning I don't want this :
USE [master]
GO
/****** Object: Database [ContosoUniversity] Script Date: 5/18/2014 7:46:01 PM ******/
CREATE DATABASE [ContosoUniversity]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'School', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\School.mdf' , SIZE = 3328KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'School_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\School_log.ldf' , SIZE = 3136KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
GO
ALTER DATABASE [ContosoUniversity] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [ContosoUniversity].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [ContosoUniversity] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [ContosoUniversity] SET ANSI_NULLS OFF
GO
ALTER DATABASE [ContosoUniversity] SET ANSI_PADDING OFF
GO
ALTER DATABASE [ContosoUniversity] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [ContosoUniversity] SET ARITHABORT OFF
GO
ALTER DATABASE [ContosoUniversity] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [ContosoUniversity] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [ContosoUniversity] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [ContosoUniversity] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [ContosoUniversity] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [ContosoUniversity] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [ContosoUniversity] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [ContosoUniversity] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [ContosoUniversity] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [ContosoUniversity] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [ContosoUniversity] SET DISABLE_BROKER
GO
ALTER DATABASE [ContosoUniversity] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [ContosoUniversity] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [ContosoUniversity] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [ContosoUniversity] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [ContosoUniversity] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [ContosoUniversity] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [ContosoUniversity] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [ContosoUniversity] SET RECOVERY SIMPLE
GO
ALTER DATABASE [ContosoUniversity] SET MULTI_USER
GO
ALTER DATABASE [ContosoUniversity] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [ContosoUniversity] SET DB_CHAINING OFF
GO
ALTER DATABASE [ContosoUniversity] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [ContosoUniversity] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
EXEC sys.sp_db_vardecimal_storage_format N'ContosoUniversity', N'ON'
GO
USE [ContosoUniversity]
GO
/****** Object: StoredProcedure [dbo].[DeleteOfficeAssignment] Script Date: 5/18/2014 7:46:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DeleteOfficeAssignment]
@InstructorID int
AS
DELETE FROM OfficeAssignment
WHERE InstructorID=@InstructorID;
GO
/****** Object: StoredProcedure [dbo].[DeletePerson] Script Date: 5/18/2014 7:46:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DeletePerson]
@PersonID int
AS
DELETE FROM Person WHERE PersonID = @PersonID;
GO
/****** Object: StoredProcedure [dbo].[GetCourses] Script Date: 5/18/2014 7:46:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetCourses]
AS
SELECT CourseID, Title, Credits, DepartmentID FROM dbo.Course
GO
/****** Object: StoredProcedure [dbo].[GetDepartmentName] Script Date: 5/18/2014 7:46:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetDepartmentName]
@ID int,
@Name nvarchar(50) OUTPUT
AS
SELECT @Name = Name FROM Department
WHERE DepartmentID = @ID
GO
/****** Object: StoredProcedure [dbo].[GetStudentGrades] Script Date: 5/18/2014 7:46:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetStudentGrades]
@StudentID int
AS
SELECT EnrollmentID, Grade, CourseID, StudentID FROM dbo.StudentGrade
WHERE StudentID = @StudentID
GO
/****** Object: StoredProcedure [dbo].[InsertInstructor] Script Date: 5/18/2014 7:46:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InsertInstructor]
@LastName nvarchar(50),
@FirstName nvarchar(50),
@HireDate datetime
AS
INSERT INTO dbo.Person (LastName,
FirstName,
HireDate)
VALUES (@LastName,
@FirstName,
@HireDate);
SELECT SCOPE_IDENTITY() as NewPersonID;
GO
/****** Object: StoredProcedure [dbo].[InsertOfficeAssignment] Script Date: 5/18/2014 7:46:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InsertOfficeAssignment]
@InstructorID int,
@Location nvarchar(50)
AS
INSERT INTO dbo.OfficeAssignment (InstructorID, Location)
VALUES (@InstructorID, @Location);
IF @@ROWCOUNT > 0
BEGIN
SELECT [Timestamp] FROM OfficeAssignment
WHERE InstructorID=@InstructorID;
END
GO
/****** Object: StoredProcedure [dbo].[InsertPerson] Script Date: 5/18/2014 7:46:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InsertPerson]
@LastName nvarchar(50),
@FirstName nvarchar(50),
@HireDate datetime,
@EnrollmentDate datetime
AS
INSERT INTO dbo.Person (LastName,
FirstName,
HireDate,
EnrollmentDate)
VALUES (@LastName,
@FirstName,
@HireDate,
@EnrollmentDate);
SELECT SCOPE_IDENTITY() as NewPersonID;
GO
/****** Object: StoredProcedure [dbo].[InsertStudent] Script Date: 5/18/2014 7:46:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InsertStudent]
@LastName nvarchar(50),
@FirstName nvarchar(50),
@EnrollmentDate datetime
AS
INSERT INTO dbo.Person (LastName,
FirstName,
EnrollmentDate)
VALUES (@LastName,
@FirstName,
@EnrollmentDate);
SELECT SCOPE_IDENTITY() as NewPersonID;
GO
/****** Object: StoredProcedure [dbo].[UpdateInstructor] Script Date: 5/18/2014 7:46:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UpdateInstructor]
@PersonID int,
@LastName nvarchar(50),
@FirstName nvarchar(50),
@HireDate datetime
AS
UPDATE Person SET LastName=@LastName,
FirstName=@FirstName,
HireDate=@HireDate
WHERE PersonID=@PersonID;
GO
/****** Object: StoredProcedure [dbo].[UpdateOfficeAssignment] Script Date: 5/18/2014 7:46:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UpdateOfficeAssignment]
@InstructorID int,
@Location nvarchar(50),
@OrigTimestamp timestamp
AS
UPDATE OfficeAssignment SET Location=@Location
WHERE InstructorID=@InstructorID AND [Timestamp]=@OrigTimestamp;
IF @@ROWCOUNT > 0
BEGIN
SELECT [Timestamp] FROM OfficeAssignment
WHERE InstructorID=@InstructorID;
END
GO
/****** Object: StoredProcedure [dbo].[UpdatePerson] Script Date: 5/18/2014 7:46:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UpdatePerson]
@PersonID int,
@LastName nvarchar(50),
@FirstName nvarchar(50),
@HireDate datetime,
@EnrollmentDate datetime
AS
UPDATE Person SET LastName=@LastName,
FirstName=@FirstName,
HireDate=@HireDate,
EnrollmentDate=@EnrollmentDate
WHERE PersonID=@PersonID;
GO
/****** Object: StoredProcedure [dbo].[UpdateStudent] Script Date: 5/18/2014 7:46:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UpdateStudent]
@PersonID int,
@LastName nvarchar(50),
@FirstName nvarchar(50),
@EnrollmentDate datetime
AS
UPDATE Person SET LastName=@LastName,
FirstName=@FirstName,
EnrollmentDate=@EnrollmentDate
WHERE PersonID=@PersonID;
GO
/****** Object: Table [dbo].[Course] Script Date: 5/18/2014 7:46:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Course](
[CourseID] [int] NOT NULL,
[Title] [nvarchar](100) NOT NULL,
[Credits] [int] NOT NULL,
[DepartmentID] [int] NOT NULL,
CONSTRAINT [PK_School.Course] PRIMARY KEY CLUSTERED
(
[CourseID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[CourseInstructor] Script Date: 5/18/2014 7:46:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CourseInstructor](
[CourseID] [int] NOT NULL,
[PersonID] [int] NOT NULL,
CONSTRAINT [PK_CourseInstructor] PRIMARY KEY CLUSTERED
(
[CourseID] ASC,
[PersonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Department] Script Date: 5/18/2014 7:46:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Department](
[DepartmentID] [int] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Budget] [money] NOT NULL,
[StartDate] [datetime] NOT NULL,
[Administrator] [int] NULL,
CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED
(
[DepartmentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[OfficeAssignment] Script Date: 5/18/2014 7:46:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OfficeAssignment](
[InstructorID] [int] NOT NULL,
[Location] [nvarchar](50) NOT NULL,
[Timestamp] [timestamp] NOT NULL,
CONSTRAINT [PK_OfficeAssignment] PRIMARY KEY CLUSTERED
(
[InstructorID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[OnlineCourse] Script Date: 5/18/2014 7:46:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OnlineCourse](
[CourseID] [int] NOT NULL,
[URL] [nvarchar](100) NOT NULL,
CONSTRAINT [PK_OnlineCourse] PRIMARY KEY CLUSTERED
(
[CourseID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[OnsiteCourse] Script Date: 5/18/2014 7:46:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OnsiteCourse](
[CourseID] [int] NOT NULL,
[Location] [nvarchar](50) NOT NULL,
[Days] [nvarchar](50) NOT NULL,
[Time] [smalldatetime] NOT NULL,
CONSTRAINT [PK_OnsiteCourse] PRIMARY KEY CLUSTERED
(
[CourseID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Person] Script Date: 5/18/2014 7:46:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Person](
[PersonID] [int] IDENTITY(1,1) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[HireDate] [datetime] NULL,
[EnrollmentDate] [datetime] NULL,
CONSTRAINT [PK_School.Student] PRIMARY KEY CLUSTERED
(
[PersonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[StudentGrade] Script Date: 5/18/2014 7:46:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StudentGrade](
[EnrollmentID] [int] IDENTITY(1,1) NOT NULL,
[CourseID] [int] NOT NULL,
[StudentID] [int] NOT NULL,
[Grade] [decimal](3, 2) NULL,
CONSTRAINT [PK_StudentGrade] PRIMARY KEY CLUSTERED
(
[EnrollmentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Course] WITH CHECK ADD CONSTRAINT [FK_Course_Department] FOREIGN KEY([DepartmentID])
REFERENCES [dbo].[Department] ([DepartmentID])
GO
ALTER TABLE [dbo].[Course] CHECK CONSTRAINT [FK_Course_Department]
GO
ALTER TABLE [dbo].[CourseInstructor] WITH CHECK ADD CONSTRAINT [FK_CourseInstructor_Course] FOREIGN KEY([CourseID])
REFERENCES [dbo].[Course] ([CourseID])
GO
ALTER TABLE [dbo].[CourseInstructor] CHECK CONSTRAINT [FK_CourseInstructor_Course]
GO
ALTER TABLE [dbo].[CourseInstructor] WITH CHECK ADD CONSTRAINT [FK_CourseInstructor_Person] FOREIGN KEY([PersonID])
REFERENCES [dbo].[Person] ([PersonID])
GO
ALTER TABLE [dbo].[CourseInstructor] CHECK CONSTRAINT [FK_CourseInstructor_Person]
GO
ALTER TABLE [dbo].[OfficeAssignment] WITH CHECK ADD CONSTRAINT [FK_OfficeAssignment_Person] FOREIGN KEY([InstructorID])
REFERENCES [dbo].[Person] ([PersonID])
GO
ALTER TABLE [dbo].[OfficeAssignment] CHECK CONSTRAINT [FK_OfficeAssignment_Person]
GO
ALTER TABLE [dbo].[OnlineCourse] WITH CHECK ADD CONSTRAINT [FK_OnlineCourse_Course] FOREIGN KEY([CourseID])
REFERENCES [dbo].[Course] ([CourseID])
GO
ALTER TABLE [dbo].[OnlineCourse] CHECK CONSTRAINT [FK_OnlineCourse_Course]
GO
ALTER TABLE [dbo].[OnsiteCourse] WITH CHECK ADD CONSTRAINT [FK_OnsiteCourse_Course] FOREIGN KEY([CourseID])
REFERENCES [dbo].[Course] ([CourseID])
GO
ALTER TABLE [dbo].[OnsiteCourse] CHECK CONSTRAINT [FK_OnsiteCourse_Course]
GO
ALTER TABLE [dbo].[StudentGrade] WITH CHECK ADD CONSTRAINT [FK_StudentGrade_Course] FOREIGN KEY([CourseID])
REFERENCES [dbo].[Course] ([CourseID])
GO
ALTER TABLE [dbo].[StudentGrade] CHECK CONSTRAINT [FK_StudentGrade_Course]
GO
ALTER TABLE [dbo].[StudentGrade] WITH CHECK ADD CONSTRAINT [FK_StudentGrade_Student] FOREIGN KEY([StudentID])
REFERENCES [dbo].[Person] ([PersonID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[StudentGrade] CHECK CONSTRAINT [FK_StudentGrade_Student]
GO
USE [master]
GO
ALTER DATABASE [ContosoUniversity] SET READ_WRITE
GO
What I want are the records in those tables . Thanks .
