May 18, 2012 at 5:04 pm
Use something like this:
USE [master]
GO
CREATE DATABASE [AdventureWorks2008R2] ON
( FILENAME = N'C:\Database\Data\AdventureWorks2008R2_Data.mdf' )
FOR ATTACH
GO
You will need to change your path. Once you execute that command, you should see something like the following:
File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_Log.ldf" may be incorrect.
New log file 'C:\Database\Log\AdventureWorks2008R2_log.LDF' was created.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 18, 2012 at 5:14 pm
Ive just tried that in a new query and its came back with this:
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "C:\Database\Data\AdventureWorks2008R2_Data.mdf" failed with the operating system error 3(failed to retrieve text for this error. Reason: 15105).
for some reason when i delete the log file it just is not attaching the new DB! Nightmare.
Is there anything else i may not have set up properly?
May 18, 2012 at 5:17 pm
You didn't change the file path in the query like I pointed out.
That file path must be changed to reflect the location of where you placed the data file on your machine. What I left there is strictly an example.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 18, 2012 at 5:38 pm
I think ive corrected it but still an error:
File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_Log.ldf" may be incorrect.
Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 5(failed to retrieve text for this error. Reason: 15105) while attempting to open or create the physical file 'C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks2008R2_log.LDF'.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'AdventureWorks2008R2'. CREATE DATABASE is aborted.
Ive looked on a few blogs and im not sure if there is an on-going problem with attaching the database? Or im just missing something completely obvious! :doze:
May 18, 2012 at 5:40 pm
post the command you used
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 18, 2012 at 5:44 pm
based off the file location in PM
USE [master]
GO
CREATE DATABASE [AdventureWorks2008R2] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks2008R2_Data.mdf' )
FOR ATTACH
GO
Also, what is the service pack level of your Dev instance?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 18, 2012 at 5:51 pm
Still the same error unfortunately;
File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_Log.ldf" may be incorrect.
Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 5(failed to retrieve text for this error. Reason: 15105) while attempting to open or create the physical file 'C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks2008R2_log.LDF'.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'AdventureWorks2008R2'. CREATE DATABASE is aborted.
And im not sure how to find out what service level? Do you mean what option is the built in account set to? If so i have just changed it to Local Service from Network service?
May 18, 2012 at 6:09 pm
Try this to get the server level and sp level
select @@version
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 18, 2012 at 6:23 pm
This is what came back:
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.0 <X64> (Build 6001: Service Pack 1)
May 18, 2012 at 6:47 pm
What you guys are struggling with is that there never was an official, meaning fully tested, release for R2.
Jason, please do your best to help him out. I will talk with my friends in Building 35 and 5 and will do my best to convince them to take care about this.
May 18, 2012 at 6:49 pm
Thanks for your help!
Would i need to download this by any chance: http://www.microsoft.com/en-us/download/details.aspx?id=26727 (SQLServer2008R2SP1-KB2528583-x64-ENU.exe)
May 18, 2012 at 7:00 pm
Yes - please patch your instance from RTM to SP1
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 18, 2012 at 7:05 pm
Sorry Jason i am totally new to this, so i should download the SP1 from the link i have shown? I thought in the reply earlier from me it states its running on SP1 ?
May 18, 2012 at 7:19 pm
The SP1 in your earlier reply is for the OS. SQL Server is at the release version still (RTM).
The database file you are trying to attach is SP1 and will not attach to RTM.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 18, 2012 at 7:31 pm
Thanks im running the update now, fingers crossed it will now work.
ill give it a go in the morning and leave the update running tonight.
Thanks again
Viewing 15 posts - 16 through 30 (of 48 total)
You must be logged in to reply to this topic. Login to reply