备份所有数据库SQL

 

USE MASTER
declare @path varchar(50)
declare @fpath varchar(100)
DECLARE tnames_cursor CURSOR 
FOR 
     SELECT NAME 
     FROM SYSDATABASES
OPEN tnames_cursor 
DECLARE @tablename sysname 
--SET @tablename = 'authors' 
FETCH NEXT FROM tnames_cursor INTO @tablename 
WHILE (@@FETCH_STATUS =0) 
BEGIN 
set @path='D:\每周自动备份\'+@tablename
set @fpath=@path+'\'+@tablename+'.bak'
    print @fpath
EXECUTE master.dbo.xp_create_subdir @path
BACKUP DATABASE @tablename TO  DISK = @fpath WITH NOFORMAT, NOINIT,  NAME = @tablename, SKIP, REWIND, NOUNLOAD,  STATS = 10 
     FETCH NEXT FROM tnames_cursor INTO @tablename 
END 
CLOSE tnames_cursor 
DEALLOCATE tnames_cursor