Backup and Restore from SQL Server 2005 to 2008 express edition?

Question

Hello there,

I'm having a huge problem with a client.

Their infrastructure blows, and most of the development done end up with mysterious errors(which only happen in their environment).

What I'm trying to do is create a separate environment (a VPS) and push the application server and the database into this new environment, so they can actually see the problem lies within their infrastructure.

I've tried recreating the database manually, but its impossible, too much constraints, indexes etc...

How do I restore a database backup(SQL Serve 2005) into a SQL Server 2008 Express edition (my test environment)?

I used this command to generate the backup...

BACKUP DATABASE [databasename] TO  
    DISK = 'c:\database_backup_20091228_1500.bak' 
    WITH NOFORMAT, NOINIT,  NAME = N'Database-Full Backup', 
    SKIP, NOREWIND, NOUNLOAD,  STATS = 10

Which seems to be working (just ran it, server is generating the file)..

Now into my Sql Server 2008 express edition, how do I restore it?

Is it possible?

Any alternatives?

Thx!

Answer 1

try this code

 RESTORE DATABASE [databasename]
       FROM DISK = 'c:\database_backup_20091228_1500.bak'
       WITH NOFORMAT, NOINIT;

Answer 2

You can restore a SQL Server 2005 database to SQL Server 2008 Express edition provided your database is no greater than 4GB in size.

Answer 3

The RESTORE operation iscapable of upgrading the database on the fly from the 2005 format to 2008 one. When you create a new database by means of restore you usually need to options added:

• REPLACE is needed to disable a normal check that occurs during RESTORE and which prevents you from replacing a database with the content of another database from a backup set. For details see Using the REPLACE Option.
• MOVE is needed to move logical files in the backup set to new locations. The RESTORE operation will attempt to create the same locations for the MDF, LDF and all NDF files in the database, and this may not work on your particular drive structure. For details see Copying Databases with Backup and Restore.

If your client is using any feature that does not work in Express then you're going to have to upgrade to an edition that supports the features you need. The best option by far is to buy a Developer edition license, that costs around $50 only and gives you all. fully functional. Enterprise features at the restriction that it can only be used for development purposes (which you are).