Database corrupted: attach

Showing posts with label attach. Show all posts

Thursday, March 29, 2012

Database Compatability

Hi everybody,
I am currently developing a website backed by an SQL 2000 datatabse, my
question is can I attach the database to an instance of SQL server 2005
and then re attach the same database to the SQL server 2000 that is on the
Production server without it beinfg upgraded to the 2005 format?
ThanksJason
Yes , you can attach SS2000 db to SS2005 instance , but cannot re-attach it
to SS2000 again
"Jason Chandler" <Jason Chandler@.discussions.microsoft.com> wrote in message
news:C9A0F01E-1AE0-4C88-8DAE-FB09BF220C84@.microsoft.com...
> Hi everybody,
> I am currently developing a website backed by an SQL 2000 datatabse, my
> question is can I attach the database to an instance of SQL server 2005
> and then re attach the same database to the SQL server 2000 that is on the
> Production server without it beinfg upgraded to the 2005 format?
> Thanks

Database Compatability

Hi everybody,
I am currently developing a website backed by an SQL 2000 datatabse, my
question is can I attach the database to an instance of SQL server 2005
and then re attach the same database to the SQL server 2000 that is on the
Production server without it beinfg upgraded to the 2005 format?
Thanks
Jason
Yes , you can attach SS2000 db to SS2005 instance , but cannot re-attach it
to SS2000 again
"Jason Chandler" <Jason Chandler@.discussions.microsoft.com> wrote in message
news:C9A0F01E-1AE0-4C88-8DAE-FB09BF220C84@.microsoft.com...
> Hi everybody,
> I am currently developing a website backed by an SQL 2000 datatabse, my
> question is can I attach the database to an instance of SQL server 2005
> and then re attach the same database to the SQL server 2000 that is on the
> Production server without it beinfg upgraded to the 2005 format?
> Thanks

Database Compatability

Monday, March 19, 2012

database attachement

I'm unable to attach a database in sql server which was detached previously
by me. The size of the mdb file is 12 gb and size of the log file is 20 gb.
Whenever I tried to attached the database the system goed hang.
Have you ensured that .mdb and .ldf files are available at the logical file
location path?
And do you see any error messages in errorlogs?
Thanks
GYK
"Paramjeet" wrote:

> I'm unable to attach a database in sql server which was detached previously
> by me. The size of the mdb file is 12 gb and size of the log file is 20 gb.
> Whenever I tried to attached the database the system goed hang.
>
|||You might try this techniquue:
Create a new databse with the same name and the same name for the data and
log files.
Stop SQL Server
Copy your database mdf and ldf files over the top of the new created files.
This should allow the database to be seen by SQL Server.
At this point I would run DBCC Checkdb to make sure there is no structural
problems with the database.
Rand
This posting is provided "as is" with no warranties and confers no rights.

database attachement

I'm unable to attach a database in sql server which was detached previously
by me. The size of the mdb file is 12 gb and size of the log file is 20 gb.
Whenever I tried to attached the database the system goed hang.Have you ensured that .mdb and .ldf files are available at the logical file
location path?
And do you see any error messages in errorlogs?
Thanks
GYK
"Paramjeet" wrote:
> I'm unable to attach a database in sql server which was detached previously
> by me. The size of the mdb file is 12 gb and size of the log file is 20 gb.
> Whenever I tried to attached the database the system goed hang.
>|||You might try this techniquue:
Create a new databse with the same name and the same name for the data and
log files.
Stop SQL Server
Copy your database mdf and ldf files over the top of the new created files.
This should allow the database to be seen by SQL Server.
At this point I would run DBCC Checkdb to make sure there is no structural
problems with the database.
Rand
This posting is provided "as is" with no warranties and confers no rights.

database attachement

I'm unable to attach a database in sql server which was detached previously
by me. The size of the mdb file is 12 gb and size of the log file is 20 gb.
Whenever I tried to attached the database the system goed hang.Have you ensured that .mdb and .ldf files are available at the logical file
location path?
And do you see any error messages in errorlogs?
Thanks
GYK
"Paramjeet" wrote:

> I'm unable to attach a database in sql server which was detached previousl
y
> by me. The size of the mdb file is 12 gb and size of the log file is 20 gb
.
> Whenever I tried to attached the database the system goed hang.
>|||You might try this techniquue:
Create a new databse with the same name and the same name for the data and
log files.
Stop SQL Server
Copy your database mdf and ldf files over the top of the new created files.
This should allow the database to be seen by SQL Server.
At this point I would run DBCC Checkdb to make sure there is no structural
problems with the database.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Database Attache Problems

When I attempt to attache an existing SQL Database in SQLExpress, I get sent to the attach databases dialog. I click on the add button and get an abend with the following message.
Locate Database Files - server\SQLEXPRESS dialog box. c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
Cannot access the specified path or file on the server. Verify that you have the necessary security privledges and that the path or file exists.

If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Location dialog box. When I click on OK, the Location Database Files dialog box opens, pointing me to my cd drive and does not permit me to alter the drive letter.

Since the database is on my C: drive, this will not work for me.

I have been unable to find any reference to the E: drive for SQLExpress in either that program directory or in the registry.

I went as far as removing the entire suite, then deleting everything that I could find having to do with it, registry entries, settings, common files, etc. I then re-installed the entire package and guess what, same thing.

Anyone have any ideas of how I get around this or how to fix it?

Any and all help will be greatly appreciated.

Thx, Tom

hi,

are the database files in the c:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data directory or elsewhere?

by default SQLExpress installs setting the NetworkService account to be the one to run the service, and this account is "limited" in NTFS permissions (and that's ok... it's just too large in permissions as well ), but should access without problems the provided path... but not c:\ pr the like...

but I already heared problems about the browsing dialog... with no additional workarounds..

but you can try yourself writing the Transact-SQL code to be executed in a "New query window"..

say you created and detached a database named testDB in the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ with physical files named testDB.mdf and testDB_log.LDF

just execute, as a logged sysadmin,

SET NOCOUNT ON;

USE master;

USE [master]

CREATE DATABASE [testDB] ON

( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\testDB.mdf' ),

( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\testDB_log.LDF' )

FOR ATTACH;

this will "manually" attach the db, according that you have enought permissions to the folder containing the physical file (obviously the account running the SQL Server instance has enought permissions on this sample location)..

regards

|||Andrea, thank-you very much for the help. Worked like a charm.

Regards, T

Database attach using .mdf and .ldf files

Hi all,
I have a problem with my sharepoint database. I lost access after i
installed SP1 on Sharepoint Portal 2007. When i tried to restore from my
backup sets i've seen that backup files corrupted. I just have mdf and ldf
files of the database. I've searched on this page and also internet but
suggested solutions did not work for me. I tried sp_attach_db and
sp_attach_single_file_db queries but they did not work. I got the same errors
that others who having the same problem. Also i tried to create a database in
the same name and changed the database files with the old ones. Then set the
database in emergency mode and tried to set online again using alter database
command. It is still being executed for 48 hours but havent been
completed.This database is very important for me and i need to get my
database back. So, do you know any alternative methods to get my database
back, just using mdf and ldf files on the same server or on the different
server?Have you tried to use (DBCC) REBUILD_LOG ?
--
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"ALKIM" <ALKIM@.discussions.microsoft.com> wrote in message
news:9A7F69FE-C2CA-40EC-9E37-E8D2178AB3FF@.microsoft.com...
> Hi all,
> I have a problem with my sharepoint database. I lost access after i
> installed SP1 on Sharepoint Portal 2007. When i tried to restore from my
> backup sets i've seen that backup files corrupted. I just have mdf and ldf
> files of the database. I've searched on this page and also internet but
> suggested solutions did not work for me. I tried sp_attach_db and
> sp_attach_single_file_db queries but they did not work. I got the same
> errors
> that others who having the same problem. Also i tried to create a database
> in
> the same name and changed the database files with the old ones. Then set
> the
> database in emergency mode and tried to set online again using alter
> database
> command. It is still being executed for 48 hours but havent been
> completed.This database is very important for me and i need to get my
> database back. So, do you know any alternative methods to get my database
> back, just using mdf and ldf files on the same server or on the different
> server?

Database attach using .mdf and .ldf files

Hi all,
I have a problem with my sharepoint database. I lost access after i
installed SP1 on Sharepoint Portal 2007. When i tried to restore from my
backup sets i've seen that backup files corrupted. I just have mdf and ldf
files of the database. I've searched on this page and also internet but
suggested solutions did not work for me. I tried sp_attach_db and
sp_attach_single_file_db queries but they did not work. I got the same errors
that others who having the same problem. Also i tried to create a database in
the same name and changed the database files with the old ones. Then set the
database in emergency mode and tried to set online again using alter database
command. It is still being executed for 48 hours but havent been
completed.This database is very important for me and i need to get my
database back. So, do you know any alternative methods to get my database
back, just using mdf and ldf files on the same server or on the different
server?
Have you tried to use (DBCC) REBUILD_LOG ?
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"ALKIM" <ALKIM@.discussions.microsoft.com> wrote in message
news:9A7F69FE-C2CA-40EC-9E37-E8D2178AB3FF@.microsoft.com...
> Hi all,
> I have a problem with my sharepoint database. I lost access after i
> installed SP1 on Sharepoint Portal 2007. When i tried to restore from my
> backup sets i've seen that backup files corrupted. I just have mdf and ldf
> files of the database. I've searched on this page and also internet but
> suggested solutions did not work for me. I tried sp_attach_db and
> sp_attach_single_file_db queries but they did not work. I got the same
> errors
> that others who having the same problem. Also i tried to create a database
> in
> the same name and changed the database files with the old ones. Then set
> the
> database in emergency mode and tried to set online again using alter
> database
> command. It is still being executed for 48 hours but havent been
> completed.This database is very important for me and i need to get my
> database back. So, do you know any alternative methods to get my database
> back, just using mdf and ldf files on the same server or on the different
> server?

Database Attach Scripts

I have a few existing 2000 instances that we are migrating to 2005 SP2. The
instances have a lot of databases on them. Is there an easy way to generate
the attach scripts for migrating?
Something like this:
declare @.dbid int,
@.sql varchar(4000),
@.name sysname,
@.old_name sysname,
@.filename nchar(260);
declare db_cr cursor
for select dbid, filename
from master..sysaltfiles
where dbid > 4
order by dbid;
open db_cr;
set @.sql = '';
set @.old_name = '';
fetch next from db_cr into @.dbid, @.filename;
while @.@.fetch_status = 0
begin
If @.old_name <> db_name(@.dbid)
select @.sql = @.sql + char(13) + char(10) +
'EXEC sp_attach_db N''' + rtrim(db_name(@.dbid)) + ''',
N''' + rtrim(@.filename) + '''';
else
select @.sql = @.sql + ', N''' + rtrim(@.filename) + '''';
select @.old_name = db_name(@.dbid);
fetch next from db_cr into @.dbid, @.filename;
end
close db_cr;
deallocate db_cr;
print @.sql;
Linchi
"Charlie" wrote:

> I have a few existing 2000 instances that we are migrating to 2005 SP2. The
> instances have a lot of databases on them. Is there an easy way to generate
> the attach scripts for migrating?
|||Charlie
http://dimantdatabasesolutions.blogspot.com/2007/03/detaching-and-attaching-database.html
"Charlie" <Charlie@.discussions.microsoft.com> wrote in message
news:220D9152-7C48-41BF-8CD8-8C3DD32C2C35@.microsoft.com...
>I have a few existing 2000 instances that we are migrating to 2005 SP2.
>The
> instances have a lot of databases on them. Is there an easy way to
> generate
> the attach scripts for migrating?
|||What does this script do exactly?
"Linchi Shea" wrote:
[vbcol=seagreen]
> Something like this:
> declare @.dbid int,
> @.sql varchar(4000),
> @.name sysname,
> @.old_name sysname,
> @.filename nchar(260);
> declare db_cr cursor
> for select dbid, filename
> from master..sysaltfiles
> where dbid > 4
> order by dbid;
> open db_cr;
> set @.sql = '';
> set @.old_name = '';
> fetch next from db_cr into @.dbid, @.filename;
> while @.@.fetch_status = 0
> begin
> If @.old_name <> db_name(@.dbid)
> select @.sql = @.sql + char(13) + char(10) +
> 'EXEC sp_attach_db N''' + rtrim(db_name(@.dbid)) + ''',
> N''' + rtrim(@.filename) + '''';
> else
> select @.sql = @.sql + ', N''' + rtrim(@.filename) + '''';
> select @.old_name = db_name(@.dbid);
> fetch next from db_cr into @.dbid, @.filename;
> end
> close db_cr;
> deallocate db_cr;
> print @.sql;
> Linchi
> "Charlie" wrote:
|||> What does this script do exactly?
Generate a T-SQL script to sp_attach_db every user database.
Linchi
"Charlie" wrote:
[vbcol=seagreen]
> What does this script do exactly?
> "Linchi Shea" wrote:
|||Where does it get a list? We are moving the user db's to a new environment.
I need to attach the raw files, sepcifying the .mdf and .ldf file locations
and db name...
Make sense?
Maybe I am not understanding what you are telling me...
"Linchi Shea" wrote:
[vbcol=seagreen]
> Generate a T-SQL script to sp_attach_db every user database.
> Linchi
> "Charlie" wrote:
|||The script reads the info from the sysaltfiles table on the current server.
If you are moving the files to a new server, you may need to edit the script
to point to the files in different locations.
Unless you have some overriding reasons to use a different directory
structure or a different drive, it's often simpler to keep the file locations
identical.
Linchi
"Charlie" wrote:
[vbcol=seagreen]
> Where does it get a list? We are moving the user db's to a new environment.
> I need to attach the raw files, sepcifying the .mdf and .ldf file locations
> and db name...
> Make sense?
> Maybe I am not understanding what you are telling me...
> "Linchi Shea" wrote:
|||That is really what I need!
This script does not print anything. A assume you want me to run it in
query analyzer?
"Linchi Shea" wrote:
[vbcol=seagreen]
> The script reads the info from the sysaltfiles table on the current server.
> If you are moving the files to a new server, you may need to edit the script
> to point to the files in different locations.
> Unless you have some overriding reasons to use a different directory
> structure or a different drive, it's often simpler to keep the file locations
> identical.
> Linchi
> "Charlie" wrote:
|||Nevermind.
Sorry!
Thanks for the help!
"Charlie" wrote:
[vbcol=seagreen]
> That is really what I need!
> This script does not print anything. A assume you want me to run it in
> query analyzer?
> "Linchi Shea" wrote:
|||It truncates after 32 db's. Is there any way to avoid this?
"Linchi Shea" wrote:
[vbcol=seagreen]
> The script reads the info from the sysaltfiles table on the current server.
> If you are moving the files to a new server, you may need to edit the script
> to point to the files in different locations.
> Unless you have some overriding reasons to use a different directory
> structure or a different drive, it's often simpler to keep the file locations
> identical.
> Linchi
> "Charlie" wrote:

Database Attach Help!

Due to space issues we had to detach one of our databases. It stopped
working because the drive ran out of space. We have moved the .mdf and the
..ldf to another drive with much more space. When I try to attach I get the
following?
Error 3624
74 transactions rolled forward in database
2006-09-22 12:07:49.53 spid53 SQL Server Assertion: File: <page.cpp>,
line=1920
Failed Assertion = 'IsValidSlot (sid)'.
2006-09-22 12:07:49.53 spid53 Error: 3624, Severity: 20, State: 1.
2006-09-22 12:07:49.53 spid53 Error: 3314, Severity: 21, State: 3
I believe that the transaction lof is corrupt. I there a way to clear the
transaction log and then re-attach?
Any other ideas allso welcome.
Thank you
Paul
I am surprised that you have X transactions rolled forward if you detached the database... You could
try sp_single_file_attach and see if that work with only the mdf file. If not, I suggest you open a
case with MS support.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul Glickenhaus" <PaulGlickenhaus@.discussions.microsoft.com> wrote in message
news:A957BF1E-7448-4363-8A23-6F8937C95CF9@.microsoft.com...
> Due to space issues we had to detach one of our databases. It stopped
> working because the drive ran out of space. We have moved the .mdf and the
> .ldf to another drive with much more space. When I try to attach I get the
> following?
> Error 3624
> 74 transactions rolled forward in database
> 2006-09-22 12:07:49.53 spid53 SQL Server Assertion: File: <page.cpp>,
> line=1920
> Failed Assertion = 'IsValidSlot (sid)'.
> 2006-09-22 12:07:49.53 spid53 Error: 3624, Severity: 20, State: 1.
> 2006-09-22 12:07:49.53 spid53 Error: 3314, Severity: 21, State: 3
>
> I believe that the transaction lof is corrupt. I there a way to clear the
> transaction log and then re-attach?
> Any other ideas allso welcome.
> Thank you
> Paul
>

Database Attach Help!

Due to space issues we had to detach one of our databases. It stopped
working because the drive ran out of space. We have moved the .mdf and the
.ldf to another drive with much more space. When I try to attach I get the
following?
Error 3624
74 transactions rolled forward in database
2006-09-22 12:07:49.53 spid53 SQL Server Assertion: File: <page.cpp>,
line=1920
Failed Assertion = 'IsValidSlot (sid)'.
2006-09-22 12:07:49.53 spid53 Error: 3624, Severity: 20, State: 1.
2006-09-22 12:07:49.53 spid53 Error: 3314, Severity: 21, State: 3
I believe that the transaction lof is corrupt. I there a way to clear the
transaction log and then re-attach?
Any other ideas allso welcome.
Thank you
PaulI am surprised that you have X transactions rolled forward if you detached t
he database... You could
try sp_single_file_attach and see if that work with only the mdf file. If no
t, I suggest you open a
case with MS support.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul Glickenhaus" <PaulGlickenhaus@.discussions.microsoft.com> wrote in mess
age
news:A957BF1E-7448-4363-8A23-6F8937C95CF9@.microsoft.com...
> Due to space issues we had to detach one of our databases. It stopped
> working because the drive ran out of space. We have moved the .mdf and th
e
> .ldf to another drive with much more space. When I try to attach I get th
e
> following?
> Error 3624
> 74 transactions rolled forward in database
> 2006-09-22 12:07:49.53 spid53 SQL Server Assertion: File: <page.cpp>,
> line=1920
> Failed Assertion = 'IsValidSlot (sid)'.
> 2006-09-22 12:07:49.53 spid53 Error: 3624, Severity: 20, State: 1.
> 2006-09-22 12:07:49.53 spid53 Error: 3314, Severity: 21, State: 3
>
> I believe that the transaction lof is corrupt. I there a way to clear the
> transaction log and then re-attach?
> Any other ideas allso welcome.
> Thank you
> Paul
>

Database Attach Help!

Due to space issues we had to detach one of our databases. It stopped
working because the drive ran out of space. We have moved the .mdf and the
.ldf to another drive with much more space. When I try to attach I get the
following?
Error 3624
74 transactions rolled forward in database
2006-09-22 12:07:49.53 spid53 SQL Server Assertion: File: <page.cpp>,
line=1920
Failed Assertion = 'IsValidSlot (sid)'.
2006-09-22 12:07:49.53 spid53 Error: 3624, Severity: 20, State: 1.
2006-09-22 12:07:49.53 spid53 Error: 3314, Severity: 21, State: 3
I believe that the transaction lof is corrupt. I there a way to clear the
transaction log and then re-attach?
Any other ideas allso welcome.
Thank you
PaulI am surprised that you have X transactions rolled forward if you detached the database... You could
try sp_single_file_attach and see if that work with only the mdf file. If not, I suggest you open a
case with MS support.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul Glickenhaus" <PaulGlickenhaus@.discussions.microsoft.com> wrote in message
news:A957BF1E-7448-4363-8A23-6F8937C95CF9@.microsoft.com...
> Due to space issues we had to detach one of our databases. It stopped
> working because the drive ran out of space. We have moved the .mdf and the
> .ldf to another drive with much more space. When I try to attach I get the
> following?
> Error 3624
> 74 transactions rolled forward in database
> 2006-09-22 12:07:49.53 spid53 SQL Server Assertion: File: <page.cpp>,
> line=1920
> Failed Assertion = 'IsValidSlot (sid)'.
> 2006-09-22 12:07:49.53 spid53 Error: 3624, Severity: 20, State: 1.
> 2006-09-22 12:07:49.53 spid53 Error: 3314, Severity: 21, State: 3
>
> I believe that the transaction lof is corrupt. I there a way to clear the
> transaction log and then re-attach?
> Any other ideas allso welcome.
> Thank you
> Paul
>

Database attach failed

Here's the scoop, I got a generic-purpose database (in the form of
*.mdb and *.ldf) from a 'reputable' source on the net. But when I
attempted to attach it to my sql server 2000 with SP3 with EM, it
failed complaining "Could not find row in sysindexes for database ID
10, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes."
Then, I attempted to command line attach (thought it may have some
option...), same outcome. Then, ran a search on this NG, and found
the following thread, the question is, MS most likely would not
support something like this, so now what? Also thought about
manually adding a row to sysindexes table to 'fool the attach process'
but after looking at some sample data in this table, I don't think
it's a good idea to try, what can I do? Thanks.

http://groups.google.com/group/comp...3bee910fa30aa9atime management (tatata9999@.gmail.com) writes:

Quote:

Originally Posted by

That message appears familiar. I seem to recall that is what happens
if you try to attach an SQL 2005 database on SQL 2000. As far as
SQL 2000 that is a database that is alien, and for which it cannot
really have any graceful handling off.

Quote:

Originally Posted by

Then, I attempted to command line attach (thought it may have some
option...), same outcome. Then, ran a search on this NG, and found
the following thread,

Which is from 1998, and applies to really old versions of SQL Server.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Nov 19, 5:32 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

time management (tatata9...@.gmail.com) writes:

Quote:

Originally Posted by

>
That message appears familiar. I seem to recall that is what happens
if you try to attach an SQL 2005 database on SQL 2000. As far as
SQL 2000 that is a database that is alien, and for which it cannot
really have any graceful handling off.
>

Quote:

Originally Posted by

Then, I attempted to command line attach (thought it may have some
option...), same outcome. Then, ran a search on this NG, and found
the following thread,

>
Which is from 1998, and applies to really old versions of SQL Server.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks for the follow-up, Erland. I tried to attach it with sql
server 2005 as well, the restored db seemed weired with "D:\aName
\blakdsm\blad..", empty, not workable. Now, suppose I can get a
clean/good copy for server 2005, what are the chances of success of
using DUMB database then BACKUP or LOAD back into server 2000? These
two boxes are not connected and they can't.

Don|||time management (tatata9999@.gmail.com) writes:

Quote:

Originally Posted by

If you need to move a database from SQL 2005 to SQL 2000 you need to
create from scripts and copy data to file with bulk copy. If the database
uses features that do not exist in SQL 2000, you will have to make some
compromises.

You cannot restore a backup from SQL 2005 on SQL 2000. For quite obvious
reasons: there are features in SQl 2005 for which SQL 2000 is not prepared.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Nov 20, 5:31 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

time management (tatata9...@.gmail.com) writes:

Quote:

Originally Posted by

>
If you need to move a database from SQL 2005 to SQL 2000 you need to
create from scripts and copy data to file with bulk copy. If the database
uses features that do not exist in SQL 2000, you will have to make some
compromises.
>
You cannot restore a backup from SQL 2005 on SQL 2000. For quite obvious
reasons: there are features in SQl 2005 for which SQL 2000 is not prepared.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks, Erland, bcp is clumsy or because I'm not an expert of it :)
Viable options for the task all seem to consume quite a bit of time
but hey got to do the job. Once again I appreciate it.

Don

Database Attach error encountered

Would appreciate any inputs.

I am running SQL2005. I am trying to attach to a database located on an external hard drive that is connected to my laptop via USB connection. The database files are a copy of the database files from a server running SQL2005.

After making the copy of the database, we confirmed that we could attach to the database on the external drive from the server and were successful attaching, opening and accessing the copy. Brought the HD home to work with the database and am getting the following error when I try to attach:

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

Program Location:

at Microsoft.SqlServer.Management.Smo.Enumerator.Process(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabaseData.PrimaryFile.GetOriginalDatabaseName(String primaryFilePath)
at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabaseData.PrimaryFile.PopulatePrimaryFileData(String primaryFilePath)
at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabaseData.PrimaryFile..ctor(SqlManagementUserControl parent, CDataContainer dc, String fullPath, String databaseOwner, ServerConnection connectionInfo)
at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabase.IsSelectedFileValid(BrowseFolder dlg)

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Program Location:

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataReader(String query)
at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
at Microsoft.SqlServer.Management.Smo.PrimaryFile.GetData(EnumResult erParent)
at Microsoft.SqlServer.Management.Smo.Environment.GetData()
at Microsoft.SqlServer.Management.Smo.Environment.GetData(Request req, Object ci)
at Microsoft.SqlServer.Management.Smo.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Smo.Enumerator.Process(Object connectionInfo, Request request)

===================================

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'E:\Navision SqlData\ForentaSQL_Data.mdf'. (.Net SqlClient Data Provider)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3159&EvtSrc=MSSQLServer&EvtID=5123&LinkId=20476

Server Name: twdlaptop1
Error Number: 5123
Severity: 16
State: 20
Line Number: 1

Program Location:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand)

It looks as though your problem is a permissions problem. When you're at home you're using a different login context than you are when you're at work. The external HD allows the work login access, but not the home login. I'd look at the security tab under the properties menu item for the directory where the database files are stored and see if you can add your home login to the list of allowed users.

Sunday, March 11, 2012

Database attach error 3624

Hi all,

I am trying to restore and SQL 2000 database into a new SQL 2005 database. I performed by SQL 2000 backup and created a blank database FERS_Production in SQL 2005. FERS_Production was the original name of the database in the SQL 2000 instance.

I have tried giving the new database the same name as the original and a different name to the original database

(Below is the scripted T-SQL that I get from the DB Admin tool

RESTORE DATABASE [Fers_Production]
FILE = N'FERS_Production_dat',
FILE = N'FERS_Production_log'
FROM DISK = N'D:\Microsoft SQL Server (2000)\MSSQL\Backup\Fers_Production\Fers_Production_db_200607270206.BAK'
WITH FILE = 1,
NOUNLOAD,
REPLACE,
STATS = 10
GO

When I run this I get the following error.

Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'Fers_Production' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Other searches I have performed trying to fix this problem have said to use the REPLACE clause with the RESTORE DATABASE command, but as you can see I am doing that.

Also I no longer have SQL 2000 installed so I cannot try to do a DTS copy which was another suggestion I came across.

Any help is much appriciated, many thanks

Derek

Hi all,

Since I was having problems with a SQL 2000 database to SQL 2005 restore (which I have posted seperately) I tried copying the data files to a new folder and just attaching to the SQL 2000 database file from the SQL 2005 managment studio but I get the following error (I am runing service pack 1 for SQL 2005)

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'DATABASESERVER'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

A system assertion check has failed. Check the SQL Server error log for details
Could not open new database 'Fers_Production'. CREATE DATABASE is aborted.
Location: IndexDataSet.cpp:12001
Expression: retCode == INSERT_SUCCESSFUL
SPID: 53
Process ID: 1092 (Microsoft SQL Server, Error: 3624)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=3624&LinkId=20476

Unfortunately the link that MS provide says there is no aditional info.

Thanks

Derek

|||

The restore process cannot restore the database from the backup file because there is already a database called Fers_Production present on your SQL 2005 server. Try deleting the Fers_Production database you created and then do the restore of the backup file.

|||

Thanks Andy. I restored the database to a name that did not already exist in the server and that seemed to do the trick as you suggested.

I had been used to being able to restore over an existing database but probably this could not work due to the backup being a SQL 2000 db and the new db is SQL 2005.

Thanks for your help.

Derek

|||I have merged these threads, as the error seems to be the same in both cases.|||

Derek,

Was your database attached with the .ldf and .mdf files in a specific location and then you detached the database, moved the files and tried to reattach the database? If this is the case, move the files back to the original location and reattach the database, then run this in the query window. Modify the part in red to where you want the new location of the files to be.

use fers_production
go
Alter database fers_production modify file (name = fers_production, filename = 'F:\Sqldata\fers_production.mdf')
go
Alter database fers_production modify file (name = fers_production_log, filename = 'F:\Sqllogs\fers_production.ldf')
go

Then restart SQL Server after you have done this.

|||

Thanks again Andy,

I have been caught up with other things hence the delay in my saying thanks.

I will keep that last suggestion in my notes as that my be useful at other times. I had manually moved the original files, I must remember not to do that in future.

Cheers

Derek

|||

Backup File = mydatabase.bak

1. Run Microsoft SQL Server Management Studio application.

2. If mydatabase is in Databases : delete mydatabase.

3. Right Click to Databases and select Restore Database ....

4. Destination for restore -> To database: mydatabase

Source for restore -> select From device -> Specify the backup media and select the backup sets to restore

Select Options from Select a page and in Restore the database file as: type the fullpath for the mydatabase new location

(for initdb_Data line with .mdf extension and for initdb_Log line with .ldf extension

ex.:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mydatabase.mdf

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mydatabase.ldf).

5. Press OK button.

Have fun!

|||

Hi,

I think I have a similar problem. Correct me if I am reading your answer wrong, Andy, but does it mean you cannot restore a database "on top" of existing db? (I must be wrong)
Here is the description of my problem,

I am trying to restore a SQL 2000 database to an existing SQL 2005 DB (and change the name of the db on the way). However when I attempt to do it I get following error

System.Data.SqlError: RESTORE cannot process database <<database name>> because it is in useby this session. It is recommended that the master databse be used when performing this operation.

I am not sure what it means that the datasbe is used by this session - is the the SQL management studio client opened? oh, btw. I've tried foing the same when i had mster database opened in the studio and got to a restore dialog from there, but no luck.
Any comments?

Regards,

Jacek

|||

Try also removing the NOUNLOAD option - you should then be able to restore over an existing database (still need REPLACE as well)

I used the following code to successfully restore a SDQL 200 backup file to a databasde with the same name in SQL 2005 that already existed.

RESTORE DATABASE [ELF2] FROM DISK = N'Z:\ELF2' WITH FILE = 1, REPLACE,
STATS = 10
GO

|||

Barry many thanks for this!

I was converting from msde 2000, I upgraded the server to 2005 express, and believed that this was enough to convert it, indeed some things will not work if you do this upgrade, then backup and then try to restore, which added fuel to my believe that upgrading the server also does the database. But apparently not completely. So after 24hrs of messing thanks for this tip.

I am creating live deployment script that due to Vistas security has now been moved from batch files called post-MSI (which now make the MSI fail in vista) So I call them now from inside the application itself on first boot-up. Here is the script: if you want to get an example .bak download the trial from http://www.SalonSoftwareSystem.com and see the c:\install directory for the .bak. I'm glad Vista is protecting the layman but its been a good 2 months of effort to get our install vista happy.

I think the real trick is to accept the system default .MDF .LDF paths although as developers we feel it is messy and unpredictable it is safer and Vista compatible.

--live copy
use tempdb

create database Platinum
go

alter database Platinum set single_user with rollback immediate
go

alter database Platinum set multi_user with rollback immediate
go

--if it has a name it will restore over the system decided path
RESTORE DATABASE [Platinum] FROM DISK = N'C:\install\Platinum.bak' WITH FILE = 1, REPLACE,
STATS = 10
GO

ALTER database Platinum set recovery SIMPLE
GO

--Training Copy exactly the same copy
use tempdb

create database PlatinumTraining
go

alter database PlatinumTraining set single_user with rollback immediate
go

alter database PlatinumTraining set multi_user with rollback immediate
go

RESTORE DATABASE [PlatinumTraining] FROM DISK = N'C:\install\Platinum.bak' WITH FILE = 1, REPLACE,
STATS = 10
GO

ALTER database PlatinumTraining set recovery SIMPLE
GO

|||

"The backup set holds a backup of a database other than the existing 'Fers_Production' database."

Make sure you go to the options of the restore database screen in 2005 - make sure you have "overwrite existing database" selected.

Database attach error 3624

Hi all,

I have tried giving the new database the same name as the original and a different name to the original database

(Below is the scripted T-SQL that I get from the DB Admin tool

When I run this I get the following error.

Other searches I have performed trying to fix this problem have said to use the REPLACE clause with the RESTORE DATABASE command, but as you can see I am doing that.

Also I no longer have SQL 2000 installed so I cannot try to do a DTS copy which was another suggestion I came across.

Any help is much appriciated, many thanks

Derek

Hi all,

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'DATABASESERVER'. (Microsoft.SqlServer.Smo)

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=3624&LinkId=20476

Unfortunately the link that MS provide says there is no aditional info.

Thanks

Derek

|||

Thanks Andy. I restored the database to a name that did not already exist in the server and that seemed to do the trick as you suggested.

I had been used to being able to restore over an existing database but probably this could not work due to the backup being a SQL 2000 db and the new db is SQL 2005.

Thanks for your help.

Derek

|||I have merged these threads, as the error seems to be the same in both cases.|||

Derek,

Then restart SQL Server after you have done this.

|||

Thanks again Andy,

I have been caught up with other things hence the delay in my saying thanks.

I will keep that last suggestion in my notes as that my be useful at other times. I had manually moved the original files, I must remember not to do that in future.

Cheers

Derek

|||

Backup File = mydatabase.bak

1. Run Microsoft SQL Server Management Studio application.

2. If mydatabase is in Databases : delete mydatabase.

3. Right Click to Databases and select Restore Database ....

4. Destination for restore -> To database: mydatabase

Source for restore -> select From device -> Specify the backup media and select the backup sets to restore

Select Options from Select a page and in Restore the database file as: type the fullpath for the mydatabase new location

(for initdb_Data line with .mdf extension and for initdb_Log line with .ldf extension

ex.:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mydatabase.mdf

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mydatabase.ldf).

5. Press OK button.

Have fun!

|||

Hi,

I am trying to restore a SQL 2000 database to an existing SQL 2005 DB (and change the name of the db on the way). However when I attempt to do it I get following error

System.Data.SqlError: RESTORE cannot process database <<database name>> because it is in useby this session. It is recommended that the master databse be used when performing this operation.

Regards,

Jacek

|||

Try also removing the NOUNLOAD option - you should then be able to restore over an existing database (still need REPLACE as well)

I used the following code to successfully restore a SDQL 200 backup file to a databasde with the same name in SQL 2005 that already existed.

RESTORE DATABASE [ELF2] FROM DISK = N'Z:\ELF2' WITH FILE = 1, REPLACE,
STATS = 10
GO

|||

Barry many thanks for this!

I think the real trick is to accept the system default .MDF .LDF paths although as developers we feel it is messy and unpredictable it is safer and Vista compatible.

--live copy
use tempdb

create database Platinum
go

alter database Platinum set single_user with rollback immediate
go

alter database Platinum set multi_user with rollback immediate
go

--if it has a name it will restore over the system decided path
RESTORE DATABASE [Platinum] FROM DISK = N'C:\install\Platinum.bak' WITH FILE = 1, REPLACE,
STATS = 10
GO

ALTER database Platinum set recovery SIMPLE
GO

--Training Copy exactly the same copy
use tempdb

create database PlatinumTraining
go

alter database PlatinumTraining set single_user with rollback immediate
go

alter database PlatinumTraining set multi_user with rollback immediate
go

RESTORE DATABASE [PlatinumTraining] FROM DISK = N'C:\install\Platinum.bak' WITH FILE = 1, REPLACE,
STATS = 10
GO

ALTER database PlatinumTraining set recovery SIMPLE
GO

|||

"The backup set holds a backup of a database other than the existing 'Fers_Production' database."

Make sure you go to the options of the restore database screen in 2005 - make sure you have "overwrite existing database" selected.

database attach error

fellows,
I have some problems!!!!!! I was given a database that is in rough shape. here are the particulars. SQL 2000 RTM. The SQL Services wouldn't start the database when the engine came up. Here is where it gets shaky. They say that they dettached the database
when it was in "emergency" mode now they have nothing. No service packs, no maintenance, and a datbase that won't start. When I try to attach i get this nasty error.
Server: Msg 3624, Level 20, State 1, Line 1
Location: recbase.cpp:1378
Expression: m_offBeginVar < m_SizeRec
SPID: 55
Process ID: 2068
Connection Broken
I have tried sp_attach_single_file_db but do not have the same directory structure so it continues to error out.
Does any one have any ideas on how to attach just so I can read or script? Then maybe I can export / import ?
I think you're going to have to call MS support... When you get a debug
error you know something really bad has happened.
"detroit" <sbowman@.broward.org> wrote in message
news:72CB734D-6E7E-482C-A09B-F1BEE2AAE948@.microsoft.com...
> fellows,
> I have some problems!!!!!! I was given a database that is in rough shape.
here are the particulars. SQL 2000 RTM. The SQL Services wouldn't start the
database when the engine came up. Here is where it gets shaky. They say that
they dettached the database when it was in "emergency" mode now they have
nothing. No service packs, no maintenance, and a datbase that won't start.
When I try to attach i get this nasty error.
> Server: Msg 3624, Level 20, State 1, Line 1
> Location: recbase.cpp:1378
> Expression: m_offBeginVar < m_SizeRec
> SPID: 55
> Process ID: 2068
> Connection Broken
> I have tried sp_attach_single_file_db but do not have the same directory
structure so it continues to error out.
>
> Does any one have any ideas on how to attach just so I can read or script?
Then maybe I can export / import ?
>
|||Create a new database with the same name and file names in a different
directory than the current MDF and LDF files. After you create the new
database, stop SQL Server and copy the old database files over the top of
the new ones. SQL Server will recognize the database. It may set the
suspect status but then you can put it in emergency mode and try to extract
whatever data out of it. Or run DBCC Checkdb and see if it can be repaired.
Rand
This posting is provided "as is" with no warranties and confers no rights.

database attach error

fellows,
I have some problems!!!!!! I was given a database that is in rough shape. he
re are the particulars. SQL 2000 RTM. The SQL Services wouldn't start the da
tabase when the engine came up. Here is where it gets shaky. They say that t
hey dettached the database
when it was in "emergency" mode now they have nothing. No service packs, no
maintenance, and a datbase that won't start. When I try to attach i get this
nasty error.
Server: Msg 3624, Level 20, State 1, Line 1
Location: recbase.cpp:1378
Expression: m_offBeginVar < m_SizeRec
SPID: 55
Process ID: 2068
Connection Broken
I have tried sp_attach_single_file_db but do not have the same directory str
ucture so it continues to error out.
Does any one have any ideas on how to attach just so I can read or script? T
hen maybe I can export / import ?I think you're going to have to call MS support... When you get a debug
error you know something really bad has happened.
"detroit" <sbowman@.broward.org> wrote in message
news:72CB734D-6E7E-482C-A09B-F1BEE2AAE948@.microsoft.com...
> fellows,
> I have some problems!!!!!! I was given a database that is in rough shape.
here are the particulars. SQL 2000 RTM. The SQL Services wouldn't start the
database when the engine came up. Here is where it gets shaky. They say that
they dettached the database when it was in "emergency" mode now they have
nothing. No service packs, no maintenance, and a datbase that won't start.
When I try to attach i get this nasty error.
> Server: Msg 3624, Level 20, State 1, Line 1
> Location: recbase.cpp:1378
> Expression: m_offBeginVar < m_SizeRec
> SPID: 55
> Process ID: 2068
> Connection Broken
> I have tried sp_attach_single_file_db but do not have the same directory
structure so it continues to error out.
>
> Does any one have any ideas on how to attach just so I can read or script?
Then maybe I can export / import ?
>|||Create a new database with the same name and file names in a different
directory than the current MDF and LDF files. After you create the new
database, stop SQL Server and copy the old database files over the top of
the new ones. SQL Server will recognize the database. It may set the
suspect status but then you can put it in emergency mode and try to extract
whatever data out of it. Or run DBCC Checkdb and see if it can be repaired.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Database attach corrupt ndf file

I have a database with 8 files. 1 mdf, 1 ldf and 6 ndf files. One of the
ndf file has a torn page. It's a file the contains only indexes. I want to
attach to this db, excluding the bad ndf file. Any way to do that?
using sp_attach_db give me an error that there's an I/O torn page problem
and gives me the ndf file name.
Any way around this?I neglected to say that I really only needed read access to this db.
I managed to find a solution.
I renamed the 8 files (prefixing them with an A_)
I created a new DB with the name I wanted with 8 files named exactly like
the originals that I renamed.
Stopped Sql Server.
Deleted the 8 new DB files.
Renamed the original 8 files back to their original names
re-started sql server.
The DB came up suspect, as I would expect it to. I set the
sysdatabases.status = 32768 for the suspect DB, which put the DB in
emergency mode. I now have read access to the DB.
"Steve" <ss@.Mailinator.com> wrote in message
news:uhqM6ACrGHA.2464@.TK2MSFTNGP03.phx.gbl...
>I have a database with 8 files. 1 mdf, 1 ldf and 6 ndf files. One of the
>ndf file has a torn page. It's a file the contains only indexes. I want to
>attach to this db, excluding the bad ndf file. Any way to do that?
> using sp_attach_db give me an error that there's an I/O torn page problem
> and gives me the ndf file name.
> Any way around this?
>

Thursday, March 29, 2012

Database Compatability

Database Compatability

Database Compatability

Monday, March 19, 2012

database attachement

database attachement

database attachement

Database Attache Problems

Database attach using .mdf and .ldf files

Database attach using .mdf and .ldf files

Database Attach Scripts

Database Attach Help!

Database Attach Help!

Database Attach Help!

Database attach failed

Database Attach error encountered

Sunday, March 11, 2012

Database attach error 3624

Database attach error 3624

database attach error

database attach error

Database attach corrupt ndf file

Database corrupted

Blog Archive

About Me