March 4, 2011 at 2:28 pm
Hi Folks, I am fairly inexperienced (obviously, since i didn't create a .bak) in the SQL world.
I am trying to move a 2005 DB to a different machine with sql server 2008:
What I did:
1. Created a copy of the .ldf (sql 2005) on day 1
2. Created a copy of the .mdf (sql 2005) on day 2 (there are additional records on the .mdf that were done on day 2 prior to copying)
3. Moved both files to new computer, tried to attach a new DB (in sql server 2008)
4. Got the infamous error:
"File activation failure. The physical file name "xxx" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.Msg 1813, Level 16, State 2, Line 9
Could not open new database 'xxx'. CREATE DATABASE is aborted."
5. Then I tried the trick in http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
to try to attached the DB without the log file
6. Got the error upon
DBCC CHECKDB(xxx, REPAIR_ALLOW_DATA_LOSS)
"Cannot open database 'xxx' version 611. Upgrade the database to the latest version."
I unfortunately do not have access to the original server where I initially got the files.
On a side note, I was able to attach another DB and only had to use the following script
EXEC sp_attach_single_file_db @dbname='TestDb',
@physname=N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf'
GO
Any help/ideas are MUCH appreciated.
Thanks!
March 4, 2011 at 2:33 pm
In database options what compatibility level do you have the database in? I would recommend setting it to version 2008 (100), then retry the CheckDB, if I'm reading your error correctly.
EDIT: Yeah, just confirmed. SQL 2k5 are version 611/612, and 2k8 are 655.
If that fails, you may have to find a SQL 2k5 system to get this working on, then do a detach/backup, and then bring it to the 2k8 server.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 4, 2011 at 3:47 pm
I created a new database with the same DB name and everything, then overwrote it with my DB. I then ran:
ALTER DATABASE xxx SET EMERGENCY
ALTER DATABASE xxx SET SINGLE_USER
ALTER DATABASE xxx SET COMPATIBILITY_LEVEL = 100
and got the error:
Msg 946, Level 14, State 1, Line 1
Cannot open database 'xxx' version 611. Upgrade the database to the latest version.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Am i doing this right?
Thanks for the help.
March 4, 2011 at 3:52 pm
elfty (3/4/2011)
I created a new database with the same DB name and everything, then overwrote it with my DB. I then ran:ALTER DATABASE xxx SET EMERGENCY
ALTER DATABASE xxx SET SINGLE_USER
ALTER DATABASE xxx SET COMPATIBILITY_LEVEL = 100
and got the error:
Msg 946, Level 14, State 1, Line 1
Cannot open database 'xxx' version 611. Upgrade the database to the latest version.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Am i doing this right?
Thanks for the help.
I'm assuming you get the errors when you run the bolded statement?
It's not upgrading to 2k8 from the original 2k5 format... and it can't because the mdf isn't properly closed. You're going to need to find a 2k5 server somewhere and do your emergency recover there, unless I'm mistaken. I'll ask a few folks to take a look who are much better at recovery than I am.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 4, 2011 at 3:57 pm
Yes the error is from the bolded statement. Thanks for the help.
March 4, 2011 at 4:17 pm
First things first. An mdf from one point in time and an ldf from an earlier time will NEVER work (unless there were no changes between the two times). If you don't have the ldf from the time that the mdf is from, throw the ldf away (it's useless) and hack-attach the database. To a SQL 2005 server (evaluation edition is fine), since this is a 2005 DB. Recovery first, upgrade later.
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
p.s. compat mode is totally irrelevant here, it's a switch for the query processor. It has no effect on the actual version of the database (which is what the errors are complaining about).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 4, 2011 at 4:28 pm
GilaMonster (3/4/2011)
First things first. An mdf from one point in time and an ldf from an earlier time will NEVER work (unless there were no changes between the two times). If you don't have the ldf from the time that the mdf is from, throw the ldf away (it's useless) and hack-attach the database. To a SQL 2005 server (evaluation edition is fine), since this is a 2005 DB. Recovery first, upgrade later.http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
Thanks Gail. As you saw, I was leaning that direction after my brain caught up with the actual error message, but I'm so used to seeing those codes in regards to the compatibility level I wasn't sure, and figured it couldn't hurt since he had copies of the file.
p.s. compat mode is totally irrelevant here, it's a switch for the query processor. It has no effect on the actual version of the database (which is what the errors are complaining about).
That makes sense, thank you. Do you happen to know of any good documentation on what the compatibility levels specifically affect? I've found only generic documents in the past regarding the T-SQL usage.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 4, 2011 at 5:06 pm
Craig Farrell (3/4/2011)
Do you happen to know of any good documentation on what the compatibility levels specifically affect? I've found only generic documents in the past regarding the T-SQL usage.
No, sorry
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 7, 2011 at 4:32 am
The most obvious error is the one Gail caught, but I have to ask.
When you copied the files, was the database online or offline?
EDIT: Or was the SQL service off / on?
March 7, 2011 at 8:09 am
it was offline
March 7, 2011 at 8:16 am
elfty (3/7/2011)
it was offline
Good to hear. Leaving the db online is easily the most commonly-made mistake when copying the .mdf and .ldf files.
March 9, 2011 at 12:07 pm
Thanks for the help all. I was fortunate enuf to get access to my old server and created a .bak. Makes life so much easier!!
One more question.. I'm trying to restore a 115gig .bak (160gig .mdf file).. its been 2.5 hours so far, how long should I expect it to take?
Thanks!
March 9, 2011 at 12:14 pm
It depends.
Seriously, size, IO throughput, other load, etc, etc
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 9, 2011 at 12:16 pm
It sounds like you're restoring from a compressed backup file. That always takes longer than a regular 1:1 restore.
It'll also take longer if you're trying to restore across a UNC path (rather than a direct drive connection).
March 9, 2011 at 12:27 pm
Brandie Tarvin (3/9/2011)
It sounds like you're restoring from a compressed backup file.
Not necessarily (if so, that's very poor compression). Backups don't include free space in the data file, so they're often smaller than the mdf
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply