How can we take all the SQL SERVER Database Backup at once

Rn2786
Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 6211 red flag

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


 Download source code for How can we take all the SQL SERVER Database Backup at once

Hello Friends,


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

First we need to create a folder in any drive to store the backup of database which we are going to take.

Here I have created a folder named "dbBackup" in my drive "C:\"


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 & you are done....!!!


Check your Folder "dbBackup" in Drive "C:/" . You will find all the databases backup in it.


Enjoy...!! :)

Page copy protected against web site content infringement by Copyscape

About the Author

Rn2786
Full Name: Raj Lakshmi Nahata
Member Level: Starter
Member Status: Member
Member Since: 2/22/2011 2:50:40 PM
Country: India
Best Regards, Rajlakshmi Nahata www.creativecoder.in
www.creativecoder.in
System Admin, Software-developer & web designer.

Login to vote for this post.

Comments or Responses

Posted by: PandianS on: 7/20/2011 | Points: 25
Hi

Nice sample!

I would like to say something, The script is used for RESTORE! not for BACKUP. Am I correct ?

The explanation is given for BACKUP but the script is given for RESTORE!

Kindly check and change accordingly!
Posted by: Akiii on: 7/21/2011 | Points: 25
Excellent stuff....it is quite helpful

Thanks for sharing.....

Regards
Akiii

Login to post response

Comment using Facebook(Author doesn't get notification)