Backup all Databases on SQL Server

You work from 9 am to 6 pm and around 4 someone comes up to you and says "We got a problem. We need the backup of all 50 databases on our server and I want you to do that before you leave for the day." What is your reaction to that? And guess what, you are not even a Database Administrator.

All you know is this:

1. Open SQL Server Management Studio.
2. Connect to a Database Engine.
3. Right click on the database you want to back up.
4. Select Tasks.
5. Select Back Up.
6. Add a backup destination.
7. Specify the name of the backup.
8. Hit "OK".

Wow that was easy :)
But then you realise, you have to repeat this process for FIFTY databases so 50 times 8 Steps = 400 steps.. WOW.. now this is going to take quite a while. Say goodbye to your evening plans cause your boss just made you HIS plan :D

Then you think.. maybe someone can help me automate this. Thats when Paul Hayman comes to rescue. He has made this cool script which will backup all the databases on a SQL instance. This script saved me hours of work. No more clicking around.. just run the scrips. Following is the script, hope it helps:

DECLARE @DBName varchar(255)

DECLARE @DATABASES_Fetch int

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
    declare @DBFileName varchar(256)    
    set @DBFileName = datename(dw, getdate()) + ' - ' + 
                       replace(replace(@DBName,':','_'),'','_')

    exec ('BACKUP DATABASE [' + @DBName + '] TO  DISK = N''c:db 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

So there you are, just a single script to take care of all that and now you can go ahead with your evening plans ;)