Moving DB to a new DSN
-
I need to move a large existing database to a newly created DSN on the same production server. The physical files and backup devices will be the same - I'm doing this simply to isolate the database in it's own environment (it's own tempdb) - as it doesn't play nicely with other just yet.
What's the best way to handle this maneuver? Here are the steps that I was considering:
- Do a full backup of the database
- Delete the existing database (keeping the backup files)
- Restore the database to the new DSN using the full backup .BAK file
Does this sound like a reasonable path forward?
Thanks,
Glenn
-
Do you mean another named instance? That would be the only way to isolate the database from the other databases on the same physical server (having its own tempdb). This way you could set up seperate security, etc.
DSN usually refers to an ODBC Data Source Name, which is basically a mechanism on the client side for connecting to a database (or other ODBC compliant source).
-
Yes, sorry - this is another named instance of SQL Server on the same box.
-
Depending on the size of the database, a somewhat easier method would be:
1) Detach the database in the original instance (default or named).
2) Move the files from the directory for the original instance (default or named) to the directory dedicated to the new named instance.
3) Attach the database in EM on the new instance.
I'd make sure that i had a good backup before I did the above process, just for recovery's sake.
(this would save a couple of steps and some disk space).
-
Well, that was easy!!!
After a bit more reading I decided to try the Detach/Attach method.
Since the named instance was on the same box and the data/log files were going to stay on the same physical disk as the original (with the same names, etc) I simply used the EM Detach and Attach (no files needed to be moved).
Viola, database moved.
Thanks for you input,
Glenn
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply