How to Move a Database That Has Database Snapshots
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