Database save query

Question

Post reply

Database save query

Nuts

SSCrazy

Points: 2547
More actions
October 16, 2006 at 7:57 pm

#192718

Hi Please ignore the previous post.

I am using sql 2005.all my databases get saved on my c drive. yesterday my hard disk crashed and I lost all my databases.

Guys is there anyway I can save my database in a different drive??

Thanks

mita

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gurpreet Singh Gill-375533 SSC Rookie Points: 45 More actions · Answer 1

Yes, you can store your .mdf as well as .ldf file any where on your hard disk.

When you create the database it take by default path which is in C: Drive, you can set the path to any drive.

In SQL Server-2005

1) Right Click on the Databases

2) Select new Database( this will open new dialogbox)

3) Click on the General (left hand side)

4) In the Database files, select the path ( you need to scroll right)

Or

if you want to change the path of existing database, you need to Deattach it first, then cut/paste it any where in your hards disk & again Attach it

Best of Luck

debrucer1 SSCertifiable Points: 5366 More actions · Answer 2

2000 had a bug that when you exported (from one db to another, locial import) it does not use the path you setup like Gurpreet suggests. You have to create the DB first, and then do the export if you want the files to be located on the other drive(s). Just a point, not sure of the 2005 operation; but something to look for.

Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982

Scott Coleman One Orange Chip Points: 27451 More actions · Answer 3

You can set the default location for data and log files in Management Studio by going to Server Properties - Database Settings. This will only affect new databases, it won't move existing ones.

You can also move exising database files. See "Moving Database Files" in BOL.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/89f01b10-5fae-4ed8-b0fb-a4b9f540fd28.htm

Nuts SSCrazy Points: 2547 More actions · Answer 4

Nuts

SSCrazy

Points: 2547

October 18, 2006 at 1:15 pm

#666623

Hi guys

The problem is i have installed sql on my c:.Its not on the network because if i try to put it on the network, it gives errors and does not get installed properly.

I cannot even take back ups of my databases on some other drive.Its only c drive where I can take backups..

What do u suggest?

Scott Coleman One Orange Chip Points: 27451 More actions · Answer 5

If you want SQL Server to work properly, you want as many local drives as you can get. You might want the OS and SQL installation plus the Windows swap file on C:, then start spreading data files, tempdb, and log files on separate disks. Ideally you have several thousand disks so that every table, index, and log can be on a separate drive (plus more drives for backups and ETL space), but unfortunately that's not practical so you have to make compromises.

You can back up to network drives (assuming your SQL Server and SQL Server Agent services are logged in with an account that has domain rights to those drives). You can't point-and-click to a network drive in the user interface, you actually have to break down and type it in.

I can't tell from your question whether you are trying to use SQL on a system with only one drive, or whether it has other drives and you can't figure out how to use them. It would help if you showed us exactly what you were trying to do, and what specific error message you're getting. Also it would help to know what hardware you're working with. Number of drives, RAID configuration, drive speed & capacity, etc.