Opções de arquivo e grupos de arquivos ALTER DATABASE (Transact-SQL)

Modifica os arquivos e grupos de arquivos associados ao banco de dados. Adiciona ou remove arquivos e grupos de arquivos de um banco de dados, altera os atributos de um banco de dados ou seus arquivos e grupos de arquivos. Para outras opções ALTER DATABASE, consulte ALTER DATABASE (Transact-SQL).

Convenções de sintaxe Transact-SQL

Sintaxe

ALTER DATABASE database_name 
{
    <add_or_modify_files>
  | <add_or_modify_filegroups>
}
[;]

<add_or_modify_files>::=
{
    ADD FILE <filespec> [ ,...n ] 
        [ TO FILEGROUP { filegroup_name } ]
  | ADD LOG FILE <filespec> [ ,...n ] 
  | REMOVE FILE logical_file_name 
  | MODIFY FILE <filespec>
}

<filespec>::= 
(
    NAME = logical_file_name  
    [ , NEWNAME = new_logical_name ] 
    [ , FILENAME = {'os_file_name' | 'filestream_path' } ] 
    [ , SIZE = size [ KB | MB | GB | TB ] ] 
    [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ] 
    [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ] 
    [ , OFFLINE ]
) 

<add_or_modify_filegroups>::=
{
    | ADD FILEGROUP filegroup_name 
        [ CONTAINS FILESTREAM ]
    | REMOVE FILEGROUP filegroup_name 
    | MODIFY FILEGROUP filegroup_name
        { <filegroup_updatability_option> 
        | DEFAULT
        | NAME = new_filegroup_name 
        }
}
<filegroup_updatability_option>::=
{
    { READONLY | READWRITE } 
    | { READ_ONLY | READ_WRITE }
}

Argumentos

<add_or_modify_files>::=

Especifica o arquivo a ser adicionado, removido ou modificado.

• database_name
  É o nome do banco de dados a ser modificado.

• ADD FILE
  Adiciona um arquivo ao banco de dados.

  • TO FILEGROUP { filegroup_name }
    Especifica o grupo de arquivos ao qual adicionar o arquivo especificado. Para exibir os grupos de arquivos atuais e qual grupo de arquivos é o padrão atual, use a exibição do catálogo sys.filegroups.

• ADD LOG FILE
  Adiciona um arquivo de log ao banco de dados especificado.

• REMOVE FILE logical_file_name
  Remove a descrição de arquivo lógica de uma instância do SQL Server e exclui o arquivo físico. O arquivo não pode ser removido, a menos que esteja vazio.

  • logical_file_name
    É o nome lógico usado no SQL Server ao referenciar o arquivo.

• MODIFY FILE
  Especifica o arquivo que deve ser modificado. Só uma propriedade de <filespec>pode ser alterada de cada vez. NAME sempre deve ser especificado em <filespec> para identificar o arquivo a ser modificado. Se SIZE for especificado, o novo tamanho deverá ser maior que o tamanho de arquivo atual.

  Para modificar o nome lógico de um arquivo de dados ou de um arquivo de log, especifique o nome do arquivo lógico a ser renomeado na cláusula NAME e especifique o novo nome lógico para o arquivo na cláusula NEWNAME. Por exemplo:

  MODIFY FILE ( NAME = logical_file_name, NEWNAME = new_logical_name ) 
  

  Para mover um arquivo de dados ou de log para um novo local, especifique o nome do arquivo lógico atual na cláusula NAME e especifique o novo caminho e nome do arquivo do sistema operacional na cláusula FILENAME. Por exemplo:

  MODIFY FILE ( NAME = logical_file_name, FILENAME = ' new_path/os_file_name ' )
  

  Quando você move um catálogo de texto completo, especifique somente o novo caminho na cláusula FILENAME. Não especifique o nome de arquivo do sistema operacional.

  Para obter mais informações, consulte Movendo arquivos de banco de dados.

  Para um grupo de arquivos FILESTREAM, NAME pode ser modificado online. FILENAME pode ser modificado online; entretanto, a operação não entra em vigor até que o contêiner seja fisicamente realocado e o servidor seja desligado e reiniciado.

  Você pode definir um arquivo FILESTREAM como OFFLINE. Quando um arquivo FILESTREAM está offline, seu grupo de arquivos pai será internamente marcado como offline; portanto, todo acesso aos dados FILESTREAM naquele grupo de arquivos falhará.

<filespec>::=

Controla as propriedades do arquivo.

• NAME logical_file_name
  Especifica o nome lógico do arquivo.

  • logical_file_name
    É o nome lógico usado em uma instância do SQL Server ao referenciar o arquivo.

• NEWNAME new_logical_file_name
  Especifica um novo nome lógico para o arquivo.

  • new_logical_file_name
    É o nome para substituir o nome do arquivo lógico existente. O nome deve ser exclusivo dentro do banco de dados e estar de acordo com a regras para identificadores. O nome pode ser uma constante de caractere ou Unicode, um identificador comum ou delimitado. Para obter mais informações, consulte Utilizando identificadores como nomes de objetos.

• FILENAME { 'os_file_name' | 'filestream_path' }
  Especifica o nome do arquivo (físico) do sistema operacional.

  • ' os_file_name '
    Para um grupo de arquivos padrão (ROWS), esse é o caminho e o nome do arquivo usado pelo sistema operacional quando você cria o arquivo. O arquivo deve residir no servidor no qual o SQL Server está instalado. O caminho especificado deve existir antes da execução da instrução ALTER DATABASE.

    Os parâmetros SIZE, MAXSIZE e FILEGROWTH não podem ser definidos quando um caminho UNC está especificado para o arquivo.

    Arquivos de dados não devem ser colocados em sistemas de arquivos compactados a não ser que os arquivos sejam secundários e somente leitura ou que o banco de dados seja somente leitura. Arquivos de log nunca devem ser colocados em sistemas de arquivos compactados. Para obter mais informações, consulte Grupos de arquivos somente leitura e compactação.

    Se o arquivo estiver em uma partição bruta, o os_file_name deverá especificar apenas a letra da unidade de uma partição bruta existente. Apenas um arquivo de dados pode ser colocado em cada partição bruta.

  • 'filestream_path'
    Para um grupo de arquivos FILESTREAM, FILENAME faz referência a um caminho onde dados FILESTREAM serão armazenados. O caminho até a última pasta deve existir e a última pasta não deve existir. Por exemplo, se você especificar o caminho C:\MyFiles\MyFilestreamData, C:\MyFiles deve existir antes da execução de ALTER DATABASE, mas a pasta MyFilestreamData não deve existir.

    O grupo de arquivos e o arquivo (<filespec>) devem ser criados na mesma instrução. Pode haver apenas um arquivo, <filespec>, para um grupo de arquivos FILESTREAM.

    As propriedades SIZE, MAXSIZE e FILEGROWTH não se aplicam a um grupo de arquivos FILESTREAM.

• SIZE size
  Especifica o tamanho do arquivo. SIZE não se aplica a grupos de arquivos FILESTREAM.

  • size
    É o tamanho do arquivo.

    Quando especificado com ADD FILE, size é o tamanho inicial para o arquivo. Quando especificado com MODIFY FILE, size é o novo tamanho para o arquivo e deve ser maior do que o tamanho do arquivo atual.

    Quando size não for fornecido para o arquivo primário, o SQL Server usará o tamanho do arquivo primário no banco de dados modelo. Quando um arquivo de dados secundário ou arquivo de log estiver especificado, mas size não estiver, o Mecanismo de Banco de Dados tornará o arquivo em um arquivo de 1 MB.

    Os sufixos KB, MB, GB e TB podem ser usados para especificar kylobytes, megabytes, gigabytes ou terabytes. O padrão é MB. Especifique um número inteiro e não inclua um decimal. Para especificar uma fração de um megabyte, converta o valor em kilobytes multiplicando o número por 1024. Por exemplo, especifique 1536 KB em vez de 1,5 MB (1.5 x 1024 = 1536).

• MAXSIZE { max_size| UNLIMITED }
  Especifica o tamanho máximo de arquivo até o qual o arquivo pode crescer. MAXSIZE não se aplica a grupos de arquivos FILESTREAM.

  • max_size
    É o tamanho máximo do arquivo. Os sufixos KB, MB, GB e TB podem ser usados para especificar kylobytes, megabytes, gigabytes ou terabytes. O padrão é MB. Especifique um número inteiro e não inclua um decimal. Se max_size não for especificado, o arquivo aumentará até que o disco esteja cheio.

  • UNLIMITED
    Especifica que o arquivo crescerá até que o disco esteja cheio. No SQL Server, um arquivo de log especificado com crescimento ilimitado tem um tamanho máximo de 2 TB e um arquivo de dados tem um tamanho máximo de 16 TB.

