BI SQL # 122 : SQL Server DBA Scripts : Script to backup all Database in SQL Server
Hi Geeks,
In this article we are going to cover Script to backup all Database in SQL Server.
In this post we are going to discuss following points:
- Problem Statement of SQL Script:
- Description of SQL Script:
- Input Parameter of SQL Script
- SQL Script Code
- User Level to execute
- SQL Script Output Screenshot
Problem Statement of SQL Script:
How to take back up all databases in SQL Server?
Description of SQL Script:
Self-Explanatory
Input Parameter of SQL Script
Folder where bak files will be stored = @Path
SQL Script Code
DECLARE @name VARCHAR(50) -- database name DECLARE @path VARCHAR(256) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup DECLARE @fileDate VARCHAR(20) -- used for file name --File Naming Format DBname_YYYYDDMM_HHMMSS.BAK --If you want to also include the time in the filename you can replace
this line in the above script: -- specify filename format SELECT @fileDate = CONVERT(VARCHAR(20), GETDATE(), 112) --with this line: -- specify filename format SELECT @fileDate = CONVERT(VARCHAR(20), GETDATE(), 112) + REPLACE(CONVERT( VARCHAR(20), GETDATE(), 108), ':', '') -- specify database backup directory SET @path = 'C:\Backup\' -- specify filename format SELECT @fileDate = CONVERT(VARCHAR(20), GETDATE(), 112) DECLARE db_cursor CURSOR FOR SELECT NAME FROM master.dbo.sysdatabases WHERE NAME NOT IN ( 'master' ,'model' ,'msdb' ,'tempdb' ) -- exclude these databases OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + '_' + @fileDate + '.BAK' BACKUP DATABASE @name TO DISK = @fileName FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor
User Level to execute
- 100
Hope you will like Script to backup all Database in SQL Server.
If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.
If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog :
For More information related to BI World visit my Mentalist Blog
SQL Server Mentalist >> SQL Learning Blog
Business Intelligence Mentalist >> BI World
Infographic Mentalist >> Image worth explaining thousand Words
Microsoft Mentalist >> MVC,ASP.NET, WCF & LinQ
DBA Mentalist >>Advance SQL Server Blog
Microsoft BI Mentalist >> MS BI Development Update
Connect With me on
SQL Server Mentalist 
By using this SQL query you can backup a SQL server database.
Single Database Backup:
BACKUP DATABASE DataBaseName TO DISK = ‘FileName’
All Database Backup:
This query will backup all the databases existing in a SQL server except System Databases (master,model, msdb, tempdb)
Query is here:
http://cybarlab.blogspot.com/2012/12/sql-server-database-backup.html