October 11, 2010 at 12:29 pm
I'm very new to this. I know how to backup a databaes and how to restore the database. I'm not too sure about the detach/re-attach aspect.
I've got to move the database from a server mapped to drives C:/ and D:/ to a server mapped to D:/ and E:/
Currently I understand that I need to make a backup on Source Server and FTP the file to the Destination Server. How do I get it to re-attach under the correct drive letters?
Don
October 11, 2010 at 1:00 pm
backup and restore is probably the safer way to do this (in terns of backout). also almost always means a smaller file to copy but you have the added time of the backup and restore operations.
when you do the restore database use the 'with move' options. run restore filelistonly from disk = 'UNC path' first to get the logical names of the files, then its:
restore database...
with move 'logical file' to 'new physical location',
move 'logical file2' to 'new physical location2'
If you prefer detach\attach look up sp_detach_db and create database...for attach
all the above is in BOL and gives full explanations.
You do not have to detach the database on the source server, you could just offline it, then you will be able to copy the files.
'alter database dbname set offline'
---------------------------------------------------------------------
October 11, 2010 at 1:05 pm
Thank you, I didn't know about the WITH MOVE option in restoring a database.
Dumb question. Does BOL stand for Books Online? And is this the Books section of SQLServerCentral.com?
Don
October 11, 2010 at 1:11 pm
Donalith (10/11/2010)
Thank you, I didn't know about the WITH MOVE option in restoring a database.Dumb question. Does BOL stand for Books Online?
Don
everyone has to start somewhere. yep BOL is books online, make it your friend.
And is this the Books section of SQLServerCentral.com?
not sure I understand what you mean? BOL comes with MSSQL, you should find it under your programs, F1 from SSMS if installed, and google searches normally bring BOL pages from msdn up at the top of the list.
---------------------------------------------------------------------
October 11, 2010 at 1:28 pm
Aha.. now I'm on the same page.
BOL is the contextual help (F1) from MSSQL.. I just didn't make the connection between how it's commonly known and how I use it. lol
Don
Thanks again, everyone!
October 11, 2010 at 1:51 pm
In SQL Server 2008, could I not just use the Copy Database Wizard after setting the database to single user mode to recreate it on the new server? It's also in simple mode so the logs aren't really an issue.
Don
October 11, 2010 at 2:05 pm
I would avoid that method like the plague.
---------------------------------------------------------------------
October 11, 2010 at 2:10 pm
Donalith (10/11/2010)
In SQL Server 2008, could I not just use the Copy Database Wizard after setting the database to single user mode to recreate it on the new server?
You could. But then you could get from New York to Seattle by walking.
The only time I'll voluntarily use that method is if I'm moving a database down the versions (2008-2005), and even then script and bcp or Import/Export wizard is often a far better option.
It's also in simple mode so the logs aren't really an issue.
Errr.... simple recovery does not imply that logs are not a problem. Logs can still grow really large in simple recovery, especially if there are long-running transactions like from reloading a large table.
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 11, 2010 at 2:17 pm
Ok, so the copy wizard is a bad idea due to the length of time it will take. Got it.
What about the difference between backup/restore and detach/(move files to new server)/re-attach?
It's been suggested to me that it would be easier to set the dbase offline... copy the mdf and ldf files to their new locations and just reattach them.
Don
October 11, 2010 at 2:31 pm
Donalith (10/11/2010)
What about the difference between backup/restore and detach/(move files to new server)/re-attach?
Up to you. Advantage of backup/restore is that it leaves a copy of the DB available and usable on the old server if anything goes wrong. Advantage of detach/attach is that it's faster
It's been suggested to me that it would be easier to set the dbase offline... copy the mdf and ldf files to their new locations and just reattach them.
Detach, move or copy, attach.
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 11, 2010 at 2:33 pm
Thank you so much for your help!
I'm going to go with the detach, copy, and reattach. That'll leave a viable database on the first server while I'm working on the second.
Don
October 11, 2010 at 2:35 pm
Make sure you have a backup first. Just in case.
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 11, 2010 at 2:39 pm
Donalith (10/11/2010)
Thank you so much for your help!I'm going to go with the detach, copy, and reattach. That'll leave a viable database on the first server while I'm working on the second.
Don
The backup option will also give you a viable database while you're working on the second.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 11, 2010 at 3:14 pm
Donalith (10/11/2010)
Thank you so much for your help!I'm going to go with the detach, copy, and reattach. That'll leave a viable database on the first server while I'm working on the second.
Don
no, that will leave you with database files on the first server that SQL cannot see.
In terms of not touching the database on the first server, backup,copy restore is best (and safest)
Detach, copy, attach fastest.
I suggested offlining the database, copy, attach as it is as fast as detach and simpler to bring the database online again on the first server (just alter database dbname set online). In fact in a backout it is better, no risk of change in db owner or dbid.
As Gail said make sure you have a backup before you start any process with the database, that can be a log or differential backup to save space. If it's a full backup you might as well copy that over and restore.
So many ways to do it, it all depends how much space you have, how much time and degree of confidence about potential backout.
---------------------------------------------------------------------
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply