September 13, 2012 at 6:08 am
I'm looking to move the database file location from one drive to another on my machine for about 20-30 databases.
Besides a copy/paste of the files from location A to location B and then creating all the databases again and deleting the existing ones, is there any other option?
The file names and database names would be identical, just the path
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLEXP\MSSQL\DATA
becomes
R:\SQL Monthly Snapshots
I'm guessing I might have to edit the permissions of that folder too to allow Management Studio to write to it?
September 13, 2012 at 6:34 am
Rob-350472 (9/13/2012)
I'm looking to move the database file location from one drive to another on my machine for about 20-30 databases.Besides a copy/paste of the files from location A to location B and then creating all the databases again and deleting the existing ones, is there any other option?
The file names and database names would be identical, just the path
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLEXP\MSSQL\DATA
becomes
R:\SQL Monthly Snapshots
I'm guessing I might have to edit the permissions of that folder too to allow Management Studio to write to it?
Something like this for all user databases
SELECT 'ALTER DATABASE ' + QUOTENAME(DB_NAME(database_id)) +
' MODIFY FILE(NAME=' + name + ', FILENAME=N''' +
REPLACE(physical_name,
'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLEXP\MSSQL\DATA\',
'R:\SQL Monthly Snapshots\') + ''')'
FROM sys.master_files
WHERE database_id > 4
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 13, 2012 at 7:52 am
Ahh, I see, that makes sense perfectly after delving into that data and seeing what's going on. I can try it with a couple of databases by changing the database_ID and see what happens.
Need to wait till I can actually close Management Studio in the first place (and SQLserver process) to move the files first of all though!
Thanks 🙂
September 13, 2012 at 8:41 am
Rob-350472 (9/13/2012)
Need to wait till I can actually close Management Studio in the first place (and SQLserver process) to move the files first of all though!Thanks 🙂
No, you don't 😉
Issue the alter database commands and then take the databases offline. Copy the files to the new locations and then bring the databases online again. When they come online delete the old files from the source directory 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 13, 2012 at 8:46 am
Ah I see, run the command, right click on DB in question, tasks --> take offline.
Copy files in explorer from source to destination
right click --> 'bring online' (at a guess!)
Confirm it works then delete the source files.
Even better!
September 13, 2012 at 8:57 am
I would recommend backing up the databases first, in case the worst happens, which it has done for me before. Given that you have taken a backup, you may want to consider doing the move by RESTOREing WITH MOVE, instead of OFFLINE -> ALTER -> ONLINE. This approach may be quicker if you have large database files with lots of empty space.
John
September 13, 2012 at 9:01 am
Thanks John, I'm currently going through the databases and shrinking them actually. These are monthly archives which are dipped into from time to time and prove very useful. They're not used in production or anything (otherwise I'd not bother shrinking them).
I was just thinking actually that I should get a decent backup routine sorted for these archives, ideally creating a .bak file and compressing then deleting hte .bak file. 7zip perhaps, if not RAR.
The shrink may buy me some more time before I have to move, perhaps I should think of going:
shrink --> backup routine --> move...
September 13, 2012 at 9:14 am
Rob-350472 (9/13/2012)
Ah I see, run the command, right click on DB in question, tasks --> take offline.Copy files in explorer from source to destination
right click --> 'bring online' (at a guess!)
Confirm it works then delete the source files.
Even better!
😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 13, 2012 at 9:16 am
Rob-350472 (9/13/2012)
perhaps I should think of going:shrink --> backup routine --> move...
no, use the method i showed you. backup and restore is a complete waste of time.
I use this method all the time even when moving system databases, which by the way is the supported way of moving them.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 13, 2012 at 9:18 am
John Mitchell-245523 (9/13/2012)
I would recommend backing up the databases first, in case the worst happens, which it has done for me before. Given that you have taken a backup, you may want to consider doing the move by RESTOREing WITH MOVE,
Are you serious? Backup and restore to move file paths for databases on the same instance!
Actually its
ALTER -> OFFLINE -> COPY files -> ONLINE 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 13, 2012 at 9:23 am
Hmm, okay! ...
Well, one thing I think we can all agree on, having rar/7zip backups of the .bak files of these databases on a different disk in my machine is a good idea, with some form of offsite backup being a bonus too.
Quite when this fits into the grand scheme of things it seems is up for debate! With the shrinking and some Treesize action I should be okay until I try to restore say the November archive so I've brought myself some time!
September 13, 2012 at 9:32 am
the worst thing thats likely to happen is you type the wrong path\filename in the alter command. If you do then just re issue the correct command and the database will come online.
I moved all databases on an instance only yesterday (including system databases) using the alter commands, it does exactly what it says on the tin
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 14, 2012 at 1:48 am
Yes, of course I'm serious, and I even took the trouble to explain why. Note that I used the word "consider" - I didn't say it was appropriate for all cases.
The worst thing that happened to me was that the database came up suspect when I brought it back online. I admit that the vast majority of time it does indeed do what it says on the tin, but it's always good to have an alternative in case it goes wrong.
John
September 14, 2012 at 6:15 am
Perry Whittle (9/13/2012)
John Mitchell-245523 (9/13/2012)
I would recommend backing up the databases first, in case the worst happens, which it has done for me before. Given that you have taken a backup, you may want to consider doing the move by RESTOREing WITH MOVE,Are you serious? Backup and restore to move file paths for databases on the same instance!
Actually its
ALTER -> OFFLINE -> COPY files -> ONLINE 😉
This is the samething as saying dettach and attach databases right?
SueTons.
Regards,
SQLisAwe5oMe.
September 14, 2012 at 7:30 am
SQLCrazyCertified (9/14/2012)
Perry Whittle (9/13/2012)
John Mitchell-245523 (9/13/2012)
I would recommend backing up the databases first, in case the worst happens, which it has done for me before. Given that you have taken a backup, you may want to consider doing the move by RESTOREing WITH MOVE,Are you serious? Backup and restore to move file paths for databases on the same instance!
Actually its
ALTER -> OFFLINE -> COPY files -> ONLINE 😉
This is the samething as saying dettach and attach databases right?
SueTons.
No, not quite. When you offline a database its still attached to sql server. Detaching a database removes it completely from the system catalogs.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply