Wilfred van Dijk

Het is altijd verstandig een actuele backup van de database te hebben. Mochten er problemen ontstaan tijdens het verplaatsen van databasefiles, dan kan altijd een backup terug gezet worden.

1. Bepaal de oude locatie van de datafiles
2. Zet de betreffende database OFFLINE
3. Kopieer de datafiles
4. Vertel MSSQL dat de files op een andere locatie staan
5. Zet de betreffende database ONLINE
6. Verwijder de datafiles op de oude locatie

Bepaal de oude locatie van de datafiles

Voordat we de datafiles gaan verplaatsen, moeten we weten waar deze nu staan. Dit is te halen uit de tabel sys.master_files in de master database:

select db_name(database_id) as databasename, type_desc, name, physical_name
from master.sys.master_files

Zet de database Offline

Om een bewerking op een datafile te kunnen doen, zul je exclusief toegang moeten hebben tot de datafiles. Dit kan op diverse manieren, ik gebruik de OFFLINE methode. Hiermee verwijder je de database en de security van de database niet, je ontzegt eindgebruikers alleen de toegang.

Let op: een database kan alleen in offline mode gezet worden als er geen connecties meer naar de database zijn. Verifieer dit dus voordat je aan deze actie begint. Denk hierbij ook aan services die een connectie hebben naar de database

Om een database offline te zetten voer je het volgende T-SQL commando uit:

USE MASTER
GO
ALTER DATABASE MyDatabase SET OFFLINE;

met USE MASTER zorg ik ervoor dat je in de MASTER database bent, want je kan alleen een database offline zetten waar niemand meer naar geconnecteerd is.

Kopieer de datafiles

Dit kan op verschillende manieren, ik gebruik vaak een DOS commando. Kopieer de bestanden van de database van de oude locatie naar de nieuwe locatie.

Vertel de nieuwe locatie aan MSSQL

Als de kopieerslag goed is uitgevoerd is de volgende stap het wijzigen van de locatie van de datafiles in de administratie van SQL Server. Dit gaat met het ALTER DATABASE MODIFY FILE commando. Het leuke (en tevens gevaarlijke) van deze actie is dat de wijziging pas van kracht wordt als de database weer online gezet wordt.
Onderstaand script maakt gebruik van de variabele @nieuwelocatie. Dit is de directory waar de datafiles naartoe gekopieerd zijn. Pas deze waarde aan voor jouw situatie. Vergeet ook niet de variabele @welkedatabase te wijzigen!

