I.T. Discussion Community!
-Collapse +Expand
DBA
Search DBA Group:

Advanced
-Collapse +Expand DBA Store
PRESTWOODSTORE

Prestwood eMagazine

October Edition
Subscribe now! It's Free!
Enter your email:

   ► KBSQL ServersMicrosoft SQ...   Print This     
 
DBA Microsoft SQL Server:
Easy SQL Server Backup Script
 
Posted 6 years ago on 5/12/2013
DBA Code Snippet:
 A flashcard from our DBA Flashcards Library
 A code snippet from our DBA Code Snippets Page

KB102439

This script can be run from a SQL Server Management Studio query window.  It will find all you databases that are not system databases, and not offline, then back them all up one at a time. 

Of course you need to specify your own location (mine is J:\Backups\) in order for this to work for you.

Syntax Example:

-- Back Up All Databases
-- by Bryan Valencia

--create temp table
declare @temp table(commands varchar(500), completed bit)

--load it with backup commands
insert into @temp (commands, completed)
(select
    'BACKUP DATABASE ['+name+
    '] TO  DISK = N''J:\Backups\'+name+
    '.bak'' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'''+name+
    '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10',
    0
from
    master.sys.databases
where
    owner_sid <> 0x01 and state_desc='ONLINE'
)

--variable for the current command
declare @thisCommand varchar(500);

--loop through the table
while (select count(1) from @temp where completed=0)>0
begin
    --find the first row that has not already been executed
    select top 1 @thisCommand = commands from @temp where completed=0

    --show the command in the "mesage" output window.
    print @thisCommand

    --execute the command
    EXEC (@thisCommand);

    --flag this row as completed.
    update @temp set completed=1 where commands=@thisCommand
end

--show the user the rows that have been found.
select * from @temp

This script makes use of the following concepts:

An in-memory temp table. 

Temp tables can be declared for processing this kind of repetitive task. 

declare @temp table(commands varchar(500), completed bit)

This creates a table named @temp with 2 columns, "commands" and "completed".  I then search the sys.databases table for all the databases that are user-created and online, inserting the complete backup command for that database  into the commands column, exactly as it would be typed in a SQL window.

A While Loop

I don't know if there is a way to cursor through a table in code, but I used this to read one command from the table, pull it into a varchar variable (@thisCommand) and pass all that to the EXEC command. 

EXEC Command

EXEC will take any string (varchar) value and attempt to execute it as if you had run it from a query window.


Comments

0 Comments.
Share a thought or comment...
 
Write a Comment...
...
Sign in...

If you are a member, Sign In. Or, you can Create a Free account now.


Anonymous Post (text-only, no HTML):

Enter your name and security key.

Your Name:
Security key = P1142A1
Enter key:
Code Contributed By Bryan Valencia:

Bryan Valencia is the author of millions of lines of code and writes for PrestwoodBoards and Visual Studio Journey on-line magazines.

Visit Profile

 KB Article #102439 Counter
6582
Since 5/12/2013

Follow PrestwoodBoards on: 


©1995-2019 PrestwoodBoards  [Security & Privacy]
Professional IT Services: Coding | Websites | Computer Tech