批处理(bat)实现SQLServer数据库备份与还原
SQLServer常见问题处理(数据库的备份与还原操作)
数据库备份与还原操作一、备份数据库操作(1) 打开企业管理器，按下图依次打开，然后右击要备份的数据库，按下图选中备份数据库（图1-1 示）。
图1-1(2) 点击删除，然后点击“添加”按钮 (图1-2示)。
图1-2(3) 系统弹出“选择备份目的”窗口，在“文件名(F)：”栏中输入或选择备份路径(图1-3示)。
图1-3(4) 系统弹出“备份设置位置”对话框，选择备份路径并键入备份文件名(如：选择D：\DataBase\，文件名为：fjjr_jwgl1db061113。
图1-4示)，并点击“确定”。
图1-4(5) 系统回到“SQL Server 备份”窗口，单击“确定”后，系统自动备份数据库，直到系统完成即可完成数据库备份操作。
图1-5二、还原数据库操作(1) 打开企业管理器，按下图依次打开，然后右击要备份的数据库，按下图选中“还原数据库(R)”（图2-1示）。
图2－1(2) 系统弹出“还原数据库”窗口，选择“从设备(M)”单选框(图2-2示)图2－2 (3) 单击“选择设备(E)...”按钮(图2-3示)。
图2－3(4) 系统弹出“选择还原设备”窗口，在此窗口中单击“添加(A)...”按钮(图2-4示)。
图2－4(5) 系统弹出“选择还原目的”窗口，在“文件名(F):”文本框中输入还原目的文件或单击“...”按钮(图2-5示)。
图2－5(6) 单击“...”后，系统弹出“备份设备位置”对话框，选择正确的备份数据库文件 (图2-6示)。
图2－6(7) 依次确定到“还原数据库”窗口，单击“选项”选项卡(图2-7示)图2－7(8) 确认“移至物理文件名”所在具体位置，并选中“在现有数据库上强制还原(F)”复选框，单击“确定”按钮，系统自动还数据数据库，等待直至数据还原完毕(图2-8示)图2－8。
SQLServer数据库的备份与还原以及在项目中是怎样去实现的
SQLServer数据库的备份与还原以及在项目中是怎样去实现的2012-03-13 11:39:21--备份的设备有2种(临时设备与永久设备) 注意：默认下的备份类型是完整备份--第一种：backup database Company to disk='d:\backup\1.bak'--临时设备/*假如这里不指定明确路径的话（如：backup database company to disk='backup\1.bak'），那么备份的数据库将会自动备份到系统指定的目录下：C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup*/--第二种：/*第一步首先建立永久备份设备（系统自带的存储过程）在master 数据库中就会找到如图1：*/--执行语句如：exec sp_addumpdevice 'disk','disk_company','D:\2.bak'--永久设备--执行结果就会出现如图2：--多了一个备份设备：disk_company--第二步：backup database company to disk_company with noinit --默认表示追加(不覆盖) --好了备份完成！--现在我来还原数据库(我用的是第一种方法备份的，因此我要第一种方法来还原) ，--原先的数据如图3：--通过我手动删除几个表后的数据库如图4:--执行语句：restore database Company from disk='d:\backup\1.bak' --注意备份到哪里去就要从还原哪里来--执行后会出现什么呢？请看错误消息：/*消息 3159，级别 16，状态 1，第 1 行尚未备份数据库 "company" 的日志尾部。
SQLServer——SQLServer备份与恢复
SQLServer——SQLServer备份与恢复1、动手备份2、动手还原3、T-SQL语句备份及还原BACKUP DATABASERESTORE FILELISTONLY FROM DISK代码示例-- 打开设置当前数据库use mastergo-- 默认创建数据库create database SQLDBgo-- 打开设置当前数据库use SQLDBgoSQLDB-- 在当前数据库创建表create table sqltable(empid char(20) not null primary key,empname char(8) not null,empbd datetime not null default getdate())go-- 将数据信息插入到表中insert into sqltable(empid,empname) values('101','张三') insert into sqltable(empid,empname) values('102','李四') insert into sqltable(empid,empname) values('103','王五') insert into sqltable(empid,empname) values('104','赵三') insert into sqltable(empid,empname) values('105','周北') go-- 查询输出表中所有的记录select *from sqltablego-- 将数据库备份到磁盘设备use SQLDBgobackup database SQLDBto disk='d:\SQLDB.bak'with format,medianame='SQLDB',name='full backup of SQLDB'go-- 还原数据库文件use mastergorestore database SQLDBss from disk='d:\SQLDB.bak' go。
SQLServer2019数据库备份与还原脚本（批量备份）
SQLServer2019数据库备份与还原脚本（批量备份）前⾔最近公司服务器到期，需要进⾏数据迁移，⽽数据库属于多⽽繁琐，通过图形化界⾯⼀个⼀个备份所需时间成本很⼤，所以想着写⼀个sql脚本来执⾏。
开始数据库单个备份数据库批量备份数据库还原数据库还原报错问题记录总结1.数据库单个备份图形化界⾯备份这⾥就不展⽰了，可以⾃⾏百度，下⾯直接贴代码USE MASTERIF EXISTS ( SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[BackupDataProc]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )DROP PROCEDURE BackupDataProcgocreate proc BackupDataProc@FullName Varchar(200)--⼊参（数据库名）asbeginDeclare @FileFlag varchar(50)Set @FileFlag='C:\myfile\database\'+@FullName+'.bak'--备份到哪个路径（C:\myfile\database\）根据⾃⼰需求来定BackUp DataBase @FullName To Disk=@FileFlag with init--核⼼代码endexec BackupDataProc xxx执⾏成功后便会⽣成⼀个.bak⽂件到指定⽂件夹中，如图2.数据库批量备份（时间有点长，请等待）USE MASTERif exists(SELECT * FROM sys.types WHERE name = 'AllDatabasesNameType')drop type AllDatabasesNameTypegocreate type AllDatabasesNameType as table--⾃定义表类型⽤于存储数据库名称(rowNum int ,name nvarchar(60),filename nvarchar(300))goIF EXISTS ( SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[BachBackupDataProc]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )DROP PROCEDURE BachBackupDataProcgocreate proc BachBackupDataProc@filePath nvarchar(300)--⼊参，备份时的⽬标路径asbeginDeclare @AllDatabasesName as AllDatabasesNameType --⽤于存储系统中的数据库名Declare @i int --循环变量insert into @AllDatabasesName(name,filename,rowNum) select name,filename,ROW_NUMBER() over(order by name) as rowNum from sysdatabases where name not in('master','tempdb','model','msdb') --赋值set @i =1--循环备份数据库while @i <= (select COUNT(*) from @AllDatabasesName)beginDeclare @FileFlag varchar(500)Declare @FullName varchar(50)Select @FullName =name from @AllDatabasesName where rowNum = @iSet @FileFlag=@filePath+@FullName+'.bak'BackUp DataBase @FullName To Disk=@FileFlag with initset @i = @i + 1endendexec BachBackupDataProc 'C:\myfile\database\'执⾏结果效果如下图：3.数据库还原IF EXISTS ( SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[ReductionProc]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )DROP PROCEDURE ReductionProcgocreate proc ReductionProc@Name nvarchar(200)--⼊参数据库名称asbeginDeclare @DiskName nvarchar(500)Declare @FileLogName nvarchar(100)Declare @FileFlagData nvarchar(500)Declare @FileFlagLog nvarchar(500)Set @FileLogName = @Name + '_log'Set @DiskName = 'C:\myfile\database\'+@Name+'.bak' ---（源）备份⽂件路径Set @FileFlagData='C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\'+@Name+'.mdf'---（⽬标）指定数据⽂件路径Set @FileFlagLog='C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\'+@FileLogName+'.ldf'---⽬标）指定⽇志⽂件路径RESTORE DATABASE @Name --为待还原库名FROM DISK = @DiskName ---备份⽂件名WITH MOVE @Name TO @FileFlagData, ---指定数据⽂件路径MOVE @FileLogName TO @FileFlagLog, ---指定⽇志⽂件路径STATS = 10, REPLACEendgoexec ReductionProc xxx执⾏后便能还原库（我是拿这三个库做测试，截的图可能没什么变化，你们可以尝试下）4.数据库还原报错问题记录当然还原的过程可能会遇到⼀些问题，⽐如：1.版本不⼀样2.SQL Sql 逻辑⽂件'XXXXX ' 不是数据库'YYY'的⼀部分。
SQLSERVER大数据库备份与还原
一、SQL数据库的备份：1、依次打开开始菜单→程序→Microsoft SQL Server 2008 →SQL Server Management Stud io →数据库：Dsideal_school_db既是我们需要备份的学籍数据库图（1）2、选择要备份的数据库“Dsideal_school_db”，点击鼠标右键→任务→备份，如图（2）：图（2）3、在打开的“备份数据库—Dsideal_school_db”对话框中，先点击删除，然后点击“添加”，如图（3）：图（3）4、在弹出的“选择备份目标”对话框中，如图（4）图（4）5、选择好备份的路径（如D盘的-学籍-数据库备份文件夹中），文件类型选择“所有文件”，“文件名”那个位置填写上您要备份的数据库的名字（最好在您备份的数据库的名字后面加上日期，以方便以后查找），之后连续点击“确定”按钮即可完成数据库的备份操作，如图（5）：图（5二、SQL数据库的还原：1、选择您要还原的数据库“Dsideal_school_db”，点击鼠标右键→任务→还原→数据库，如图（6）：图（6）2、在出现的“还原数据库—school_web_db”对话框中选择“源设备”，然后点击后面的“…”按钮，如图（7）：图（7）3、在出现的“指定备份”对话框中，点击“添加”按钮，如图（8）：图（8）4、找到数据库备份的路径，选择您所要还原的数据库“Dsideal_school_db”（注意：文件类型选择所有文件），然后连续“两次”点击“确定”按钮，如图（9）：图（9）5、在出现的“还原数据库—Dsideal_school_db”对话框中，勾选上“选择用户还原的备份集”下的数据库前的复选框，如图（10）图（10）6、然后选择“选项”，勾选上“覆盖现有数据库”，如图（11）：图（11）三、还原数据库问题解决方案在还原数据库“Dsideal_school_db”时，有时会遇见上图中的问题“因为数据库正在使用，所以无法获得对数据库的独占访问权”，此时我们可以按照以下步骤解决此问题1、右键数据库“Dsideal_school_db”，然后选择“属性”，如下图2、在出现的“数据库属性—Dsideal_school_db ”对话框中，选择“选项”，在“其他选项”下的“状态”中找到“限制访问”。
Bat脚本备份sqlserver表结构、存储过程、函数、指定表数据
Bat脚本备份sqlserver表结构、存储过程、函数、指定表数据Bat脚本备份sqlserver 表结构、存储过程、指定表数据：@echo offcd /d %~dp0::备份表结构、存储过程和部分配置表的数据set LogFile=report.logset servername="192.168.43.9"set DBname="TEST"set User="sa"set Password="123456*"echo Deployed Time: %date% %time% >> %LogFile%echo Server Name: %servername% >> %LogFile%echo DB Name: %DBname% >> %LogFile%set zip7=C:\Program Files\7-Zip\7z.exe::需要压缩的⽂件set Files=back\%date:~0,4%%date:~5,2%%date:~8,2%_*.sql::压缩后的⽂件名set curdate=%date:~0,4%-%date:~5,2%-%date:~8,2%echo -------------------------------备份表结构开始------------------------------------------------>> %LogFile%Sqlcmd -S %servername% -d %DBname% -U %User% -P %Password% -i table.sql -y 0 -u -X -o back\%date:~0,4%%date:~5,2%%date:~8,2%_table.sqlecho -------------------------------备份表结构结束------------------------------------------------>> %LogFile%echo -------------------------------备份表存储开始------------------------------------------------>> %LogFile%Sqlcmd -S %servername% -d %DBname% -U %User% -P %Password% -i usp.sql -y 0 -u -X -o back\%date:~0,4%%date:~5,2%%date:~8,2%_usp.sqlecho -------------------------------备份表存储结束------------------------------------------------>> %LogFile%echo -------------------------------备份HD_ZBMX_HZ数据开始------------------------------------------------>> %LogFile%Sqlcmd -S %servername% -d %DBname% -U %User% -P %Password% -i HD_ZBMX_HZ.sql -y 0 -u -X -o back\%date:~0,4%%date:~5,2%%date:~8,2%_HD_ZBMX_HZ_data.sql echo -------------------------------备份HD_ZBMX_HZ数据结束------------------------------------------------>> %LogFile%::echo "%Files%"::压缩"%zip7%" a -tzip "back\%curdate%.zip" "%Files%"::删除DEL /Q "%Files%"::删除超过30天的备份--start--FORFILES /P back\ /M *.zip -d -30 /c "cmd /c del @path"::pauseexit配置⽂件table.sqlset nocount ondeclare@tablenames varchar(max),@tablename varchar(max)beginselect @tablenames =''/*定义游标*/declare table_sql cursor forSELECT name FROM sysobjects where xtype='U' order by nameopen table_sqlfetch next from table_sql into @tablenamewhile @@FETCH_STATUS=0beginset @tablenames = @tablenames+@tablename+','--print '---1-->'+@tablenames-- EXEC sp_gettext 'Address_Base'fetch next from table_sql into @tablenameendclose table_sqldeallocate table_sqlEXEC sp_gettext @tablenamesend配置⽂件：usp.sqlset nocount ondeclare@tablenames varchar(max),@tablename varchar(max)beginselect @tablenames =''/*定义游标*/declare table_sql cursor forSELECT name FROM sysobjects where xtype='P' order by nameopen table_sqlfetch next from table_sql into @tablenamewhile @@FETCH_STATUS=0beginset @tablenames = @tablenames+@tablename+','--print '---1-->'+@tablenames-- EXEC sp_gettext 'Address_Base'fetch next from table_sql into @tablenameendclose table_sqldeallocate table_sqlEXEC sp_gettext @tablenamesend配置⽂件:HD_ZBMX_HZ.sqlset nocount ondeclare@tablenames varchar(max),@tablename varchar(max)beginexec UspOutputData 'HD_ZBMX_HZ'end对应库⾥需要部署的存储和函数：sp_gettextif exists (select1from sysobjects where id =object_id('sp_gettext') and type ='P')drop proc sp_gettextgocreate procedure[sp_gettext]@name VARCHAR(max) =NULL, @identity BIT=1, @index TINYINT=2-- 0不创建索引 1不创建表 2创建索引, @new BIT=0as/*[版本号]1.0.0.0.0[创建时间]2019.09.10[作者][版权][描述][功能说明]获取建表语句,存储语句[参数说明][返回值][结果集、排序][调⽤的usp]sp_gettext 'DIM_KSXX,DIM_YYXX'sp_gettext 'usp_dim_ksxx'[调⽤实例][修改记录]*/SET ARITHABORT ON;SET CONCAT_NULL_YIELDS_NULL ON;SET QUOTED_IDENTIFIER ON;SET ANSI_NULLS ON;SET ANSI_PADDING ON;SET ANSI_WARNINGS ON;SET NUMERIC_ROUNDABORT OFF;DECLARE@crlf CHAR(2);SET@crlf=CHAR(13) +CHAR(10);DECLARE@objid INT;DECLARE@results TABLE (definition NVARCHAR(max))DECLARE@objects TABLE (id VARCHAR(100), type CHAR(2));WITH db1(dbname)AS (SELECT VALUE AS dbnameFROM Split(@name,',')),db2 AS (SELECT--CASE WHEN CHARINDEX('.', dbname) = 0 AND CHARINDEX('[', dbname) = 0 THEN '[cn9c080].' + QUOTENAME(dbname)-- ELSE dbname-- END AS dbnameCASE WHEN o.[object_id]IS NULL AND tt.[name]IS NULL THEN'%'ELSE LTRIM(ISNULL(o.[object_id],tt.[type_table_object_id])) END AS object_id,CASE[db1].[dbname]WHEN'tables'THEN'U'WHEN'procs'THEN'P'elseo.[type]END typeFROM db1left JOIN sys.[objects] o ON (PARSENAME(db1.[dbname],1) = o.[name]OR OBJECT_ID('[cn9c080].'+QUOTENAME(dbname)) = o.[object_id])LEFT JOIN sys.table_types tt ON db1.dbname=)INSERT INTO@objectsSELECT*FROM db2 ;WITH ColumnDefsAS (SELECT TableObj = c.[object_id], ColSeq = c.column_id,ColumnDef =QUOTENAME() +''+CASE WHEN c.is_computed =1THEN'as '+COALESCE(k.[definition], '') +CASEWHEN k.is_persisted =1THEN' PERSISTED'+CASEWHEN k.is_nullable =0THEN' NOT NULL'ELSE''ENDELSE''ENDELSE DataType +CASE WHEN DataType IN ('decimal','numeric')THEN'('+CAST(c.precision AS VARCHAR(10)) +CASEWHEN c.scale <>0THEN','+CAST(c.scale AS VARCHAR(10))ELSE''END+')'WHEN DataType IN ('char','varchar','nchar','nvarchar','binary','varbinary')THEN'('+CASEWHEN c.max_length =-1THEN'max'ELSE CASEWHEN DataType IN ('nchar','nvarchar')THEN CAST(c.max_length /2AS VARCHAR(10))ELSE CAST(c.max_length AS VARCHAR(10))ENDEND+')'WHEN DataType ='float'AND c.precision<>53THEN'('+CAST(c.precision AS VARCHAR(10)) +')'WHEN DataType IN ('time','datetime2','datetimeoffset') AND c.scale <>7THEN'('+CAST(c.scale AS VARCHAR(10)) +')'ELSE''ENDEND+CASE WHEN c.is_identity =1AND@identity=1THEN' IDENTITY('+CAST(IDENT_SEED(QUOTENAME(OBJECT_SCHEMA_NAME(c.[object_id])) +'.'+QUOTENAME(OBJECT_NAME(c.[object_id]))) AS VARCHAR(30)) +ELSE''END+CASE WHEN c.is_rowguidcol =1THEN' ROWGUIDCOL'ELSE''END+CASE WHEN c.xml_collection_id >0THEN' (CONTENT '+QUOTENAME(SCHEMA_NAME(x.schema_id)) +'.'+QUOTENAME() +')'ELSE''END+CASE WHEN c.is_computed =0AND UserDefinedFlag =0THEN CASEWHEN c.collation_name <>CAST(DATABASEPROPERTYEX(DB_NAME(),'collation') AS NVARCHAR(128))THEN' COLLATE '+ c.collation_nameELSE''ENDELSE''END+CASEWHEN c.is_computed =0THEN CASEWHEN c.is_nullable =0THEN' NOT'ELSE''END+' NULL'ELSE''END+CASEWHEN c.default_object_id >0AND ISNULL(@new,0) =0THEN' CONSTRAINT '+QUOTENAME() +' DEFAULT '+COALESCE(d.[definition],'')WHEN c.default_object_id >0AND ISNULL(@new,0) =1THEN' DEFAULT '+COALESCE(d.[definition],'')ELSE''ENDFROM sys.columns cCROSS APPLY (SELECT DataType = TYPE_NAME(er_type_id), UserDefinedFlag =CASE WHEN c.system_type_id = er_type_id THEN0ELSE1END) F1LEFT JOIN sys.default_constraints d ON c.default_object_id = d.[object_id]LEFT JOIN puted_columns k ON c.[object_id]= k.[object_id]AND c.column_id = k.column_idLEFT JOIN sys.xml_schema_collections x ON c.xml_collection_id = x.xml_collection_id),IndexDefsAS (SELECT TableObj = i.[object_id], IxName =QUOTENAME(+CASE WHEN@new=1THEN'_'+LEFT(NEWID(),4) ELSE''end), IxPKFlag = i.is_primary_key, IxType =CASE WHEN i.is_primary_key =1THEN'PRIMARY KEY 'WHEN i.is_unique =1THEN'UNIQUE 'ELSE''END+LOWER(type_desc),IxDef ='('+ IxColList +')'+COALESCE(' INCLUDE ('+ IxInclList +')', ''),IxOpts = IxOptListFROM sys.indexes iLEFT JOIN sys.stats s ON i.index_id = s.stats_id AND i.[object_id]= s.[object_id]CROSS APPLY (SELECT STUFF((SELECT CASE WHEN i.is_padded =1THEN', PAD_INDEX=ON'ELSE''END+CASE WHEN i.fill_factor <>0THEN', FILLFACTOR='+CAST(i.fill_factor AS VARCHAR(10))ELSE''END+CASE WHEN i.ignore_dup_key =1THEN', IGNORE_DUP_KEY=ON'ELSE''END+CASE WHEN s.no_recompute =1THEN', STATISTICS_RECOMPUTE=ON'ELSE''END+CASE WHEN i.allow_row_locks =0THEN', ALLOW_ROW_LOCKS=OFF'ELSE''END+CASE WHEN i.allow_page_locks =0THEN', ALLOW_PAGE_LOCKS=OFF'ELSE''END), 1, 2, '')) F_IxOpts (IxOptList)CROSS APPLY (SELECT STUFF((SELECT','+QUOTENAME() +CASE WHEN ic.is_descending_key =1AND i.type <>3THEN' DESC'WHEN ic.is_descending_key =0AND i.type <>3THEN' ASC'ELSE''ENDFROM sys.index_columns icJOIN sys.columns c ON ic.[object_id]= c.[object_id]AND ic.column_id = c.column_idWHERE ic.[object_id]= i.[object_id]AND ic.index_id = i.index_id AND ic.is_included_column =0ORDER BY ic.key_ordinalFOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '')) F_IxCols (IxColList)CROSS APPLY (SELECT STUFF((SELECT','+QUOTENAME()FROM sys.index_columns icJOIN sys.columns c ON ic.[object_id]= c.[object_id]AND ic.column_id = c.column_idWHERE ic.[object_id]= i.[object_id]AND ic.index_id = i.index_id AND ic.is_included_column =1ORDER BY ic.key_ordinalFOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '')) F_IxIncl (IxInclList)WHERE i.type_desc <>'HEAP'),FKDefsAS (SELECT TableObj = f.parent_object_id, FKName =QUOTENAME(),FKRef =QUOTENAME(OBJECT_SCHEMA_NAME(f.referenced_object_id)) +'.'+QUOTENAME(OBJECT_NAME(f.referenced_object_id)),FKColList = ParentColList, FKRefList = RefColList,FKDelOpt =CASE f.delete_referential_actionWHEN1THEN'CASCADE'WHEN2THEN'SET NULL'WHEN3THEN'SET DEFAULT'END, FKUpdOpt =CASE f.update_referential_actionWHEN1THEN'CASCADE'WHEN2THEN'SET NULL'WHEN3THEN'SET DEFAULT'END, FKNoRepl = f.is_not_for_replicationFROM sys.foreign_keys fCROSS APPLY (SELECT STUFF((SELECT','+QUOTENAME()FROM sys.foreign_key_columns kJOIN sys.columns c ON k.parent_object_id = c.[object_id]AND k.parent_column_id = c.column_idWHERE k.constraint_object_id = f.[object_id]ORDER BY constraint_column_idFOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '')) F_Parent (ParentColList)CROSS APPLY (SELECT STUFF((SELECT','+QUOTENAME()FROM sys.foreign_key_columns kJOIN sys.columns c ON k.referenced_object_id = c.[object_id]AND k.referenced_column_id = c.column_idWHERE k.constraint_object_id = f.[object_id]ORDER BY constraint_column_idFOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '')) F_Ref (RefColList))INSERT INTO@results ([definition])SELECT--TableName,[definition]+CHAR(10)+'GO'+CHAR(10) AS definitionFROM sys.tables tINNER JOIN@objects o ON(t.[object_id]LIKE o.[id]AND o.[type]='U')CROSS APPLY (SELECT TableName =QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id])) +'.'+QUOTENAME(OBJECT_NAME(t.[object_id]))) F_NameCROSS APPLY (SELECT STUFF((SELECT@crlf+' ,'+ ColumnDefFROM ColumnDefsWHERE TableObj = t.[object_id]ORDER BY ColSeqFOR XML PATH(''), 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 FOR REPLICATION 'ELSE''END+COALESCE([definition],'')FROM sys.check_constraintsWHERE parent_object_id = t.[object_id]FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 2, '')) F_Const (ChkConstList)CROSS APPLY (SELECT STUFF((SELECT@crlf+' ,CONSTRAINT '+ IxName +''+ IxType +''+ IxDef +COALESCE(' WITH ('+ IxOpts +')','')FROM IndexDefsWHERE TableObj = t.[object_id]AND IxPKFlag =1FOR XML PATH(''), 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''ENDFROM FKDefsWHERE TableObj = t.[object_id]FOR XML PATH(''), 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 +')','')FROM IndexDefsWHERE TableObj = t.[object_id]AND IxPKFlag =0FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 2, '')) F_Indexes (IndexList)CROSS APPLY (SELECT[definition]=( SELECT CASE WHEN@index<>1THEN'CREATE TABLE '+ TableName +@crlf+'('+@crlf+''+ ColumnList +COALESCE(@crlf+ ChkConstList,'') +COALESCE(@crlf+ IxConstList,'') +COALESCE(@crlf+ FKConstList,'') +@crlf+')'+@crlfELSE''END+CASE WHEN@index<>0THEN COALESCE(@crlf+ IndexList, '')ELSE''ENDFOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')) F_LinkWHERE t.[is_ms_shipped]=0AND[definition]<>'';-- 视图/过程/函数/触发器INSERT INTO@results ([definition])SELECT--QUOTENAME(object_schema_name(m.object_id))+'.'+ QUOTENAME(object_name(m.object_id)) AS [name],o.type,m.definition+CHAR(10)+'GO'+CHAR(10) AS definitionFROM sys.sql_modules mINNER JOIN sys.objects o ON m.object_id= o.object_idINNER JOIN@objects a ON(m.[object_id]LIKE a.[id]AND a.[type]<>'U');-- 字段说明insert into@results ([definition])select'EXEC sys.sp_addextendedproperty @name=N''MS_Description'', @value=N'''+cast(ep.[value]as varchar(100))+''', @level0type=N''SCHEMA'',@level0name=N'''+schema_name(schema_id)+''', @level1type=N''TABLE'',@level1name=N'''+t.[name]+''', @level2type=N''COLUMN'',@level2name=N'''+c.[name]+''''+CHAR(10)+'GO'+CHAR(10)FROM sys.tables AS tINNER JOIN sys.columns AS c ON t.object_id= c.object_idLEFT JOIN sys.extended_properties AS ep ON ep.major_id = c.object_id AND ep.minor_id = c.column_idINNER JOIN@objects o ON(t.[object_id]LIKE o.[id]AND o.[type]='U')WHERE ep.class =1SELECT*FROM@results;goSplitif exists (select*from sys.objects where object_id=OBJECT_ID(N'[dbo].[Split]') and type in (N'FN', N'IF', N'TF', N'FS', N'FT'))drop function[dbo].[Split]goCREATE FUNCTION[dbo].[Split](@Text VARCHAR(max),@Sign NVARCHAR(4000))RETURNS@tempTable TABLE(id INT IDENTITY(1,1)PRIMARY KEY,[VALUE]NVARCHAR(4000))ASBEGIN-- select [dbo].[Split]('1,2',',')DECLARE@StartIndex INT--开始查找的位置DECLARE@FindIndex INT--找到的位置DECLARE@Content VARCHAR(4000)--找到的值--初始化⼀些变量SET@StartIndex=1--T-SQL中字符串的查找位置是从1开始的SET@FindIndex=0--开始循环查找字符串逗号WHILE(@StartIndex<=LEN(@Text))BEGIN--查找字符串函数CHARINDEX第⼀个参数是要找的字符串--第⼆个参数是在哪⾥查找这个字符串--第三个参数是开始查找的位置--返回值是找到字符串的位置SELECT@FindIndex=CHARINDEX(@Sign,@Text,@StartIndex)--判断有没找到没找到返回0IF(@FindIndex=0OR@FindIndex IS NULL)BEGIN--如果没有找到者表⽰找完了SET@FindIndex=LEN(@Text)+1END--截取字符串函数SUBSTRING第⼀个参数是要截取的字符串--第⼆个参数是开始的位置--第三个参数是截取的长度--@FindIndex-@StartIndex表⽰找的的位置-开始找的位置=要截取的长度--LTRIM和RTRIM是去除字符串左边和右边的空格函数SET@Content=LTRIM(RTRIM(SUBSTRING(@Text,@StartIndex,@FindIndex-@StartIndex)))--初始化下次查找的位置SET@StartIndex=@FindIndex+1--把找的的值插⼊到要返回的Table类型中INSERT INTO@tempTable([VALUE])VALUES(@Content)ENDRETURNENDgoUspOutputDataif exists (select1from sysobjects where id =object_id('UspOutputData') and type ='P')drop proc UspOutputDatagoCREATE PROCEDURE pOutputData@tablename sysnameASdeclare@column varchar(1000)declare@columndata varchar(1000)declare@sql varchar(4000)declare@xtype tinyintdeclare@name sysnamedeclare@objectId intdeclare@objectname sysnamedeclare@ident intset nocount onif@tablename='HD_ZBMX_HZ'-- HD_ZBMX_HZ 特殊处理beginset nocount onselect'insert HD_ZBMX_HZ(zb_id,zb_name,sql_start,sql_middle,sql_end,sql_text,tqsql_text,jlzt,memo,dependindex,orderno,sql_text_first,proc_name,zbzt,ysfs,kslx,fact_table,fact_dlz,condition,kxwdz,gspz,hqsql_text,dlzgs,dylx) values( returnendset@objectId=object_id(@tablename)if@objectId is null-- 判断对象是否存在beginprint'The object not exists'returnendset@objectname=rtrim(object_name(@objectId))if@objectname is null or charindex(@objectname,@tablename)=0--此判断不严密beginprint'object not in current database'returnendif OBJECTPROPERTY(@objectId,'IsTable') <>1-- 判断对象是否是tablebeginprint'The object is not table'returnendselect@ident=status&0x80from syscolumns where id =@objectId and status&0x80=0x80if@ident is not nullprint'SET IDENTITY_INSERT '+@tablename+' ON'declare syscolumns_cursor cursorfor select ,c.xtype from syscolumns c where c.id=@objectId order by c.colidopen syscolumns_cursorset@column=''set@columndata=''fetch next from syscolumns_cursor into@name,@xtypewhile@@fetch_status<>-1beginif@@fetch_status<>-2beginif@xtype not in(189,34,35,99,98) --timestamp不需处理，image,text,ntext,sql_variant 暂时不处理beginset@column=@column+case when len(@column)=0then''else','end+@nameset@columndata=@columndata+case when len(@columndata)=0then''else','','','end+case when@xtype in(167,175) then'''''''''+'+@name+'+'''''''''--varchar,charwhen@xtype in(231,239) then'''N''''''+'+@name+'+'''''''''--nvarchar,ncharwhen@xtype=61then'''''''''+convert(char(23),'+@name+',121)+'''''''''--datetimewhen@xtype=58then'''''''''+convert(char(16),'+@name+',120)+'''''''''--smalldatetimewhen@xtype=36then'''''''''+convert(char(36),'+@name+')+'''''''''--uniqueidentifierelse@name endendendfetch next from syscolumns_cursor into@name,@xtypeendclose syscolumns_cursordeallocate syscolumns_cursorset@sql='set nocount on select ''insert '+@tablename+'('+@column+') values(''as ''v1'','+@columndata+','')'' from '+@tablename --print '--'+@sql--print @columndata--print @columnexec(@sql)if@ident is not nullprint'SET IDENTITY_INSERT '+@tablename+' OFF'GO。
Sqlserver数据库备份与还原
Sqlserver数据库备份与还原⼀、数据库存储概述1、数据⽂件类型· Primary data files:每个数据库都有⼀个单独的主要数据⽂件，默认以.mdf扩展名。
主要数据⽂件不仅包含数据信息，还包含与该数据库结构相关的信息。
创建数据库时，数据库结构相关信息不仅存在于master数据库中，同时还包含在primary data file上.· Secondary data files⼀个数据库可以有⼀个或者多个⼆级数据⽂件，默认以.ndf为扩展名。
⼀般来说⼆级数据⽂件并不是必须的，因为⼆级数据⽂件不包含⽂件位置等信息。
· Transaction logs: 数据库必须⾄少有⼀个事务⽇志⽂件，默认以.ldf为扩展名。
⽇志是整个数据库的命脉，事务⽇志不可读的话，将⽆法对数据库进⾏任何操作。
当你在数据库上进⾏数据操作时，数据并不是直接写⼊数据⽂件，⽽是先将相关操作信息写⼊事务⽇志⽂件。
当⼀个事务结束时，该事务被标记为已提交，但这也并不意味着数据从⽇志⽂件写⼊了数据⽂件中。
⼀个标记为已提交的事务仅仅意味着所有与该事务相关的元素已经成功完成。
The buffer cache may be updated, but not necessarily the data file.检查点(checkpoint)会周期性的发⽣。
检查点发⽣时，是确认所有已提交的事务，不管是在buffer cache或者事务⽇志中，都被写⼊相关的数据⽂件中。
检查点(checkpoint)可以通过以下⽅式来触发：1、显⽰调⽤checkpoint命令；2、Recover Interval 实例设置的周期达到（⽤来标识多久发⽣⼀次checkpoint）3、做了数据库备份（在简单模式下）；4、数据库⽂件结构被改变（在简单模式下）；5、数据库引擎被结束。
⼀般来说，写数据的过程是由系统⾃动完成的，如下图所⽰，但数据并不是直接写⼊.mdf或者.ndf ⽂件中，⽽是先将有关变化写⼊事务⽇志中，这也是数据库中的write-ahead机制。
sqlserver数据库的备份与还原——完整备份与还原
sqlserver数据库的备份与还原——完整备份与还原sqlserver提供四种数据库备份⽅式
完整备份：备份整个数据库的所有内容包括书屋和⽇志
差异备份：只备份上次完整备份后更⾼的数据部分
事务⽇志备份：只备份事务⽇志⾥的内容
⽂件或⽂件组备份：只备份⽂件或⽂件组中的某些⽂件
在数据库完整备份期间，sqlserver做⼀下⼯作：
备份数据以及数据库中的所有表的结构和相应的⽂件结构
备份在备份期间发⽣的所有活动
备份在事务⽇志中未确认的事务
备份过程：
右击要备份的数据库--->任务--->备份
可以选择备份类型以及备份⽂件的存放位置。
还原过程；
在还原数据库的时候需要将原数据库删除，然后单击数据库--->还原数据库
将源选择为设备，然后选择备份⽂件的存储位置，添加进去，然后填写上⽬标⽂件名，点击确定即可。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
批处理(bat)实现SQLServer数据库备份与还原
-- SQLServer2000数据库系统清理用户数据库日志和备份用户数据库数据脚本
declare @dumpfile varchar(50)
declare @msg varchar(70)
select @dumpfile ='d:\backup\north'+datename(dw,getdate())+'.bak'
select @msg=convert(char(26),getdate(),9)+'-----正在清理日志......'
print @msg
backup tran northwind with truncate_only
if(@@ERROR<> 0 )
begin
select @msg=convert(char(26),getdate(),9)+'-----清理日志失败或出现异常......' print @msg
end
else
begin
select @msg=convert(char(26),getdate(),9)+'-----清理日志完毕......'
print @msg
end
select @msg=convert(char(26),getdate(),9)+'-----开始备份northwind数据库.....' print @msg
backup database northwind to disk=@dumpfile
if(@@ERROR<> 0 )
begin
select @msg=convert(char(26),getdate(),9)+'-----备份数据失败或出现异常'
print @msg
end
else
begin
select @msg=convert(char(26),getdate(),9)+'-----数据库备份完毕'
print @msg
end
@net start "mssqlserver"
@isql -Usa -Psa -i d:\backup\sqlserverbackup.sql -o d:\backup\sqlserverbackup.out @echo ……正在备份中……
@pause
--2.数据库还原脚本sqlserverrestore.sql
1
declare @dumpfile varchar(50)
declare @msg varchar(70)
select @dumpfile ='d:\backup\north'+datename(dw,getdate())+'.bak'
select @msg=convert(char(26),getdate(),9)
print @msg
restore database northwind from disk=@dumpfile
if(@@ERROR<> 0 )
begin
select @msg=convert(char(26),getdate(),9)+'-----还原数据失败或出现异常'
print @msg
end
else
begin
select @msg=convert(char(26),getdate(),9)+'-----数据库还原完毕'
print @msg
end
--将下面的脚本保存为: 备份数据库.bat
@net start "mssqlserver"
@isql -Usa -Padmin -i d:\backup\sqlserverbackup.sql -o d:\backup\sqlserverbackup.out @echo ……正在备份中……
@pause
--将下面的脚本保存为:还原数据库.bat
@echo ******开始还原数据库*****
@net start "mssqlserver"
@isql -Usa -Psa -i d:\backup\sqlserverrestore.sql -o d:\backup\sqlserverrestore.out
@pause
- 本材料由愚公从网上获取资源并通过实践整理而得-
2。