July 12, 2009 at 10:22 am
Hello, guys how are you doing?
I have DHCP server and my HDD has two partitions which is PRAIMARY partition ( C & D). and Drive "C" partition is very small and its capacitiy = 39GB & free space = 18GB while Drive "D" partition is capacity = 164GB and free space = 155GB. this is a big mistake made by some one before i joined to the company. right now what i am trying to say is; everything is in drive "C" installed; such as, SQL server 2000 and 20005 databases and applications such as VB6, windows application, .NET application and Web applications ( Accounting, HR, casher, Department, M.R, SMS.....) and also, Norton Anti-virus Corporate EDDITION and so on. Now, my question is could it be possible to change the SQL server Database from c:\programfiles\microsoftsqlserver......into "D" Drive?
:crazy: I don't know what to do?
Please guys help me
thank you very much
July 12, 2009 at 10:33 am
See the information article "How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server" at http://support.microsoft.com/kb/224071
SQL = Scarcely Qualifies as a Language
July 13, 2009 at 1:04 am
Hi
U can follow the below link.
http://deepakrangarajan.blogspot.com/2008/11/moving-system-databases-in-sql-server.html
July 13, 2009 at 1:46 am
Hi, guys how are you doing? i have visited to 2 websites and was trying to follow the steps but i could do it. one thing i did not tell you is that, i am a network administrator ofcourse i am familiar with sql server database but if you have easy steps, please help me
take a look guys, this is the path of the SQL server 2000 and 2005 and if you see here, which one is the correct path i mean 4 mssql are here. what is this all files?
c:\programfiles\microsoftsqlserver
-80
-90
- intetpub
-mssql
- mssql&server2
-mssql1
-mssql2
What i need is just to change from the "C" Drive in to "D" Drive
thank you very much
July 13, 2009 at 7:44 am
USE MASTER
GO
SP_DETACH_DB ‘ENTER_YOUR_DB_NAME_HERE'
GO
Move the database files to the new location, there will be two files with the database name, a .mdf and a .ldf. These will more than likely be located in the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data directory, otherwise go to the properties of the database for file mapping.
USE MASTER
GO
SP_ATTACH_DB ‘ENTER_YOUR_DB_NAME_HERE’,’D:\NewPlace\ENTER_YOUR_DB_NAME_HERE.mdf’,’D:\NewPlace\ENTER_YOUR_DB_NAME_HERE.ldf’
GO
July 13, 2009 at 7:45 am
USE MASTER
GO
SP_ATTACH_DB ‘ENTER_YOUR_DB_NAME_HERE’,’D:\NewPlace\ENTER_YOUR_DB_NAME_HERE.mdf’,’D:\NewPlace\ENTER_YOUR_DB_NAME_HERE.ldf’
GO
July 13, 2009 at 9:39 am
Hi, guys how are you doing?
let say this is my database ' Accounting' and i think you gave me two steps i.e (detach and attach ). and this will be just like this
1. use master
go
sp_detach_db 'Accounting'
go
2. use master
go
sp_attach_db 'Acounting','D:\DB_restored\Accounting.mdf','D:\DB_restored\Accounting.ldf'
go
my question is, 1. am i right about this script? 2. you gave me two steps ( detach and attach) so, is it must to exceuse both of them? i mean what if i excute the 2nd script only? 3. i tried to excute the detach script in the server and gave me an error message " database is in use" i think yes, we are in working hours, So, is there any problem during working hours or i have to wait till they finish the job?
thanks
July 13, 2009 at 9:44 am
Your modification of the scripts look great. Keep in mind that all users must be out of the database before you can detach the database. After you detach the database (step 1), then you will move the Accounting.mdf and Accounting.ldf files to 'D:\DB_restored\Accounting.mdf' via copy/paste. Once that is complete you will attach the database (step 2).
Chris
July 13, 2009 at 10:44 am
Thank you so much and i reallly appriciate that. and also i have one more question i think this is the destination of my restored database; 'C:\Programfiles\microsoftSQLserver\msql&server2\Data' inside this data
-Accounting_Data.MDF
-Accounting_Data.LDF..... and so on
Look this files
-Distmdl.mdf = 2,304kb ...
-Dstmdl.ldf = 768kb ... this two have different color which is blue
-master.mdf
-master.ldf
-msdb
-nothwind...
do you think they need to transfer like the others? because in drive "D" I have big space (155gb)? could you please expain it?
thanks
July 13, 2009 at 11:12 am
You cannot detach a system database (master), (model), (msdb), (tempdb). Plus, let's be honest, these databases are typically very small in size as they house little information.
July 13, 2009 at 10:41 pm
If you still want to move system database to another drive, links are here:
http://www.takeabyte.com.au/2008/10/moving-sql-master-database-in-sql-2005/
JYM
July 14, 2009 at 3:42 am
Hello, guys how are you doing?
today morning i had a big problem for what i did last night moving database from drive 'c' to 'd' but before i did this i had a back up and right now, i restored again.
- first i detach my Accounting database and the command was successfully executed but when i run the attach' command, this ERROR massage says " Device activation eror. the physical file name 'D:\DB_restored\Accounting.mdf' may be in correct. this norning when i came, every body is shouting because the system is stopped ( my inistitute brunch is in Accounting database) then i restored agin huh! thank GOD it is fine now.
please guys could you tell me what kind of mistake i did? one think i like to tell you is my hard drive "D" is commpressed, do you think this is the problem?
please tell me if there is another method
thanks
July 14, 2009 at 10:31 pm
Device activation eror. the physical file name 'D:\DB_restored\Accounting.mdf' may be in correct.
It looks like the physical path of the .mdf went wrong.
You must properly detach the db and move the .MDF, .LDF files to D drive, then attach the database.
July 15, 2009 at 3:56 am
Hi, guys how are you doing?
i think there is an easy step but i want to make sure that it's ok or not and take a look the steps ( using SQL server 2000)
1. open enterprise manager and databses ( Back up my database) in to "D" drive and create New folder for Dbase restore (D:\DB_RESTORED)
2. Restore the the Backed up Data from " D" drive using the following steps
- select the database you want to restore and go to restore option
- click the radio button of " leave database read-only and able to restore additional transaction logs"
-click an icon beside undofile and select a place you want to save the data and ok. then coppy the path of the data you choose
- click the radio button for "leave databse oprational. no additional transaction logs can be restored"
-go to the logical file name and there is two database MDF and LDF
- Paste the Dbase but leave the .MDF and .LDF files and then Click OK. that is it.
i think it seems fine. but is it correct? what do you think?
July 20, 2009 at 5:36 am
hi
it is posiable u can do this by in staling the path , cis the default type u can chose and select ,
Regards
sat_sql
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply