Misterious Change of Compatibility Level in a Data Base

Hi everybody:

We have recently migrated our DDBB from SQL 2000 to SQL 2005 in several Servers. We have 2 DDBB per Server and the size of mdf files are between 10 and 40 GB.

We put Compatibility Level in 90 in SQL 2005 but when we arrive at work we see that our Maintenace Plans failed because the Compatibility Level of one of Data Bases changed to 70.

We have a Trace executing the whole day registrying the execution of stored procedure 'sp_dbcmptlevel' but in despite of Compatibility Level changes, the Trace does not registry anything.

Has anyone passed before me for this situation? Thank you in advance and greetings,

Nuria

After migrating (backup/restore or detach /attahc) to SQL Server 2005 have u changed the Comaptibility level? By default, the compatibility level will be 80. What was the compatibility leve of this database in sql 2000? check that. I assume that it was 70

Madhu

|||

your sql 2000 db probably would have had a compatability of either 80 or 70 (65,60 are not supported in sql 2005).......so once you restore it in sql 2005 it would be in the default compatability level as it was in sql 2000 unless you manually change it.......

|||

Yes, after migrating (attach in our case) to SQL 2005 we have changed the Compatibility Level to 90.

Not sure what was the Compatibility Level before migrating of this specific Server because we have 52 Servers in total; some of them had 65, others had 70 and other had 80.

The amazing thing is that Compatibility Level changes alone from 90 to 70.

|||

Yes, after migrating (attach in our case) to SQL 2005 we have changed the Compatibility Level to 90.

The amazing thing is that Compatibility Level changes alone from 90 to 70.

Missing MDF files but databases run fine?

About two months ago, we had a problem with a couple SQL 2005 databases. We
got everything working and all has been fine. Today, I noticed that the
databases that we had a problem with have LDF file but no MDF files. I don't
know much about SQL but does that make sense? Is there any way to make the
MDF file at this point?
Thanks!
Arch WillinghamWhat do you mean by 'no MDF files'? You can have a scenario where the drive
that hosts a database file disappears and the database may still function as
usual for a while until it has to access the disk.
Linchi
"Arch Willingham" wrote:
> About two months ago, we had a problem with a couple SQL 2005 databases. We
> got everything working and all has been fine. Today, I noticed that the
> databases that we had a problem with have LDF file but no MDF files. I don't
> know much about SQL but does that make sense? Is there any way to make the
> MDF file at this point?
> Thanks!
> Arch Willingham
>
>|||Never mind....weird thing. I am a holdout thatstill uses the old file
manager (WINFILE.EXE). For whatever reason, teh MDF files are hidden to it
but they are there when you look at the directory with Explorer.
Weird as I can see all other hidden and system files.
Arch
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:CEE64FB0-73F3-483E-A4DF-3EEB750908DC@.microsoft.com...
> What do you mean by 'no MDF files'? You can have a scenario where the
> drive
> that hosts a database file disappears and the database may still function
> as
> usual for a while until it has to access the disk.
> Linchi
> "Arch Willingham" wrote:
>> About two months ago, we had a problem with a couple SQL 2005 databases.
>> We
>> got everything working and all has been fine. Today, I noticed that the
>> databases that we had a problem with have LDF file but no MDF files. I
>> don't
>> know much about SQL but does that make sense? Is there any way to make
>> the
>> MDF file at this point?
>> Thanks!
>> Arch Willingham
>>

missing ldf file

