July 24, 2013 at 11:31 am
Thanx for the advice. How much work is it to set up a new server? We use VM here.
July 24, 2013 at 11:34 am
Build a normal windows machine, provision disks to it, install SQL, copy logins and server permissions for the required logins, copy over the databases. I'm sure there is a bit more but this is something you should already know how to do.. The setup of the base machine is often handled by a different group but DBAs typically handle all the database portions.
CEWII
July 24, 2013 at 11:47 am
Thanx.
July 24, 2013 at 1:14 pm
NineIron (7/24/2013)
I need to create 5 test databases by copying the live db's. There are many stored procedures in each db. Each sp is told which db to use, Use [NEHEN_prod]. I've been told that I have to go into each sp, about 50 per db, and change the NEHEN_prod to NEHEN_test. Is this necessary? If so, is there an easy way of doing it?
Below is a script I just put together that will do through each stored procedure in a DB and perform a find/replace (replace @oldvalue with @newvalue). It works by (1) deleting the proc then recreating it with the old value (@oldvalue) replaced by the new value (@newvalue). It works as-is but I highly recommend you add some error handling...
The proc:
CREATE PROC dbo.proc_find_replace
(@oldValue varchar(100),
@newValue varchar(100))
AS
BEGIN
SET NOCOUNT ON;
-- global temp table for storing proc names and DDL definition
IF OBJECT_ID('tempdb..##sprocDef') IS NOT NULL
DROP TABLE ##sprocDef;
CREATE TABLE ##sprocDef (sproc varchar(100), ROUTINE_DEFINITION varchar(max));
DECLARE @sproc varchar(100),
@routine varchar(max),
@execSQL varchar(max),
@n int=1,
@m int=
(SELECT COUNT(*)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE');
-- FOR EACH proc in {yourdb}.INFORMATION_SCHEMA.ROUTINES:
-- (1) drop it
-- (2) recreate it replacing @oldValue with @newValue
WHILE @n<=@m
BEGIN
--colect the name and DDL for each proc in ##sprocDef
DECLARE @sprocDef varchar(1000)=
'INSERT INTO ##sprocDef'+CHAR(13)+
'SELECT TOP 1
SPECIFIC_SCHEMA+''.''+SPECIFIC_NAME AS Sproc,
OBJECT_DEFINITION (OBJECT_ID(SPECIFIC_SCHEMA+''.''+SPECIFIC_NAME)) AS ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = ''PROCEDURE''
AND SPECIFIC_SCHEMA+''.''+SPECIFIC_NAME NOT IN (SELECT sproc FROM ##sprocDef)';
EXEC(@sprocDef);
SELECT@sproc=sproc,
@routine=ROUTINE_DEFINITION
FROM ##sprocDef;
SET @execSQL=
'IF OBJECT_ID(''ajbTest.'+@sproc+''') IS NOT NULL DROP PROC '+@sproc+';'
PRINT 'Executing:'+@execSQL
EXEC(@execSQL);
SET @execSQL=''+@routine+CHAR(13);
SET @execSQL=REPLACE(@execSQL,@oldValue,@newValue);
PRINT 'Executing:'+@execSQL
EXEC(@execSQL);
SET @n=@n+1;
END;
PRINT REPLICATE('-',80)+CHAR(13)+'ALL DONE :)'
DROP TABLE ##sprocDef;
END
GO
Example:
EXEC dbo.proc_find_replace @oldValue='NEHEN_prod', @newValue='NEHEN_test';
Lastly: backup your procs before running this script!
-- Itzik Ben-Gan 2001
July 24, 2013 at 1:28 pm
Thanx. Will do.
July 25, 2013 at 9:01 am
Elliott Whitlow (7/24/2013)
...2. I have an article in the works that discusses NEVER using 3 part names in code and using synonyms to point to objects external to the local database. But I do see your point.
CEWII
We actually just started doing that in one of our new applications. It messes with intellisense a bit, but overall, it's worked fairly well for us. As we have different names for our prod and dev databases for this application, we even created a script that will repoint the synonyms to the appropriate database.
July 25, 2013 at 9:31 am
LightVader (7/25/2013)
Elliott Whitlow (7/24/2013)
...2. I have an article in the works that discusses NEVER using 3 part names in code and using synonyms to point to objects external to the local database. But I do see your point.
CEWII
We actually just started doing that in one of our new applications. It messes with intellisense a bit, but overall, it's worked fairly well for us. As we have different names for our prod and dev databases for this application, we even created a script that will repoint the synonyms to the appropriate database.
The tool to handle repointing is the part 2 of the article. But I highly recommend it.. And yeah it does freak out intellisense.. But the code not having to change or any of the other methods is well worth it..
CEWII
July 25, 2013 at 9:37 am
Elliott Whitlow (7/25/2013)
LightVader (7/25/2013)
Elliott Whitlow (7/24/2013)
...2. I have an article in the works that discusses NEVER using 3 part names in code and using synonyms to point to objects external to the local database. But I do see your point.
CEWII
We actually just started doing that in one of our new applications. It messes with intellisense a bit, but overall, it's worked fairly well for us. As we have different names for our prod and dev databases for this application, we even created a script that will repoint the synonyms to the appropriate database.
The tool to handle repointing is the part 2 of the article. But I highly recommend it.. And yeah it does freak out intellisense.. But the code not having to change or any of the other methods is well worth it..
CEWII
Agreed. It was a nice way to separate most of our code from the vendor database.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply