2

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.

  • 1
    Your script makes a full backup that cannot miss anything. Maybe your developers do not see something because they don't have permissions. – sepupic Jul 17 '18 at 12:49
  • I asked them and they told me they're using sa for this – aseques Jul 17 '18 at 12:53
  • 4
    Perhaps the file contains more than one backup (since the name has only date rather than full timestamp). The first (oldest) backup is restored by default. – Dan Guzman Jul 17 '18 at 13:05
  • 2
    ...add WITH INIT to the backup command to avoid this. Or add time to the filename. – Aaron Bertrand Jul 17 '18 at 15:21
  • @AaronBertrand, I am adding date in the filename, and I'm 100% sure I only did the backup/restore once this day, I'll check about the WITH INIT flag – aseques Jul 18 '18 at 6:09
4

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

  • It's a nice addon to the backup script I'm using, but I only did the procedure once, so the cause must be somewhere else. I'll do another set of backup restore and see at the moment if the migration went fine – aseques Jul 18 '18 at 6:19
0

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.

  • I'm using dated backups to avoid this case, the convert(varchar, getdate(), 112) part creates a date with format 20180718 – aseques Jul 18 '18 at 10:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.