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

 


Comments (3)

  1. On the same machine... says:

    Good info.  However from the title I thought you were talking about moving databases between machines rather than between folders/drives on the same machine (and instance of SQL Server).  

    Not that I've ever really needed to, but is there a (supported or even unsupported) way to move a database and its snapshots from one server to another?

  2. I agree with the previous question. How does one move all to another machine ?

  3. dbrowne says:

    There's no way to move a snapshot to another machine.  The snapshot does not really contain a copy of all the data, so you can't move it off-box.  A SAN snapshot can be mounted to another server.