I think your example will work to get all of the databases. Another approach
would be to use a cursor to step through the tables. Here is the example:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
use master
declare @vsdbname sysname

declare cc cursor for select name from sysdatabases
open cc
fetch cc into @vsdbname

while (@@fetch_status=0)
begin
/* INSERT BACKUP CODE HERE */

fetch cc into @vsdbname
end

close cc
deallocate cc
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

- Jim

-----Original Message-----
From: isahhar [mailto:[Email Address Removed]
Sent: Wednesday, April 28, 2004 11:54 AM
To: 'Jim Lowell'; 'LazyDBA.com Discussion'
Subject: RE: Backup/restore procedures.

Hello Jim
Thank you for this code. I was wondering if you could use some changes to
allow for all the databases to be dumped/backed up with code that looks
something like this??

maybe instead of JUST passing in the db name

select @vsDB_name=ltrim(rtrim(db_name()))

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- do something like this -----------
DECLARE @MinDBName VARCHAR(50),
@MaxDBName VARCHAR(50)

SELECT @MinDBName = MIN(name) FROM sysdatabases
SELECT @MaxDBName = MAX(name) FROM sysdatabases

WHILE @MinDBName <= @MaxDBName

BEGIN

-- Set the filename
if @intfulldump=1
set @vsFileName=@vsDB_name + '_full_' + @sDOW + '-' + @vsHour +
'-' + @vsMinute + '.dat'
else
set @vsFileName=@vsDB_name + '_trans_' + @sDOW + '-' + @vsHour +
'-' + @vsMinute + '.dat'

-- Set the dump string
if @intfulldump=1
set @vsDump_String='dump database ' + @vsDB_name + ' to
disk=''\\nas01\intouch_backup\' + @vsServer_Name + '\' + @vsDB_name +
'\week1\'
+ @vsFileName + ''' with init, stats=5'
else
set @vsDump_String='dump transaction ' + @vsDB_name + ' to
disk=''\\nas01\intouch_backup\' + @vsServer_Name + '\' + @vsDB_name +
'\week1\'
+ @vsFileName + ''' with init, stats=5'

-- Run the backup
exec (@vsDump_String)

------ Select next database -----------------------------------
SELECT @MinDBName = MIN(name)
FROM sysdatabases
WHERE name > @MinDBName
END
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Peace
Ishaq Sahhar
<><


-----Original Message-----
From: Jim Lowell [mailto:[Email Address Removed] Wednesday, April 28, 2004 11:53 AM
To: LazyDBA.com Discussion
Subject: RE: Backup/restore procedures.

We wrote our own scripts because we have multiple databases and wanted to be
able to schedule a single job using a single procedure that put the backup
files in the correct locations on our NAS box. The stored procedure we use
is attached to this email although I don't know if it will come through the
mailing list or not. If the file doesn't attach, you can see the stored
procedure at this URL:

http://wwwmain1.intouchavl.com/lazydba/

The gist of it is that the procedure builds a filepath that consists of the
server name and the database name. The file name is then build based on the
day of the week and the backup type (full or transaction). The resulting
filepath + filename are used to call DUMP DATABASE or DUMP TRANSACTION to
the filename that was generated. We run the exact same script on all of our
database servers and just have to create the matching directory structure on
our network path.

The restore procedure is much easier. Assuming you know the filename you
want to restore, you call something like this:

restore database mydatabase
from disk='\\networkserver\filepath\dbfile.dat'
with replace, stats=10

If you want to restore the main database with and apply transaction logs
after that, make this call instead:

restore database mydatabase
from disk='\\networkserver\filepath\dbfile.dat'
with replace, norecovery, stats=10

And then call this for each transaction log to apply after it:

restore log intouch_comms
from disk='\\networkserver\filepath\logfile.dat'
with norecovery, stats=10

The LAST log file must be called with RECOVERY instead of NORECOVERY or the
database will be left in a loading state and you won't be able to use it.

Hope this help.

- Jim

-----Original Message-----
From: tessier.[Email Address Removed] [mailto:tessier.[Email Address Removed] Wednesday, April 28, 2004 7:46 AM
To: LazyDBA.com Discussion
Subject: Backup/restore procedures.

Hi all.
I'm considering writing backup and restore procedures. Any of you DBAs have
procedures that you would be ready to share or know about resources I could
look at to start with. Any help will be greatly appreciated.

Thanks

Pierre Tessier
IT Applications Specialist | Spécialiste des applications de TI Information
Management and Technology Directorate | Directorat de la gestion de
l'information et de la technologie Corporate Services Branch | Direction des
services ministériels Department of Finance and Treasury Board of Canada
Secretariat | Ministère des Finances et Secrétariat du Conseil du Trésor du
Canada Ottawa, Canada K1A 0G5
613-996-8958 | Tessier.[Email Address Removed] | facsimile/télécopieur 613-995-5235

MS Sql Server LazyDBA home page