Backuping up multiple databases at once

I'm going through the docs and I can't find a way to backup multiple databases at once, rather than them individually. I see you can create a backup device, but you have to select every individual database. I know in 2000 you could do it ....

Are you referring to using a maintanence plan or t-sql? I assume a maintanence plan?

Regardless, you can't backup multiple db's in a single backup operation...what you can do is create multiple backup statements, one for each database (which is what would happen in SQL 2000 if you chose multiple db's from the GUI), then each database will be backed up serially (i.e. one after the other), and would look like this in t-sql:

backup db1 to disk....

backup db2 to disk....

backup db3 to disk....

If you'd like to backup multiple db's in parallel, you could take each of your t-sql backup commands and run them across seperate connections, or create seperate scheduled jobs for each...

I don't have the 2005 GUI on this computer, so I can't validate/verify the selection of multiple db's from it, but I would be surprised if you can't do it from the Maintanence Plan...seems odd...I'll check later and get back to you if nobody else responds before I get to it...

HTH,

|||

Thanks Chad

That works out just fine.. i can do it individually . I thought I was missing an obvious setting or something . I'm not that experienced with 2005 and I wasn't sure.

I appreciate the response.

|||

Backup task UI of Maintenace plan provides selection of multiple databases.

Gops Dwarak

Backup via Maintenance Plan

Hi all,
Just a quick question. I've searched through the docs for this but am still
not certain. Can you tell me if, during a Maintenance Plan's backup
procedure, the database is still available or does the MP put it into
Single-User Mode / Offline?
Cheers,
IMAs in all backups, the database is available to all users.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Ian Murphy" <IanMurphy@.discussions.microsoft.com> wrote in message
news:E1C32915-5794-45BF-BE2F-513937DBCD1A@.microsoft.com...
Hi all,
Just a quick question. I've searched through the docs for this but am still
not certain. Can you tell me if, during a Maintenance Plan's backup
procedure, the database is still available or does the MP put it into
Single-User Mode / Offline?
Cheers,
IM|||Many thanks Tom. That's what I thought but it's good to know for sure.
Cheers,
IM
"Tom Moreau" wrote:

> As in all backups, the database is available to all users.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Ian Murphy" <IanMurphy@.discussions.microsoft.com> wrote in message
> news:E1C32915-5794-45BF-BE2F-513937DBCD1A@.microsoft.com...
> Hi all,
> Just a quick question. I've searched through the docs for this but am sti
ll
> not certain. Can you tell me if, during a Maintenance Plan's backup
> procedure, the database is still available or does the MP put it into
> Single-User Mode / Offline?
> Cheers,
> IM
>|||This is true but there are a few file management type operations that
cannot run while a backup is in progress:
* ALTER DATABASE (with ADD FILE or REMOVE FILE)
* DBCC SHRINKDATABASE
* DBCC SHRINKFILE
A backup won't start while these are in progress and you can't start one
of these operations while a backup is in progress.
*mike hodgson*
/ mallesons stephen jaques/
blog: http://sqlnerd.blogspot.com
Tom Moreau wrote:

>As in all backups, the database is available to all users.
>
>

Backup via Maintenance Plan

Hi all,
Just a quick question. I've searched through the docs for this but am still
not certain. Can you tell me if, during a Maintenance Plan's backup
procedure, the database is still available or does the MP put it into
Single-User Mode / Offline?
Cheers,
IMAs in all backups, the database is available to all users.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Ian Murphy" <IanMurphy@.discussions.microsoft.com> wrote in message
news:E1C32915-5794-45BF-BE2F-513937DBCD1A@.microsoft.com...
Hi all,
Just a quick question. I've searched through the docs for this but am still
not certain. Can you tell me if, during a Maintenance Plan's backup
procedure, the database is still available or does the MP put it into
Single-User Mode / Offline?
Cheers,
IM|||Many thanks Tom. That's what I thought but it's good to know for sure.
Cheers,
IM
"Tom Moreau" wrote:
> As in all backups, the database is available to all users.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Ian Murphy" <IanMurphy@.discussions.microsoft.com> wrote in message
> news:E1C32915-5794-45BF-BE2F-513937DBCD1A@.microsoft.com...
> Hi all,
> Just a quick question. I've searched through the docs for this but am still
> not certain. Can you tell me if, during a Maintenance Plan's backup
> procedure, the database is still available or does the MP put it into
> Single-User Mode / Offline?
> Cheers,
> IM
>|||This is a multi-part message in MIME format.
--010201060107060106030405
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
This is true but there are a few file management type operations that
cannot run while a backup is in progress:
* ALTER DATABASE (with ADD FILE or REMOVE FILE)
* DBCC SHRINKDATABASE
* DBCC SHRINKFILE
A backup won't start while these are in progress and you can't start one
of these operations while a backup is in progress.
--
*mike hodgson*
/ mallesons stephen jaques/
blog: http://sqlnerd.blogspot.com
Tom Moreau wrote:
>As in all backups, the database is available to all users.
>
>
--010201060107060106030405
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>This is true but there are a few file management type operations
that cannot run while a backup is in progress:<br>
</tt>
<ul>
<li><tt>ALTER DATABASE (with ADD FILE or REMOVE FILE)</tt></li>
<li><tt>DBCC SHRINKDATABASE</tt></li>
<li><tt>DBCC SHRINKFILE</tt></li>
</ul>
<tt>A backup won't start while these are in progress and you can't
start one of these operations while a backup is in progress.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<em><font face="Tahoma" size="2"> mallesons</font><font face="Tahoma"> </font><font
face="Tahoma" size="2">stephen</font><font face="Tahoma"> </font><font
face="Tahoma" size="2"> jaques</font></em><font face="Tahoma"><br>
</font><font face="Tahoma" size="2">blog:</font><font face="Tahoma"
size="2"> <a href="http://links.10026.com/?link=/">http://sqlnerd.blogspot.com">
http://sqlnerd.blogspot.com</a></font></span> </p>
</div>
<br>
<br>
Tom Moreau wrote:
<blockquote cite="miduOjdsJ%23dFHA.1136@.TK2MSFTNGP12.phx.gbl"
type="cite">
<pre wrap="">As in all backups, the database is available to all users.
</pre>
</blockquote>
</body>
</html>
--010201060107060106030405--

Backup via Maintenance Plan

Hi all,
Just a quick question. I've searched through the docs for this but am still
not certain. Can you tell me if, during a Maintenance Plan's backup
procedure, the database is still available or does the MP put it into
Single-User Mode / Offline?
Cheers,
IM
As in all backups, the database is available to all users.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Ian Murphy" <IanMurphy@.discussions.microsoft.com> wrote in message
news:E1C32915-5794-45BF-BE2F-513937DBCD1A@.microsoft.com...
Hi all,
Just a quick question. I've searched through the docs for this but am still
not certain. Can you tell me if, during a Maintenance Plan's backup
procedure, the database is still available or does the MP put it into
Single-User Mode / Offline?
Cheers,
IM
|||Many thanks Tom. That's what I thought but it's good to know for sure.
Cheers,
IM
"Tom Moreau" wrote:

> As in all backups, the database is available to all users.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Ian Murphy" <IanMurphy@.discussions.microsoft.com> wrote in message
> news:E1C32915-5794-45BF-BE2F-513937DBCD1A@.microsoft.com...
> Hi all,
> Just a quick question. I've searched through the docs for this but am still
> not certain. Can you tell me if, during a Maintenance Plan's backup
> procedure, the database is still available or does the MP put it into
> Single-User Mode / Offline?
> Cheers,
> IM
>
|||This is true but there are a few file management type operations that
cannot run while a backup is in progress:
* ALTER DATABASE (with ADD FILE or REMOVE FILE)
* DBCC SHRINKDATABASE
* DBCC SHRINKFILE
A backup won't start while these are in progress and you can't start one
of these operations while a backup is in progress.
*mike hodgson*
/ mallesons stephen jaques/
blog: http://sqlnerd.blogspot.com
Tom Moreau wrote:

>As in all backups, the database is available to all users.
>
>