Re: Sql2005-move system database

B_gd_n[ ]Sahlean — Fri, 26 Feb 2010 18:37:54 GMT

Vezi aici.

Sql2005-move system database

muraruadrian — Fri, 26 Feb 2010 11:13:14 GMT

Buna ziua,

Din motive de mentenanta, sunt nevoit sa reloc bazele de date pe alta partitie. Pentru bazele de date utilizator si cele system exceptand 'master' si 'mssqlsystemresource' totul a fost ok(detalii sunt in BOL). Adica,

For each file to be moved, run the following statement.


ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )

Stop the instance of SQL Server or shut down the system to perform maintenance. For more information, see Stopping Services.
Move the file or files to the new location.
Restart the instance of SQL Server or the server.

Verify the file change by running the following query.


SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'<database_name>');

Problema apare la mutarea lui 'master' si 'mssqlsystemresource'. Acestea, in final trebuie sa fie in aceeasi locatie. Pentru 'master', relocarea functioneaza. Adica, cf BOL:

Start-All Programs-Microsoft SQL Server 2005-Configuration Tools-SQL Server Configuration Manager.
SQL Server 2005 Services node, right-click MSSQLSERVER-Properties.
In SQL Server (MSSQLSERVER) Properties-Advanced tab.
Modificam Startup Parameters pentru noua locatie a bazei 'master'.
Oprire instanta SQL Server.
Mutare fisiere master.mdf si mastlog.ldf in noua locatie.

Repornire instanta of SQL Server in master-only recovery mode

NET START MSSQLSERVER /f /T3608


NET START MSSQLSERVER /f /T3608

Pana aici, totul e ok. Ce nu functioneaza de fapt? Acum ar trebui sa rulez din Sqlcmd comanda de realocare a bazei mssqlsystemresource, urmata de mutarea fisierelor in noua locatie:


ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= 'new_path_of_master\mssqlsystemresource.mdf'); GO ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= 'new_path_of_master\mssqlsystemresource.ldf'); GO Practic, nici nu ma mai pot conecta cu sqlcmd. Mesajul este: HResult 0xE9, Level 16, State 1 Shared Memory Provider: No process is on the other end of the pipe. Sqlcmd: Error: Microsoft SQL Native Client : Communication link failure.

ALTER DATABASE mssqlsystemresource 
    MODIFY FILE (NAME=data, FILENAME= 'new_path_of_master\mssqlsystemresource.mdf');
GO
ALTER DATABASE mssqlsystemresource 
    MODIFY FILE (NAME=log, FILENAME= 'new_path_of_master\mssqlsystemresource.ldf');
GO

Practic, nici nu ma mai pot conecta cu sqlcmd. Mesajul este:

HResult 0xE9, Level 16, State 1
Shared Memory Provider: No process is on the other end of the pipe.
Sqlcmd: Error: Microsoft SQL Native Client : Communication link failure.

Intrebari generale

Re: Sql2005-move system database

Sql2005-move system database