MIS Systems Thread, move sims db and log files to different partitions in Technical; Hello
i want to move the database and the log files onto a different partition on the sims server, i ...
15th April 2011, 10:46 PM #1
move sims db and log files to different partitions
i want to move the database and the log files onto a different partition on the sims server, i used dbattch to detach the database then i moved thhe log files and database to were i want them, then used sql management studio to re attach the db but it appears it has lost logins, lucky enough i snap shot it first
whats the correct way of doing this?
Last edited by ful56_uk; 15th April 2011 at 10:53 PM.
15th April 2011, 10:58 PM #2
you will need to reinstall sql to the partition you require - for example if you need to install sql on the E: drive AFAIK - so follow the migration guides on the Supportnet its the same process although you don't actually move server just reinstall sql to the other drive.
16th April 2011, 01:46 AM #3
You won't need to reinstall SQL to move it at all.
All you need to do is execute the following statement:
Where 'sims' is the name of the database, 'sims_log' is the logical name of the database file, and the path is obviously the full path to the log file. Make sure you check these values are correct for your install.
ALTER DATABASE sims MODIFY FILE ( NAME = sims_log, FILENAME = 'E:\SQL Logs\sims\sims_log.ldf' )
After that shutdown the SQL services, move the log file to the new location (and ensure the file name is correct) and the start the SQL services again.
For more information see:
Moving User Databases
16th April 2011, 01:40 PM #4
Yep, just need to run the right commands.
Attaching the database will only copy it back to the default location (allocated when SQL instance was installed). However if you need to detach the DB....
PLEASE USE DBATTACH TO DETACH AND ALSO REATTACH THE DB FILES. It does special magic with the logins.
Not using it might be the reason for losing logins. If you had restored a file it would have been fine and any logins stored in the db previously should still work. Not sure what happens when you just attach using management studio.
16th April 2011, 08:02 PM #5
If you're just moving the database files, the logins won't be affected as long as you keep the database name the same, so no need for the magic commands!
17th April 2011, 11:30 AM #6
I assume your recovery model is FULL?
28th April 2011, 03:11 PM #7
As per previous lengthy discussions!
Originally Posted by vikpaw
28th April 2011, 06:16 PM #8
Or just run SIMS_Transfer_Logins.exe before creating a backup.
By rocknrollstar in forum Windows
Last Post: 27th December 2012, 06:55 AM
By jonwitts in forum MIS Systems
Last Post: 12th October 2010, 03:59 PM
Last Post: 27th October 2009, 01:15 PM
Last Post: 17th October 2009, 04:55 PM
By zx2012 in forum Windows
Last Post: 20th August 2009, 01:53 PM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)