2

These are the steps I have performed:

  • I have run a 'restore backup' query for an old database backup
  • Then, I restore a backup with SQL Server Management Studio. I changed the type of some columns and then I back up the database again.
  • Finally, I restore this back up again.

If I try the last step with SQL Server Management Studio, this works as expected - the database with changed columns is restored. However, if I try this with the 'restore backup' query, it restores a wrong version of the database, without the column type changes. This is most likely an old version, but I am sure that the path I use refers to the latest backup.

The query I run, looks as follows:

RESTORE DATABASE {db_name} FROM DISK = '{path to .bak file}' WITH REPLACE,
MOVE '{db_name}' TO 'D:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Data\{db_name}.mdf', 
MOVE '{db_name}_Log' TO 'D:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Data\{db_name}_Log.ldf'

I use SQL Server 2017. The 'move' statements are necessary because the original backup originates from another path which does not exist on my system.

I have also tried deleting the database and removing the .mdf and .ldf files from the Program Files folder before running the restore backup query, but the same thing keeps happening. Could it be that SQL Server stores a backup, or .mdf and .ldf files somewhere else, and these are somehow restored?

EDIT: I have learned that you can see the query from SQL Server Management Studio by pressing 'script' before pressing 'OK' while restoring the backup. I see that a different query is run, then, namely:

USE [master]
RESTORE DATABASE [{db_name}] FROM  DISK = N'{path to .bak file}' WITH  FILE = 2,  NOUNLOAD,  STATS = 5

GO
0

1 Answer 1

3

Run RESTORE HEADERONLY FROM DISK = '{path to .bak file}'; to see if the file contains multiple backups. The first (oldest) is restored by default so you need to specify the FILE= option of T-SQL RESTORE DATABASE to restore the newer one when the file contains multiple backups.

Example to restore the latest when the RESTORE HEADERONLY shows 2 rows:

RESTORE DATABASE {db_name} FROM DISK = '{path to .bak file}' WITH REPLACE,
MOVE '{db_name}' TO 'D:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Data\{db_name}.mdf', 
MOVE '{db_name}_Log' TO 'D:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Data\{db_name}_Log.ldf',
FILE = 2;

Per your comment, you would like to always restore the latest backup. The example below uses dynamic SQL and a template script to accomplish the task. Note this makes several assumptions, such as the backup file contains only full database backups from a single database, the database has exactly one data and log file, the source logical file names are consistent, etc.

DECLARE 
     @RestoreCommand nvarchar(MAX)
    ,@FileNumber int
    ,@RestoreCommandTemplate nvarchar(MAX) = N'
RESTORE DATABASE {db_name} 
FROM DISK = ''{path to .bak file}''
WITH
    REPLACE,
    MOVE ''{db_name}'' TO ''D:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Data\{db_name}.mdf'', 
    MOVE ''{db_name}_Log'' TO ''D:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Data\{db_name}_Log.ldf'',
    FILE = {file_number};'
    ,@BackupFilePath nvarchar(255) = N'C:\SqlBackups\YourBackupFile.bak'
    ,@DatabaseName sysname = N'YourDatabaseName';

RESTORE HEADERONLY FROM DISK=@BackupFilePath;
SET @FileNumber = @@ROWCOUNT; --last backup is number of rows returned by RESTORE HEADERONLY
SET @RestoreCommand = REPLACE(@RestoreCommandTemplate,N'{db_name}', @DatabaseName);
SET @RestoreCommand = REPLACE(@RestoreCommand,N'{path to .bak file}', @BackupFilePath);
SET @RestoreCommand = REPLACE(@RestoreCommand,N'{file_number}', @FileNumber);
PRINT @RestoreCommand;
--EXECUTE sp_executesql @RestoreCommand;

This above approach can be extended and generalized for more complex needs. For example, one can insert RESTORE FILELISTONLY results into a temp table/variable to determine the source backup logical file names instead of relying on a particular naming convention, number of database files, etc.

4
  • That works correctly! One more challenge is that I run the query in a script and I don't know how many backup files are associated beforehand, so the value for 'FILE' must be variable. One option would be, first counting the number of rows from the RESTORE HEADERONLY FROM DISK = '{path to .bak file}'; query, but ideally, I would like to be able to state that I would always like to restore the last file. Feb 17, 2023 at 12:54
  • I'm with Aaron on this. You could insert the output from RESTORE HEADERONLY into a table using INSERT ... EXEC('RESTORE ...'), and than work off of that table. But that feels like a hack to me compared to using different backup files. Feb 17, 2023 at 13:39
  • 1
    @GuidoPassage, unfortunately, the FILE= option must specify a constant; a variable is not permitted so you need to use dynamic SQL. I updated my answer with an example you can extend for your use case.
    – Dan Guzman
    Feb 17, 2023 at 13:52
  • I like the WITH INIT option for backups, but we then depend on the person who created the backup (which is often a manual process within my context). Feb 17, 2023 at 15:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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