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]