每天固定备份db sqlserver

DECLARE @DBName varchar(255)

DECLARE @DATABASES_Fetch int

DECLARE DATABASES_CURSOR CURSOR FOR
    select
        DATABASE_NAME   = db_name(s_mf.database_id)
    from
        sys.master_files s_mf
    where
       -- ONLINE
        s_mf.state = 0 

       -- Only look at databases to which we have access
    and has_dbaccess(db_name(s_mf.database_id)) = 1 

        -- Not master,tempdb,model or msdb
    and db_name(s_mf.database_id) in (‘EIP-ZH‘)
    group by s_mf.database_id
    order by 1

OPEN DATABASES_CURSOR

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
    declare @DBFileName varchar(256)
	declare @BackupPath varchar(256)    
    set @DBFileName = datename(dw,getdate()) + ‘ - ‘ + 
                       replace(replace(@DBName,‘:‘,‘_‘),‘‘,‘_‘) + ‘.bak‘

	-- Change the following path setting to client‘s backup path
	set @BackupPath = ‘D:EIP-ZH_Backup‘ + datename(dw,getdate()) + ‘‘
 
	exec (‘BACKUP DATABASE [‘ + @DBName + ‘] TO  DISK = N‘‘‘ + @BackupPath + 
        @DBFileName + ‘‘‘ WITH NOFORMAT,INIT,NAME = N‘‘‘ + 
        @DBName + ‘-Full Database Backup‘‘,SKIP,NOREWIND,NOUNLOAD,STATS = 100‘)

    FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
END

CLOSE DATABASES_CURSOR
DEALLOCATE DATABASES_CURSOR

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!