• FILEGROWTH growth_increment
  Especifica o incremento de crescimento automático do arquivo. A configuração de FILEGROWTH de um arquivo não pode exceder a configuração de MAXSIZE. FILEGROWTH não se aplica a grupos de arquivos FILESTREAM.

  • growth_increment
    É a quantidade de espaço adicionada ao arquivo sempre que novo espaço é necessário.

    O valor pode ser especificado em MB, KB, GB, TB ou porcentagem (%). Se um número estiver especificado sem um sufixo MB, KB ou %, o padrão será MB. Quando % está especificada, o tamanho do incremento de crescimento é a porcentagem especificada do tamanho do arquivo no momento em que ocorre o incremento. O tamanho especificado é arredondado para os 64 KB mais próximos.

    Um valor 0 indica que o crescimento automático está definido como off e nenhum espaço adicional é permitido.

    Se FILEGROWTH não estiver especificado, o valor padrão será de 1 MB para arquivos de dados e 10% para arquivos de log e o valor mínimo será de 64 KB.

    Observação
    A partir do SQL Server 2005, o incremento de crescimento padrão para arquivos de dados foi alterado de 10% para 1 MB. O padrão de 10% do arquivo de log permanece inalterado.

• OFFLINE
  Define o arquivo como offline e torna todos os objetos no grupo de arquivos inacessíveis.

  Cuidado
  Só use essa opção quando o arquivo estiver corrompido e puder ser restaurado. Um arquivo definido como OFFLINE só pode ser definido como online restaurando o arquivo do backup. Para obter mais informações sobre como restaurar um único arquivo, consulte RESTORE (Transact-SQL).

<add_or_modify_filegroups>::=

Adicione, modifique ou remova um grupo de arquivos do banco de dados.

• ADD FILEGROUP filegroup_name
  Adiciona um grupo de arquivos ao banco de dados.

• CONTAINS FILESTREAM
  Especifica que o grupo de arquivos armazena BLOBs (objetos binários grandes) FILESTREAM no sistema de arquivos.

• REMOVE FILEGROUP filegroup_name
  Remove um grupo de arquivos do banco de dados. O grupo de arquivos não pode ser removido, a menos que esteja vazio. Remova todos os arquivos do grupo de arquivos primeiro. Para obter mais informações, consulte "REMOVE FILE logical_file_name," anteriormente neste tópico.

• MODIFY FILEGROUP filegroup_name { <filegroup_updatability_option> | DEFAULT | NAME **=**new_filegroup_name }
  Modifica o grupo de arquivos definindo o status como READ_ONLY ou READ_WRITE, tornando o grupo de arquivos o padrão para o banco de dados ou alterando o nome do grupo de arquivos.

  • <filegroup_updatability_option>
    Define a propriedade somente leitura ou de leitura/gravação para o grupo de arquivos.

  • DEFAULT
    Altera o grupo de arquivos do banco de dados padrão para filegroup_name. Apenas um grupo de arquivos no banco de dados pode ser o grupo de arquivos padrão. Para obter mais informações, consulte Compreendendo arquivos e grupos de arquivos.

  • NAME = new_filegroup_name
    Altera o nome de grupo de arquivos para o new_filegroup_name.

<filegroup_updatability_option>::=

Define a propriedade somente leitura ou de leitura/gravação para o grupo de arquivos.

• READ_ONLY | READONLY
  Especifica que o grupo de arquivos é somente leitura. Não são permitidas atualizações nos objetos. O grupo de arquivos primário não pode ser somente leitura. Para alterar esse estado, é necessário ter acesso exclusivo ao banco de dados. Para obter mais informações, consulte a cláusula SINGLE_USER.

  Como um banco de dados somente leitura não permite modificações de dados:

  • A recuperação automática é ignorada na inicialização de sistema.

  • Não é possível reduzir o banco de dados.

  • Não ocorrem bloqueios em bancos de dados somente leitura. Isso pode acelerar o desempenho da consulta.

  Observação
  A palavra-chave READONLY será removida em uma versão futura do MicrosoftSQL Server. Evite usar READONLY em novos trabalhos de desenvolvimento e planeje modificar os aplicativos que atualmente o utilizam. Em vez disso, use READ_ONLY.

