数据库backup脚本

最新推荐文章于 2025-11-23 21:36:02 发布

最新推荐文章于 2025-11-23 21:36:02 发布 · 147 阅读

文章标签：


exec master..xp_cmdshell 'net use Y:  /delete'
exec master..xp_cmdshell 'net use Y: \\server1\DB_Backup mypassword /user:Domain\userID'

USE master 
go

DECLARE @dbName SYSNAME 
DECLARE @strExec CHAR(8000)
DECLARE  @AbsKey as int
DECLARE @time1 CHAR(8)

SET @time1 =  CONVERT(VARCHAR(8),GETDATE(),112);

/* Cursor declartion */
DECLARE dbNameCursor CURSOR READ_ONLY FAST_FORWARD  FOR 
SELECT [name] FROM master..sysdatabases WHERE -- take all database that are not system, offline or recovery
status NOT IN (512,32) 
AND
[name] NOT IN('master','model','msdb','tempdb')
OPEN dbNameCursor
FETCH NEXT FROM dbNameCursor INTO @dbName

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT  @AbsKey = count(AbsKey) from [DB1234]..T123 where DBName =  @dbName

	SET @strExec = 'BACKUP DATABASE [' + @dbName + '] TO DISK =''Y:\' + @dbname + @time1 + '.bak''  WITH FORMAT;' 
   if @AbsKey <> 0
	BEGIN
		print (@strExec)-- Execute the string 
		exec (@strExec) -- Execute the string 
	END

   FETCH NEXT FROM dbNameCursor INTO @dbName
END

CLOSE dbNameCursor
DEALLOCATE dbNameCursor