Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jbates99
Constraint Violating Yak Guru
396 Posts |
 Posted - 2014-02-17 : 16:34:45
|
| Is it possible to make a database snapshot but have the new database (ss file) created on a different SQL Server?This is what I ran:CREATE DATABASE Database1Snapshot ON( NAME = Database1, FILENAME = '\\Server1\D$\SQL_Data\Database1Snapshot.ss' )AS SNAPSHOT OF Database1;But I get this error:Msg 5133, Level 16, State 1, Line 1Directory lookup for the file "\\Server1\D$\SQL_Data\Database1Snapshot.ss" failed with the operating system error 5(Access is denied.).It seems I should be able to do this. Any ideas?Thanks |
|
|
jbates99
Constraint Violating Yak Guru
396 Posts |
Posted - 2014-02-17 : 17:10:16
|
| After reviewing the MS documentation, I see that it's not possible:"A database snapshot always resides on the same server instance as its source database."I'm looking for a good technique to use for refreshing my Data Warehouse. In the past we used regular Snapshot replication to refresh just a few dozen tables but I'm hoping to replace that with a more "modern" technique.- I can't use Database Snapshot for the reasons described above- My Data Warehouse will be on SQL Server 2012 Enterprise but Because my source data is SQL Server 2008 R2, I am not able to use the new Always On feauture(someone please correct me if Always On is available in 2008 R2)- That leaves CDC Change Data Capture which I will experiment with. It is avail on 2008 R2 and 2012 |
 |
|
|
|
|
|
Creating a Database snapshot to a different Server