Author |
Topic |
nomannishat
Starting Member
9 Posts |
Posted - 2011-07-05 : 05:58:42
|
Dear All,I need some basic things for sqlserver 2008, Actually i am oracle dba but now working on sqlserver 2008 as dba.Please let me know.1) Is there any temp option just like in oracle. Like oracle have temp tablespace for sorting information so i need is there any temp tablespace ? 2) what is tempdb ?3) how can i move data file (MDF and LDF) file from one drive to another drive like in c drive i have install (C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA) now want to move on f drive so how can i do this? what are the steps ?4) Need certification on sqlserver 2008 how can i do any recomended book ?RegardsNoman |
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-05 : 06:19:16
|
quote: Originally posted by nomannishat 1) Is there any temp option just like in oracle. Like oracle have temp tablespace for sorting information so i need is there any temp tablespace ?
Yes, it's the database called TempDBquote: 3) how can i move data file (MDF and LDF) file from one drive to another drive like in c drive i have install (C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA) now want to move on f drive so how can i do this? what are the steps ?
Two options:1) Detach the database, copy files, attach2) Run an ALTER DATABASE with the MODIFY FILE option, take the DB offline, move the files, bring DB online.quote: 4) Need certification on sqlserver 2008 how can i do any recomended book ?
Get some experience before you write the certs--Gail ShawSQL Server MVP |
 |
|
nomannishat
Starting Member
9 Posts |
Posted - 2011-07-05 : 07:51:55
|
quote: Originally posted by nomannishat Dear All,I need some basic things for sqlserver 2008, Actually i am oracle dba but now working on sqlserver 2008 as dba.Please let me know.1) Is there any temp option just like in oracle. Like oracle have temp tablespace for sorting information so i need is there any temp tablespace ? 2) what is tempdb ?3) how can i move data file (MDF and LDF) file from one drive to another drive like in c drive i have install (C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA) now want to move on f drive so how can i do this? what are the steps ?4) Need certification on sqlserver 2008 how can i do any recomended book ?RegardsNoman
|
 |
|
nomannishat
Starting Member
9 Posts |
Posted - 2011-07-05 : 08:17:57
|
Thanks for the reply can you please tell me more1) is the deattached is necessary ? without deattached can we move the datafile ?2) if suppose i deattached and move the datafile so tempdb is also need to move ? is it necessary ?RegardsNoman |
 |
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-05 : 09:21:17
|
I listed the two methods of moving user databases. One uses detach, one doesn't. Those are the only practical ways to move a database.As for moving tempDB, depends. Is this a production database? If so, TempDB should really be on its own drive.--Gail ShawSQL Server MVP |
 |
|
nomannishat
Starting Member
9 Posts |
Posted - 2011-07-05 : 23:42:26
|
Thanks now please tell me 1) how can identify that i need add more data files?2) I have right click on one database check the properties mdf and ldf datafile size is 500M unlimited growth so what is means of unlimited growth and second thing is space available shows one 87M so what is that means ?RegardsNoman |
 |
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-06 : 03:46:54
|
Adding more data files for what reason?2) The file can grow without limit (up to the available space on the drive). There is currently 87 MB free within the data file.--Gail ShawSQL Server MVP |
 |
|
nomannishat
Starting Member
9 Posts |
Posted - 2011-07-06 : 05:19:59
|
Thanks. Actually i compare with oracle 1) like in oracle if we found tablespace full so we can add datafile. So what is the procedure to find out what MDF or LDF file full ? if full so can we add more datafile like abc1.mdf, abc2.mdf and so on ?2) i have click right mouse on database through sqlserver managment 2008 and check the properties there some feilds like database size and availablespace. what are that means ?Regards |
 |
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-06 : 05:40:37
|
Well, your file is unlimited in size (actually 16 TB iirc) so I doubt you're in any danger of hitting that soon. Just make sure you have enough space on the drive. There are reasons to add files, but hitting the max size of a file is not one of them unless you're playing in the VLDB area (and I seriously hope not).Please do some basic reading on SQL database and data file management. There's a lot in Books Online on all of this.--Gail ShawSQL Server MVP |
 |
|
nomannishat
Starting Member
9 Posts |
Posted - 2011-07-06 : 06:07:11
|
Thanks, i am moving datafile from one location to another please correct me if i am wrong .1) Take database detached.2) cut and past from one location to another like c:\sqlserver\abc.mdf and c:\sqlserver\abc.ldf to f:\sqlserver\abc.mdf and f:\sqlserver\abc.ldf 3) Now try to database online.4) when i try for that db attached its attached but offline why ? is there any mistake ?5) and i also try to drop the same db but showing error."The database does not exist on server"Regards |
 |
|
nomannishat
Starting Member
9 Posts |
Posted - 2011-07-06 : 07:39:10
|
Hi Gail,Any update ?Regards,Noman |
 |
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-06 : 09:13:53
|
Patience. I'm a SQL consultant with several paying clients. I post here in my spare time. If you want instant reply, I'll give you my billing rate and we can work something out.Does the SQL service account have full permission to that folder?--Gail ShawSQL Server MVP |
 |
|
nomannishat
Starting Member
9 Posts |
Posted - 2011-07-06 : 09:30:39
|
yes i have logon with 'SA' account. Regards, |
 |
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-07-06 : 09:34:13
|
I wouldn't do it like that - especially if you are new to the product.To move a user database my default position is backup and restore. In that way you always have a backup t work from.If it is critical do a restore to another database to check the backup (you should do that to backups anyway).Then the move becomesBackupdetach databaserestore with moveNow you have a backup and the database files in case anything goes wrong. This is assuming you have space.You do have a backup of everything as a matter of course and are testing them? I wouldn't even start this without this.I worked with an Oracle guy once who created his user database with 2 data and 2 log files because he said that was more efficient in Oracle. He said it was poor of sql server for it not to be always more efficient. Don't get into that sort of thinking - they are different products and are not trying to copy one another.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-06 : 09:53:08
|
quote: Originally posted by nomannishat yes i have logon with 'SA' account.
I didn't ask about your account. I asked if the SQL Service account (the windows account that SQL starts under) has full permission to that folder.--Gail ShawSQL Server MVP |
 |
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-07-06 : 10:14:23
|
Try a restore to that folder.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|