Here I am going to share how can you take the database backup of all the SQL Server databases at once instead of taking it individually.

First, you need to create a folder in any drive to store the backup of database which you are going to take. For example, you have to create a folder named "dbBackup" in my drive "C:\"

Then, copy the below code & paste it in your SQL Server Query Executer page:

    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:\dbBackup\' +
            @DBFileName + '.bak' + ''' 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

Now execute the page and check your Folder "dbBackup" in Drive "C:/" . You will find all the databases backup in it.

Now, you are done! Simple, right?

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting
Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc.