Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

Back Up All Databases Using T-SQL

, 9 May 2013
Rate this:
Please Sign up or sign in to vote.
Backing up all databases using T-SQL

Introduction

All of us have a daily routine to create a back up of databases on a daily or weekly basis. I am here presenting a simple script that can create backup of all databases except the system databases. Backup files will be saved in the format NameOfDatabase_YYYYMMDDHHMMSS.bak, where the name of database will be appended with date at which back up is created in format NameOfDatabase_YYYYMMDDHHMMSS where YYYY is Year, MM is month in numeric, DD Date, HHMMSS is hours, minutes and seconds.

  1. Backup all databases except the system databases
  2. Backup files will be saved in the format NameOfDatabase_YYYYMMDDHHMMSS.bak
-Name of database
DECLARE @DatabaseName VARCHAR(50)

-Path of backup folder
DECLARE @BackupPath VARCHAR(256)

-Name of backup file
DECLARE @BackUpFileName VARCHAR(256)

-Get current date used for suffixing with file name
DECLARE @Date VARCHAR(20)

-Specify path for database backup directory. 
-Make sure directory exists before executing script, else script will give error
SET @BackupPath = ‘C:\Backup\’

-Get current date used for suffixing with file name
SELECT @Date = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),‘:’,”)

-Declaring cursor for storing database names
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases

-Excluding system databases
WHERE name NOT IN (‘master’,‘model’,‘msdb’,‘tempdb’)

-For specific database, replace TestDB with 
-required database name in the below line and uncomment it: 
AND name IN (‘TestDB’)

-Fetching database names from cursor to local variable
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DatabaseName

-Loop for all databases
WHILE @@FETCH_STATUS = 0
BEGIN

    -Setting file name in format NameOfDatabase_YYYYMMDDHHMMSS
    SET @BackUpFileName = @BackupPath + @DatabaseName + ‘_’ + @Date + ‘.bak’

    -Creating back up
    BACKUP DATABASE @DatabaseName TO DISK = @BackUpFileName

    -Fetching next database name
    FETCH NEXT FROM db_cursor INTO @DatabaseName

END

-Close and deallocate cursor
CLOSE db_cursor
DEALLOCATE db_cursor

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

PRANAV SINGH

India India
No Biography provided

Comments and Discussions

 
QuestionResembles with MS SQL SEREVER Pinmembercarlospenny9-May-13 10:34 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 9 May 2013
Article Copyright 2013 by PRANAV SINGH
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid