Restore with Replace, but keep old views?

• sickpup

  SSCommitted

  Points: 1541

  More actions

  May 16, 2013 at 11:22 am

  #277908

  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.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 16, 2013 at 12:02 pm

  #1615945

  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!