Backup not taking latest position

Mahreen 1 Reputation point
2022-08-27T08:18:59.397+00:00

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?

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
    2022-08-27T13:37:21.07+00:00

    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  
      
    

    235397-image.png

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

    0 comments No comments

  2. Erland Sommarskog 102.3K Reputation points
    2022-08-27T20:37:36.453+00:00

    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.

    0 comments No comments

  3. Seeya Xi-MSFT 16,451 Reputation points
    2022-08-29T07:20:09.257+00:00

    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.

    0 comments No comments