script to get all databases present on a server ?

Hi all,

I'm really new to creating scripts for MS SQL Server administration; but i need to do this :

create a script that will get all datbases names created on a specific server and then backup them all to 1 file per database.

I found that i could take the name of all my databases using :

SELECT name, filename FROM master.dbo.sysdatabases

and that backup could be done using such a command line :

BACKUP DATABASE [MYDATABASE_NAME] TO DISK = N'D:\Backups\DATABASES\DB_MYDATABASE_NAME.bak' WITH INIT , NOUNLOAD , NAME = N'Backup for MYDATABASE_NAME', NOSKIP , STATS = 10, NOFORMAT

Finally, the difficulty is to do a loop that would get all databases names, run the specific command and ignore if database name is TEMPDB of course.

Then being able to send an email with the log report into it would be great ;-))

thanks for your help

Florent, from France

I need to do it in order to not have to manually add a scheduled job every time a database is created / deleted ;-))

that way, i'd be sure to backup EVERY databases of the server

thanks

Florent

set quoted_identifier off
exec sp_msforeachdb 'backup database ? to disk = ''d:\?.bak'' '

sorry,

but where can i see the content of that sp ?

--use this.

set quoted_identifier off
declare @query varchar(1000)
declare @path varchar(100)
declare @dbname varchar(128)
set @path ='c:\backup\'
declare DBcursor cursor for select name from sysdatabases where name not in ('master','msdb','tempdb','model')
open DBCursor
Fetch DBCursor into @dbname
while @@fetch_status = 0
begin
set @query = 'backup database ' + @dbname+ ' to disk="' +@path+ @DBname+ '.bak" with init'
print @query
exec (@query)
Fetch DBCursor into @dbname
end
close DBCursor
deallocate DBCursor

thanks, all is working fine ;-))

Jsut 2 more questions :

1) How could I insert the today's date and time into backup file name

2) How can i schedule this sql script execution ?

thanks

Florent

i often see such a line :

-- Start by truncating the logs.
BACKUP LOG MASTER WITH TRUNCATE_ONLY

should i add such a command too ?

Florent

--Date and time as suffix
set quoted_identifier off
declare @query varchar(1000)
declare @path varchar(100)
declare @dbname varchar(128)
set @path ='c:\backup\'
declare DBcursor cursor for select name from sysdatabases where name not in ('master','msdb','tempdb','model')
open DBCursor
Fetch DBCursor into @dbname
while @@fetch_status = 0
begin
set @query = 'backup database ' + @dbname+ ' to disk="' +@path+ @DBname+ '-'+replace(replace(convert (varchar(22),getdate(),120),':','-'),' ','-')+ '.bak" with init'
print @query
exec (@query)
Fetch DBCursor into @dbname
end
close DBCursor
deallocate DBCursor

create SQL Jobs

1. Open enterprise manager
2. Expand "Management"
3. Expand "SQL Server Agent"
4. Right Click on "Jobs"
5. Click on "New Job"
then......you will know.

Thanks MAK,

it works fine ;-))

Another thing :
When i open a sql command window and copy/paste de script, the output window show me :

error messages and succes messages (generated by MS SQL Server itself) + my own messages that i inserted using the "print" command...

how could i send each in a separated log file in order to schedule this in a batch file and get a log of errrors / succes + run a specific command after script is completed (sending an email with log file into it using a command line tool like "Blat" for example...)

thanks

Florent

use OSQL.exe

I dont think you can separate the success and failures in a script.
(somebody correct me if I am wrong)

you can redirect all the output to a file using
>

example:

osql -SSQL -E _Dpubs -Q"create table x1(
id int)" >x.txt

Mak,

I finally used Enterprise Manager to schedule my job.

Now, i'd like to create a second step for my job that would send by email, the content of the log generated during its execution.

here is the sample code i wanted to use, but i don't know how to run it from my job, should i use "CmdExec" for my second step job command type ?

' Send Email from a Script
' http://www.microsoft.com/technet/com.../scrent15.mspx
' SMTP service must running on computer
Set objEmail = CreateObject("CDO.Message")
objEmail.From = "alerts@MYDOMAIN.com"
objEmail.To = "webmaster@MYDOMAIN.com"
objEmail.Subject = "Alert : Test Email"
objEmail.Textbody = "This is a test Email."
objEmail.Send

florent

you can use SQLMail if you had set it up already.

you can use SMTP email using VBScript. Save you code in a file (c:\sendmail.vbs)
and select commandexec as type in your job and in the command type c:\sendmail.vbs

you can do it from SQL Server
http://www.databasejournal.com/sqlet...le.php/1585201