Backup not taking latest position

Mahreen 1 Reputation point

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]

FROM DISK = N'C:\Room\db1.bak'
FILE = 15,
MOVE N'db11_data' TO N'C:\room\db1.mdf',
MOVE N'db1_log' TO N'C:\Room\db1_1.ldf',

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,913 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Bjoern Peters 8,856 Reputation points

    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'   
    			NAME = N'forum1-Full Database Backup',  
    			STATS = 10  


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

    0 comments No comments

  2. Erland Sommarskog 102.3K Reputation points

    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)  
       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.

    0 comments No comments

  3. Seeya Xi-MSFT 16,451 Reputation points

    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:
    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,

    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.

    0 comments No comments