sql - How to export into a file all records from the tables of a database ? -

this question has answer here:

• script data sql server database 3 answers

i have lot of records stored multiple tables in database , example contosouniversity .

i want export records file , , restore time want .

any idea how in sql server 2012 ?

please note don't want code of creating db , meaning don't want :

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                 delete officeassignment         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                 delete person 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]                          select courseid, title, credits, departmentid 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             select @name = name department       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                         select enrollmentid, grade, courseid, studentid dbo.studentgrade             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                  insert dbo.person (lastname,                       firstname,                       hiredate)          values (@lastname,               @firstname,               @hiredate);          select scope_identity() 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)                 insert dbo.officeassignment (instructorid, location)         values (@instructorid, @location);         if @@rowcount > 0         begin             select [timestamp] officeassignment                  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                 insert dbo.person (lastname,                      firstname,                      hiredate,                      enrollmentdate)         values (@lastname,              @firstname,              @hiredate,              @enrollmentdate);         select scope_identity() 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                  insert dbo.person (lastname,                       firstname,                       enrollmentdate)          values (@lastname,               @firstname,               @enrollmentdate);          select scope_identity() 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                  update person set lastname=@lastname,                   firstname=@firstname,                  hiredate=@hiredate          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                 update officeassignment set location=@location          instructorid=@instructorid , [timestamp]=@origtimestamp;         if @@rowcount > 0         begin             select [timestamp] officeassignment                  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                 update person set lastname=@lastname,                  firstname=@firstname,                 hiredate=@hiredate,                 enrollmentdate=@enrollmentdate         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                  update person set lastname=@lastname,                   firstname=@firstname,                  enrollmentdate=@enrollmentdate          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]  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]  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]  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]  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]  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]  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]  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]  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 want records in tables . .

you have 2 simple option,

1. use database scripting.

2. use export/import wizard.

where find advance scripting options

to select data when generating scripts