Partager via

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

Comments

  • Anonymous
    July 27, 2013
    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?

  • Anonymous
    October 13, 2013
    I agree with the previous question. How does one move all to another machine ?

  • Anonymous
    December 10, 2013
    The comment has been removed