Home > Link, Microsoft SQL Server, MSBI, Optimization, Query, Script, SQL, SQL Mentalist, SQL PraRup, SQL Query, SQL Server, Technology,, Vishal Pawar > BI SQL # 122 : SQL Server DBA Scripts : Script to backup all Database in SQL Server

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 :

Link Resource Website

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

| FaceBook |Twitter | linkedIn| Google+ | WordPress | RSS |

Advertisements
  1. World Traveler
    July 2, 2013 at 11:00 am

    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

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: