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.