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

Advanced
-Collapse +Expand DBA Store
PRESTWOODSTORE

Prestwood eMagazine

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

   ► KBSQL ServersMicrosoft SQ...   Print This     
Next Random Article Next Random Tidbit || Change Topic Random From All

Random Tidbit

DBA Code Snippet:
 A flashcard from our DBA Flashcards Library
 A code snippet from our DBA Code Snippets Page
Easy SQL Server Backup Script

Learn how to make an easy SQL Server Script that will automatically back up all your databases in a simple way.


-- 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

Switch to longer article version of Easy SQL Server Backup Script (DBA, Databases, & Data)

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 = P1182A1
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
6613
Since 5/12/2013

Follow PrestwoodBoards on: 


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