SQLServerPedia

The full original question is:  How do you change the default location of a mdb file? Currently when I detach it is set for C: Drive path and I need to change it to D: Drive path. How can I change it?

First, thanks for the question submission!  You can detach the database, move the file(s), and reattach using the CREATE DATABASE… FOR ATTACH command.

This is a SQL Server 2005/2008 example. For SQL Server 2000, you would use sp_attach_db/sp_attach_single_file_db to perform the attach.

For example:

Detach the database during a maintenance window – make sure you have a backup just in case something goes wrong:

USE master;
GO
EXEC sp_detach_db <db_name>;
GO

Now move the database file to the D: drive and reattach:

– Execute CREATE DATABASE FOR ATTACH statement
CREATE DATABASE
ON (FILENAME = ‘D:\\’) FOR ATTACH;
GO

Keep the questions coming!  We get a large volume of submissions and love seeing what the community is thinking about. 

David

Hi all, here’s a vblog entry that covers a bit more the topic of clock drift in virtualized environments.  This vblog entry corresponds to my blog post earlier this week. 

Enjoy and I look forward to your feedback – Kevin

[youtube=http://www.youtube.com/watch?v=BYoL7UPSNvs]

Here’s the actual question in its entirety:

We are trying to implement log shipping… We implement a transactional backup hourly. If our nightly full backup takes 1 hour and 45 minutes, what is our recovery process? Is it a bad thing to have a transactional backup in the middle of a full backup? 

Now, your question, whether you realize it or not, is pretty involved and has three major elements.  First, you want to know about implementing log shipping.  Second, you want to know about the recovery process considering your current backup process of hourly transaction log dumps and nightly full database dumps.  And finally, you want to know if a transaction log backup can cause problems if it occurs while a database backup is already processing. 

FULL DISCLOSURE - I’ll go ahead and say now that I’m going to include verbiage around a Quest product in this response – not a blatant advert, but I will be mentioning one of our products.

So, to your first question – remember that log shipping is a high-availability technique used to speed recovery of a database should you ever have a crash.  You don’t say which version of SQL Server you’re running.  But I assume that you must be running on SQL Server 2000, otherwise you would have mentioned other (and better, IMO) alternatives such as database mirroring.  Assuming you’re on SQL2000, then log shipping is not a big deal to implement and is widely discussed elsewhere on the internet, such as Microsoft’s TechNet (www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship1.mspx) and MSDN (msdn.microsoft.com/en-us/library/ms187103.aspx).  I won’t rehash an elementary concept of SQL Server when others have already done an excellent job of telling you how to implement it.  Since you might still be in the planning stages of your high-availability solution, here are two caveats that are often overlooked by newbies.  One, don’t forget that the database that is the target of log shipping is going to be in recovery mode all the time until it becomes the primary database.  You pretty much cannot use it unless the source database fails.  Two, log shipping works great with LiteSpeed.  So, if you’re a Quest Software customer using LiteSpeed, then you can enjoy all the benefits of speed, reduced disk consumption, and encryption that LiteSpeed offers.

To your second question, your current backup process, in which you backup the transaction logs every hour and the database nightly, is pretty sound.  Keep in mind that you could potentially lose up to 59 minutes of data with any given database failure.  If 59 minutes does not represent a significant amount of work on the database in question, then great!  However, I rarely had a production database where I was comfortable making users re-key up to 59 minutes of work.  Fifteen minute intervals for a transaction log dump were more in my range of comfort.  Additionally, consider that most applications do NOT have users working around the clock.  Because of that very natural business cycle, you can often lessen or even completely discontinue transaction log backups during the wee hours.  For example, you might run the transaction log backups every 15 minutes from 6:00 AM EST to 8:00 PM EST every day, since you have few if any users after that time.  Alternately, you might run the log dumps ever 15 minutes from 6:00 AM to 8:00 PM EST, run them hourly from 8:00 – 10:00 PM EST, and then only do one more full database backup until the next morning.  Frequent log backups are important also because that’s the primary means by which you keep the transaction log from growing too large.

Finally, with your last question, starting a transaction log backup while a full database backup is still running will behave differently on different versions of SQL Server.  For SQL2000, SQL Server doesn’t allow you to run the transaction log backup while the full database backup is running (at least that’s what my fragile memory is telling me).  For SQL2005 and later, transaction log backups and full database backups can run concurrently though the transaction log backup will certainly be slower because of it and the database backup will likely be slower too.  The full database backup will contain all data in the state of the database at the time it completed, while the transaction log backup will contain all of the transactions that have run since the last full database backup OR transaction log backup.  Since the transaction log backup will probably finish before the 90 minute long database backup, you should think of that transaction log backup as the last log backup before the new full database backup starts the transaction log backup process all over again.  Thus, in an emergency restore situation, you could apply the previous day’s full database backup plus all transaction logs up to the time that the transaction log ran concurrently with tonight’s full database backup to get a recovery that included all transactions up to the most recent point in time.  Once the full database backup completes, however, the clock is started over and you would have to start applying transaction log backups that were subsequent to the completion of the full database backup.

Hope this helps,

-Kevin

Hello, this is Kevin Kline – join me tomorrow, along with Quest Domain Expert Brent Ozar and SQL Server MVP Ron Talmage, for a roundtable discussion on SQL Server consolidation and virtualization.  We’ll discuss a variety of issues that seem to come up constantly in the discussion forums – How are your consolidated / virtualized SQL Servers going to be used?  Are they running production environments with strict SLAs and heavy workloads?  Do SQL Server licensing costs make a big difference for your organization?  What about SQL Server management costs?  Will performance troubleshooting be an issue?  

The webcast is tomorrow, Thursday July 30th, at 8:00 AM PST / 11:00 AM EST.  Register for the webcast here.

I hope to see you there tomorrow!

Question: I am setting myself up a Test Server for my eTail store and I’m getting these SQL errors.
My production Server (hosted externally, works just fine and always has, so it shouldn’t be the ASP code)
Here’s the error:

Number: -2147217865
Page: /Scripts/default.asp
Desc: Invalid object name
SQL: SELECT configValLong FROM storeAdmin WHERE configVar = controlRec’ AND adminType = ‘C’

- SQL 2005

- Locally, the table does exist

- If I paste the SQL statement into SQL Management Studio I get the same error BUT yet if slightly change it to ‘FROM nextinline.storeAdmin’ it works fine
and returns properly.

- If I open the ‘storeAdmin’ table then show the SQL and past in the above without changing it also works.

- Connection string defaults to the Database name and uses SQL authentication

- My guess is it’s a Server setting which (if changed) allows SQL statements to run without the Fully qualified dB name…however that is over my head.

James Delve says: SQL Server 2005 introduces the notion of “schema”, and a schema is a collection of database objects that form a single namespace. A namespace is a set in which every element has a unique name.

In SQL Server 2005, each user has a default schema, which specifies the first schema that will be searched by the server when it resolves the names of objects. The default schema can be set and changed using the DEFAULT_SCHEMA option of CREATE USER and ALTER USER.

If DEFAULT_SCHEMA is left undefined, the database user will have DBO as its default schema. This is why when you specify the “nextinline.storeAdmin” wording it works.

To resolve this, you can continue to use the “nextinline.storeAdmin” in your code, or simply change the user to have a default schema of “nextinline” and this will save you having to be explicit in your code.

The code to change the default schema is:

ALTER USER user_name
WITH <set_item>  [   ,...n  ]

<set_item>   : : =
NAME = new_user_name
|    DEFAULT_SCHEMA = schema_name

More Information About Schemas and Invalid Objects

Ari Weil blogged about whether to create a SQL Server schema.