Sunday, 24 August 2014

SQL Server Database BackUp Using Query

DECLARE @name VARCHAR(50) -- database name  DECLARE @path VARCHAR(256) -- path for backup files  DECLARE @fileName VARCHAR(256) -- filename for backup  DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE   @path  VARCHAR(50)= 'D:\op\Cooper\DB\OKTest\'
DECLARE  @fileDate VARCHAR(50)= CONVERT(VARCHAR(20),GETDATE(),112) 

DECLARE  @filename  VARCHAR(100)=null
DECLARE db_cursor CURSOR FOR  SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases
 OPEN db_cursor   FETCH NEXT FROM db_cursor INTO @name  
 WHILE @@FETCH_STATUS = 0   BEGIN          SET @fileName = @path + @name + '_' + @fileDate + '.BAK'         BACKUP DATABASE @name TO DISK = @fileName 
        FETCH NEXT FROM db_cursor INTO @name   END  
 CLOSE db_cursor   DEALLOCATE db_cursor

No comments:

Post a Comment