May 16, 2013 at 11:22 am
Hello,
Just a quick question -
I have two DBs - "DB 1" and "DB 2"
DB 1 is, essentially, the production DB.
DB 2 is, essentially, a reporting environment.
I'm trying to get replication working, but in the meantime, I would like to take the newer data from DB 1 and move it to DB 2, but I would like to keep some custom views that have been created on DB 2.
What's the least painful way to do this?
Ordinarily, I just use the latest backup for DB 1 and restore with replace on DB 2. But, that's going to drop all the custom views located there, right?
To save time, I'd rather not have to write scripts to manually pull data from one DB and write it to the other...
Thanks.
May 16, 2013 at 12:02 pm
well, it's going to be an extra/separate process to save the view definitions, but you could at least automate it;
for example, you could guard all the view definitions, restore, then use a cursor to recreate them if they don't exist, or ALTER if existing;
my example worked fine on my testing below and either created or altered my sample views
USE SandBox;
--get my view definitions
SELECT name AS ViewName,OBJECT_DEFINITION(OBJECT_ID) AS definition INTO #tmp FROM .sys.views ;
USE master;
GO
--kick everyone off my database, prevent anyone from reconnecting
ALTER DATABASE [SandBox] SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO
USE master;
GO
--restore it!
RESTORE DATABASE [SandBox]
FROM DISK = N'F:\SQLData\DEV223\Backup\Sandbox.bak'
WITH FILE = 1,
NOUNLOAD,
REPLACE,
STATS = 10
--now a cursor to add anything that doesn't exist.
GO
--reconnect to it, add my views back
USE SandBox;
DECLARE
@isql VARCHAR(MAX),
@viewname VARCHAR(64)
DECLARE c1 CURSOR FOR SELECT ViewName,definition FROM #tmp
OPEN c1
FETCH NEXT FROM c1 INTO @viewname,@isql
--lazy assumption that everything is dbo; modify to include schema name if needed.
WHILE @@FETCH_STATUS <> -1
BEGIN
PRINT 'Validating view ' + quotename(@viewname);
IF NOT EXISTS(SELECT 1 FROM sys.views WHERE name = @viewname)
BEGIN
PRINT 'CREATING view ' + quotename(@viewname);
PRINT @isql
EXEC(@isql)
END
ELSE
BEGIN
PRINT 'ALTERING view ' + quotename(@viewname);
--note this requires an assumption on view defintion conventions, exactly one space betwene CREATE VIEW
SET @isql = REPLACE(@isql,'CREATE VIEW','ALTER VIEW')
PRINT @isql
EXEC(@isql)
END
FETCH NEXT FROM c1 INTO @viewname,@isql
END
CLOSE c1
DEALLOCATE c1
GO
drop table #tmp
Lowell
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply