在SQLserver使用脚本备份数据库的时候需要注意的问题是:
1.指向的文件名必须是有读写权限。
2.在使用批量数据库备份时候需要根据自己需求选择性备份。
-- ============================================= -- Author: <奔跑的金鱼> -- Blog: <http://www.cnblogs.com/OliverQin/> -- Create date: <2015/01/04> -- Description: <备份指定数据库> -- ============================================= DECLARE @FileName VARCHAR(200), @CurrentTime VARCHAR(50), @DBName VARCHAR(100), @SQL VARCHAR(1000) BEGIN SET @CurrentTime = CONVERT(CHAR(8),GETDATE(),112) + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) + CAST(DATEPART(mi, GETDATE()) AS VARCHAR) SET @DBName='test'---------你要备份的数据库名 SET @FileName = 'C:\backup\' + @DBName + '_' + @CurrentTime SET @SQL = 'BACKUP DATABASE ['+ @DBName +'] TO DISK = ''' + @FileName + '.bak' + ''' WITH NOINIT, NOUNLOAD, NAME = N''' + @DBName + '_backup'', NOSKIP, STATS = 10, NOFORMAT' PRINT @SQL EXEC(@SQL) END --************************************************************************************************* -- ============================================= -- Author: <奔跑的金鱼> -- Blog: <http://www.cnblogs.com/OliverQin/> -- Create date: <2015/01/04> -- Description: <批量备份数据库> -- ============================================= ----------------------------------------------------申明变量 DECLARE @FileName VARCHAR(200), @CurrentTime VARCHAR(50), @DBName VARCHAR(100), @SQL VARCHAR(1000) ---------------------------------------------------获取当前时间 SET @CurrentTime = CONVERT(CHAR(8),GETDATE(),112) + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) + CAST(DATEPART(mi, GETDATE()) AS VARCHAR) ---------------------------------------------------申明游标 DECLARE CurDBName CURSOR FOR SELECT NAME FROM Master..SysDatabases where dbid>4 ---------------------------------------------------打开游标 OPEN CurDBName -----------------------------------------------------移动游标指向到第一条数据，提取第一条数据存放在变量中 FETCH NEXT FROM CurDBName INTO @DBName -----------------------------------------------------如果上一次操作成功则继续循环 WHILE @@FETCH_STATUS = 0 BEGIN --Execute Backup SET @FileName = 'C:\backup\' + @DBName + '_' + @CurrentTime SET @SQL = 'BACKUP DATABASE ['+ @DBName +'] TO DISK = ''' + @FileName + '.bak' + ''' WITH NOINIT, NOUNLOAD, NAME = N''' + @DBName + '_backup'', NOSKIP, STATS = 10, NOFORMAT' EXEC(@SQL) --print @SQL -----------------------------------------------------继续提下一行数据 FETCH NEXT FROM CurDBName INTO @DBName END ---------------------------------------------关闭游标 CLOSE CurDBName ---------------------------------------------删除游标 DEALLOCATE CurDBName --************************************************************************************************* DECLARE @SQL VARCHAR(MAX) SELECT @SQL = COALESCE(@SQL,'') + ' BACKUP DATABASE '+ QUOTENAME(name,'[]') + ' TO DISK = ''C:\backup\'+ name + '_' + CONVERT(CHAR(8),GETDATE(),112) + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) + CAST(DATEPART(mi, GETDATE()) AS VARCHAR) + '.bak' + ''' WITH NOINIT, NOUNLOAD, NAME = N''' + name + '_backup'', NOSKIP, STATS = 10, NOFORMAT' FROM sys.databases WHERE database_id >4 AND name like '%%' AND state =0 PRINT(@SQL) EXECUTE(@SQL) |
只有自己想通了，说明你是真会了
