Moure base de dades SQL Server

Es pot donar el cas que necessitem moure la ubicació dels fitxers de les bases de dades Microsoft SQL Server (versions 2012, 2014). Ja sigui per falta d’espai en disc, per canviar el tipus d’allotjament, per requeriments de rendiment, sistemes de còpies de seguretat, etc… Però, no només cal moure les bases de dades d’usuari, sinó que, segurament, també cal tocar les bases de dades de sistema, és a dir, la master, model, msdb i tempdb.

Per fer-ho, es parteix d’una instal·lació de Microsoft SQL Server en què les bases de dades, ja siguin dades d’usuari o de sistema, amb els seus fitxers de dades i transaccions, es troben a la mateixa carpeta de la instància, ubicades en un únic disc dur. La necessitat és separar aquests fitxers, posant els fitxers de dades en un disc dur dedicat i els fitxers de transaccions en un altre. La resta de dades de la instància es mantenen a la mateixa carpeta.

Per comprendre-ho millor, seguiré un exemple on es mouen les bases de dades de la seva ubicació original (E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA).

A la unitat K:\SQL Server Data, pels fitxers de dades, i la unitat L:\SQL Server Logs, pels fitxers de transaccions.

Moure bases de dades

Per fer les operacions amb la base de dades necessitarem de l’administrador de SQL Server, vaja, el Microsoft SQL Server Management Studio. Des del menú d’aplicacions clicar a SQL Server Management Studio.

Indicar la instància SQL Server on connectar per gestionar amb permisos d’administrador. És a dir, a la pregunta que es fa indiquem com a tipus de servidor: Motor de base de dades, com a nom del servidor es pot seleccionar el propi nom de l’equip, o localhost si és el servidor local. Com autenticació s’indica la integrada de Windows. Clicar Connectar per continuar.

Es carreguen els objectes del servidor Microsoft SQL Server al panell d’explorador d’objectes. Clicar al símbol de més (+) a la carpeta de base de dades per visualitzar el seu contingut. En la fotografia s’observa una base de dades d’usuari (servicedesk) i les 4 de sistema (master, model, msdb, tempdb).

Canviar les ubicacions per defecte dels fitxers de bases de dades

Per evitar que les noves bases de dades que es creïn ho facin a la carpeta per defecte de la instància, s’ha de canviar la ruta per defecte on es creen els nous fitxers de bases de dades. Botó dret damunt el nom del servidor i clicar a Propietats.

Es visualitza una nova finestra amb les propietats del servidor. A la banda esquerra, clicar a Configuració de base de dades.

A la banda dreta, a la part inferior, es troben les opcions d’ubicació de les bases de dades (fitxers, transaccions i còpies de seguretat). Clicar el botó amb els tres punts (…) Per canviar la ubicació per defecte de cadascuna d’elles.

Seleccionar la nova carpeta i clicar el botó Acceptar.

Repetir el procés pel registre de transaccions. Quan estigui correcte, clicar el botó Acceptar. Recordar que això fa que les noves bases de dades s’ubiquin en aquestes carpetes, però no mou les bases de dades ja creades.

Per aplicar els canvis s’ha de reiniciar el servei. Botó dret damunt el nom del servidor. Clicar a Reiniciar.

Es demana confirmació ja què es perdrà l’accés a les bases de dades, clicar el botó Si.

Comprovar que s’han aplicat correctament els canvis després del reinici tornant a accedir a les propietats del servidor SQL Server (Botó dret damunt el nom del servidor, clicar l’opció Propietats i accedir a l’apartat configuració de base de dades).

Per acabar de validar el canvi es pot crear una nova base de dades. Botó dret damunt de la carpeta base de dades i clicar a Nova base de dades.

Indicar el nom de la base de dades (BD_Prova). En el llistat de fitxers de base de dades es pot veure que els camins on s’ubiquen els fitxers són els nous que s’han definit. Clicar el botó Acceptar.

Moure la ubicació física de les bases de dades existents

Des de l’administrador de SQL Server (SQL Server Management Studio), botó dret damunt la base de dades que es vol moure, clicar a Propietats.

Clicar a l’opció de fitxers.

S’observa que el camí on hi ha els fitxers és el comú de la instància (unitat E en aquest cas). Clicar Acceptar per tancar les propietats.

Arribats a aquest punt, tenim dues opcions:

Desmuntar la base de dades, moure els fitxers i carregar-la de nou amb les noves ubicacions. L’afectació només seria a aquesta base de dades i no implica la parada del servei de SQL. No es permet canviar les bases de dades de sistema (master, model, msdb, tempdb).
Canviar totes les ubicacions de les bases de dades, parar el servidor SQL Server, moure els fitxers i tornar a engegar. Implica la parada del servei de SQL. Permet canviar totes les bases de dades.

Personalment, opto per aquesta última al trobar-me en fase de manteniment, ja què es pretén canviar les ubicacions de totes les bases de dades i és una bona manera de fer-ho de cop.

Des de l’administrador de SQL Server, iniciar una nova consulta clicant el botó Nova consulta de la barra de tasques.

Tot i que a les comandes es faci la selecció de la base de dades, també prefereixo seleccionar a la finestra de consulta la base de dades correcta. A la barra de tasques, al desplegable on hi ha el nom de la base de dades, seleccionar la base de dades MASTER que és on s’executaran les diferents instruccions.

Comprovar la ubicació física de les diferents bases de dades per comandes. Escriure les següents instruccions a la finestra de consulta, canviant el nom de la base de dades BD_Prova pel que correspongui en cada cas:

Use master
Go
Select name,physical_name from sys.master_files
 Where database_id=DB_ID(N'BD_Prova');
Go

Clicar al botó Executar o prèmer la tecla F5. En la part inferior del codi es visualitza el seu resultat. Per la base de dades que s’acaba de crear (BD_Prova) les ubicacions ja són les correctes.

El resultat és el mateix que hem visualitzat a les seves propietats. Podem fer el mateix per la resta de bases de dades de sistema i usuari:

Use master
Go
Select name,physical_name from sys.master_files
 Where database_id=DB_ID(N'master');
Go
Select name,physical_name from sys.master_files
 Where database_id=DB_ID(N'model');
Go
Select name,physical_name from sys.master_files
 Where database_id=DB_ID(N'msdb');
Go
Select name,physical_name from sys.master_files
 Where database_id=DB_ID(N'tempdb');
Go

Per no esborrar el llistat amb els noms dels fitxers i la seva ubicació, que necessitarem i al que tornarem després per comprovar, obrir una nova consulta (botó Nova consulta) i executar la comanda de modificació de la ubicació física del fitxer dins la base de dades:

MODIFY FILE (NAME = (Correspon a la primera columna del llistat anterior), FILENAME='(nova ubicació del fitxer)’);

D’aquesta forma, pel nostre exemple, les comandes per les bases de dades model, msdb, tempdb i la d’usuari servicedesk (no s’inclou a l’script al ser un exemple), exceptuant la MASTER que és diferent, serien :

Use master;
Go
ALTER DATABASE model
 MODIFY FILE (NAME = modeldev, FILENAME = 'K:\SQL Server Data\model.mdf');
 Go
ALTER DATABASE model
 MODIFY FILE (NAME = modellog, FILENAME = 'L:\SQL Server Logs\modellog.ldf');
 Go
ALTER DATABASE msdb
 MODIFY FILE (NAME = MSDBData, FILENAME = 'K:\SQL Server Data\MSDBData.mdf');
 Go
ALTER DATABASE msdb
 MODIFY FILE (NAME = MSDBLog, FILENAME = 'L:\SQL Server Logs\MSDBLog.ldf');
 Go
ALTER DATABASE tempdb
 MODIFY FILE (NAME = tempdev, FILENAME = 'K:\SQL Server Data\tempdb.mdf');
 Go
ALTER DATABASE tempdb
 MODIFY FILE (NAME = templog, FILENAME = 'L:\SQL Server Logs\templog.ldf');
 Go

Iniciar el configurador de SQL Server. Clicar al botó INICI de Windows i al llistat d’aplicacions, clicar a Administrador de configuració de SQL Server.

A la banda esquerra, si no està ja seleccionat, clicar a Serveis de SQL Server. A la banda dreta, botó dret damunt SQL Server (Nom de la instància) i clicar a l’opció Parar per aturar el servei. I sí, es pot fer des de molts llocs diferents més (administrador de serveis, administrador de SQL Server), s’ha fet des d’aquí perquè s’han de canviar uns paràmetres en aquesta finestra.

Amb el servei ja aturat, botó dret damunt SQL Server (Nom de la instància) i clicar a Propietats.

Clicar la pestanya Paràmetres d’inici.

De la llista de paràmetres, ens interessa el -d i el -l, corresponents a la ubicació del fitxer de dades i transaccions de la base de dades Master.

Seleccionar el paràmetre -d… El seu valor es carrega al quadre de text de la part superior. Modificar la ruta del fitxer i clicar el botó Actualitzar.

Repetir el procés pel paràmetre -l… Quan sigui tot correcte, clicar el botó Acceptar. S’adverteix que perquè els canvis tinguin validesa s’ha de reiniciar el servei. No és problema perquè ja hi ha el servei aturat.

Abans d’engegar s’han de moure els fitxers manualment a les noves ubicacions. Recordar que el tipus de fitxer SQL Server Database Primary Data (marcats en vermell) va a la unitat K i el fitxer SQL Server Database Transaction Log (marcats en blau) a la unitat L.

Tornant a l’administrador de configuració SQL Server, botó dret damunt SQL Server (nom instància) i clicar a Iniciar. Si tot és correcte s’ha d’engegar el servei de SQL Server i ja es pot tancar aquesta aplicació.

Tornar a l’Administrador SQL Server (SQL Management Studio), actualitzar la connexió amb el servidor, botó dret damunt el nom del servidor i clicar Actualitzar.

Obrir la pestanya amb la consulta de les ubicacions dels fitxers de la base de dades per tornar-la a executar (Tecla F5 o clicar a Executar). Les ubicacions dels fitxers de les bases de dades ja són a les noves. Operació completada amb èxit.

T’ha agradat l’article? El pots compartir a les xarxes socials. També pots deixar la teva opinió, comentari o suggeriment. Gràcies!

Moure bases de dades

Canviar les ubicacions per defecte dels fitxers de bases de dades

Moure la ubicació física de les bases de dades existents

Similar Posts by The Author:

Compartir: