SQL Server Performance Forum – Threads Archive

Moving a database

Hello,
I am looking for an example of how to move a DB with multiple files. Do I just keep adding a comma and the location of the file? I checked BOL and couldn’t find a thing. sp_attach_db ‘xxx’, ‘C

ATAFILE.mdf’, ‘C

ATAFILE.ndf’, ‘C

ATAFILE.ndf’, ‘D

ATAFILE.ldf’, ‘D

ATAFILE.ldf’ And so on? Or is there more too it? Thanks
J

That is what I find in BOL. EXEC sp_attach_db @dbname = N’pubs’,
@filename1 = N’c

rogram FilesMicrosoft SQL ServerMSSQLDatapubs.mdf’,
@filename2 = N’c

rogram FilesMicrosoft SQL ServerMSSQLDatapubs_log.ldf’ Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

As Luis Martin said; Its the perfect way to do the attach the database. Instead of the T-SQL script, you can attach the database using Enterprise Manager too.
Thats for beginers. You don’t have the worry of syntax, only select the database from the respective locations. But if you are using multiple files and with filegroups. Better to use the script and keep that in documented, so that it can be helpfull for next time or to your successor. -Johnson

check this link http://support.microsoft.com/default.aspx?scid=kb;EN-US;314546 krishna chaitanya.s
project engineer-SQLDBA

quote:Originally posted by johnson_ef Instead of the T-SQL script, you can attach the database using Enterprise Manager too.
Thats for beginers. You don’t have the worry of syntax, only select the database from the respective locations. -Johnson

Why the bias against Enterprise Manager? I’ve never understood this? Some people seem to think you have to do everything with T-SQL to be a REAL DBA? I say rubbish! Enterprise Manager makes many things (not all) easier, why not take advantage of that?

Dtipton: You can use what you want[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]. Luis Martin Moderator SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important Bertrand Russell All postings are provided â€œAS ISâ€ with no warranties for accuracy.

It is easy to be lazy to use wizards and easy options, but for a real DBA you must have the real strength and better understanding on the statements and admin commands used. I always suggest to use QA and TSQL statements and not Enterprise Manager.

quote:Originally posted by dtipton

Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by satya It is easy to be lazy to use wizards and easy options, but for a real DBA you must have the real strength and better understanding on the statements and admin commands used. I always suggest to use QA and TSQL statements and not Enterprise Manager. <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by dtipton <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by johnson_ef Instead of the T-SQL script, you can attach the database using Enterprise Manager too. Thats for beginers. You don’t have the worry of syntax, only select the database from the respective locations. -Johnson <hr height="1" noshade id="quote"></blockquote id="quote"> Why the bias against Enterprise Manager? I’ve never understood this? Some people seem to think you have to do everything with T-SQL to be a REAL DBA? I say rubbish! Enterprise Manager makes many things (not all) easier, why not take advantage of that? <hr height="1" noshade id="quote"></blockquote id="quote"> <hr noshade size="1">Satya SKJ Contributing Editor & Forums Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.</center> <hr height="1" noshade id="quote"></blockquote id="quote"> I couldn’t disagree more!!!!![<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />!] It’s not a matter of being lazy it’s a matter of being productive. I agree it’s a good idea for beginners to become familiar with T-SQL so they know what is being executed "behind the scenes" but using EM doesn’t make someone lazy just as being able to write correct T-SQL syntax doesn’t mean you understand what the command is doing. When I started in my current job the previous DBA was running the production databases in FULL recovery mode and executing a perfectly composed T-SQL script that backed up the tlog every 15 minutes with the NO_LOG option. Does his ability to write T-SQL commands mean he understood what he was doing? Heck NO. Reminds me of when I worked in a shop that had SQL Server and DB2 on the mainframe. The SQL people weren’t real DBA’s because we didn’t write JCL. What a joke. Learn how the product works and then use the right tool for the situation.

Dtipton: In my last post I’ll try to be kindly. I’ll try again. You can use what you want, EM or QA, period. All moderators in this Forum (you can read old posts) are agree with Satya in this matter. Is our obligation, as moderators, to give the best oppinion we have. And that is all, is just and oppinion. Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

quote:Originally posted by dtipton

For ad-hoc tasks on dev db server EM is ok. However, if you need to perform admin tasks on production db, it is much better to write t-sql script, test and tune it on dev or qa db server and then apply tested script in production.

Thanks Luis, I just take exception to the Lazy label. Thanks mmarovic, I agree that T-SQL is the way to go with most admin tasks in a production environment. However, If I just want some basic information on a scheduled job you better believe I’m using EM instead of sp_help_job. Thanks to the moderators, this forum is a wealth of information.

dtipton,
I just don’t want to debate on this issue which leads to nowhere with a never ending talk. In my 11 years of experience in database field and 8 years in SQL Server I found TSQL statements very useful and productive to keepup the knowledge. I agree few times it is better to use Enterprise Manager. Due to flakiness of that tool sometimes the information will be mistyfied and can lead to never ending. Even I do use EM few times in my job, but I found QA as a better tool to get whatever information you need. SQL 2005 has got better tools as compared to SQL 7 & 2000.
My experience in forums highlights most people don’t know even basic commands such as SP_HELP to know information about a database. So in this case getting a good practice on TSQL statement will give a stretch to their knowledge. It is fault of M$ that most of the products gives you length of GUI tools that makes brain bit of lazy. I have no comparison with JCL & SQL, but still no one can complete with DB2 nowadays and major companies rely upon mainframe for their data security. Lastly, as Luis said it is an individual option and I have seen many people who are really expereinced in SQL also asks about basic TSQL commands due to their regular practice with EM.
…my 2c. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hi, As mentioned earlier it could be endless debate about EM or QA , here is a very good article from *Vyas* that describes why we should use QA rather EM , just have a look : <a href=’http://vyaskn.tripod.com/sql_enterprise_manager_or_t-sql.htm’ target=’_blank’ title=’http://vyaskn.tripod.com/sql_enterprise_manager_or_t-sql.htm'<a target="_blank" href=http://vyaskn.tripod.com/sql_enterprise_manager_or_t-sql.htm>http://vyaskn.tripod.com/sql_enterprise_manager_or_t-sql.htm</a></a> [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] HTH Regards Hemantgiri S. Goswami [email protected] "Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

Dtipton, I use EM to review job history or job progress. I also use EM to get quickly some other information. However, when I want to apply changes I may use it just on dev server when i want to prepare some quick tests. The main reason I mentioned before. Another one is that EM produces extremely inefficient scripts in most cases.

It was not my point to argue that EM is the perfect tool or that we should all be using it for every task during daily administration. My point was, we shouldn’t be labeling people as STRONG DBA’s or LAZY DBA’s based on the tools they use to do their job. ghemant, Thanks for the link. I agree that DDL changes via EM are a BIG no-no. Satya, <<GUI tools that makes brain bit of lazy>> This is what I’m talking about. Either you are lazy or you’re not! Using the GUI tool doesn’t make you lazy and using T-SQL isn’t going to make you motivated. Example: I recently worked with a new DBA who had been to some classes, but had little experience. He had been asked to move a database to a different drive on the server. He asked me if I could walk him through it. It was a DEV database so no emergency, but the developers were waiting on him to make the move. I could have him type out the commands in QA and fight with the syntax (remember he’s new and I want him to learn) but that would take a while and people are waiting. Instead we jump into EM, detach the DB, move the files in the OS, and attach the DB in the new location. He tells me he was told in class that he shouldn’t use EM because only LAZY DBA’s use it. I say hogwash!! However, I do have him read the sections in BOL on sp_attach_db and sp_detach_db and we walk through the execution of these commands using QA in our "sandbox". Did I make him lazy by showing him how to use EM for a detach/attach?? I think I gave him some confidence because he was able to quickly accomplish his task and wasn’t frustrated by numerous syntax errors while people were waiting on him. I STRESSED the importance of knowing the associated T-SQL commands and we walked through them later in the day.

quote:Originally posted by dtipton Thanks Frank,

??? You meant Luis??? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

You bring up few points how DBA should work, I appreciate it.
I prefer using both EM and QA will make things better and never rely results on EM completely. It is better to check with QA often for correct information. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks again to all of the moderators and participants. This site is very helpful!

I think this is a great issue, and the debate has been spectacular. Is it too late for my 2 cents? I think at the crux of the issue is the fact that Microsoft by releasing Enterprise Manager accomplished 2 things at that the same time: 1. Allowed people like me who knew SQL to suddenly have the opportunity to quickly manage databases. I was far from a DBA, but in small startup companies where money is scarce, guess what? I was the DBA. And why not, I was able to use the Enterprise Manager and accomplish what DBA’s did, right? Well 95% of what DBA’s did and that was good enough in a startup environment. As long as the application we wrote (we being many different small companies) worked and could be brought to market quickly, then voila Microsoft SQL Server was the database to use, because lets face it, nobody without serious training could possibly do the same in Oracle. You had to be a serious DBA in order to get any kind of resemblence to decent performance in an Oracle environment. So that was a good thing for the application market, it allows companies to be quick to the market, and allows companies to buy those products with very little overhead for maintenance. Oh it also allowed Microsoft to grab a huge share of the RDBMS market. Sheer coincidence I’m sure. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> 2. The negative side was that suddenly any programmer with T-SQL abilities could now look like a DBA and accomplish the quick 95% of the tasks that a DBA was needed for, and the application companies could deploy their applications, or companies could buy applications running on SQL Server and didn’t need a DBA either (or at least they thought so.) After all with the cost savings companies were seeking, what a great fit. Problem with that was that in the "real" world companies almost always end up encountering situations that require the other 5% of things that EM didn’t provide, and weren’t equipped to deal with those things. Fortunately for me (and others like me) as I’ve begun focusing more and more on the DBA side of the fence, and away from the programming and T-SQL development side of the fence, there are forums like this where we can learn from those that have always been on the heavy-duty, last 5%, of the work DBA side of the fence. The economical side of this debate couldn’t be plainer. To get certified you have to know the T-SQL way of coding the "DBA" stuff or you simply can’t pass the test(s). The harsh reality is that Luis, Frank, Satya and others could walk in and pass any test with flying colors because they have the practice in typing things out the "hard way", while I haven’t been able to pass the tests becasue I did grow accustomed to letting Enterprise Manager do it for me. I thought I was doing the right thing using what Microsoft provided for me to do the job for my customers quicker/faster, but in the end it bit me in the rear, because while Microsoft makes it so easy to use Enterprise Manager, encourages companies to buy SQL Server and not have to have a "real" DBA, they don’t certify based on using Enterprise Manager. So for the past few years, I’ve been on the outside looking in. Don’t feel too sorry for me though, I am gainfully employed as a DBA and am being paid while I sit here on this forum learning and having this debate. So "it’s all good."

Your opinion is counted and it is worth to mention one’s experience. Experience always gives you brigher side of results, based upon that I always suggest to use QA to have more understanding on what you’re doing. I agree EM is a GUI tool that can achieve short-cut to accomplish the task, so that you can concentrate on other parts of your job. But do not always depend upon the EM and take some time to learn more about TSQL statements that can help to get more awareness on SQL Server. There is an open chance for all us to use the tools that are available and get the tasks finished, but it is a human tendency to get the things completed in easy way than learn more about the process. There is always 2 sides in any coin, so workout at your best.
Have a good weekend. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Anyone can make SQL Server run, only a small subset can make it run well!

Finally I would like to know if Loiter99 (Original Post) fix the problem[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />][<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />]. Luis Martin Moderator SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important Bertrand Russell All postings are provided â€œAS ISâ€ with no warranties for accuracy.

>>Finally I would like to know if Loiter99 (Original Post) fix the problem I am also interested to know that [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

]]>