The stored procedure code looks like this:
CREATE PROCEDURE [dbo].[sp_backupdb]( @BackPath NVARCHAR( $),--backup path, such as: D:\Backup\ @BackDbName NVARCHAR( -),--the name of the database that needs to be backed up @BackName NVARCHAR( -)=@BackDbNameOUTPUT,--the database name after the backup, No. bak suffix is required. If not preached, it is equivalent to @backdbname @BackPathIsDate BIT=1,--Whether you need to add a date folder to the backup path, add by default @BackNameIsTime BIT=1 --whether the database name after backup needs to be appended to the current time, appended by default) asBEGIN IF(@BackPathIsDate=1) BEGIN SET @BackPath=@BackPath + CONVERT(VARCHAR,GETDATE(), the)+'\'; END ---------------------------Create a folder Begin--------------------------- DECLARE @FolderSQL NVARCHAR(MAX), @Return INT= 0; SET @FolderSQL =N'EXEC sp_configure"'Show advanced Options"', 1 RECONFIGURE with OVERRIDE; EXEC sp_configure"'xp_cmdshell"', 1 RECONFIGURE with OVERRIDE; EXEC @Return =xp_cmdshell"'mkdir' + @BackPath + " ", No_output--Call DOS command to create a folder; EXEC sp_configure"'xp_cmdshell"', 0 RECONFIGURE with OVERRIDE; EXEC sys.sp_configure N"'Show advanced Options"', N"'0"'RECONFIGURE with OVERRIDE; '; --PRINT @FolderSQL; EXECsp_executesql@FolderSQLN'@Return INT OUTPUT',@ReturnOUTPUT; --SELECT @Return; --PRINT @Return ---------------------------Create folder End--------------------------- ---------------------------Backup Database Begin--------------------------- DECLARE @NowTime VARCHAR( -); DECLARE @BackUpName VARCHAR( -); SET @NowTime=CONVERT(VARCHAR,GETDATE(), the)+REPLACE(CONVERT(VARCHAR,GETDATE(),108),':',"'); /*IF (LEN (@BackName) <=0) BEGIN SET @[email protected]; END*/ IF(@BackNameIsTime=1) BEGIN SET @BackName=@BackName +'_'+ @NowTime; END SET @BackName=@BackName+'. bak'; SET @BackUpName=@BackPath + @BackName; BACKUP DATABASE @BackDbName to DISK=@BackUpName withINIT; ---------------------------back up the database End--------------------------- SELECT @BackNameBackname,@BackPathBackpath,@BackPath + @BackNameFullbackname;ENDGO
Using stored procedures to back up SQL Server databases