I have a 8Gb database which i attached to Sql 2008 and i have shrinked it.

Hi,

I have a 8Gb database which i attached to Sql 2008 and i have shrinked it.
And now i backed up and when try to restore in 2005 it does not work.

When i try to add the 8gb database to 2005 to shrink it does not allow and says above limit.

My goal is the database which is 8gb has to be restored to Sql 2005. Whats are the ways.

regards
raja
LVL 11
bsharathAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
MSSQL 2005 does not have a restricted DB size (below some zillion bytes maybe). Are you certain you have a full-featured edition (not Express)?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Olaf DoschkeSoftware DeveloperCommented:
There is no 8gb limit in sql2005, so what is really the problem of attaching an 8gb database to sql2005 in the first place? Is the 2005 server an express server? then the limit is indeed 4gb.

Bye, Olaf.
bsharathAuthor Commented:
I have sql management studio 9.00.1399.00
is this it?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

bsharathAuthor Commented:
I get this

TITLE: Microsoft SQL Server Management Studio
------------------------------

Restore failed for Server 'DEV8\SQLEXPRESS'.  (Microsoft.SqlServer.Smo)

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

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 4096 MB per database. (Microsoft.SqlServer.Smo)

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

------------------------------
BUTTONS:

OK
------------------------------
Ryan McCauleyEnterprise Analytics ManagerCommented:
As others have suggested, the 2005 server you're using is most likely SQL Express, which limits databases to 4GB.

Also, you can't restore a database to SQL 2005 once it's been mounted to SQL 2008 - there are differences in the database structure that are not compatible.
bsharathAuthor Commented:
Ok now with 2005 and express 2005 how can i get it loaded into 2005
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Yep, all correct. Neither backup nor attach method will help because of the downgrade incompatibility issue. The only way to do that properly is by using the Copy Database wizard, but I don't think it is available with the Express Edition. Though it might be available on the 2008 side.
bsharathAuthor Commented:
Where can i download a version where i can add the 8 Gb database .
2005 version
and can you please elain on copy database
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Only the Express Edition is free. I could not find a 180-days offer (has been available earlier).

The Copy Database Wizard is available in your Mangement Studio, if you right-click on a particular database, choose "Tasks", then "Copy Database". Anything else should be straight-forward then.
bsharathAuthor Commented:
Copy database i get this

TITLE: Copy Database Wizard
------------------------------

The destination server cannot be a SQL Server 2005 or later Express instance.

------------------------------
BUTTONS:

OK
------------------------------
Olaf DoschkeSoftware DeveloperCommented:
>9.00.1399.00
The version of management studio is irrelevant, it's the SQL Server which handles the database files.

You indirectly gave the answer already:
>Restore failed for Server 'DEV8\SQLEXPRESS'

This means you're useing sql2005 express and cannot mount or restore 8gb. Even if sql2008 is able to reduce the logs and data to less than 4gb that shrinked database is incompatible with sql2005.

So why not stay in sql2008 once you're there with the database?

You are also able to use the shrinked version of the database with a sql2008 express installation, if the shrinked version is less than 4gb.

Bye, Olaf.
Anthony PerkinsCommented:
>>You are also able to use the shrinked version of the database with a sql2008 express installation, if the shrinked version is less than 4gb.<<
SQL Server 2008 Express supports databases up to 10GB

Olaf DoschkeSoftware DeveloperCommented:
Raja obviously has to cope with sql2005 express and a sql2008 server (the latter perhaps a standard or even bigger "normal" sql server). So why add another sql server 2008 r2 express edition?

If the database is that big already it's only a question of time the transaction log will rise again, so why not move to a full sql server version and use the available sql2008?

Or is that also only an express edition?

Bye, Olaf.
bsharathAuthor Commented:
Ok now to fix this issue i need to have Sql 2008 server right?
Can we install this in Windows XP?
Where Sql 2005 express is installed

Also does Sql express come in client and server
Olaf DoschkeSoftware DeveloperCommented:
SQL Server always is a server, read the system requirements, yes, you can install sql server at client OS systems, not only the express versions.

But why do you need to install sql2008, you said you used it already. You then could also install sql2008 r2 express (r2 is important) to XP and move the database there. As acperkins correctly reminds me, this has a 10GB limit.

Bye, Olaf.
Ramesh Babu VavillaCommented:
no database once restored on sql server 2008 cannot to restored to 2005 .
2005 can backup set can be restored on 2008 but
2008 backup cann't be restored on 2005
you can use generate script or copy wizard to copy the database from sql server 2008 to sql server 2005
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.