SQL Server – RESTORE Missing Views

restoresql server

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.

Best 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