SQL Server 自動備份數據庫,並鏡像備份到異地電腦

--功能說明:自動備份數據庫,並鏡像備份到異地電腦，保存一周的備份文件。

 

declare @physicalname nvarchar(260)

declare @logicalname   sysname

declare @localphysicalname nvarchar(260)

declare @locallogicalname nvarchar(260)

declare @databasename sysname

declare @medianame sysname

 

declare @weekday int

select @weekday=datepart(weekday,getdate())

 

DECLARE database_cursor CURSOR FOR

SELECT [name] FROM  sys.databases

 

OPEN database_cursor;

FETCH NEXT FROM database_cursor INTO @databasename

 

WHILE @@FETCH_STATUS = 0

   BEGIN

        select @medianame=@databasename+N'Set1'

        --設置備份裝置名稱

        select @logicalname=@databasename+N'_mirror'+convert(nvarchar(1),@weekday)

        select @locallogicalname=@databasename+convert(nvarchar(1),@weekday)

        --設置鏡像網絡路徑

        select @physicalname= N'//192.168.1.1/f$/backup/'+@databasename+'/'+@logicalname+N'.bak'

        --設置本地備份路徑

        select @localphysicalname=N'F:/backup/'+@databasename+'/'+@databasename+'.bak'

 

        --create local device

        if not exists(select null from master.dbo.sysdevices where [name] = @locallogicalname)

            EXEC master.dbo.sp_addumpdevice  @devtype = N'disk', @logicalname =@locallogicalname, @physicalname =@localphysicalname

 

        --create mirror device

        if not exists(select null from master.dbo.sysdevices where [name] = @logicalname)

            EXEC master.dbo.sp_addumpdevice  @devtype = N'disk', @logicalname =@logicalname, @physicalname =@physicalname

 

        --完整备 ¥÷到device

        Backup Database @databasename To @locallogicalname

        Mirror

        To @logicalname

        WITH INIT,FORMAT,

           MEDIANAME = @medianame

      FETCH NEXT FROM database_cursor INTO @databasename

END

 

CLOSE database_cursor

DEALLOCATE database_cursor