SQL数据库备份解决方案完整版

SQL数据库备份解决方案 命令备份：BACKUP DATABASE test to DISK ='C/dfasd.bak' 1、查询出指定数据库中的所有表名称 Select TABLE_NAMEFROM数据库名称.INFORMATION_SCHEMA.TABLESWhereTABLE_TYPE='BASE TABLE' 2、查询出指定表中的所有字段及精度 select a.

SQL数据库备份解决方案

命令备份：BACKUP DATABASE test to DISK ='C/dfasd.bak'

1、查询出指定数据库中的所有表名称

Select TABLE_NAMEFROM 数据库名称.INFORMATION_SCHEMA.TABLESWhere TABLE_TYPE='BASE TABLE'

2、查询出指定表中的所有字段及精度

select a.name,

case a.precision

when 0 then

case a.is_ansi_padded

when 1 then

convert(nvarchar(15),b.name+'('+convert(nvarchar(10),a.max_length)+')')

when 0 then

b.name

end

else

case a.scale

when 0 then

b.name

else

b.name+'('+convert(nvarchar(10),a.precision)+','+convert(nvarchar(10),a.scale)+')'

end

end

as typelengthfrom sys.columnsa left join sys.typesb on a.system_type_id=b.system_type_idand a.user_type_id=b.user_type_idwhere a.object_id=(select object_id from sys.objectswhere name ='表名');

3、查询出SQL中数据库中所有表的定义

SET ARITHABORTON

SET CONCAT_NULL_YIELDS_NULLON

SET QUOTED_IDENTIFIERON

SET ANSI_NULLSON

SET ANSI_PADDINGON

SET ANSI_WARNINGSON

SET NUMERIC_ROUNDABORTOFF

declare @crlf char(2)

SET @crlf=char(13)+char(10)

;WITHColumnDefs as

(

select TableObj=c.[object_id]

,ColSeq=c.column_id

,ColumnDef=quotename(c.Name)+' '

+case

whenc.is_computed=1then 'as '+coalesce(k.[definition],'')

+casewhen k.is_persisted=1then ' PERSISTED'+casewhen k.is_nullable=0then ' NOT NULL' else '' endelse '' end

elseDataType

+case

when DataType in('decimal','numeric')then '('+cast(c.precisionas varchar(10))+casewhen c.scale<>0then ','+cast(c.scaleas varchar(10))else '' end +')'

when DataType in('char','varchar','nchar','nvarchar','binary','varbinary')then '('+casewhen c.max_length=-1then 'max' else case whenDataType in('nchar','nvarchar')then cast(c.max_length/2as varchar(10))else cast(c.max_lengthas varchar(10))end end +')'

when DataType='float'and c.precision<>53then '('+cast(c.precisionas varchar(10))+')'

when DataType in('time','datetime2','datetimeoffset')and c.scale<>7then '('+cast(c.scaleas varchar(10))+')'

else ''

end

end

+casewhen c.is_identity=1then ' IDENTITY('+cast(IDENT_SEED(quotename(object_schema_name(c.[object_id]))+'.'+quotename(object_name(c.[object_id])))as varchar(30))+','+cast(ident_incr(quotename(object_schema_name(c.[object_id]))+'.'+quotename(object_name(c.[object_id])))as varchar(30))+')'else '' end

+casewhen c.is_rowguidcol=1then ' ROWGUIDCOL'else '' end

+casewhen c.xml_collection_id>0THEN' (CONTENT '+QUOTENAME(SCHEMA_NAME(x.SCHEMA_ID))+'.'+QUOTENAME(x.name)+')'ELSE '' end

+case

whenc.is_computed=0and UserDefinedFlag=0

thencase

when c.collation_name<>cast(databasepropertyex(db_name(),'collation')as nvarchar(128))

then ' COLLATE '+c.collation_name

else ''

end

else''

end

+casewhen c.is_computed=0then case when c.is_nullable=0then ' NOT' else '' end+' NULL'else '' end

+case

whenc.default_object_id>0

then' CONSTRAINT '+quotename(d.name)+' DEFAULT '+coalesce(d.[definition],'')

else''

end

from sys.columnsc

cross apply(

select DataType=type_name(c.user_type_id)

,UserDefinedFlag=case

whenc.system_type_id=c.user_type_id

then 0

else 1

end)F1

left joinsys.default_constraintsd ON c.default_object_id=d.[object_id]

left joinsys.computed_columnsk ON c.[object_id]=k.[object_id]

andc.column_id=k.column_id

left join sys.xml_schema_collectionsxON c.xml_collection_id= x.xml_collection_id

)

,IndexDefsas

(

select TableObj=i.[object_id]

,IxName=quotename(i.name)

,IxPKFlag=i.is_primary_key

,IxType=casewhen i.is_primary_key=1then 'PRIMARY KEY 'when i.is_unique=1then 'UNIQUE ' else '' end

+lower(type_desc)

,IxDef='('+IxColList+')'

+coalesce(' INCLUDE ('+IxInclList+')','')

,IxOpts=IxOptList

from sys.indexesi

left joinsys.statss ON i.index_id=s.stats_idand i.[object_id]=s.[object_id]

cross apply(

select stuff((selectcase when i.is_padded=1then ', PAD_INDEX=ON'else '' end

+casewheni.fill_factor<>0then ', FILLFACTOR='+cast(i.fill_factoras varchar(10))else '' end

+casewheni.ignore_dup_key=1then ', IGNORE_DUP_KEY=ON'else'' end

+casewhens.no_recompute=1then ',STATISTICS_RECOMPUTE=ON'else '' end

+casewheni.allow_row_locks=0then ', ALLOW_ROW_LOCKS=OFF'else'' end

+casewheni.allow_page_locks=0then ', ALLOW_PAGE_LOCKS=OFF'else'' end)

,1,2,''))F_IxOpts(IxOptList)

cross apply(

select stuff((select','+quotename(c.name)

+case

when ic.is_descending_key=1AND i.type<>3

then ' DESC'

WHEN ic.is_descending_key=0AND i.type<>3

THEN ' ASC'

ELSE ''

end

fromsys.index_columnsic

joinsys.columnsc ON ic.[object_id]=c.[object_id]

andic.column_id=c.column_id

whereic.[object_id]=i.[object_id]

andic.index_id=i.index_id

andic.is_included_column=0

orderbyic.key_ordinal

FORxmlpath(''),type).value('.','nvarchar(max)')

,1,1,''))F_IxCols(IxColList)

cross apply(

select stuff((select','+quotename(c.name)

fromsys.index_columnsic

joinsys.columnsc ON ic.[object_id]=c.[object_id]

andic.column_id=c.column_id

whereic.[object_id]=i.[object_id]

andic.index_id=i.index_id

andic.is_included_column=1

orderbyic.key_ordinal

FORxmlpath(''),type).value('.','nvarchar(max)')

,1,1,''))F_IxIncl(IxInclList)

where i.type_desc<>'HEAP'

)

,FKDefsas

(

select TableObj=f.parent_object_id

,FKName=quotename(f.name)

,FKRef=quotename(object_schema_name(f.referenced_object_id))+'.'

+quotename(object_name(f.referenced_object_id))

,FKColList=ParentColList

,FKRefList=RefColList

,FKDelOpt=casef.delete_referential_action

when1 then 'CASCADE'

when2 then 'SET NULL'

when3 then 'SET DEFAULT'

end

,FKUpdOpt=casef.update_referential_action

when1 then 'CASCADE'

when2 then 'SET NULL'

when3 then 'SET DEFAULT'

end

,FKNoRepl=f.is_not_for_replication

from sys.foreign_keysf

cross apply(

select stuff((select','+quotename(c.name)

fromsys.foreign_key_columnsk

joinsys.columnsc ON k.parent_object_id=c.[object_id]

and k.parent_column_id=c.column_id

wherek.constraint_object_id=f.[object_id]

orderbyconstraint_column_id

FORxmlpath(''),type).value('.','nvarchar(max)')

,1,1,''))F_Parent(ParentColList)

cross apply(

select stuff((select','+quotename(c.name)

fromsys.foreign_key_columnsk

joinsys.columnsc ON k.referenced_object_id=c.[object_id]

and k.referenced_column_id=c.column_id

wherek.constraint_object_id=f.[object_id]

orderbyconstraint_column_id

FORxmlpath(''),type).value('.','nvarchar(max)')

,1,1,''))F_Ref(RefColList)

)

select TableName

,[definition]

from sys.tablest

cross apply(

select TableName=quotename(object_schema_name(t.[object_id]))+'.'

+quotename(object_name(t.[object_id])))F_Name

cross apply(

select stuff((select@crlf+' ,'+ColumnDef

fromColumnDefs

whereTableObj=t.[object_id]

orderbyColSeq

FORxmlpath(''),type).value('.','nvarchar(max)')

,1,5,''))F_Cols(ColumnList)

cross apply(

select stuff((select@crlf+' ,CONSTRAINT '+quotename(name)+' CHECK '

+casewhen is_not_for_replication=1then'NOT FORREPLICATION ' else'' end

+coalesce([definition],'')

fromsys.check_constraints

whereparent_object_id=t.[object_id]

FORxmlpath(''),type).value('.','nvarchar(max)')

,1,2,''))F_Const(ChkConstList)

cross apply(

select stuff((select@crlf+' ,CONSTRAINT '+IxName+' '+IxType+' '+IxDef+coalesce(' WITH ('+IxOpts+')','')

fromIndexDefs

whereTableObj=t.[object_id]

andIxPKFlag=1

FORxmlpath(''),type).value('.','nvarchar(max)')

,1,2,''))F_IxConst(IxConstList)

cross apply(

select stuff((select@crlf+' ,CONSTRAINT '+FKName+' FOREIGN KEY '+'('+FKColList+')'+' REFERENCES '+FKRef+' ('+FKRefList+')'

+casewhen FKDelOpt is NOT NULLthen ' ON DELETE '+FKDelOptelse '' end

+casewhen FKUpdOpt is NOT NULLthen ' ON UPDATE '+FKUpdOptelse '' end

+casewhen FKNoRepl=1then ' NOT FOR REPLICATION'else'' end

fromFKDefs

whereTableObj=t.[object_id]

FORxmlpath(''),type).value('.','nvarchar(max)')

,1,2,''))F_Keys(FKConstList)

cross apply(

select stuff((select@crlf+'CREATE '+IxType+' INDEX '+IxName+' ON '+TableName+' '+IxDef+coalesce(' WITH ('+IxOpts+')','')

fromIndexDefs

whereTableObj=t.[object_id]

andIxPKFlag=0

FORxmlpath(''),type).value('.','nvarchar(max)')

,1,2,''))F_Indexes(IndexList)

cross apply(

select [definition]=(select'CREATE TABLE '+TableName+@crlf+'('+@crlf+' '+ColumnList+coalesce(@crlf+ChkConstList,'')+coalesce(@crlf+IxConstList,'')+coalesce(@crlf+FKConstList,'')+@crlf+')'+coalesce(@crlf+IndexList,'')+@crlf

FOR xmlpath(''),type).value('.','nvarchar(max)'))F_Link

3、查询出SQL中数据库中所有视图，函数，存储过程触发器脚本

SELECT QUOTENAME(object_schema_name(m.object_id))+'.'+QUOTENAME(object_name(m.object_id))AS [name],o.type,m.definition

FROM sys.sql_modulesm INNER JOIN sys.objectso ON m.object_id= o.object_id

4、查询出SQL中数据库中某个表中数据的Insert语句

set nocounton

declare @table_name varchar(100)

declare @table_full_name varchar(100)

declare @sql nvarchar(max)

declare @sqlvalues nvarchar(max)

declare @identity int

DECLARE S_Cursor CURSOR FOR

SELECT o.nameasname, '[' + s.name + '].[' + o.name + ']' as full_name

FROM sys.objectso inner join sys.schemass on o.schema_id= s.schema_id

where o.name='Product'

and s.name='dbo'

and o.type='U'

order byo.name

OPEN S_Cursor

FETCH NEXTFROM S_Cursor INTO @table_name,@table_full_name

WHILE @@FETCH_STATUS = 0

BEGIN

set @sql= ' ('

set @sqlvalues= 'values (''+'

set @identity= 0

select @sqlvalues = @sqlvalues+ col + ' + '','' + ',@sql = @sql + '[' + name + '],', @identity= @identity + is_identity

from (selectcase

when t.name = 'varchar' then'case when ['+ c.name +'] is null Then ''null'' Else '+''''''''' + ' + 'replace(['+c.name+'],'''''''','''''''''''')' + '+'''''''''+' End'

when t.name = 'nvarchar' Then 'case When ['+ c.name +'] Is Null Then ''null'' Else '+'''N'''''' + ' + 'replace(['+ c.name+'],'''''''','''''''''''')' + '+'''''''''+' End'

when t.name = 'char' Then 'case When ['+ c.name +'] Is Null Then ''null'' Else '+''''''''' + ' + 'cast(Replace(['+ c.name+'],'''''''','''''''''''') As Char(' + Cast(c.max_length As Varchar) + '))+'''''''''+' End'

when t.name = 'nchar' Then 'case When ['+ c.name +'] Is Null Then ''null'' Else '+'''N'''''' + ' + 'cast(Replace(['+ c.name+'],'''''''','''''''''''') As Char(' + Cast(c.max_length As Varchar) + '))+'''''''''+' End'

when t.name = 'datetime' Then 'case When ['+ c.name +'] Is Null Then ''null'' Else '+''''''''' + ' + 'convert(char(23),['+c.name+ '],121)'+ '+'''''''''+' End'

when t.name = 'smalldatetime' Then 'case When ['+ c.name +'] Is Null Then ''null'' Else '+''''''''' + ' + 'convert(char(23),['+c.name+ '],120)'+ '+'''''''''+' End'

when t.name in('int','smallint','tinyint')Then 'case When ['+ c.name +'] Is Null Then ''null'' Else ' + 'cast(['+ c.name + '] As Varchar)'+' End'

when t.name = 'uniqueidentifier' Then'case When ['+ c.name +'] Is Null Then ''null'' Else '+''''''''' + ' + 'convert(char(36),['+c.name+ '])'+ '+'''''''''+' End'

--whent.name='ntext' Then 'case When ['+c.name+'] Is Null Then ''null'' Else '+'''N'''''' + ' + 'replace(['+ c.name+'],'''''''','''''''''''')' + '+'''''''''+' End'

else 'case When ['+ c.name +'] Is Null Then ''null'' Else ' +'''N'''''' + ' + 'cast(['+ c.name + '] As nvarchar(4000))'+ '+'''''''''+' End'

End as col

,c.nameas name

,c.column_idas column_id

,c.is_identityas is_identity

from sys.columnsc

inner join sys.typest on c.system_type_id= t.system_type_idand c.user_type_id= t.user_type_id

inner join sys.objectso on o.object_id= c.object_id

where o.type = 'U'

and o.name= @table_name)t

order bycolumn_id

set @sql ='select ''set identity_insert'+@table_full_name+' on Insert Into '+ @table_full_name + Left(@Sql,Len(@Sql)-1)+') ' + Left(@Sqlvalues,Len(@Sqlvalues)-4) + ')'' From '+@table_full_name

exec(@sql)

FETCH NEXTFROM S_Cursor INTO @table_name,@table_full_name

END

CLOSE S_Cursor

DEALLOCATE S_Cursor