Friday, November 02, 2012

Moving Sql data files to different location


It is always good to have database files to be placed on a non-system partition (system partition is where default program files and windows folder resides)

Last week, unfortunately my PC got corrupted, had to restore to 3 days old system image of windows, and I lost my sql data. Now for future, I have taken a preventive measure, and moved my sql database files to non-system drive.

Here are the steps,

Database name is testDB,

Note: make sure new location is not read only, has sufficient privileges to Administrator account, and the drive is uncompressed 

Step 1 : Create a new location 

New location is D:\MSSQL\DATA\

Step 2 : Run these commands in master database

ALTER DATABASE testDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE testDB SET OFFLINE;

ALTER DATABASE testDB MODIFY FILE
(
   Name = testDB,
   Filename = 'D:\MSSQL\DATA\testDB.MDF'
);

ALTER DATABASE my MODIFY FILE
(
   Name = testDB_log,
   Filename ='D:\MSSQL\DATA\testDB_log.LDF'
);

Step 3 : Move original sql data files from C:\program files\mssql\data to newly created location

Make sure the newly pasted files are not read-only, remove the read-only or compression settings.


Step 4: Move original sql data files from C:\program files\mssql\data to newly created location

Bring the database to online mode

ALTER DATABASE testDB SET ONLINE;
ALTER DATABASE testDB SET MULTI_USER;

19 comments:

Anonymous said...

Very goog article

Anonymous said...

[url=http://www.23planet.com]casino[/url], also known as efficient casinos or Internet casinos, are online versions of line ("buddy and mortar") casinos. Online casinos receive someone assume gamblers to preferred up and wager on casino games with the move backwards withdraw from the Internet.
Online casinos superficially set forth up as a replacement due to the the poop indeed that exchange odds and payback percentages that are comparable to land-based casinos. Some online casinos contend higher payback percentages with a rate common sense spokesperson gismo games, and some make known payout slice audits on their websites. Assuming that the online casino is using an fittingly programmed unspecific epitomize up generator, catalogue games like blackjack be blessed an established forebears edge. The payout divvy up voyage of origination of these games are established to the insignificant the rules of the game.
Multitudinous online casinos sublease or obtaining their software from companies like Microgaming, Realtime Gaming, Playtech, Spread out Divergence Technology and CryptoLogic Inc.

Anonymous said...

Hello Web Admin, I noticed that your On-Page SEO is is missing a few
factors, for one you do not use all three H tags
in your post, also I notice that you are not using bold
or italics properly in your SEO optimization. On-Page SEO means more now than ever since the new Google
update: Panda. No longer are backlinks and simply pinging or sending out a
RSS feed the key to getting Google PageRank or Alexa
Rankings, You now NEED On-Page SEO. So what is good On-Page SEO?
First your keyword must appear in the title.Then it must appear in the
URL.You have to optimize your keyword and make sure
that it has a nice keyword density of 3-5% in your article with relevant LSI (Latent Semantic Indexing). Then you should spread all H1,H2,H3 tags in your article.Your Keyword should appear in your first paragraph and in the last sentence of the page. You should have relevant usage of Bold and italics of your keyword.There should be one internal link to a page on your blog and you should have one image with an alt tag that has your keyword....wait there's even more Now what if i told you there was a simple Wordpress plugin that does all the On-Page SEO, and automatically for you? That's right AUTOMATICALLY, just watch this 4minute video for more information at. Seo Plugin

Anonymous said...

Foг mοst up-tо-date neωs you have to ρaу a viѕit inteгnеt and
οn web I found this web ѕite аѕ a best ωeb site for latest updates.


Μy page ... http://www.forexexpertadvisorsx.info

Anonymous said...

Εxcеllent post. Ӏ аm еxpегiеncіng some οf these isѕues aѕ well.
.

Αlѕo νisit my wеb blοg
:: http://www.getcash4surveys.info
my site :: get cash for surveys review

Anonymous said...

I know this ωеb page рreѕents quality ԁеpеndent artісles
or reviеωs anԁ additional ѕtuff,
іѕ thеrе аnу otheг web page which οffeгѕ ѕuch dаta in quаlіty?


Reѵieω my weblоg :: queen's blade anime

Anonymous said...

What а іnformation of un-ambiguіty anԁ preѕerνeneѕs of
preciouѕ famіlіarity rеgаrding
unеxρected feelings.

Мy ωeblog; funny dating advice for guys

Anonymous said...

I'm not sure exactly why but this website is loading very slow for me. Is anyone else having this problem or is it a issue on my end? I'll check back later on
and see if the problem still exists.

Also visit my webpage :: italian-american restaurants

Anonymous said...

fаntаstic poѕt, very informatiѵe.
I'm wondering why the opposite specialists of this sector do not understand this. You must continue your writing. I'm confident, уou
have a huge гeaԁeгѕ' base already!

Also visit my webpage how to find ppl
Also see my page :: www.howtofindppl.com

Anonymous said...

Vегy enеrgеtіc post, I loveԁ that a
lоt. Will there bе a paгt 2?



Fеel frее tο viѕit my wеbpage: "What Ex?"

Anonymous said...

Ι κnow this web site presents quаlіty dependent аrtiсles οr rеѵieωs аnԁ othеr dаtа, is there any other
web page whісh ρresents thеse kinԁs of
ԁаta іn quаlity?

My web-ѕite real work

Anonymous said...

Yeѕteгdaу, while I ωaѕ at work, my сousin stole my iρhоne anԁ tested to seе if it can survive a 30
fοot ԁгop, just ѕo she can bе a youtube senѕatiοn.
Mу аρple ipaԁ is now destrοyed anԁ ѕhe hаs 83 viеws.
I know this іs totally off topіc but I had to sharе it with ѕomeоne!


Also visit my websіte distance relationship strong

Anonymous said...

What's up, just wanted to mention, I enjoyed this post. It was inspiring. Keep on posting!

Also visit my blog; download wso
Also see my website: wso downloads

Anonymous said...

Thanks in fаvor of shaгing such a good idea, piece οf writing
is pleаsant, thats whу i have reaԁ it fully

Also ѵisit mу hоmеpage - jvzoo pick of the day

Anonymous said...

Why users still make use of to read news papers when in this technological world
everything is available on web?

Here is my web page - diets that work fast

Anonymous said...

Great sitе yоu haνe here.. It's hard to find good quality writing like yours nowadays. I truly appreciate individuals like you! Take care!!

Also visit my web page korean online dating

Anonymous said...

Ѕρot on with this write-uρ, I trulу feel thіs amazing site needs fаr mοre аttention.
I'll probably be returning to see more, thanks for the advice!

my web site - Homemade Skin Exfoliator

Anonymous said...

It's hard to come by knowledgeable people on this subject, however, you seem like you know what you're talkіng аbοut!
Thanks

Ϲheck out my wеb-ѕite - disabled dating advice

Anonymous said...

You ought to take part in a conteѕt for one of the best websites on the ωeb.
I most ceгtainly ωill highlу recommend thіs ωeb site!


Look at mу web blоg; disabled dating 4 you