January 22, 2025 at 12:42 pm
So I backup & restore db as db_copy.
within both there is a proc that does:
update [db].
.[column]
I need it to say:
update [db_copy].
.[column]
(Simplified, above x 1000)
Is there a step in backup / restore to do this? Is there a quick resolution? Is this poor coding in the first place?
Thank you
January 22, 2025 at 3:29 pm
I'm guessing generating scripts for all objects & amending is the way. Unless there is a simpler way.
January 23, 2025 at 8:33 am
That's exactily the reason why we scan and alert for such usage (use of own database name).
Do this for as well sprocs, views, functions, ...)
"Sloppy development" like "start from object browser, rightclick a view/table and select top x " will generate 3-part naming usage.
Many forget to remove the database name in the from clause.
Copy-paste such objects for new objects are prone to the same mistake.
They will bite you in the back at DRP time !
Keep in mind there may actually be objects that need 3-part naming to another database.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 23, 2025 at 8:53 am
That's exactily the reason why we scan and alert for such usage (use of own database name).
Do this for as well sprocs, views, functions, ...)
"Sloppy development" like "start from object browser, rightclick a view/table and select top x " will generate 3-part naming usage.
Many forget to remove the database name in the from clause.
Copy-paste such objects for new objects are prone to the same mistake.
They will bite you in the back at DRP time !
Keep in mind there may actually be objects that need 3-part naming to another database.
the only objects that should have 3 naming (or 4) are synonyms. ALL remaining code should reference the synonym, and in cases like this only the synonym needs to be updated.
in some of my systems I've implemented a DDL trigger that prevents creation of any code with hardcoded db names precisely because of this. (shame that dynamic sql can't be caught by it)
January 23, 2025 at 9:23 am
That is indeed the best practice
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 23, 2025 at 3:55 pm
The short answer is: No, the BACKUP / RESTORE process will not recode any of your stored procedures and views.
There are SSMS add-ins like Redgate SQL Search that you can leverage to search across all objects looking for references to 4 part named objects.
Or you can use this ugly old script I wrote years ago to query list of all objects containing specified criteria.
Once you've identified the objects, you will need to edit them to remove the database name prefixes.
/*
This script searches across LIKE databases for object text matching LIKE expression.
2017/11/01 Eric Russell
*/
DECLARE @search_expression VARCHAR(8000) = '%MyDatabase[.]%';
DECLARE @search_database VARCHAR(8000) = 'MyDatabase';
SET QUOTED_IDENTIFIER OFF;
DECLARE @SearchDbSQL VARCHAR(8000) =
"
IF '?' NOT LIKE '" + @search_database + "' RETURN;
RAISERROR ('Searching database [%s] ...', 0, 1, '?') WITH NOWAIT;
set nocount on;
set lock_timeout 10000;
set deadlock_priority low;
set transaction isolation level read uncommitted;
declare @c int; select @c = (select count(*) from [?].[sys].[objects]);
select @@SERVERNAME AS server_name
, '?' as db_name
, o.type_desc as object_type
, s.name + '.' + o.name as object_name
, o.modify_date
, TRY_CAST('<sql_text><![CDATA[' + m.definition + ']]></sql_text>' AS XML)as object_definition
from [?].[sys].[objects] as o
left join [?].[sys].[schemas] as s on s.schema_id = o.schema_id
left join [?].[sys].[sql_modules] as m on m.object_id = o.object_id
where is_ms_shipped = 0
and (m.definition LIKE '" + @search_expression + "');
RAISERROR ('%d matches were found in %d objects.', 0, 1, @@rowcount, @c ) WITH NOWAIT;
";
SET QUOTED_IDENTIFIER ON;
PRINT @SearchDbSQL;
IF OBJECT_ID ('tempdb..#search') IS NOT NULL
DROP TABLE #search;
CREATE TABLE #search
(
server_name VARCHAR(255) NULL
, db_name VARCHAR(255) NULL
, object_type VARCHAR(255) NULL
, object_name VARCHAR(255) NULL
, modify_date datetime NULL
, object_definition XML NULL
);
BEGIN TRY
insert into #search (
server_name
, db_name
, object_type
, object_name
, modify_date
, object_definition
)
EXEC sp_MSforeachdb @SearchDbSQL ;
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE();
END CATCH;
SELECT *
FROM #search
ORDER BY db_name
, object_type
, object_name;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply