How to Move a Database That Has Database Snapshots

  • Article

Database snapshots cannot be attached/detached, so how do you move a database that has database snapshots to a different disk location?

It turns out the _other_ method for moving databases works.  Take the database offline, alter the database to move the files, actually move the files, then online the database.  The twist with database snapshots is that you can't take them offline, but there's no need.  Once you offline the database you will be able to alter all the snapshots to change the location of the files.  Then when you online the databases, all the snapshots will come back online too.

EG

USE master;

go

--drop database SnapTest_Snap

--drop database SnapTest

GO

CREATE DATABASE SnapTest

ON PRIMARY ( NAME = data, FILENAME = 'c:\temp\snaptest.mdf' )

LOG ON ( NAME = log, FILENAME = 'c:\temp\snaptest.ldf') ;

 

go

CREATE DATABASE SnapTest_Snap ON

( NAME = data, FILENAME = 'c:\temp\snaptest.ss' )

AS SNAPSHOT OF SnapTest;

go

 

alter database SnapTest set offline

alter database SnapTest modify file (name = data, filename= 'c:\temp\newlocation\snaptest.mdf')

alter database SnapTest modify file (name = log, filename= 'c:\temp\newlocation\snaptest.ldf')

alter database SnapTest_Snap modify file (name = data, filename= 'c:\temp\newlocation\snaptest.ss')

 

 

go

--move the files, probably not with xp_cmdshell :)

exec xp_cmdshell 'move c:\temp\snaptest.* c:\temp\newlocation'

go

alter database SnapTest set online

 

--verify the snapshot is working

use SnapTest

create table t(id int)

select * from sys.tables

use SnapTest_Snap

select * from sys.tables

go

use master

restore database SnapTest from database_snapshot='SnapTest_Snap'

use SnapTest

select * from sys.tables