attaching and detaching databases between 2005 and 2008 and back again 1

[TomKane]

Hi,

I have a question about attaching and detaching dbs between 2005 and 2008 and would appreciate any feedback that you might have.

We have a situation where our OLTP application is in SQL 2005. Our backups are performed using a thing called snap manager - the benefit of this is that our backups are stored as point in time snapshots of our database that can be mounted if required.

To take the pressure off our OLTP system we want to have a point in time copy of our db available on our data warehouse server (sql 2008) so that reporting and the like can be performed from there.

My question is: is it possible to attach a sql 2005 snapshot to a sql 2008 instance AND then be able to detach it and reattach it to sql 2005?

My feeling is that you can but I'm not keen to start "breaking" our snapshots in case I'm wrong - I'd be really interested to hear your opinions.

Regards and thanks,
Tom

 

[gmmastros]

is it possible to attach a sql 2005 snapshot to a sql 2008 instance AND then be able to detach it and reattach it to sql 2005?

No.

When you attach a 2005 DB to a 2008 instance, it is automatically upgraded to a 2008 format. You cannot attach it to a 2005 instance afterwards.

If you want to test this....

In your 2008 instance....

Create Database TestCompatibility
go
Create Table ThisIsATest(Data Int)
Insert Into ThisIsATest Values(13)

Now, detach this simple database from 2008 and attempt to attach it to 2005. I guarantee it will fail. As far as I know, the only way to downgrade a database (from 2008 to 2005) is to complete script it and run the script on a new database in a 2005 instance.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TomKane]

Thanks for that! Much appreciated.