sqlserver 2008 Basic information Required

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

sqlserver 2008 Basic information Required

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 ?

Regards
Noman

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 TempDB

quote:
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, attach
2) 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 Shaw
SQL 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 ?

Regards
Noman

nomannishat
Starting Member

9 Posts

Posted - 2011-07-05 : 08:17:57

Thanks for the reply can you please tell me more

1) 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 ?

Regards
Noman

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 Shaw
SQL 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 ?

Regards
Noman

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 Shaw
SQL 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 Shaw
SQL 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 Shaw
SQL 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 becomes
Backup
detach database
restore with move

Now 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 Shaw
SQL 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources