none
Verificar caminhos dos arquivos MDF e LDF RRS feed

  • Pergunta

  • Olá amigos!

    Estou com uma dúvida sobre os caminnhos dos banco de dados do MS SQL Server.

    Gostaria de saber se existe uma maneira de listar todos os caminhos das bases  de dados que estão criadas na instancia do MS SQL Server 2000, 2005, etc.

    A procedure sp_helpfile me mostra o caminho certo do arquivo. Exemplo: o MDF está no D:\Data e o log esta no E:\Log. Porém, só me mostra se o banco de dados estiver selecionado e não de todos de uma só vez.

    Mas preciso de uma procedure ou select que me mostre de todos os databases inclusive os offline.

    Tem como?

    Obrigado

    Forte abraço


    Clayton Santos
    clayton1745@gmail.com
    ITIL|MCTS
    • Movido Gustavo Maia Aguiar quarta-feira, 8 de junho de 2011 21:52 (De:SQL Server - Desenvolvimento Geral)
    quarta-feira, 8 de junho de 2011 14:42

Respostas

  • No SQL Server 2005 pra frente você pode utilizar o seguinte SELECT:

    SELECT database_id, name, physical_name
    FROM sys.master_files AS mf

     

    Att.


    Erickson Ricci SQL Server DBA @ BB Mapfre ericksonricci.wordpress.com MCTS Database Developer 2008, Database Administrator 2008 Classifique as respostas. O seu feedback é imprescindível
    • Sugerido como Resposta Fausto.Branco quarta-feira, 8 de junho de 2011 20:54
    • Não Sugerido como Resposta Clayton Santos quinta-feira, 9 de junho de 2011 13:37
    • Marcado como Resposta Clayton Santos quinta-feira, 9 de junho de 2011 15:21
    quarta-feira, 8 de junho de 2011 19:08
  • Clayton,

    Segue um modo mais simples...

    select DB_NAME(dbid), name, filename from sysaltfiles 
    
    

    att.
    Marcelo Fernandes


    MCP, MCDBA, MCSA, MCTS.
    Se útil, classifique!!!
    Me siga no twitter: @marcelodba

    quinta-feira, 9 de junho de 2011 13:49
    Moderador

Todas as Respostas

  • Clayton, nunca tinha me deparado com essa necessidade. Criei um script para lhe ajudar.

    Ele cria uma tabela (coloquei como temporária) e cria uma procedure que irá executar a sys.database_files para cada banco. Você tem um retorno que te mostra inclusive o status do banco (Online ou Offline). Depois de criada a tabela e a procedure, basta executar a procedure. Segue o script:

    -- Cria uma tabela para armazenar as informações de cada banco

    Create Table #Arquivos

    (

     file_id                 int,

     file_guid               uniqueidentifier,

     type                    tinyint,

     type_desc               nvarchar(120),

     data_space_id           int,

     name                    sysname,

     physical_name           nvarchar(520),

     state                   tinyint,

     state_desc              nvarchar(120),

     size                    int,

     max_size                int,

     growth                  int,

     is_media_read_only      bit,

     is_read_only            bit,

     is_sparse               bit,

     is_percent_growth       bit,

     is_name_reserved        bit,

     create_lsn              numeric,

     drop_lsn                numeric,

     read_only_lsn           numeric,

     read_write_lsn          numeric,

     differential_base_lsn   numeric,

     differential_base_guid  uniqueidentifier,

     differential_base_time  datetime,

     redo_start_lsn          numeric,

     redo_start_fork_guid    uniqueidentifier,

     redo_target_lsn         numeric,

     redo_target_fork_guid   uniqueidentifier,

     backup_lsn              numeric

    )

    go

     

    Create Procedure stp_SelecionaArquivos as

    Begin

      SET NOCOUNT ON

     

      Truncate Table #Arquivos

     

      if OBJECT_ID('TempDb..#Comandos', 'U') is not Null

        Drop Table #Comandos

       

      -- Cria uma tabela com os comandos a serem executados

      Select

        Name,

        Comando = 'Use ' + Name + '; ' +

                  'Insert Into #Arquivos Select * From sys.database_files;'

      Into

        #Comandos

      From

        sys.databases

     

      -- Coloca todos os comandos numa única linha, para uma única execução

      Declare @Comando VarChar(max) = ''

      Select @Comando = Comando + @Comando From #Comandos

      Exec (@Comando)

     

      -- Resgata as informações selecionadas

      Select * From #Arquivos

     

      SET NOCOUNT OFF

    End

     

     

    Exec stp_SelecionaArquivos


    Roberson Ferreira - Database Developer

    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    quarta-feira, 8 de junho de 2011 15:26
  • See outra solucao,

     

    DECLARE @Script VARCHAR(8000)

    SET @Script = ''

    DECLARE @Databases TABLE

    (

    DB VARCHAR(100),

    FilePath VARCHAR(100)

    )

     

    SELECT @Script = @Script + 'USE '+Name+CHAR(13)+'SELECT DB_NAME(), Physical_Name FROM SYS.DATABASE_FILES'+CHAR(13) FROM SYS.DATABASES

    INSERT INTO @Databases EXEC (@Script)

    SELECT * FROM @Databases


    Oracle OCA11g, MCC 2011! Dicas e novidades: www.fabrizziocaputo.wordpress.com
    quarta-feira, 8 de junho de 2011 16:17
    Moderador
  • Olá Fabrizzio, como vai?

    No SQL Server 2005 ele funciou perfeitamente porém no SQL Server 2000 ele me dá a seguinte mensagem de erro:

    Msg 197, Level 15, State 1, Line 12

    EXECUTE cannot be used as a source when inserting into a table variable.

    No SQL Server 2000 acredito que tenha que alterar alguma linha de comando para uma mais antiga, certo?

    Um forte abraço


    Clayton Santos
    clayton1745@gmail.com
    ITIL|MCTS
    quarta-feira, 8 de junho de 2011 17:34
  • Clayton,

     

    Infelizmente no SQL 2000, o comando: INSERT INTO EXEC não é permitido, infelizmente nao possuo conhecimento o sulficiente do sql 2000 para pensar em uma alternativa, talvez alguma outra pessoa do forum possa lhe ajudar.


    Oracle OCA11g, MCC 2011! Dicas e novidades: www.fabrizziocaputo.wordpress.com
    quarta-feira, 8 de junho de 2011 17:43
    Moderador
  • No SQL Server 2005 pra frente você pode utilizar o seguinte SELECT:

    SELECT database_id, name, physical_name
    FROM sys.master_files AS mf

     

    Att.


    Erickson Ricci SQL Server DBA @ BB Mapfre ericksonricci.wordpress.com MCTS Database Developer 2008, Database Administrator 2008 Classifique as respostas. O seu feedback é imprescindível
    • Sugerido como Resposta Fausto.Branco quarta-feira, 8 de junho de 2011 20:54
    • Não Sugerido como Resposta Clayton Santos quinta-feira, 9 de junho de 2011 13:37
    • Marcado como Resposta Clayton Santos quinta-feira, 9 de junho de 2011 15:21
    quarta-feira, 8 de junho de 2011 19:08
  • Clayton, você fez o teste com o script que lhe passei? Acredito que ele funciona no 2000. Não funcionou? Deu algum erro?

    Ele está grande porque o fiz numa visão macro. Mas ele pode ser reduzido de acordo com as informações que quiser resgatar.

    Segue outro exemplo, menor:

    Create Table #Arquivos

    (type_desc nvarchar(120), name sysname, physical_name nvarchar(520))

    go

     

    Select Comando = 'Use ' + Name + '; ' + 'Insert Into #Arquivos Select type_desc, name, physical_name From sys.database_files;'

    Into #Comandos From sys.databases

    go

     

    Declare @Comando VarChar(max) = ''

    Select @Comando = Comando + @Comando From #Comandos

    Exec (@Comando)

     

    Select * From #Arquivos


    Roberson Ferreira - Database Developer

    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    quarta-feira, 8 de junho de 2011 20:50
  • Olá Roberson, como vai?

    Eu testei sim, obrigado!

    Só que essa tabela de sistema sys.database_files não existe no MS SQL Server 2000 só no MS SQL Server 2005 pra frente. =S

    Eu procurei outra tabela no MS SQL Server 2000 mas não encontrei.

    Eu precisava mais no MS SQL Server 2000 do que as outras versões mais novas, por que aqui o que mais tenho é servidor utilizando a versão antiga.

    Forte abraço


    Clayton Santos
    clayton1745@gmail.com
    ITIL|MCTS
    quinta-feira, 9 de junho de 2011 13:36
  • Clayton,

    Segue um modo mais simples...

    select DB_NAME(dbid), name, filename from sysaltfiles 
    
    

    att.
    Marcelo Fernandes


    MCP, MCDBA, MCSA, MCTS.
    Se útil, classifique!!!
    Me siga no twitter: @marcelodba

    quinta-feira, 9 de junho de 2011 13:49
    Moderador
  • Clayton, no 2000 me parece que a tabela correspondente é a "sysfiles". Se puder testar e postar pra gente saber, eu agradeço. Não tenho o 2000 instalado aqui.
    Roberson Ferreira - Database Developer

    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    quinta-feira, 9 de junho de 2011 14:31
  • Ola marcelo.

    Não conhecia essa tabela de sistema. Deu certinho no SQL Server 2000.

    Vou utilizar sua solução e do Erikson que são as mais simples.

    Agradeço muito pela ajuda do Roberson e Fabrizzio. Muito obrigado mesmo!!!

    Um forte abraço


    Clayton Santos
    clayton1745@gmail.com
    ITIL|MCTS
    quinta-feira, 9 de junho de 2011 15:21