Results 1 to 13 of 13

Thread: script to get all databases present on a server ?

  1. #1
    Join Date
    Jul 2004
    Posts
    16

    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

  2. #2
    Join Date
    Jul 2004
    Posts
    16
    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

  3. #3
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    set quoted_identifier off
    exec sp_msforeachdb 'backup database ? to disk = ''d:\?.bak'' '

  4. #4
    Join Date
    Jul 2004
    Posts
    16
    sorry,

    but where can i see the content of that sp ?

  5. #5
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    --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

  6. #6
    Join Date
    Jul 2004
    Posts
    16
    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

  7. #7
    Join Date
    Jul 2004
    Posts
    16
    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

  8. #8
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    --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

  9. #9
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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.

  10. #10
    Join Date
    Jul 2004
    Posts
    16
    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

  11. #11
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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

  12. #12
    Join Date
    Jul 2004
    Posts
    16
    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

  13. #13
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •