October 6, 2008 at 3:11 pm
I've posted a question about migrating from one Windows Server to another one, but this is a different idea. Starting this Friday and going into Saturday we're going to migrate our production SQL Server 2005 to a new Windows Server. Right now I've got it working fine, on an old set of data that I restored from a full backup from about a month or so ago. Our system administrator is thinking of using a snapshot of the databases and trying to restore using that.
I'm wondering, is that possible? Is it possible to restore a database from one location to another from a snapshot on the old database server?
Kindest Regards, Rod Connect with me on LinkedIn.
October 6, 2008 at 4:16 pm
Unless there's some obscure trick out there for doing so, no.
See these articles in BOL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a1d0693f-6784-4d1e-b1e4-074a5e56769b.htm
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/32abdbce-df5b-492a-b65d-5a9cc06e8497.htm
Is it possible he's talking about a SAN snapshot? That would be a different story altogether.
October 7, 2008 at 8:00 am
Seems your mixing some concepts?
Snapshots are an easy escape if you want to revert a database update. So if you upgrade your application, make a snapshot of your database first. If the upgrade fails, revert your database to the snapshot. But this has to be on the same server
If I'm right, you're just moving your current 2005 databases to another server? (thus not upgrading from 2000 to 2005). Just backup/restore your databases, or detach/attach (make sure you leave a copy on the source server just in case).
Now the confusing part: a full backup acts like a snapshot 😛
Wilfred
The best things in life are the simple things
October 7, 2008 at 8:15 am
Rod at work (10/6/2008)
I'm wondering, is that possible? Is it possible to restore a database from one location to another from a snapshot on the old database server?
No. Snapshots can only be used to revert the source database for that snapshot to the point-in-time that the snapshot was created.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 8, 2008 at 7:38 pm
It is possible to do what you are describing. We use NetApp for our SANS. With SnapManager handling the snapshots. Initially the first snapshot is essentially a full backup, everything there after is more like a differential snapshot which are much smaller. On NetApps if you want to restore a database to another server from a snapshot you will need to attach the production servers lun to the new server as well as the snapmgr lun which actually stores your snapshot data and acts as temp space during the restore process. After that you can restore snapshots from the old server to the new server. if you have never done this then i don't recommend you try it the first time out the gate with your production box. Talk to the guy managing your SAN if you just doing daily snapshots then I would just use a standard full SQL backup and restore using traditional methods (if you are setup for point in time restores then you don't use the sql backups cause you will throw your logs out of sequence). Another good option is to use the Database transfer wizard which you can reach by right clicking the db and choosing copy database. This allows you to move a copy of the database to any server yu like, and your choice of everything associated with the db (like sql agent jobs, logins, etc.) It works really well and takes a lot of the gotchas out of the process.
October 9, 2008 at 1:39 am
You're mixing snapshot backups (performed by NetApp Snapmanager) with SQL database snapshots.
This is a totally different concept.
When we are talking about snapshots, what do you mean? When your system administrator is talking about snapshots, what does he mean?
Seems we need a babelfish 😀
Wilfred
The best things in life are the simple things
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply