on my developper pc is installed SQL 2008 R2
on the internal server is installed SQL 2008 R2 (some applications are hosted here)
on the external 3.rd party hosting company uses SQL 2008 (no r2).
so when I created DB on my developper pc I need to restore on external hosting server which is SQL 2008 (w/o r2).... but I get an build version error... it says SQL2008 r2 has higher build number so I can't restore on the w/o r2 server.
I thought creating the DB on w/o R2 server and restore on my localhost and make changes and restore back to w/o r2 server, but it says the same error... Build number is changed.
I thought I can delete r2 version on my developper pc and use SQL 2008 w/o r2, but the issue remains I can't restore the DB from internal server to my developper pc...:(
so in short:
If I want to restore/backup the DB between SQL2008 R2 and SQL2008, how to handle this (I als need the data of database ? Any property to assign to the DB or whatever?
Is there a procedure that I can use SQL2008 R2 DB as SQL2008 DB?
I can't touch the SQL 2008 versions on the server...
please advice?
There is no simple property that you can switch to be able to restore to older versions.
You might try this, in Management Studio:
- Select your source Database
- Select Tasks>Generate Scripts.
- Select 'Script entire database and all database objects', press 'Next'
- Select 'Save to File' and click on the 'Advanced' button
- Select 'Script for Server Version' and select the version you want: 2000/2005/2008
- Select 'Type of data to Script' and select Schema/Data/both
- Click 'OK',Next and do it!
- Copy the resulting file to the target machine.
- Log onto your SQL Management Studio and open the copied .sql file...
It's likely to work if you have a small database. For a larger one you will run into limitations.
You have to export/import using the wizard in SSMS. Or use some 3rd party tool (eg Red Gate)
There is NO downgrade option using attach/detach or backup/restore. And no magic setting.
I'm afraid its been that way since SQL 6.5 / 7. In general there is no downgrade path except the following:
Script out the stucture and other objects as explain in another post here then create a new database on a instance with the lower build / version number (R2 is SQL 10.5, 2088 is 10.0)
You can then use SSIS or once again get SSMS (Managemenet studio) to script insert statements for the data (you'll need to do in order of forign key dependancies and remember to SET INDENTITYINSERT [table] ON and then off again at the end of each table insert if you have an identity columns.
You can use INSERT INTO 2008Inst.2008dbName.dbo.Table (col1, ...) SELECT (col1, ...) FROM 2008R2Inst.2008dbName.dbo.Table to get the actual data accross