/*
Datafiles verplaatsen
Script geschreven door Wilfred van Dijk
http://www.wilfredvandijk.nl
Voor reacties of opmerkingen mail naar wfvdijk@gmail.com
DE SOFTWARE WORDT GELEVERD "ZOALS HET IS", ZONDER ENIGE VORM VAN GARANTIE, EXPLICIET OF IMPLICIET,
INCLUSIEF MAAR NIET BEPERKT TOT DE GARANTIES VAN GESCHIKTHEID VOOR EEN BEPAALD DOEL EN NIET-INBREUK.
IN GEEN GEVAL ZAL DE AUTEURS OF AUTEURSRECHTHOUDERS AANSPRAKELIJK VOOR ENIGE CLAIM, SCHADE OF ANDERE
AANSPRAKELIJKHEID, HETZIJ OP BASIS VAN CONTRACT, ONRECHT OF ANDERS, ALS GEVOLG VAN UIT OF IN VERBAND
MET DE SOFTWARE OF HET GEBRUIK OF ANDERE HANDELINGEN IN DE SOFTWARE.
*/
set nocount on
go
use master
go
declare @nieuwe_data_locatie varchar(256)
declare @nieuwe_log_locatie varchar(256)
declare @welkedatabase sysname
declare @rc int
declare @DOSCmd varchar(1000)
declare @SQLCmd nvarchar(1000)
create table #sqlcmd(Cmd nvarchar(max))
create table #doscmd(Cmd nvarchar(max))
-- pas onderstaande variabelen aan:
set @nieuwe_data_locatie = 'f:\temp\'
set @nieuwe_log_locatie = 'f:\temp\'
set @welkedatabase = 'wdi_biztalk'
/*
start
*/
print '/* Datafile copy mbv OFFLINE en ONLINE */'
print ''
print '-- database: ' + @welkedatabase
print '-- datafiles worden verplaatst naar pad ' + @nieuwe_data_locatie
print '-- logfiles worden verplaatst naar pad ' + @nieuwe_log_locatie
print ''
/*
Stap 1a: creeer SQL statements
*/
raiserror('-- Creeer SQL statements',10,1) with NOWAIT
insert
into #sqlcmd
select 'ALTER DATABASE '+ quotename(b.name) + ' modify file (name = "'+a.name+ '", filename = "'+ @nieuwe_data_locatie + right(a.physical_name, charindex('\', reverse(a.physical_name))-1) + '");'
from master.sys.master_files a
join master.sys.databases b
on  a.database_id = b.database_id
where b.name = @WelkeDatabase
and  a.type_desc = 'ROWS'
and  a.physical_name not like @nieuwe_data_locatie + '%'
union
select 'ALTER DATABASE '+ quotename(b.name) + ' modify file (name = "'+a.name+ '", filename = "'+ @nieuwe_log_locatie + right(a.physical_name, charindex('\', reverse(a.physical_name))-1) + '");'
from master.sys.master_files a
join master.sys.databases b
on  a.database_id = b.database_id
where b.name = @WelkeDatabase
and  a.type_desc = 'LOG'
and  a.physical_name not like @nieuwe_log_locatie + '%';
if (select count(*) from #sqlcmd) = 0
begin
print '-- Database bestaat niet, of pad klopt niet'
set @rc = 1
goto einde
end
/*
Stap 1b: Creeer copy statements
*/
raiserror('-- Creeer DOS copy statements',10,1) with NOWAIT
insert
into #doscmd
select 'copy "' + a.physical_name + '" "' + @nieuwe_data_locatie + '" /v'
from master.sys.master_files a
join master.sys.databases b
on  a.database_id = b.database_id
where b.name = @WelkeDatabase
and  a.type_desc = 'ROWS'
and  a.physical_name not like @nieuwe_data_locatie + '%'
union
select 'copy "' + a.physical_name + '" "' + @nieuwe_log_locatie + '" /v'
from master.sys.master_files a
join master.sys.databases b
on  a.database_id = b.database_id
where b.name = @WelkeDatabase
and  a.type_desc = 'LOG'
and  a.physical_name not like @nieuwe_log_locatie + '%';
/*
Stap 2: wacht totdat database OFFLINE is
*/
print '-- Alle statements staan klaar. Zet de database nu (handmatig) OFFLINE, of breek het script nu af.'
print ''
while not exists(select 1 from master.sys.databases where name = @welkedatabase and state_desc = 'OFFLINE')
begin
raiserror('-- Wachten totdat database [%s] OFFLINE is ...',10,1, @welkedatabase) with NOWAIT
waitfor delay '00:00:15'
end
/*
Stap 3: Kopieer bestanden
*/
declare c_lus cursor local static for
select * from #doscmd
open c_lus
fetch next from c_lus into @DOSCmd
while @@Fetch_status = 0
begin
raiserror(@DOSCmd,10,1) with NOWAIT
exec @rc = master..xp_cmdshell @DOSCmd
if @rc > 0
begin
print '-- FOUTMELDING tijdens kopieren'
break
end
fetch next from c_lus into @DOSCmd
end
close c_lus
deallocate c_lus
if @rc > 0
goto einde
/*
Stap 4: Voer wijzigingen door in SQL
*/
declare c_lus cursor local static for
select * from #sqlcmd
open c_lus
fetch next from c_lus into @SQLCmd
while @@Fetch_status = 0
begin
raiserror(@SQLCmd,10,1) with NOWAIT
exec (@SQLCmd)
fetch next from c_lus into @SQLCmd
end
close c_lus
deallocate c_lus
/*
Stap 5: wacht totdat database ONLINE
*/
set @SQLCmd = 'ALTER DATABASE '+ QUOTENAME(@welkedatabase) + ' SET ONLINE'
raiserror(@SQLCmd,10,1) with NOWAIT
exec (@SQLCmd)
if not exists(select 1 from master.sys.databases where name = @welkedatabase and state_desc = 'ONLINE')
begin
set @rc = 1
print '-- ERROR: Database komt niet ONLINE'
end
einde:
if @rc = 0
print '-- Success! (files op oude locatie kunnen gewist worden)'
else
print '-- Fouten opgetreden'
drop table #doscmd
drop table #sqlcmd

Wanneer je dit script uitvoert worden de commando's gegenereerd om de locatiegegevens in MSSQL te wijzigen. De commando's zijn dan nog niet uitgevoerd! Controleer de commando's op juistheid en volledigheid, knip/plak ze in een nieuwe Query window en voer ze uit.

Zet de database online

We hebben de datafiles verplaatst en de administratie van MSSQL bijgewerkt. We kunnen de database weer beschikbaar maken. Dit doen we door de database ONLINE te brengen:

USE MASTER
GO
ALTER DATABASE MyDatabase SET ONLINE;

Verwijder de datafiles op de oude locatie

Als de database foutloos op de nieuwe locatie is opgestart (check errorlog), kunnen de datafiles op de oude locatie verwijderd worden.

Samenvatting

Met het OFFLINE commando kun je op een snelle manier datafiles verplaatsen. De downtime is nagenoeg gelijk aan de tijd die het kost om de bestanden te kopieren.