[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 914
  • Last Modified:

Import SQL 2005 database example into SQL 2008

How do I import a Sql 2005 database so that I can read it using Sql 2008 using SQL Server Manager?? I have a database file AddressBook.mdf that is a database file created with SQL Express 2005 and I would like to import it onto my SQL 2008 server. I tried attaching the database file using SQL Server Management Studio but it does not work. It is looking for a AddressBook_log.ldf file.
So how do I get SMS to import the older database. I want to use the older database to work through a visual studio example that is in my book but I can't get my Sql 2008 server to recognise the older Sql 2005 examples. I would rather not load Sql 2005 if I can avoid it.
Thanks
0
ninjadragon
Asked:
ninjadragon
1 Solution
 
Mark WillsTopic AdvisorCommented:
OK,

There are (at least) two parts to the database - the MDF and the LDF. The MDF is the data and the LDF is the log file. Normally you should be able to attach the Database when you have both parts - chances are it has been moved so the internal pointer from the MDF is not finding the LDF.

It can sometimes allow you to create a new database without the LOG, and if you do not have the log then you might be running into some of those warning / errors.

There are also some stored procedures which might help you (for example) :

EXEC sp_attach_single_file_db @dbname = 'AdventureWorks',  @physname = N'C:\Databases\AdventureWorks_Data.mdf';

or

EXEC sp_attach_db @dbname = N'AdventureWorks',
        @filename1 = N'c:\Databases\AdventureWorks_Data.mdf',
        @filename2 = N'c:\Databases\AdventureWorks_log.ldf' ;

But those physical files must exist on disk in those locations.

Best way is to restore from a backup copy of the file - do you have one ?
0
 
ninjadragonAuthor Commented:
No I don't have a backup copy because the files are part of the books examples that came with it. The book is C# for Programmers 3rd edition. There are no .ldf files. I will try running your code. My other option is to fix my windows XP computer so I can load SQL2005 Express. The problem there is I can't seem to get rid of the other older versions of SQL. I already asked this as a question before and I am still working on resolving it.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
ninjadragonAuthor Commented:
I tried the code EXEC sp_attach_single_file_db @dbname = 'AddressBook',  @physname = N'C:\SQL Server 2000 Sample Databases\ AddressBook.mdf';
It gave the following error:
Unable to open the physical file "C:\SQL Server 2000 Sample Databases\ AddressBook.mdf"Operating System error 5

I do know the file is there because I can see it using Computer this is a Windows Vista Home Premium system.
0
 
Mark WillsTopic AdvisorCommented:
Well it might be there - the error you are getting is "Access Denied"

you will need to make sure you give  the MSSQLSERVER service account "Full Control" permissions on the database file.
0
 
Mark WillsTopic AdvisorCommented:
oh, and the folders / path as well (needs modify permissions at least)
0
 
ninjadragonAuthor Commented:
Can you give me step by step instructions as I am new to SQL Server?
0
 
Mark WillsTopic AdvisorCommented:
Well, it is not SQL Server as such - it is the operating system and folder/file security.

To check/change your current SQL Server Service Account, you can use SQL Server Configuration Manager.

On the Start menu, point to All Programs, point to Microsoft SQL Server 2005/8, point to Configuration Tools, and then click SQL Server Configuration Manager.

Also, have a read of : http://msdn.microsoft.com/en-us/library/ms143504.aspx

Unfortunately I am not on Vista so cannot give you step by step instructions - but this should help (seems easiest to follow) http://paulstamatiou.com/how-to-quickie-vista-permissions  and a general read on security is : http://msdn.microsoft.com/en-us/magazine/cc982153.aspx now it says 2008 but also applies (by and large) to vista (same code base).
0
 
hegotITCommented:
Looking at your example:

EXEC sp_attach_single_file_db @dbname = 'AddressBook',  @physname = N'C:\SQL Server 2000 Sample Databases\ AddressBook.mdf';

It looks to me like you have a space between the backslash and the file name: \ AddressBook.mdf';

Another thing you could try would be to create the database in SQL 2008 using the same .MDF and .LDF file names, then stop the MSSQLSERVER service (or whatever your instance is named), copy your .MDF file over the new one you just created, then restart the service.
0
 
ninjadragonAuthor Commented:
I will try these tonight and get back to both of you. Thanks.. You would think they would make it easier to copy in an older database.. :-( I have now installed SQL 2005 express on my laptop so I should be able to bring up the database with that.
0
 
Mark WillsTopic AdvisorCommented:
It might depend on how (or who) you installed as - it *might* give you the same file permissions error.  Let's hope not.
0
 
ninjadragonAuthor Commented:
This is making me wish I had never heard of SQL Server. :-( On my laptop I opened Microsoft SQL Server Management Studio and I connected to BIGRED\SQL2005EXPRESS which is the name of the SQL 2005 install. No problem. BUT I tried to attach a database example and then I tried to create a new database and both times I get the following error
Create failed for database Test. additional information CREATE DATABASE permission denied in database 'master' Error 262.
I am the only one on this laptop and have administration rights. I installed the server using Windows Authentication. So I don't know what security permissions I am missing.
0
 
Mark WillsTopic AdvisorCommented:
OK, go into services, stop SQL Server Service change the Account to your own user - it probably has LocalSystem or NetworkService there currently (or something similar).  Restart the service.

Or,

Go to all programs ==> Microsoft SQL 2005 ==> TOOLKIT ==> Surface configuration
Then :   Add new administrator, Select the SQL instance (on the left) and move it to the right

And have a quick read of : http://msdn.microsoft.com/en-us/library/aa905868(SQL.90).aspx  before you start.




0
 
Mark WillsTopic AdvisorCommented:
By the way, the problems are Vista related - it reckons it knows better than you do about security and permissions :)
0
 
ninjadragonAuthor Commented:
Okay! I will try this over the New Year and get back to you. I see Microsoft has removed some of the pages you reffered me too in their infinite wisdom. It looks like it could be the UAC giving me greif. It will take me a little while to figure this out but Thanks a bunch. Have a HAPPY NEW YEAR!! and I will post back the day after.
0
 
Mark WillsTopic AdvisorCommented:
They might be "restricted" pages - sorry about that...  Yep that UAC accounts for a lot of problems in Vista...

Happiest of new years to you too, and will catch you next year...
0
 
ninjadragonAuthor Commented:
Hi mark_wills: I am very happy to say your solution worked!!! It was the UAC that was giving me the problem so I turned it off. I then used your query above to attach the sample databases. So this problem is solved. Can you look at my other SQL question as how to clear my Dell Windows XP Pro of it's errors. It gives me an SQL error (see attached) and I can't get rid of SQL from my system.
THANKS FOR SOLVING THIS FOR ME!!!!
StartUp-Error-2.JPG
0
 
ninjadragonAuthor Commented:
Thanks Mark!!
0
 
Mark WillsTopic AdvisorCommented:
Hi ninjadragon, yep, will have a look at it in a short while - wont be long - and very pleased to hear that your Vista system is now working :)

Cheers,
Mark Wills
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now