locked
Database rename to new name and existing SQLs refer old data RRS feed

  • Question

  • Hi

    I had a DB name VisionMin on test env.  There is the production DB in prod env call "VisionMin". When I refresh to test env as different name(ex VisionMinEOF) one view output result is same. When I checked the view I realised created view from SQL which used <visonmin>.<schemanname>.<table name>.
     That mean its refer to old "visonmin" database table reside on same instance.
    I suspect bunch of  Store procedures might have written in same way. To avoid this kind of scenarios when DB restore to different name As a DBA and Developer what action should take for avoid this situation.

    Restore procedure was. First create black DB name “VisionMinEOF”. Then restore on from production “VisionMin”  with “replace “option .

    Basically query will work when change into <VisionminEOF>.<schemanname>.<table name>. Hundred of SP,Views inside and not practical to change one by one.
    Any idea to avoid this situations? Many thanks


    • Edited by ashwan Thursday, July 26, 2018 9:53 PM
    Thursday, July 26, 2018 9:51 PM

Answers

  • Hi ashwan,

    Could you please share more information to help me understand this problem better?

    Do you mean that the DBA will restore the VisionMin database backed up from production server to the develop server as the name “VisionMinEOF”?

    But why not restore the database as "VisionMin" directly? We can first change the name of the old database to “VisionMinEOF”, then restore the new database as the name "VisionMin".

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, July 27, 2018 6:27 AM
  • Hello,

    I see 2 option
    - Use a different SQL Server instance, where the origin database not exixts
    - Use a Login, which don't have permissions to access the origin database

    But both will end in an error, which makes it useless. So the only way is to change all three part qualifier, which is already bad design.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Friday, July 27, 2018 6:29 AM
    Answerer

  • What is the best solution to  identify which view or SP written by using <dbname>. then we can keep developers updated.

    I can think of two ways:

    1) Change the views definition in prod to a two-part naming convention i.e. <Schema>.<TableName>. That way, it'll only run within the current DB context.

    2) Another quick dirty way is after the DB is restored as "VisionMinEOF", expand the DB, click on "Views"-->Go to View tab in SSMS on the top-->select Object Explorer Details (or press F7)--> Select all Views you like-->Right-click and select 'script view as'-->select drop and create to--> New Query editor window.

    Once the new query window is populated with all drop and create view commands, do a CTRL+F and find "VisionMin" and replace with "VisionMinEOF: so that all occurrences of VisionMin are replaced with VisionminEOF. Alternatively, you can replace "VisionMin" with blank space so the view becomes a two-part name i.e. <Schema>.<TableName>. Just run it and that should do it. Test it out first.

    You can follow the same for SPs too i.e. replace VisionMin with VisionMinfEOF)

    Hope this helps!


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.


    Friday, July 27, 2018 10:03 PM
  • 3 days have passed. Practicality has no bearing on this - the sooner you start the sooner you will finish. 

    You have no choice but to find, change, and test every instance. You are paying the price for poor decisions and poor oversight. And if your tsql code is in such poor shape, your applications are likely to be in similar shape. You will need to apply this same logic to all your applications and any scripts hosted outside of sql server. 

    With minimal effort you can find scripts that will search the definitions of your procedures, views, triggers, etc for a particular string. Get started now; stop wasting time complaining about how much work this will be.  

    Sunday, July 29, 2018 11:52 AM
  • Use SSDT = SQL Server Data Tool to create a database Project, import the existing database, searchg & replace the database name and run a schema compare to get a change script.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, July 30, 2018 6:20 AM
    Answerer
  • It is extremely bad practice to put the database name inside your views/procs unless you are referring to a different database.  The best practice is to use VS SSDT database projects for this work, and it would show you an error on these items.  If you use VS to reverse engineer your database into a database project, it will show you all the errors.

    The only option if the other database exists is to fix all the view/procs/functions to 2 part names, so they refer to the current database.  If the original database name does not exist, you could create a synonym to point the old name, to the new name.  However, that is not an option if the old database exists.


    Monday, August 6, 2018 11:46 AM
    Answerer
  • Hi Mohsin This is not practical to do each VIEW or SP one by one. Can we find globally any SP,View created script with dbname and find each related objects to replace ?

    Have you tried that method in dev/test? that's not one-by-one. If you don't have thousands of complex views or SPs, that may actually be one of the quickest ways to do it. I'd rather try it out before concluding that it's not feasible.

    Btw, you'd have to watch for permissions as dropping/recreating will remove the permissions. You can script out all permissions prior. Well, the DB copy comes from prod anyway, you might already be removing permissions that were carried over from prod and applying the dev/test-version permissions.

    As others have pointed out, you'd run into this issue again on the next refresh so the best bet is to fix it at the source.


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Monday, August 6, 2018 12:42 PM

All replies

  • Hi ashwan,

    Could you please share more information to help me understand this problem better?

    Do you mean that the DBA will restore the VisionMin database backed up from production server to the develop server as the name “VisionMinEOF”?

    But why not restore the database as "VisionMin" directly? We can first change the name of the old database to “VisionMinEOF”, then restore the new database as the name "VisionMin".

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, July 27, 2018 6:27 AM
  • Hello,

    I see 2 option
    - Use a different SQL Server instance, where the origin database not exixts
    - Use a Login, which don't have permissions to access the origin database

    But both will end in an error, which makes it useless. So the only way is to change all three part qualifier, which is already bad design.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Friday, July 27, 2018 6:29 AM
    Answerer
  • Hi Olaf

    This is the situation. VisionMin on test env which define as env for this system. basically every system prod has similar dev,test. Now our business is already use old "VisionMin"  on test as its very important. I cant change it to different name.  Now the issue is we need to have same DB from production to have different purpose(VisionMinEOF). Now with in VisionMinEOF views created to use as

    create view cusmust as select * from visionMin.dbo.tales1 a leftjoin visionmin.dbo.table2

    Even I restore new DB, with the DB referred old DB data on same instance.

    Neither  use different users account to access data or  change every view is not a practical . I suspect may have stored procedures as well.

    What is the best solution to  identify which view or SP written by using <dbname>. then we can keep developers updated.

    Why developers hard cord dbname when they write SQL which on the current DB?

    many thanks  

      

    Friday, July 27, 2018 8:54 PM
  • Hi Teigo its not practical  to rename old one  as people heavy use through applications .
    Friday, July 27, 2018 8:54 PM

  • What is the best solution to  identify which view or SP written by using <dbname>. then we can keep developers updated.

    I can think of two ways:

    1) Change the views definition in prod to a two-part naming convention i.e. <Schema>.<TableName>. That way, it'll only run within the current DB context.

    2) Another quick dirty way is after the DB is restored as "VisionMinEOF", expand the DB, click on "Views"-->Go to View tab in SSMS on the top-->select Object Explorer Details (or press F7)--> Select all Views you like-->Right-click and select 'script view as'-->select drop and create to--> New Query editor window.

    Once the new query window is populated with all drop and create view commands, do a CTRL+F and find "VisionMin" and replace with "VisionMinEOF: so that all occurrences of VisionMin are replaced with VisionminEOF. Alternatively, you can replace "VisionMin" with blank space so the view becomes a two-part name i.e. <Schema>.<TableName>. Just run it and that should do it. Test it out first.

    You can follow the same for SPs too i.e. replace VisionMin with VisionMinfEOF)

    Hope this helps!


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.


    Friday, July 27, 2018 10:03 PM
  • Hi Mohsin This is not practical to do each VIEW or SP one by one. Can we find globally any SP,View created script with dbname and find each related objects to replace ?

    Sunday, July 29, 2018 11:40 AM
  • 3 days have passed. Practicality has no bearing on this - the sooner you start the sooner you will finish. 

    You have no choice but to find, change, and test every instance. You are paying the price for poor decisions and poor oversight. And if your tsql code is in such poor shape, your applications are likely to be in similar shape. You will need to apply this same logic to all your applications and any scripts hosted outside of sql server. 

    With minimal effort you can find scripts that will search the definitions of your procedures, views, triggers, etc for a particular string. Get started now; stop wasting time complaining about how much work this will be.  

    Sunday, July 29, 2018 11:52 AM
  • Hi ScottIf you say poor T SQl written by developer, What is the best we to write it with out using DB name?

    ex

    Old : select * from <dbname>.<schema>.<table>

    new: select * from <schema>.<table>

     Let say if we need  a requirement to write a query access the DB from from out side, then how do we do that.
    What is the practical best way to aviod use DB name ?

    Sunday, July 29, 2018 11:24 PM
  • Hi Mohsin

    I use following SQL to find globally any other SQL available

    SELECT [Scehma]=schema_name(o.schema_id), o.Name,case
    when o.type ='P' Then 'Procedure'
    when o.type ='TR' Then 'Trigger'
    when o.type ='V' Then 'View'
    ELSE 'Unknown'
    END
    FROM sys.sql_modules m
    INNER JOIN sys.objects o
    ON o.object_id = m.object_id
    WHERE m.definition like '%gis_maint%'
    Order by o.type desc
    GO

    Its returns over 1000. Unless modify are we able to use public synonym for each view.But not sure for view.Any idea ?

    Sunday, July 29, 2018 11:27 PM
  • Use SSDT = SQL Server Data Tool to create a database Project, import the existing database, searchg & replace the database name and run a schema compare to get a change script.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, July 30, 2018 6:20 AM
    Answerer
  • You need to define your goal here. There's no way to avoid this, since your developers use 3-part naming. So, what is your goal? :

    Make it easy for you to find the objects which has three part naming, so it is easy for you to go and fix these after the restore?

    Or, try to find some mechanism that help the developers to *not* use 3-art naming in the first place?

    No magic bullet here. You have to decide which of above two is for you...


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, August 6, 2018 7:38 AM
  • It is extremely bad practice to put the database name inside your views/procs unless you are referring to a different database.  The best practice is to use VS SSDT database projects for this work, and it would show you an error on these items.  If you use VS to reverse engineer your database into a database project, it will show you all the errors.

    The only option if the other database exists is to fix all the view/procs/functions to 2 part names, so they refer to the current database.  If the original database name does not exist, you could create a synonym to point the old name, to the new name.  However, that is not an option if the old database exists.


    Monday, August 6, 2018 11:46 AM
    Answerer
  • Hi Mohsin This is not practical to do each VIEW or SP one by one. Can we find globally any SP,View created script with dbname and find each related objects to replace ?

    Have you tried that method in dev/test? that's not one-by-one. If you don't have thousands of complex views or SPs, that may actually be one of the quickest ways to do it. I'd rather try it out before concluding that it's not feasible.

    Btw, you'd have to watch for permissions as dropping/recreating will remove the permissions. You can script out all permissions prior. Well, the DB copy comes from prod anyway, you might already be removing permissions that were carried over from prod and applying the dev/test-version permissions.

    As others have pointed out, you'd run into this issue again on the next refresh so the best bet is to fix it at the source.


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Monday, August 6, 2018 12:42 PM