Restore with Replace, but keep old views?

  • 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.

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply