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

SQL数据库备份解决方案

 

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

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

Select TABLE_NAMEFROM数据库名称.INFORMATION_SCHEMA.TABLESWhereTABLE_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 typelengthfromsys.columnsaleftjoin sys.typesbona.system_type_id=b.system_type_idanda.user_type_id=b.user_type_idwherea.object_id=(selectobject_idfromsys.objectswherename='表名');

 

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)

 

;WITHColumnDefsas

(

  select TableObj=c.[object_id]

        ,ColSeq=c.column_id

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

                  +case

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

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

                     elseDataType

                         +case

                            when DataType in('decimal','numeric')then'('+cast(c.precisionasvarchar(10))+casewhenc.scale<>0then','+cast(c.scaleasvarchar(10))else''end +')'

                            when DataType in('char','varchar','nchar','nvarchar','binary','varbinary')then'('+casewhenc.max_length=-1then'max'else case whenDataTypein('nchar','nvarchar')thencast(c.max_length/2asvarchar(10))elsecast(c.max_lengthasvarchar(10))endend+')'

                            when DataType='float'andc.precision<>53then'('+cast(c.precisionasvarchar(10))+')'

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

                            else ''

                          end

                   end

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

                  +casewhenc.is_rowguidcol=1then' ROWGUIDCOL'else ''end

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

                  +case

                     whenc.is_computed=0andUserDefinedFlag=0

                     thencase

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

                            then ' COLLATE '+c.collation_name

                            else ''

                          end

                     else''

                   end

                  +casewhenc.is_computed=0thencasewhen 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_constraintsdONc.default_object_id=d.[object_id]

  left joinsys.computed_columnskONc.[object_id]=k.[object_id]

                                   andc.column_id=k.column_id

   left join  sys.xml_schema_collectionsxONc.xml_collection_id=x.xml_collection_id                           

)

,IndexDefsas

(

  select TableObj=i.[object_id]

        ,IxName=quotename(i.name)

        ,IxPKFlag=i.is_primary_key

        ,IxType=casewheni.is_primary_key=1then'PRIMARY KEY 'wheni.is_unique=1then'UNIQUE ' else '' end

               +lower(type_desc)

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

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

        ,IxOpts=IxOptList       

  from sys.indexesi

  left joinsys.statssONi.index_id=s.stats_idandi.[object_id]=s.[object_id]

  cross apply( 

    select stuff((selectcasewheni.is_padded=1then', PAD_INDEX=ON'else''end

                        +casewheni.fill_factor<>0then', FILLFACTOR='+cast(i.fill_factorasvarchar(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=1ANDi.type<>3

                           then ' DESC'

                           WHEN ic.is_descending_key=0ANDi.type<>3

                           THEN ' ASC'

                           ELSE ''

                         end

                  fromsys.index_columnsic

                  joinsys.columnscONic.[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.columnscONic.[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.columnscONk.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.columnscONk.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 '

                      +casewhenis_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+')'

                      +casewhenFKDelOptis NOT NULLthen' ON DELETE '+FKDelOptelse''end

                      +casewhenFKUpdOptis NOT NULLthen' ON UPDATE '+FKUpdOptelse''end

                      +casewhenFKNoRepl=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_modulesmINNERJOIN sys.objectso ONm.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.objectsoinnerjoin sys.schemassono.schema_id=s.schema_id

where o.name='Product'

and s.name='dbo'

and o.type='U'

order byo.name

 

OPEN S_Cursor

FETCH NEXTFROMS_CursorINTO @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.nameasname

    ,c.column_idascolumn_id

    ,c.is_identityasis_identity

   from sys.columnsc

   inner join sys.typestonc.system_type_id=t.system_type_idandc.user_type_id=t.user_type_id

   inner join sys.objectsoono.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 NEXTFROMS_CursorINTO @table_name,@table_full_name

END

CLOSE S_Cursor

DEALLOCATE S_Cursor

 个人博客：http://blog.amtemai.com