2023年7月30日发(作者：)

SqlServer备份数据库脚本----open sp_configure启⽤‘xp_cmdshell

--sp_configure 'show advanced options',1

--reconfigure

--go

--sp_configure 'xp_cmdshell',1

--reconfigure

--go

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

DECLARE @databaseCount int

DECLARE @databaseName nvarchar(300)

DECLARE @backUupDatabaseName nvarchar(300)

--set path

DECLARE @CreateFilePath nvarchar(200)

DECLARE @FilePath nvarchar(200)

SET @FilePath = 'C:DBBackUp' + (SELECT REPLACE(REPLACE(REPLACE(CONVERT(CHAR(19), GETDATE(), 120), '-', ''), ':',''), ' ', '-')) + ''

SET @CreateFilePath ='md ' + @FilePath

EXEC _cmdshell @CreateFilePath

--insert new create table

IF EXISTS (SELECT 1 FROM sysobjects WHERE id =object_id('DatabaseTableList') AND type = 'U')

BEGIN DROP TABLE DatabaseTableList END

CREATE TABLE DatabaseTableList

(id int IDENTITY(1,1),Name nvarchar(300))

Insert into DatabaseTableList(Name)

SELECT Name FROM ses ORDER BY name

SET @databaseCount = (SELECT COUNT(Name) FROM DatabaseTableList)

--for datatable list backup to file

WHILE(@databaseCount >0)

BEGIN

SET @databaseName = (SELECT TOP(1) Name FROM DatabaseTableList where id = @databaseCount)

IF(@databaseName <> 'master' AND @databaseName <> 'model' AND @databaseName <> 'msdb' AND @databaseName <>'tempdb')

BEGIN

SET @backUupDatabaseName = @FilePath + @databaseName + '.bak'

BACKUP DATABASE @databaseName

TO DISK = @backUupDatabaseName

WITH DESCRIPTION = 'Full backup for AdventureWorks'

END

--set while count -1

SET @databaseCount =@databaseCount-1

END

---------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----close sp_configure启⽤‘xp_cmdshell

--sp_configure 'show advanced options',1

--reconfigure

--go

--sp_configure 'xp_cmdshell',0

--reconfigure

--go