sqlserver RESTORE missing views

Posted on

Question :

I amb trying to do a full database backup from one server to restore into another one, this is the backup script (creating a dated name for the backup).

declare @DB sysname
declare @DISK sysname
SET @DB = 'DBNAME'
SET @DISK = 'L:' + @DB + '_'+convert(varchar, getdate(), 112)+'.bak'
BACKUP DATABASE @DB
TO  DISK = @DISK
WITH COMPRESSION, COPY_ONLY, STATS = 5
GO

And this is the restore one (restoring from the previously created file):

declare @DB sysname
declare @DISK sysname
SET @DB = 'DBNAME'
SET @DISK = 'L:' + @DB + '_'+convert(varchar, getdate(), 112)+'.bak'
-- #commented until needed (dangerous)
SELECT @DISK
--EXEC('ALTER DATABASE '+ @DB +' SET OFFLINE WITH ROLLBACK IMMEDIATE')
--EXEC('DROP DATABASE '+ @DB)
-- #show 1% fractions as it is restoring
--RESTORE DATABASE @DB FROM DISK = @DISK WITH REPLACE, STATS = 1
GO

Both scripts run without error messages, but the developers are saying that they are missing some VIEWs from the restored database. About those views:

  • Some of them use linked servers, some don’t
  • The linked server in the DEST server has a different name that the one in the source (for those using linked servers).

I saw this about clr, but doensn’t seem related.

Any ideas on what might be failing?
UPDATE1: After asking averyone, on of the devs told me that they did a failed update after my restore that deleted the views I was missing, my procedure was fine.
UPDATE2: I accepted Aaron’s reply because altough it wasn’t the cause in my case, it’s nice addon to the backup script.

Answer :

Backup is most likely Appending to the Last Backup–add WITH INIT to the backup (see below)

https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-2017

“A disk device does not have to exist before it is specified in a BACKUP statement. If the physical device exists and the INIT option is not specified in the BACKUP statement, the backup is appended to the device.”

declare @DB sysname
declare @DISK sysname
SET @DB = 'DBNAME'
SET @DISK = 'L:' + @DB + '_'+convert(varchar, getdate(), 112)+'.bak'
BACKUP DATABASE @DB
TO  DISK = @DISK
WITH COMPRESSION, COPY_ONLY, STATS = 5, INIT
GO

p.s. Just saw Aaron’s comment–if it’s right–please give him a bump as well. Done

Backup and restore will transfer everything that database, even views and stored procedures.

Any ideas on what might be failing?

I think this was because of an old .bak file, when backing up from a database would just copy what was there currently, but it turns out that the backups come from snapshots taken of the database instead. Those backups did not contain the missing views, stored procedures and foreign keys. In this case you might missing Views otherwise no issues.

Leave a Reply

Your email address will not be published.