December 15, 2009 at 12:24 am
Hi all,
One of my sqlserver having two instances(default+named)20+20 user databases. i want to move this user dbases .mdf & .ldf which are in the C:\Program Files\Microsoft SQL Server\MSSQL\Data (default path to another drive.
c:drive is getting full thats why i need to move to another drive
iam using
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Personal Edition on Windows NT 5.2 (Build 3790: )
Please give the good solution to finish this task.
Thanks in advance
December 15, 2009 at 2:00 am
go google:-P
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 15, 2009 at 4:16 am
hi
i searched in google different ways thay r telling
can you pls give the best solution steps to fix this issue
This is high priority issue
Thanks in advance
December 15, 2009 at 4:34 am
sivark1 (12/15/2009)
hii searched in google different ways thay r telling
can you pls give the best solution steps to fix this issue
This is high priority issue
Thanks in advance
Hi Siva,
First u need to plan the application downtime.
Look the BOL :Database Attach/Detach method
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 15, 2009 at 5:02 am
1. For each file to be moved, run the following statement.
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )
2. Stop the instance of SQL Server
3. Move the file or files to the new location.
4. Restart the instance of SQL Server or the server.
5. Verify the file change by running the following query.
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'<database_name>');
December 15, 2009 at 9:59 am
Check out this link: http://support.microsoft.com/kb/314546
December 15, 2009 at 11:58 am
yerram.santhosh (12/15/2009)
1. For each file to be moved, run the following statement.ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )
2. Stop the instance of SQL Server
3. Move the file or files to the new location.
4. Restart the instance of SQL Server or the server.
5. Verify the file change by running the following query.
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'<database_name>');
Do you need to stop & start SQL ??
Detach-Attach does not require restart, so other databases are not affected.
Also, you can change the default from 'C' to 'x' so any future databases go to the correct location.
December 15, 2009 at 10:19 pm
If you are going with detach and attach the database no need to stop and start the sqlservices. But if you want change the path using ALTER DATABASE scripts( mentioned above) u need to stop and start the services. Any way you can't detach the system databases right.So this method will work for system databases and user databases except Master database.
December 15, 2009 at 10:51 pm
Thanks for your valuble sugessions to all,
During the search in this forum i found one of the solution (Tsql script to move the (x no.of) all 20 userdbases the LDF & MDF files.)
the link is
http://qa.sqlservercentral.com/Forums/Topic541190-1349-1.aspx
How about this script is it good to use?
Instead of each database we can move or use it for all databases ?
Thanks in advance
December 16, 2009 at 2:24 am
sivark1 (12/15/2009)
Thanks for your valuble sugessions to all,During the search in this forum i found one of the solution (Tsql script to move the (x no.of) all 20 userdbases the LDF & MDF files.)
the link is
http://qa.sqlservercentral.com/Forums/Topic541190-1349-1.aspx
How about this script is it good to use?
Instead of each database we can move or use it for all databases ?
Thanks in advance
Did u read the articel ?
Hi,
Read the articel fully and test the script in your Testing server.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 16, 2009 at 3:27 am
simplest way is using the enterprise manager. i assume a simple db with 1 mdf and 1 ldf and the owner is sa or not relevant. if your unsure use sp_helpdb on the database or check the database options screen.
(as your using sql 2000 i assume sql 2000 enterprise manager)
-right click on the database you want to move
-select all tasks and detach database
-choose clear button if you need to kill active connections (up to you to determine if you can just kill 'm or have to call users or something)
- press ok
- mdf/ldf files are detached and can now be moved or copied to the location you want
- after copy on databases click all tasks + attach database
- press the ... button and go to the mdf file
- if the ldf is not green on the left fill in that path also
- fill in attach as: the db name
- specify db owner and select ok
and it doesn't hurt to try it out on a test system first.
December 17, 2009 at 6:04 pm
Hi,
If using alter database modify file to specify the new path/filename, you can use
alter database myDatabase set offline
then after the physical move
alter database myDatabase set online
HTH
David
December 18, 2009 at 12:39 am
hi all
finally i followed this below link it works for me.
http://www.mssqltips.com/tip.asp?tip=1774
its very good link try this
Thanks
December 20, 2009 at 1:30 pm
Hi,
Great news that you got it sorted.
If you look up bol for sp_attach_db and sp_detach_db, it recommends instead using the alter database set offline. That way SQL keeps history and other things relating to the database, instead of deleting them.
That is, there may be subtle changes that occur on detaching and re-attaching a database.
HTH
David
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply