Backup not taking latest position

Question

We are doing a backup/Restore with maintenance plans. Running a scheduled job for the maintenance plan to backup a database and save it to another server. The backup is being created every night and we can see it.

When we restore the backup we have another job running a script and its working for one day then stops. It seems that the script puts in a hard number for the with file = 15 The backups keep going.

For example... position 1, 2, 3, 4..etc. The script only runs at position 2. Is there a way to get the latest position every restore?

USE [master]

RESTORE DATABASE [db1]
FROM DISK = N'C:\Room\db1.bak'
WITH
FILE = 15,
MOVE N'db11_data' TO N'C:\room\db1.mdf',
MOVE N'db1_log' TO N'C:\Room\db1_1.ldf',
NOUNLOAD,
REPLACE,
STATS = 5

GO
How do we get the With FILE = 15 to always go to the next backup position?

Is there a better way to detach a database, clear logs, and restore a database?

Answer 1

To answer this question or to give you any advise it would be necessary that you post more information on how you are doing the backup.

Normal procedure... if I use the default backup command, to have a full backup of my database... I only got the numbers 1 and 2 (1 = FULL, 2 = LOG)

BACKUP DATABASE [forum1] TO  DISK = N'/var/opt/mssql/data/forum1.bak'   
	WITH	NOFORMAT,  
			NOINIT,  
			NAME = N'forum1-Full Database Backup',  
			SKIP,  
			NOREWIND,  
			NOUNLOAD,  
			STATS = 10  
GO  
  

So your MP must do something differently... please show/explain to us.

Answer 2

You can use RESTORE HEADERONLY to find all backup in the file. The column Position gives you value to use for FILE.

You can capture the output with

   INSERT #tmp(....)  
      EXEC ('RESTORE HEADERONLY...')  

Then you can do

   SELECT [@](/users/na/?userId=07da7035-7ffe-0003-0000-000000000000) = MAX(Position)  
   RESTORE DATABASE ...  
   WITH FILE = [@](/users/na/?userId=07da7035-7ffe-0003-0000-000000000000)  

To get the file you want.

This is some tedious, as your temp table needs to have all columns returned by RESTORE HEADERONLY:

Many people use a BACKUP command with timestamp in the filename, and only one backup in the file. Having many backups in the same file, is not very practical, in my opinion.

Answer 3

Hi @Mahreen ,

Welcome to Microsoft Q&A!
Please refer this blog about View Contents of SQL Server Backup Files with RESTORE LABELONLY, HEADERONLY and FILELISTONLY
Also, you can use FILELISTONLY.
As for me, I prefer the timestamp format. I have the following habits:
The restored database will have the same name as the backed up database
The restored database will be restored in the same location as the backed up database
The files have the following naming format: dbName_YYYYMMDDHHMM.xxx
File extensions are as follows:
Full backup - BAK
Differential backup - DIF
Transaction log backup - TRN
Of course I agree with Erland. It all depends on your situation.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.