How can we take all the SQL SERVER Database Backup at once - Online Free Computer Tutorials.

'Software Development, Games Development, Mobile Development, iOS Development, Android Development, Window Phone Development. Dot Net, Window Services,WCF Services, Web Services, MVC, MySQL, SQL Server and Oracle Tutorials, Articles and their Resources

Thursday, July 21, 2011

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.

No comments:

Post a Comment