Hello
I have been given an sql database but its just the .mdf file
the .ldf file is not recoverable
Im unable to attach this as the server needs the ldf file which is thinks is
mapped to a t: drive but this doesnt exist
is there anyway i can get this attached to an sql server as i dont have the
ldf file
thank you for any comments
Martin"Martin Palmer" <MartinPalmer@.discussions.microsoft.com> wrote in message
news:D252C85C-B7C0-4C8D-BBA5-4E0892AE2480@.microsoft.com...
> Hello
> I have been given an sql database but its just the .mdf file
> the .ldf file is not recoverable
> Im unable to attach this as the server needs the ldf file which is thinks
> is
> mapped to a t: drive but this doesnt exist
> is there anyway i can get this attached to an sql server as i dont have
> the
> ldf file
I assume you have tried sp_attach_single_file_db?
If that doesn't work, the answer is:
"Maybe".
MS Customer Service has a procedure they can walk you through that MIGHT
work.
As I am not MS, I won't provide it here. (Though if you google sometimes
you can provide it.)
> thank you for any comments
> Martin
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||hello i forgot to add this is sql server 2000
any help would be mostly appriciated
regards
"Martin Palmer" wrote:
> Hello
> I have been given an sql database but its just the .mdf file
> the .ldf file is not recoverable
> Im unable to attach this as the server needs the ldf file which is thinks is
> mapped to a t: drive but this doesnt exist
> is there anyway i can get this attached to an sql server as i dont have the
> ldf file
> thank you for any comments
> Martin|||Attach database may not work in below situations:-
1. If the database is not detached using sp_detach_db or using enterprise
manager.
2. sp_attach_single_file_db will fail incase if you have multiple LDF files
associated.
Since the database attach failed you could try below steps:-
---
1. Create a new database with same MDF and LDF name
2. stop sql server and delete the new MDF file and copy the old MDF file
3. Start SQL Server and see the status. If the database is suspect do the
below steps:-
Setting the database status to emergency mode tells SQL Server to skip
automatic recovery and lets you access the data. To get your data, use this
script:
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
Create a new empty database and you might be able to use bulk copy program
(bcp), simple SELECT commands, or use DTS to extract your data /objects from
problematic database to new database.
Note:
In both the above approch the data integrity will be lost. So it is
recommended to contact Microsoft PSS or restore from a good backup file.
Thanks
Hari
"Martin Palmer" <MartinPalmer@.discussions.microsoft.com> wrote in message
news:D252C85C-B7C0-4C8D-BBA5-4E0892AE2480@.microsoft.com...
> Hello
> I have been given an sql database but its just the .mdf file
> the .ldf file is not recoverable
> Im unable to attach this as the server needs the ldf file which is thinks
> is
> mapped to a t: drive but this doesnt exist
> is there anyway i can get this attached to an sql server as i dont have
> the
> ldf file
> thank you for any comments
> Martin

missing ldf file

Hello
I have been given an sql database but its just the .mdf file
the .ldf file is not recoverable
Im unable to attach this as the server needs the ldf file which is thinks is
mapped to a t: drive but this doesnt exist
is there anyway i can get this attached to an sql server as i dont have the
ldf file
thank you for any comments
Martin
"Martin Palmer" <MartinPalmer@.discussions.microsoft.com> wrote in message
news:D252C85C-B7C0-4C8D-BBA5-4E0892AE2480@.microsoft.com...
> Hello
> I have been given an sql database but its just the .mdf file
> the .ldf file is not recoverable
> Im unable to attach this as the server needs the ldf file which is thinks
> is
> mapped to a t: drive but this doesnt exist
> is there anyway i can get this attached to an sql server as i dont have
> the
> ldf file
I assume you have tried sp_attach_single_file_db?
If that doesn't work, the answer is:
"Maybe".
MS Customer Service has a procedure they can walk you through that MIGHT
work.
As I am not MS, I won't provide it here. (Though if you google sometimes
you can provide it.)
> thank you for any comments
> Martin
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||hello i forgot to add this is sql server 2000
any help would be mostly appriciated
regards
"Martin Palmer" wrote:

> Hello
> I have been given an sql database but its just the .mdf file
> the .ldf file is not recoverable
> Im unable to attach this as the server needs the ldf file which is thinks is
> mapped to a t: drive but this doesnt exist
> is there anyway i can get this attached to an sql server as i dont have the
> ldf file
> thank you for any comments
> Martin
|||Attach database may not work in below situations:-
1. If the database is not detached using sp_detach_db or using enterprise
manager.
2. sp_attach_single_file_db will fail incase if you have multiple LDF files
associated.
Since the database attach failed you could try below steps:-
1. Create a new database with same MDF and LDF name
2. stop sql server and delete the new MDF file and copy the old MDF file
3. Start SQL Server and see the status. If the database is suspect do the
below steps:-
Setting the database status to emergency mode tells SQL Server to skip
automatic recovery and lets you access the data. To get your data, use this
script:
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
Create a new empty database and you might be able to use bulk copy program
(bcp), simple SELECT commands, or use DTS to extract your data /objects from
problematic database to new database.
Note:
In both the above approch the data integrity will be lost. So it is
recommended to contact Microsoft PSS or restore from a good backup file.
Thanks
Hari
"Martin Palmer" <MartinPalmer@.discussions.microsoft.com> wrote in message
news:D252C85C-B7C0-4C8D-BBA5-4E0892AE2480@.microsoft.com...
> Hello
> I have been given an sql database but its just the .mdf file
> the .ldf file is not recoverable
> Im unable to attach this as the server needs the ldf file which is thinks
> is
> mapped to a t: drive but this doesnt exist
> is there anyway i can get this attached to an sql server as i dont have
> the
> ldf file
> thank you for any comments
> Martin

missing ldf file

Hello
I have been given an sql database but its just the .mdf file
the .ldf file is not recoverable
Im unable to attach this as the server needs the ldf file which is thinks is
mapped to a t: drive but this doesnt exist
is there anyway i can get this attached to an sql server as i dont have the
ldf file
thank you for any comments
Martin"Martin Palmer" <MartinPalmer@.discussions.microsoft.com> wrote in message
news:D252C85C-B7C0-4C8D-BBA5-4E0892AE2480@.microsoft.com...
> Hello
> I have been given an sql database but its just the .mdf file
> the .ldf file is not recoverable
> Im unable to attach this as the server needs the ldf file which is thinks
> is
> mapped to a t: drive but this doesnt exist
> is there anyway i can get this attached to an sql server as i dont have
> the
> ldf file
I assume you have tried sp_attach_single_file_db?
If that doesn't work, the answer is:
"Maybe".
MS Customer Service has a procedure they can walk you through that MIGHT
work.
As I am not MS, I won't provide it here. (Though if you google sometimes
you can provide it.)
> thank you for any comments
> Martin
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||hello i forgot to add this is sql server 2000
any help would be mostly appriciated
regards
"Martin Palmer" wrote:

> Hello
> I have been given an sql database but its just the .mdf file
> the .ldf file is not recoverable
> Im unable to attach this as the server needs the ldf file which is thinks
is
> mapped to a t: drive but this doesnt exist
> is there anyway i can get this attached to an sql server as i dont have th
e
> ldf file
> thank you for any comments
> Martin|||Attach database may not work in below situations:-
1. If the database is not detached using sp_detach_db or using enterprise
manager.
2. sp_attach_single_file_db will fail incase if you have multiple LDF files
associated.
Since the database attach failed you could try below steps:-
---
1. Create a new database with same MDF and LDF name
2. stop sql server and delete the new MDF file and copy the old MDF file
3. Start SQL Server and see the status. If the database is suspect do the
below steps:-
Setting the database status to emergency mode tells SQL Server to skip
automatic recovery and lets you access the data. To get your data, use this
script:
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
Create a new empty database and you might be able to use bulk copy program
(bcp), simple SELECT commands, or use DTS to extract your data /objects from
problematic database to new database.
Note:
In both the above approch the data integrity will be lost. So it is
recommended to contact Microsoft PSS or restore from a good backup file.
Thanks
Hari
"Martin Palmer" <MartinPalmer@.discussions.microsoft.com> wrote in message
news:D252C85C-B7C0-4C8D-BBA5-4E0892AE2480@.microsoft.com...
> Hello
> I have been given an sql database but its just the .mdf file
> the .ldf file is not recoverable
> Im unable to attach this as the server needs the ldf file which is thinks
> is
> mapped to a t: drive but this doesnt exist
> is there anyway i can get this attached to an sql server as i dont have
> the
> ldf file
> thank you for any comments
> Martin