0

I am trying to copy a database from sql server 2016 server to another server in sql server managment studio 2005. (server1.database1) to (server2.database1) I tried detach and attached method and it did not work. I also tried to copy the data base and it did not work either! I need all of tables and views and pr! finally I could copy all of tables and pr's but problem is I can not have the views as a view and they just saved as a table. to solve this issue I thought if I copy the query from server1.views and execute that query in server2.database1.views I will have the view since I have all objects copied in server2.database1. but I get some errors: one was "format" function that they used in sql server 2016 which is not identified in 2005. I changed all of that "format" functions and executed the query and now I left with this errors: Msg 15135, Level 16, State 8, Procedure sp_addextendedproperty, Line 37 [Batch Start Line 35] Object is invalid. Extended properties are not permitted on 'dbo.v_RASAM', or the object does not exist.

So view that I want to copy calls : dbo.v_RASAM And I am sure (because I checked the procedure name sp_addextendedproperty) sp_addextendedproperty exist! So question that I have is possible that procedure follows some statements that needs to be change since "view" had functions that are not available in sql server management studio 2005?(this maybe sounds stupid but I think of any possible reason that may cause this issue) how can I have those views copy to server2.database1 any other way that I have not tried?

any help or thought will be much appreciate! Thank you so much for your time!

Pedram Salamati
  • 137
  • 10
  • Can you access the two instances on the same network? If so then you might want to think about using a linked aerver to push the data across. – Mark Kram Sep 19 '16 at 01:37
  • 1
    there's a chance you can restore a backup from the 2016 server to the 2016 server, then change the compatibility level from 2016 down to 2005 on the restored backup, then backup that version, which would allow you to restore it to a 2005 instance. Admittedly haven't tried it, but could be worth a whirl. – CodeMonkey1313 Sep 20 '16 at 17:22

2 Answers2

1

You could try using the generate scripts feature to script out all the objects and data (if required) into a script file and execute that on your target instance.

In SSMS 2016, Right-click DB > Tasks > Generate Scripts..

There are quite a few options under the 'Advanced' button in the wizard that you will probably want to tweak.

innomatics
  • 370
  • 1
  • 10
  • This will be work if my destination server was 2016 and my source was 2005. But as I said for some reason I need to transfer views from 2016 to 2005 and I get errors on scripts because for example "format" function does not identified in sql 2005 and I have it in our views in 2016 server – Pedram Salamati Sep 19 '16 at 16:20
  • 1
    In that case you have no option but to re-write any object code to be backwards compatible – innomatics Sep 20 '16 at 00:39
  • Right, Thank you so much @innomatics! – Pedram Salamati Sep 20 '16 at 17:17
0

In my Case I find the Solution By changing all "format" Functions in the view script which was Format(Convert(int,databse.table.column),'####-##-##') with Replace(convert(varchar(20),Cast(database.table.column as datetime).

It is really rare fore someone to have this issue but Only way to do it is re-build the queries from scratch and try to compatible 2012 or 2016 to 2005 manually!

Pedram Salamati
  • 137
  • 10