SQL2008备份 sql备份批处理 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 我有个类似的--定义备份时间,精确到秒DECLARE @date NVARCHAR(64)SELECT @date = SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 121), 1, 4) + '_' + SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 121), 6, 2) + '_' + SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 121), 9, 2) + '_' + CONVERT(CHAR(2), DATEPART(hh, GETDATE())) + CONVERT(CHAR(2), DATEPART(mi, GETDATE())) + CONVERT(CHAR(2), DATEPART(ss, GETDATE()))--定义要备份的数据库名DECLARE @db NVARCHAR(20)SET @db = '' + '数据库名' + ''--定义备份文件的全名DECLARE @bakname NVARCHAR(128)SELECT @bakname = @db + '_' + @date--定义备份存放路径DECLARE @disk NVARCHAR(256)SELECT @disk = N'DataBak\' + @bakname + '.bak'--定义备份描述DECLARE @name NVARCHAR(128)SELECT @name = @db + '-完整 数据库 备份'--定义错误信息DECLARE @error NVARCHAR(128)SELECT @error = '验证失败。找不到数据库“' + @db + '”的备份信息。'BACKUP DATABASE @db TO DISK = @disk WITH COPY_ONLY, NOFORMAT, NOINIT, NAME =@name, SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM ;DECLARE @backupSetId AS INTSELECT @backupSetId = positionFROM msdb..backupsetWHERE database_name = @db AND backup_set_id = ( SELECT MAX(backup_set_id) FROM msdb..backupset WHERE database_name = @db )IF @backupSetId IS NULL BEGIN RAISERROR(@error, 16, 1) ENDRESTORE VERIFYONLY FROM DISK = @disk WITH FILE = @backupSetId, NOUNLOAD, NOREWINDGO 完整备份,你替换那个@dbname的值就好了--定义备份时间,精确到秒DECLARE @date NVARCHAR(64)SELECT @date = SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 121), 1, 4) + '_' + SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 121), 6, 2) + '_' + SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 121), 9, 2) + '_' + CONVERT(CHAR(2), DATEPART(hh, GETDATE())) + CONVERT(CHAR(2), DATEPART(mi, GETDATE())) + CONVERT(CHAR(2), DATEPART(ss, GETDATE()))--定义要备份的数据库名DECLARE @dbname NVARCHAR(20)SET @dbname='AdventureWorks2012' --这里是库名DECLARE @db NVARCHAR(20)SET @db = '' + @dbname + ''--定义备份文件的全名DECLARE @bakname NVARCHAR(128)SELECT @bakname = @db + '_' + @date--定义备份存放路径DECLARE @disk NVARCHAR(256)SELECT @disk = N'D:\BACKUP\' + @bakname + '.bak'--定义备份描述DECLARE @name NVARCHAR(128)SELECT @name = @db + '-完整 数据库 备份'--定义错误信息DECLARE @error NVARCHAR(128)SELECT @error = '验证失败。找不到数据库“' + @db + '”的备份信息。'BACKUP DATABASE @db TO DISK = @disk WITH NOFORMAT, NOINIT, NAME =@name, SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM ;DECLARE @backupSetId AS INTSELECT @backupSetId = positionFROM msdb..backupsetWHERE database_name = @db AND backup_set_id = ( SELECT MAX(backup_set_id) FROM msdb..backupset WHERE database_name = @db )IF @backupSetId IS NULL BEGIN RAISERROR(@error, 16, 1) ENDRESTORE VERIFYONLY FROM DISK = @disk WITH FILE = @backupSetId, NOUNLOAD, NOREWINDGO差异备份:--定义备份时间,精确到秒DECLARE @date NVARCHAR(64)SELECT @date = SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 121), 1, 4) + '_' + SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 121), 6, 2) + '_' + SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 121), 9, 2) + '_' + CONVERT(CHAR(2), DATEPART(hh, GETDATE())) + CONVERT(CHAR(2), DATEPART(mi, GETDATE())) + CONVERT(CHAR(2), DATEPART(ss, GETDATE()))--定义要备份的数据库名DECLARE @dbname NVARCHAR(20)SET @dbname='AdventureWorks2012' --这里是库名DECLARE @db NVARCHAR(20)SET @db = '' + @dbname + ''--定义备份文件的全名DECLARE @bakname NVARCHAR(128)SELECT @bakname = @db + '_' + @date--定义备份存放路径DECLARE @disk NVARCHAR(256)SELECT @disk = N'D:\BACKUP\' + @bakname + '.bak'--定义备份描述DECLARE @name NVARCHAR(128)SELECT @name = @db + '-差异 数据库 备份'--定义错误信息DECLARE @error NVARCHAR(128)SELECT @error = '验证失败。找不到数据库“' + @db + '”的备份信息。'BACKUP DATABASE @db TO DISK = @disk WITH DIFFERENTIAL, NOFORMAT, NOINIT, NAME =@name, SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM ;DECLARE @backupSetId AS INTSELECT @backupSetId = positionFROM msdb..backupsetWHERE database_name = @db AND backup_set_id = ( SELECT MAX(backup_set_id) FROM msdb..backupset WHERE database_name = @db )IF @backupSetId IS NULL BEGIN RAISERROR(@error, 16, 1) ENDRESTORE VERIFYONLY FROM DISK = @disk WITH FILE = @backupSetId, NOUNLOAD, NOREWINDGO但是我还是建议你用维护计划 也没见出什么问题 ,就是不能执行哦。就把@dbname值改为数据库名就好了是吧。其他不用修改吧。 使得,盘符你自己根据需要改吧,我已经写死D:\Backup了 MSSQL2000统计、查询、过滤问题 用游标批量更改数据的问题 sql2000升级到2005 求sql语句!!!以时间来分组 急,如何在数据中快速查询所在在区域范围内的点 看看这行代码,在线等待。 相对于 oracle的 “select count(rowid) from tablename” 有谁能够解决vfp程序运行不出现该死的vfp主窗口。 如何建立一个有效的mapi profile name文件? 向高手请教问题:如何学习sql的存储过程 sqlsever 导出csv文件后,再导入,其中一个ntext类型的字段乱码 SQL Server 2008 R2最大连接数
DECLARE @date NVARCHAR(64)
SELECT @date = SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 121), 1, 4) + '_'
+ SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 121), 6, 2) + '_'
+ SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 121), 9, 2) + '_'
+ CONVERT(CHAR(2), DATEPART(hh, GETDATE()))
+ CONVERT(CHAR(2), DATEPART(mi, GETDATE()))
+ CONVERT(CHAR(2), DATEPART(ss, GETDATE()))
--定义要备份的数据库名
DECLARE @db NVARCHAR(20)
SET @db = '' + '数据库名' + ''
--定义备份文件的全名
DECLARE @bakname NVARCHAR(128)
SELECT @bakname = @db + '_' + @date
--定义备份存放路径
DECLARE @disk NVARCHAR(256)
SELECT @disk = N'DataBak\' + @bakname + '.bak'
--定义备份描述
DECLARE @name NVARCHAR(128)
SELECT @name = @db + '-完整 数据库 备份'
--定义错误信息
DECLARE @error NVARCHAR(128)
SELECT @error = '验证失败。找不到数据库“' + @db + '”的备份信息。'BACKUP DATABASE @db TO DISK = @disk WITH COPY_ONLY, NOFORMAT, NOINIT,
NAME =@name, SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM ;
DECLARE @backupSetId AS INT
SELECT @backupSetId = position
FROM msdb..backupset
WHERE database_name = @db
AND backup_set_id = ( SELECT MAX(backup_set_id)
FROM msdb..backupset
WHERE database_name = @db
)
IF @backupSetId IS NULL
BEGIN
RAISERROR(@error, 16, 1)
END
RESTORE VERIFYONLY FROM DISK = @disk WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
--定义备份时间,精确到秒
DECLARE @date NVARCHAR(64)
SELECT @date = SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 121), 1, 4) + '_'
+ SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 121), 6, 2) + '_'
+ SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 121), 9, 2) + '_'
+ CONVERT(CHAR(2), DATEPART(hh, GETDATE()))
+ CONVERT(CHAR(2), DATEPART(mi, GETDATE()))
+ CONVERT(CHAR(2), DATEPART(ss, GETDATE()))
--定义要备份的数据库名
DECLARE @dbname NVARCHAR(20)
SET @dbname='AdventureWorks2012' --这里是库名
DECLARE @db NVARCHAR(20)
SET @db = '' + @dbname + ''
--定义备份文件的全名
DECLARE @bakname NVARCHAR(128)
SELECT @bakname = @db + '_' + @date
--定义备份存放路径
DECLARE @disk NVARCHAR(256)
SELECT @disk = N'D:\BACKUP\' + @bakname + '.bak'
--定义备份描述
DECLARE @name NVARCHAR(128)
SELECT @name = @db + '-完整 数据库 备份'
--定义错误信息
DECLARE @error NVARCHAR(128)
SELECT @error = '验证失败。找不到数据库“' + @db + '”的备份信息。'BACKUP DATABASE @db TO DISK = @disk WITH NOFORMAT, NOINIT,
NAME =@name, SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM ;
DECLARE @backupSetId AS INT
SELECT @backupSetId = position
FROM msdb..backupset
WHERE database_name = @db
AND backup_set_id = ( SELECT MAX(backup_set_id)
FROM msdb..backupset
WHERE database_name = @db
)
IF @backupSetId IS NULL
BEGIN
RAISERROR(@error, 16, 1)
END
RESTORE VERIFYONLY FROM DISK = @disk WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
差异备份:--定义备份时间,精确到秒
DECLARE @date NVARCHAR(64)
SELECT @date = SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 121), 1, 4) + '_'
+ SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 121), 6, 2) + '_'
+ SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 121), 9, 2) + '_'
+ CONVERT(CHAR(2), DATEPART(hh, GETDATE()))
+ CONVERT(CHAR(2), DATEPART(mi, GETDATE()))
+ CONVERT(CHAR(2), DATEPART(ss, GETDATE()))
--定义要备份的数据库名
DECLARE @dbname NVARCHAR(20)
SET @dbname='AdventureWorks2012' --这里是库名
DECLARE @db NVARCHAR(20)
SET @db = '' + @dbname + ''
--定义备份文件的全名
DECLARE @bakname NVARCHAR(128)
SELECT @bakname = @db + '_' + @date
--定义备份存放路径
DECLARE @disk NVARCHAR(256)
SELECT @disk = N'D:\BACKUP\' + @bakname + '.bak'
--定义备份描述
DECLARE @name NVARCHAR(128)
SELECT @name = @db + '-差异 数据库 备份'
--定义错误信息
DECLARE @error NVARCHAR(128)
SELECT @error = '验证失败。找不到数据库“' + @db + '”的备份信息。'BACKUP DATABASE @db TO DISK = @disk WITH DIFFERENTIAL, NOFORMAT, NOINIT,
NAME =@name, SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM ;
DECLARE @backupSetId AS INT
SELECT @backupSetId = position
FROM msdb..backupset
WHERE database_name = @db
AND backup_set_id = ( SELECT MAX(backup_set_id)
FROM msdb..backupset
WHERE database_name = @db
)
IF @backupSetId IS NULL
BEGIN
RAISERROR(@error, 16, 1)
END
RESTORE VERIFYONLY FROM DISK = @disk WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO但是我还是建议你用维护计划
就把@dbname值改为数据库名就好了是吧。其他不用修改吧。