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 |
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.
|