操作方法:快速&肮脏的SQL Express预定备份 [英] How-to: Quick & Dirty SQL Express scheduled backup

问题描述

操作方法:快速&肮脏的SQL Express计划备份

How-to: Quick & Dirty SQL Express scheduled backup

推荐答案

Step1:
打开SQL Management Studio，并将之前创建的以下SQL脚本复制到新的查询窗口中.

USE [master]
GO
设置ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

创建过程[dbo].[sp_BackupDb]
@dbName sysname，@ backupTypeToRun CHAR(1)
AS
开始
设置NOCOUNT ON；

DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)

SELECT @dateTime = REPLACE(CONVERT(VARCHAR，GETDATE()，111)，''/''，''-'')+''-''+
REPLACE(CONVERT(VARCHAR，GETDATE()，108)，'':''，'''')

宣告@databaseFileName NVARCHAR(200)
SET @databaseFileName = replace(@dbName，'']''，'''')
SET @databaseFileName = replace(@databaseFileName，''[''，'''')

IF @backupTypeToRun =``F''
SET @sqlCommand =''BACKUP DATABASE''+ @dbName +
''TO DISK =''''C:\ DbBackups \''+ @databaseFileName +``_Full_''+ @dateTime +``.BAK''''''

IF @backupTypeToRun =``D''
SET @sqlCommand =''BACKUP DATABASE''+ @dbName +
''TO DISK =''''C:\ DbBackups \''+ @databaseFileName +``_Diff_''+ @dateTime +``.BAK''''带差异''

IF @backupTypeToRun =``L''
SET @sqlCommand =``备份日志''+ @dbName +
''TO DISK =''''C:\ DbBackups \''+ @databaseFileName +``_Log_''+ @dateTime +``.TRN''''''

执行sp_executesql @sqlCommand
结束

步骤2:
步骤2:在文本编辑器中，创建一个名为sqlbackup.bat的批处理文件，然后根据您的情况将以下示例之一的文本复制到该文件中:
例如:sqlcmd -S.\ SQLEXPRESS -E -Q使用主数据库； EXEC dbo.sp_BackupDb @ dbName =" sample，@ backupTypeToRun =" F"

步骤3:
将该批处理文件添加到创建基本任务向导"(计划任务)

Step1:
Open SQL Management Studio and copy the following SQL script i created earlier into a new query window.

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[sp_BackupDb]
@dbName sysname, @backupTypeToRun CHAR(1)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)

SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),''/'',''-'') +''-'' +
REPLACE(CONVERT(VARCHAR, GETDATE(),108),'':'','''')

DECLARE @databaseFileName NVARCHAR(200)
SET @databaseFileName = replace(@dbName,'']'','''')
SET @databaseFileName = replace(@databaseFileName,''['','''')

IF @backupTypeToRun = ''F''
SET @sqlCommand = ''BACKUP DATABASE '' + @dbName +
'' TO DISK = ''''C:\DbBackups\'' + @databaseFileName + ''_Full_'' + @dateTime + ''.BAK''''''

IF @backupTypeToRun = ''D''
SET @sqlCommand = ''BACKUP DATABASE '' + @dbName +
'' TO DISK = ''''C:\DbBackups\'' + @databaseFileName + ''_Diff_'' + @dateTime + ''.BAK'''' WITH DIFFERENTIAL''

IF @backupTypeToRun = ''L''
SET @sqlCommand = ''BACKUP LOG '' + @dbName +
'' TO DISK = ''''C:\DbBackups\'' + @databaseFileName + ''_Log_'' + @dateTime + ''.TRN''''''

EXECUTE sp_executesql @sqlCommand
END

Step2:
Step 2: In a text editor create a batch file that is named sqlbackup.bat and then copy the text from one of the following examples depending on your scenario into that file:
ex:sqlcmd -S .\SQLEXPRESS -E -Q "use master; EXEC dbo.sp_BackupDb @dbName=''sample'',@backupTypeToRun=''F''"

Step3:
Add that batch file to Create basic task wizard(task schedular)

这篇关于操作方法:快速&肮脏的SQL Express预定备份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！