Home
Manage Your Code
Snippet: Backup All SQL Server Databases (SQL)
Title: Backup All SQL Server Databases Language: SQL
Description: backup all your sql server databases and save them to a folder with date format file name. Views: 669
Author: dot net Date Added: 7/30/2011
Copy Code  
DECLARE @DBName VARCHAR(255)
DECLARE @DATABASES_Fetch INT
DECLARE @DBFileName VARCHAR(256)    
DECLARE @DateName VARCHAR(256)    

SET @DateName = (SELECT CONVERT(VARCHAR,GETDATE(),20))
SET @DateName = REPLACE(REPLACE(@DateName,':',''),'-','') 

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 or model
		AND DB_NAME(s_mf.database_id) NOT IN ('Master','tempdb','model')
    GROUP BY s_mf.database_id
    ORDER BY 1

OPEN DATABASES_CURSOR
FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @DBFileName = @DateName + ' - ' + REPLACE(REPLACE(@DBName,':','_'),'\','_') + '.bak'
    EXEC ('BACKUP DATABASE [' + @DBName + '] TO  DISK = N''c:\backup\' + 
        @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