How can I attach AdventureWorks Database to SQL Server Management Studio Express?

Hi all,

I just downloaded AdventureWorks_Data.mdf, AdventureWorks_Data.ldf, AdventureWorksDW_Data.mdf, and AdventureWorksDW_Data.LDF files to the C:\Program Files\Microsoft SQL Server\MSSQL.1|\MSSQL\Data folder of my Windows XP Pro PC. I tried to attach the AdventureWorks database in my SQL Server Management Studio Express in the following way: I clicked on SQL Server 2005 Express=>SQL Server Management Studio Express=>Right clicked on Databases=> clicked on "Attach..."=> "Attach Databese" showed up. But I can not type in anything under the MDF File Location, Database Name, Attach As, Owner, Status, Message!!!? What is wrong in my way of doing the attachment of AdventureWorks database (described above)? Please help and advise.

Thanks,

Scott Chang

P. S. I do not have Microsoft Visual Studio 2005. Walkthrough: Install the AdventureWorks Database (http://msdn2.microsoft/en-us/library/ms31035.aspx) : To confirm your SQL Server data directory (Start=>All Programs, Microsoft Visual Studio 2005, Visual Studio Tools, Visual Studio 2005 Command Prompt) steps 1 thru 4 can not be applied to my SQL Server Management Studio Express. I read an SQL Express post dated 19 April 2006 and learned the above-mentioned way to attach the AdventureWorks database in the SQL Server Management Studio Express. But that way does not work for me.

Hi Scott,

You can't type in the name of the database, you need to click the Add button, which will open a browse window onto the Data folder, where you can select the MDF file you desire. Once added, you can click OK to complete the attach.

Mike

|||

Why are some folders unbrowsable in SSMSE? I have an .mdf file located under c:\documents and settings\myusername\blah blah blah.... and when I try to browse to my mdf file I get no further than c:\mydocuments\myusername. As if there are no subfolders. Is this a folder rights issue?

|||

Yes, it's a folder rights issue.

SQL Express runs as the Network Service account, which doesn't have permissions to any of the User profile directories such as My Documents. If you want to have your database always attached to SQL Express and available to everyone, then you should put the file in the SQL Data directory and attach it from there. If you want the database to be reserved for just a single user, then I would suggest you explore User Instances, which is a special way to run SQL Express under specific users accounts so that it does have permission to the User profile directories.

User Instances are used by default when you insert a database into a Visual Studio project. You can find more information here.

Mike

How can I attach AdventureWorks Database to SQL Server Management Studio Express?

Hi all,

I just downloaded AdventureWorks_Data.mdf, AdventureWorks_Data.ldf, AdventureWorksDW_Data.mdf, and AdventureWorksDW_Data.LDF files to the C:\Program Files\Microsoft SQL Server\MSSQL.1|\MSSQL\Data folder of my Windows XP Pro PC. I tried to attach the AdventureWorks database in my SQL Server Management Studio Express in the following way: I clicked on SQL Server 2005 Express=>SQL Server Management Studio Express=>Right clicked on Databases=> clicked on "Attach..."=> "Attach Databese" showed up. But I can not type in anything under the MDF File Location, Database Name, Attach As, Owner, Status, Message!!!? What is wrong in my way of doing the attachment of AdventureWorks database (described above)? Please help and advise.

Thanks,

Scott Chang

P. S. I do not have Microsoft Visual Studio 2005. Walkthrough: Install the AdventureWorks Database (http://msdn2.microsoft/en-us/library/ms31035.aspx) : To confirm your SQL Server data directory (Start=>All Programs, Microsoft Visual Studio 2005, Visual Studio Tools, Visual Studio 2005 Command Prompt) steps 1 thru 4 can not be applied to my SQL Server Management Studio Express. I read an SQL Express post dated 19 April 2006 and learned the above-mentioned way to attach the AdventureWorks database in the SQL Server Management Studio Express. But that way does not work for me.

Hi Scott,

You can't type in the name of the database, you need to click the Add button, which will open a browse window onto the Data folder, where you can select the MDF file you desire. Once added, you can click OK to complete the attach.

Mike

|||

Why are some folders unbrowsable in SSMSE? I have an .mdf file located under c:\documents and settings\myusername\blah blah blah.... and when I try to browse to my mdf file I get no further than c:\mydocuments\myusername. As if there are no subfolders. Is this a folder rights issue?

|||

Yes, it's a folder rights issue.

SQL Express runs as the Network Service account, which doesn't have permissions to any of the User profile directories such as My Documents. If you want to have your database always attached to SQL Express and available to everyone, then you should put the file in the SQL Data directory and attach it from there. If you want the database to be reserved for just a single user, then I would suggest you explore User Instances, which is a special way to run SQL Express under specific users accounts so that it does have permission to the User profile directories.

User Instances are used by default when you insert a database into a Visual Studio project. You can find more information here.

Mike

how can attack database from sqlserver management studio express?

I have created a database named s_office,now i want add file data.MDF and log.LDF into it. .How can i do it?Could you show me the script that attack file .MDF and .LDF into database.thankxhttp://msdn2.microsoft.com/en-us/library/ms179877.aspx

hotfixes, mssqlsystemresource.ldf

I've just discovered that applying a hotfix to SQL2005 can fail if you
have moved the 'mssqlsystemresource.ldf' logfile to a different
subirectory from the MDF.
It is well documented that the mssqlsystemresource.mdf must be in the
same location as master.mdf, but I haven't seen anything about keeping
the ldf with the mdf (our practice is to run with LDF files on a
different set of spindles from the MDF files).
If you have this setup and haven't done a service pack, you can readily
fix it by moving the LDF from the MDF folder and putting it where it
'belongs' according to the master catalog.
However, you're better off leaving both the LDF and MDF together with
master.mdf.> However, you're better off leaving both the LDF and MDF together with
> master.mdf.
Yes, this is the Best Practice. Unfortunately, it's easy to forget about
those mssqlsystemresource files.
Hope this helps.
Dan Guzman
SQL Server MVP
"horseradish" <millardjk@.gmail.com> wrote in message
news:OWUWEgDuHHA.4412@.TK2MSFTNGP02.phx.gbl...
> I've just discovered that applying a hotfix to SQL2005 can fail if you
> have moved the 'mssqlsystemresource.ldf' logfile to a different
> subirectory from the MDF.
> It is well documented that the mssqlsystemresource.mdf must be in the same
> location as master.mdf, but I haven't seen anything about keeping the ldf
> with the mdf (our practice is to run with LDF files on a different set of
> spindles from the MDF files).
> If you have this setup and haven't done a service pack, you can readily
> fix it by moving the LDF from the MDF folder and putting it where it
> 'belongs' according to the master catalog.
> However, you're better off leaving both the LDF and MDF together with
> master.mdf.

hotfixes, mssqlsystemresource.ldf

I've just discovered that applying a hotfix to SQL2005 can fail if you
have moved the 'mssqlsystemresource.ldf' logfile to a different
subirectory from the MDF.
It is well documented that the mssqlsystemresource.mdf must be in the
same location as master.mdf, but I haven't seen anything about keeping
the ldf with the mdf (our practice is to run with LDF files on a
different set of spindles from the MDF files).
If you have this setup and haven't done a service pack, you can readily
fix it by moving the LDF from the MDF folder and putting it where it
'belongs' according to the master catalog.
However, you're better off leaving both the LDF and MDF together with
master.mdf.
> However, you're better off leaving both the LDF and MDF together with
> master.mdf.
Yes, this is the Best Practice. Unfortunately, it's easy to forget about
those mssqlsystemresource files.
Hope this helps.
Dan Guzman
SQL Server MVP
"horseradish" <millardjk@.gmail.com> wrote in message
news:OWUWEgDuHHA.4412@.TK2MSFTNGP02.phx.gbl...
> I've just discovered that applying a hotfix to SQL2005 can fail if you
> have moved the 'mssqlsystemresource.ldf' logfile to a different
> subirectory from the MDF.
> It is well documented that the mssqlsystemresource.mdf must be in the same
> location as master.mdf, but I haven't seen anything about keeping the ldf
> with the mdf (our practice is to run with LDF files on a different set of
> spindles from the MDF files).
> If you have this setup and haven't done a service pack, you can readily
> fix it by moving the LDF from the MDF folder and putting it where it
> 'belongs' according to the master catalog.
> However, you're better off leaving both the LDF and MDF together with
> master.mdf.

hotfixes, mssqlsystemresource.ldf

I've just discovered that applying a hotfix to SQL2005 can fail if you
have moved the 'mssqlsystemresource.ldf' logfile to a different
subirectory from the MDF.
It is well documented that the mssqlsystemresource.mdf must be in the
same location as master.mdf, but I haven't seen anything about keeping
the ldf with the mdf (our practice is to run with LDF files on a
different set of spindles from the MDF files).
If you have this setup and haven't done a service pack, you can readily
fix it by moving the LDF from the MDF folder and putting it where it
'belongs' according to the master catalog.
However, you're better off leaving both the LDF and MDF together with
master.mdf.> However, you're better off leaving both the LDF and MDF together with
> master.mdf.
Yes, this is the Best Practice. Unfortunately, it's easy to forget about
those mssqlsystemresource files.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"horseradish" <millardjk@.gmail.com> wrote in message
news:OWUWEgDuHHA.4412@.TK2MSFTNGP02.phx.gbl...
> I've just discovered that applying a hotfix to SQL2005 can fail if you
> have moved the 'mssqlsystemresource.ldf' logfile to a different
> subirectory from the MDF.
> It is well documented that the mssqlsystemresource.mdf must be in the same
> location as master.mdf, but I haven't seen anything about keeping the ldf
> with the mdf (our practice is to run with LDF files on a different set of
> spindles from the MDF files).
> If you have this setup and haven't done a service pack, you can readily
> fix it by moving the LDF from the MDF folder and putting it where it
> 'belongs' according to the master catalog.
> However, you're better off leaving both the LDF and MDF together with
> master.mdf.

Hot Backup

Someone told me should backup the live data files (files with
extension MDF and LDF) instead of the backup files (file with
extension BAK) in hot backup. I don't think it work in the case I need
to restore these data files from tape backup. Am I correct?That person might be thinking about the various type of snapshot backup products that exists. This
type of backup uses some technique to "freeze" the I/O for some period of time and then use some
journaling to keep track of the modifications performed while the backup is running (while you are
copying the files). There's some information in SQL Server 2005 Books Online, search for instance
for "VSS Writer" (which is the service in SQL server to assist these types of backup programs).
But in general you are right. You don't want to just "grab the database files".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<viet@.rocketmail.com> wrote in message
news:2341c184-ec8b-42d7-b250-3e5acd594e49@.w8g2000prd.googlegroups.com...
> Someone told me should backup the live data files (files with
> extension MDF and LDF) instead of the backup files (file with
> extension BAK) in hot backup. I don't think it work in the case I need
> to restore these data files from tape backup. Am I correct?|||On Apr 24, 6:47=A0pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> That person might be thinking about the various type of snapshot backup pr=oducts that exists. This
> type of backup uses some technique to "freeze" the I/O for some period of =time and then use some
> journaling to keep track of the modifications performed while the backup i=s running (while you are
> copying the files). There's some information in SQL Server 2005 Books Onli=ne, search for instance
> for "VSS Writer" (which is the service in SQL server to assist these types= of backup programs).
> But in general you are right. You don't want to just "grab the database fi=les".
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph=
ttp://sqlblog.com/blogs/tibor_karaszi
> <v...@.rocketmail.com> wrote in message
> news:2341c184-ec8b-42d7-b250-3e5acd594e49@.w8g2000prd.googlegroups.com...
>
> > Someone told me should backup the live data files (files with
> > extension MDF and LDF) instead of the backup files (file with
> > extension BAK) in hot backup. I don't think it work in the case I need
> > to restore these data files from tape backup. Am I correct... Hide quoted= text -
> - Show quoted text -
If the intention is to backup the database before tape backup . You
can always schedule the db backups online before tape backup and
the .bak files can be backed up on tape.
If the database size is very large you can use the tools like
Litespeed which will help you to save on time.
Thanks
Ajay Rengunthwar
MCTS,MCDBA,MCAD