• READ_WRITE | READWRITE
  Especifica que o grupo é READ_WRITE. As atualizações são habilitadas para os objetos no grupo de arquivos. Para alterar esse estado, é necessário ter acesso exclusivo ao banco de dados. Para obter mais informações, consulte a cláusula SINGLE_USER.

  Observação
  A palavra-chave READWRITE será removida em uma versão futura do MicrosoftSQL Server. Evite usar READWRITE em novos trabalhos de desenvolvimento e planeje modificar os aplicativos que atualmente o utilizam. Em vez disso, use READ_WRITE.

O status dessas opções pode ser determinado examinando a coluna is_ready_only na exibição do catálogo sys.databases ou a propriedade Updateability da função DATABASEPROPERTYEX.

Comentários

Para diminuir o tamanho de um banco de dados, use DBCC SHRINKDATABASE.

Você não poderá adicionar ou remover um arquivo enquanto uma instrução BACKUP estiver em execução.

Um máximo de 32.767 arquivos e 32.767 grupos de arquivos pode ser especificado para cada banco de dados.

No SQL Server 2005 ou posterior, o estado de um arquivo de banco de dados (por exemplo, online ou offline) é mantido independentemente do estado do banco de dados. Para obter mais informações, consulte Estados de arquivo. O estado dos arquivos dentro de um grupo de arquivos determina a disponibilidade de todo o grupo. Para que um grupo de arquivos fique disponível, todos os seus arquivos devem estar online. Se um grupo de arquivos estiver offline, toda a tentativa de acessá-lo por uma instrução SQL falhará com erro. Quando você cria planos de consulta para instruções SELECT, o otimizador de consultas evita índices não clusterizados e exibições indexadas que residam em grupos de arquivos offline. Isso permite que essas instruções tenham êxito. Porém, se o grupo de arquivos offline contiver o heap ou índice clusterizado da tabela de destino, as instruções SELECT falharão. Além disso, qualquer instrução INSERT, UPDATE ou DELETE que modifica uma tabela contendo algum índice em um grupo de arquivos offline falhará.

Movendo arquivos

No SQL Server 2005 ou versão posterior, você pode mover dados do sistema ou definidos pelo usuário e arquivos de log, especificando o novo local em FILENAME. Isso pode ser útil nos seguintes cenários:

• Recuperação de falha. Por exemplo, o banco de dados está em modo suspeito ou desligado por falha no hardware.

• Realocação planejada.

• Realocação para manutenção de disco programada.

Para obter mais informações, consulte Movendo arquivos de banco de dados.

Iniciando arquivos

Por padrão, arquivos de dados e de log são iniciados ao serem preenchidos com zeros quando você executa uma das seguintes operações:

• Criar um banco de dados.

• Adicionar arquivos a um banco de dados existente.

• Aumentar o tamanho de um arquivo existente.

• Restaurar um banco de dados ou grupo de arquivos.

Arquivos de dados podem ser iniciados de imediato. Isso permite uma execução rápida dessas operações de arquivo. Para obter mais informações, consulte Inicialização de arquivos de bancos de dados.

Exemplos

A. Adicionando um arquivo a um banco de dados.

O exemplo a seguir adiciona um arquivo de dados de 5-MB ao banco de dados AdventureWorks.

USE master;
GO
ALTER DATABASE AdventureWorks 
ADD FILE 
(
    NAME = Test1dat2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\t1dat2.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB

);
GO

B. Adicionando um grupo de arquivos com dois arquivos a um banco de dados

O exemplo a seguir cria o grupo de arquivos Test1FG1 no banco de dados AdventureWorks e adiciona dois arquivos de 5-MB ao grupo de arquivos.

USE master
GO
ALTER DATABASE AdventureWorks
ADD FILEGROUP Test1FG1;
GO

ALTER DATABASE AdventureWorks 
ADD FILE 
(
    NAME = test1dat3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\t1dat3.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1dat4,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\t1dat4.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP Test1FG1;
GO

C. Adicionando dois arquivos de log a um banco de dados

O exemplo a seguir adiciona dois arquivos de log de 5-MB ao banco de dados AdventureWorks.

USE master;
GO
ALTER DATABASE AdventureWorks 
ADD LOG FILE 
(
    NAME = test1log2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test2log.ldf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1log3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test3log.ldf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
);
GO

D. Removendo um arquivo de um banco de dados

O exemplo a seguir remove um dos arquivos adicionado no exemplo B.

USE master;
GO
ALTER DATABASE AdventureWorks
REMOVE FILE test1dat4;
GO

E. Modificando um arquivo

O exemplo a seguir aumenta o tamanho de um dos arquivos adicionados no exemplo B.

USE master;
GO
ALTER DATABASE AdventureWorks 
MODIFY FILE
    (NAME = test1dat3,
    SIZE = 20MB);
GO

F. Movendo um arquivo para um novo local

O exemplo a seguir move o arquivo Test1dat2 criado no exemplo A para um novo diretório.

Observação
Você deve mover o arquivo fisicamente para o novo diretório antes de executar este exemplo. Depois, pare e reinicie a instância do SQL Server coloque o banco de dados AdventureWorks OFFLINE e, em seguida, ONLINE para implementar a alteração.

USE master;
GO
ALTER DATABASE AdventureWorks
MODIFY FILE
(
    NAME = Test1dat2,
    FILENAME = N'c:\t1dat2.ndf'
);
GO

G. Movendo tempdb para um novo local

O exemplo a seguir move tempdb de seu local atual no disco para outro local no disco. Como tempdb é recriado cada vez que o serviço MSSQLSERVER é iniciado, não é necessário mover fisicamente os arquivos de dados e de log. Os arquivos são criados quando o serviço é reiniciado na etapa 3. Enquanto o serviço não é reiniciado, tempdb continua funcionando em seu local existente.

1. Determine os nomes de arquivo lógicos do banco de dados tempdb e o seu local atual no disco.

   SELECT name, physical_name
   FROM sys.master_files
   WHERE database_id = DB_ID('tempdb');
   GO
   

2. Altere o local de cada arquivo usando ALTER DATABASE.

   USE master;
   GO
   ALTER DATABASE tempdb 
   MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
   GO
   ALTER DATABASE  tempdb 
   MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
   GO
   

3. Pare e reinicie a instância do SQL Server.

4. Verifique a alteração do arquivo.

   SELECT name, physical_name
   FROM sys.master_files
   WHERE database_id = DB_ID('tempdb');
   

5. Exclua os arquivos tempdb.mdf e templog.ldf de seu local de origem.

H. Tornando um grupo de arquivos o padrão

O exemplo a seguir torna o grupo de arquivos Test1FG1 criado no exemplo B no grupo de arquivos padrão. Depois, o grupo de arquivos padrão é redefinido para o grupo de arquivos PRIMARY. Observe que PRIMARY deve ser delimitado por parênteses ou aspas.

USE master;
GO
ALTER DATABASE AdventureWorks 
MODIFY FILEGROUP Test1FG1 DEFAULT;
GO
ALTER DATABASE AdventureWorks 
MODIFY FILEGROUP [PRIMARY] DEFAULT;
GO

I. Adicionando um grupo de arquivos usando ALTER DATABASE

O exemplo a seguir adiciona um FILEGROUP que contém a cláusula FILESTREAM ao banco de dados FileStreamPhotoDB.

--Create and add a FILEGROUP that CONTAINS the FILESTREAM clause to
--the FileStreamPhotoDB database.
ALTER database FileStreamPhotoDB
ADD FILEGROUP TodaysPhotoShoot
CONTAINS FILESTREAM
GO

--Add a file for storing database photos to FILEGROUP 
ALTER database FileStreamPhotoDB
ADD FILE
(
    NAME= 'PhotoShoot1',
    FILENAME = 'C:\Users\Administrator\Pictures\TodaysPhotoShoot.ndf'
)
TO FILEGROUP TodaysPhotoShoot
GO

Consulte também

Referência

CREATE DATABASE (Transact-SQL)

DATABASEPROPERTYEX (Transact-SQL)

DROP DATABASE (Transact-SQL)

sp_spaceused (Transact-SQL)

sys.databases (Transact-SQL)

sys.database_files (Transact-SQL)

sys.data_spaces (Transact-SQL)

sys.filegroups (Transact-SQL)

sys.master_files (Transact-SQL)

Outros recursos

Projetando e implementando armazenamento FILESTREAM