December 4, 2013 at 2:08 pm
Hi all,
I was asked to create a test environment for one of our apps that is supported by 3 databases.
I can restore latest backup of production version of each, but then I will have to edit every procedure to make sure that joins point to test instance of the database.
I can create database script and edit it in text editor.
I am looking for an optimal solution as number of stored procedures is growing and manual edit will become problematic with time.
Thanks,
December 5, 2013 at 6:12 am
rightontarget (12/4/2013)
Hi all,I was asked to create a test environment for one of our apps that is supported by 3 databases.
I can restore latest backup of production version of each, but then I will have to edit every procedure to make sure that joins point to test instance of the database.
I can create database script and edit it in text editor.
I am looking for an optimal solution as number of stored procedures is growing and manual edit will become problematic with time.
Thanks,
I might be missing something here. You say you have to create a test environment with 3 databases, but then mention editing the instance referenced. Are you specifying the instance in your procedures? If not, then you shouldn't have to edit anything because the procedures will run in their own databases within their own instance. I prefer using the two-part naming convention (schema.object) and the three-part convention (db.schema.object) only where necessary. I would not want to rely on manually editing the procedures since you're going to miss something eventually, especially as the amount of code grows.
As for how to copy the databases from production to test, I use the approach of backing up production, copying the backup files to the test server, then restoring the databases from the backup files into test. The only caveat here is that if you're using a different server then you'll have to check your SQL logins to make sure the SIDs match. If they don't match, you have to rebuild them because the SQL logins won't have the permissions they need. This doesn't apply to Windows logins unless you move from one domain to another.
December 5, 2013 at 6:23 am
rightontarget (12/4/2013)
but then I will have to edit every procedure to make sure that joins point to test instance of the database.
so your procedures are using three part naming conventions to explicitly identify the database? is that the problem? ie SELECT * from Production.dbo.Table 1 Inner Join Production.dbo.table2?
or you also have cross database queries, that join, say the Production database and CommonSecurity database?
i could see how that would be an issue; i'd end up putting something together that did a find-replace-alter on all the stored procs in that case, and make it something reusable.
I have a sample fo that somewhere, using a cursor, if you thought that might help, but it assumes certain conventions, like the definitiosn are "CREATE PROCEDURE" with a single space between them, which you might need to change depending on your style of coding procs/functions
Lowell
December 5, 2013 at 6:52 am
rightontarget (12/4/2013)
Hi all,I was asked to create a test environment for one of our apps that is supported by 3 databases.
I can restore latest backup of production version of each, but then I will have to edit every procedure to make sure that joins point to test instance of the database.
I can create database script and edit it in text editor.
I am looking for an optimal solution as number of stored procedures is growing and manual edit will become problematic with time.
Thanks,
You may have to clarify how you are referencing your objects.
If you are referencing the Server name in your object naming, is there any particular reason why?
Does "test instance" mean the Server or Database here?
Is your test environment on its own instance of sql server?
Either way, wouldn't the idea of changing code for this be unwieldy and prone to error?
December 5, 2013 at 10:53 am
Thanks all for replies. Based on our replies I get the idea that I did not explain what I am facing correctly.
Let's say I have a server A on which I have database customers and database orders. Some of my queries will have joins between the two.
I need to create a copy of each database on the same server and call them customers_test and orders_test.
If I simply restore production backup into new databases, I will end up with queries that join prod databases. I need them to join '_test' databases.
I guess the problem here is that I am dealing with a copy of database (named differently) on the same server.
As I see, the only way is to edit every procedures to replace.
select ....
from customers.table a
join orders.table b on a.customer_id = b.customer_id
with
select ....
from customers_test.table a
join orders_test.table b on a.customer_id = b.customer_id
Am I on right path? How would you do it?
Hope it makes more sense now.
Thanks,
December 5, 2013 at 11:49 am
here's an adapted example of some code i use to check for invalid objects; this will recompile your procs/functions/views to allow you to point to a different database.
at the end, anything that didn't compile correctly(invalid referneces, drop tables/columns, etc) will appear .
DECLARE @BadObjects TABLE (ALLINVALIDOBJECTS nvarchar(4000))
DECLARE @objname NVARCHAR(4000),
@cmd NVARCHAR(max),
@OldDatabase1 varchar(128),
@NewDatabase1 varchar(128),
@OldDatabase2 varchar(128),
@NewDatabase2 varchar(128)
SET @OldDatabase1 = 'PRODUCTION'
SET @NewDatabase1 = 'TESTPROD'
SET @OldDatabase2 = 'CommonSecurity'
SET @NewDatabase2 = 'TESTSecurity'
--#################################################################################################
--Views
--#################################################################################################
DECLARE c1 CURSOR FOR
SELECT
QUOTENAME(s.name) + '.' + QUOTENAME(obs.name) ,
mods.definition
FROM sys.objects obs
INNER JOIN sys.sql_modules mods ON obs.object_id = mods.object_id
INNER JOIN sys.schemas s ON obs.schema_id = s.schema_id
WHERE obs.is_ms_shipped = 0
AND obs.type_desc IN('VIEW')
OPEN c1
FETCH NEXT FROM c1 INTO @objname,@cmd
WHILE @@fetch_status <> -1
BEGIN
BEGIN TRANSACTION
BEGIN TRY
SET @cmd = REPLACE(@cmd,convert(varchar(max),N'CREATE VIEW'),convert(varchar(max),N'ALTER VIEW'))
--modify three part naming conventions to point to the test database
SET @cmd = REPLACE(@cmd,convert(varchar(max),@OldDatabase1 +'.'),convert(varchar(max),@NewDatabase1 +'.'))
SET @cmd = REPLACE(@cmd,convert(varchar(max),@OldDatabase2 +'.'),convert(varchar(max),@NewDatabase2 +'.'))
SET @cmd = REPLACE(@cmd,convert(varchar(max),N'[' + @OldDatabase1 + '].'),convert(varchar(max),N'[' + @NewDatabase1 + '.'))
SET @cmd = REPLACE(@cmd,convert(varchar(max),N'[' + @OldDatabase2 + '].'),convert(varchar(max),N'[' + @NewDatabase2 + '].'))
print @cmd
exec (@cmd)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
INSERT @BadObjects VALUES (@objname)
END CATCH
FETCH NEXT FROM c1 into @objname,@cmd
END --WHILE
CLOSE c1
DEALLOCATE c1
--#################################################################################################
--Procs
--#################################################################################################
DECLARE c1 CURSOR FOR
SELECT
QUOTENAME(s.name) + '.' + QUOTENAME(obs.name) ,
mods.definition
FROM sys.objects obs
INNER JOIN sys.sql_modules mods ON obs.object_id = mods.object_id
INNER JOIN sys.schemas s ON obs.schema_id = s.schema_id
WHERE obs.is_ms_shipped = 0
AND obs.type_desc IN('SQL_STORED_PROCEDURE')
OPEN c1
FETCH NEXT FROM c1 INTO @objname,@cmd
WHILE @@fetch_status <> -1
BEGIN
BEGIN TRANSACTION
BEGIN TRY
SET @cmd = REPLACE(@cmd,convert(varchar(max),N'CREATE PROCEDURE'),convert(varchar(max),N'ALTER PROCEDURE'))
--modify three part naming conventions to point to the test database
SET @cmd = REPLACE(@cmd,convert(varchar(max),@OldDatabase1 +'.'),convert(varchar(max),@NewDatabase1 +'.'))
SET @cmd = REPLACE(@cmd,convert(varchar(max),@OldDatabase2 +'.'),convert(varchar(max),@NewDatabase2 +'.'))
SET @cmd = REPLACE(@cmd,convert(varchar(max),N'[' + @OldDatabase1 + '].'),convert(varchar(max),N'[' + @NewDatabase1 + '.'))
SET @cmd = REPLACE(@cmd,convert(varchar(max),N'[' + @OldDatabase2 + '].'),convert(varchar(max),N'[' + @NewDatabase2 + '].'))
print @cmd
exec (@cmd)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
INSERT @BadObjects VALUES (@objname)
END CATCH
FETCH NEXT FROM c1 into @objname,@cmd
END --WHILE
CLOSE c1
DEALLOCATE c1
--#################################################################################################
--Functions
--#################################################################################################
DECLARE c1 CURSOR FOR
SELECT
QUOTENAME(s.name) + '.' + QUOTENAME(obs.name) ,
mods.definition
FROM sys.objects obs
INNER JOIN sys.sql_modules mods ON obs.object_id = mods.object_id
INNER JOIN sys.schemas s ON obs.schema_id = s.schema_id
WHERE obs.is_ms_shipped = 0
AND obs.type_desc IN('AGGREGATE_FUNCTION','SQL_INLINE_TABLE_VALUED_FUNCTION','SQL_TABLE_VALUED_FUNCTION','SQL_SCALAR_FUNCTION')
OPEN c1
FETCH NEXT FROM c1 INTO @objname,@cmd
WHILE @@fetch_status <> -1
BEGIN
BEGIN TRANSACTION
BEGIN TRY
SET @cmd = REPLACE(@cmd,convert(varchar(max),N'CREATE FUNCTION'),convert(varchar(max),N'ALTER FUNCTION'))
--modify three part naming conventions to point to the test database
SET @cmd = REPLACE(@cmd,convert(varchar(max),@OldDatabase1 +'.'),convert(varchar(max),@NewDatabase1 +'.'))
SET @cmd = REPLACE(@cmd,convert(varchar(max),@OldDatabase2 +'.'),convert(varchar(max),@NewDatabase2 +'.'))
SET @cmd = REPLACE(@cmd,convert(varchar(max),N'[' + @OldDatabase1 + '].'),convert(varchar(max),N'[' + @NewDatabase1 + '.'))
SET @cmd = REPLACE(@cmd,convert(varchar(max),N'[' + @OldDatabase2 + '].'),convert(varchar(max),N'[' + @NewDatabase2 + '].'))
print @cmd
exec (@cmd)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
INSERT @BadObjects VALUES (@objname)
END CATCH
FETCH NEXT FROM c1 into @objname,@cmd
END --WHILE
CLOSE c1
DEALLOCATE c1
SELECT * FROM @BadObjects
Lowell
December 5, 2013 at 12:31 pm
Thank you Lowell for sharing your code.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply