Can I safely use NORECOVERY option when restoring a database by a BAK file?

Question

Can I safely use NORECOVERY option when restoring a database by a BAK file?

dave 0

Hello, I am trying to extract the data from a customer-provided BAK file and then discard the SQL server container after. I am using the syntax below.

RESTORE DATABASE [ABC123] FROM DISK = '/var/opt/mssql/data/ABC123.bak' 
WITH    
   MOVE 'ABC123.mdf' TO '/var/opt/mssql/data/ABC123.mdf',   
   MOVE 'ABC123.ldf' TO '/var/opt/mssql/data/ABC123.ldf',  
REPLACE,   
NORECOVERY;

below is the resulting logs. The data tables are accessible and I can extract data from them. These are none sys tables.

Processed 12776 pages for database 'ABC123', file 'ABC123.mdf' on file 1.
Processed 2 pages for database 'ABC123', file 'ABC123.ldf' on file 1.
RESTORE DATABASE successfully processed 12778 pages in 2.130 seconds (46.867 MB/sec).

Question: can i safely say that the data in those none sys tables are valid and the database does not need to be COMPLETELY recovered?

The background to this is that, when i am not using the NORECOVERY option, the operation fails midway through the upgrading processes. Below are the logs

5 percent processed.
10 percent processed.
15 percent processed.
20 percent processed.
25 percent processed.
30 percent processed.
35 percent processed.
40 percent processed.
45 percent processed.
50 percent processed.
55 percent processed.
60 percent processed.
65 percent processed.
70 percent processed.
75 percent processed.
80 percent processed.
85 percent processed.
90 percent processed.
95 percent processed.
100 percent processed.
Processed 12776 pages for database 'ABC123', file 'ABC123.mdf' on file 1.
Processed 2 pages for database 'ABC123', file 'ABC123.ldf' on file 1.

Converting database 'ABC123' from version 852 to the current version 957.
Database 'ABC123' running the upgrade step from version 852 to version 853.
Database 'ABC123' running the upgrade step from version 853 to version 854.
Database 'ABC123' running the upgrade step from version 854 to version 855.
Database 'ABC123' running the upgrade step from version 855 to version 856.
Database 'ABC123' running the upgrade step from version 856 to version 857.
Database 'ABC123' running the upgrade step from version 857 to version 858.
Database 'ABC123' running the upgrade step from version 858 to version 859.
Database 'ABC123' running the upgrade step from version 859 to version 860.
Database 'ABC123' running the upgrade step from version 860 to version 861.
Database 'ABC123' running the upgrade step from version 861 to version 862.
Database 'ABC123' running the upgrade step from version 862 to version 863.
Database 'ABC123' running the upgrade step from version 863 to version 864.
Database 'ABC123' running the upgrade step from version 864 to version 865.
Database 'ABC123' running the upgrade step from version 865 to version 866.
Database 'ABC123' running the upgrade step from version 866 to version 867.
Database 'ABC123' running the upgrade step from version 867 to version 868.
Database 'ABC123' running the upgrade step from version 868 to version 869.
Database 'ABC123' running the upgrade step from version 869 to version 875.
Database 'ABC123' running the upgrade step from version 875 to version 876.
Database 'ABC123' running the upgrade step from version 876 to version 877.
Database 'ABC123' running the upgrade step from version 877 to version 878.
Database 'ABC123' running the upgrade step from version 878 to version 879.
Database 'ABC123' running the upgrade step from version 879 to version 880.
Database 'ABC123' running the upgrade step from version 880 to version 881.
Database 'ABC123' running the upgrade step from version 881 to version 882.
Database 'ABC123' running the upgrade step from version 882 to version 883.
Database 'ABC123' running the upgrade step from version 883 to version 884.
Database 'ABC123' running the upgrade step from version 884 to version 885.
Database 'ABC123' running the upgrade step from version 885 to version 886.
Database 'ABC123' running the upgrade step from version 886 to version 887.
Database 'ABC123' running the upgrade step from version 887 to version 888.
Database 'ABC123' running the upgrade step from version 888 to version 889.
Database 'ABC123' running the upgrade step from version 889 to version 890.
Database 'ABC123' running the upgrade step from version 890 to version 891.
Database 'ABC123' running the upgrade step from version 891 to version 892.
Database 'ABC123' running the upgrade step from version 892 to version 893.
Database 'ABC123' running the upgrade step from version 893 to version 894.
Database 'ABC123' running the upgrade step from version 894 to version 895.
Database 'ABC123' running the upgrade step from version 895 to version 896.
Database 'ABC123' running the upgrade step from version 896 to version 897.
Database 'ABC123' running the upgrade step from version 897 to version 898.
Database 'ABC123' running the upgrade step from version 898 to version 899.
Database 'ABC123' running the upgrade step from version 899 to version 900.
Database 'ABC123' running the upgrade step from version 900 to version 901.
Database 'ABC123' running the upgrade step from version 901 to version 902.
Database 'ABC123' running the upgrade step from version 902 to version 903.
Database 'ABC123' running the upgrade step from version 903 to version 904.
Database 'ABC123' running the upgrade step from version 904 to version 905.
Database 'ABC123' running the upgrade step from version 905 to version 906.
Database 'ABC123' running the upgrade step from version 906 to version 907.
Database 'ABC123' running the upgrade step from version 907 to version 908.
Database 'ABC123' running the upgrade step from version 908 to version 909.
Database 'ABC123' running the upgrade step from version 909 to version 910.
Database 'ABC123' running the upgrade step from version 910 to version 911.
Database 'ABC123' running the upgrade step from version 911 to version 912.
Database 'ABC123' running the upgrade step from version 912 to version 913.
Database 'ABC123' running the upgrade step from version 913 to version 914.
Database 'ABC123' running the upgrade step from version 914 to version 915.
Database 'ABC123' running the upgrade step from version 915 to version 916.
Database 'ABC123' running the upgrade step from version 916 to version 917.
Database 'ABC123' running the upgrade step from version 917 to version 918.
Database 'ABC123' running the upgrade step from version 918 to version 919.
Database 'ABC123' running the upgrade step from version 919 to version 920.
Database 'ABC123' running the upgrade step from version 920 to version 921.
Database 'ABC123' running the upgrade step from version 921 to version 922.
Database 'ABC123' running the upgrade step from version 922 to version 923.
Database 'ABC123' running the upgrade step from version 923 to version 924.
Database 'ABC123' running the upgrade step from version 924 to version 925.
Database 'ABC123' running the upgrade step from version 925 to version 926.
Database 'ABC123' running the upgrade step from version 926 to version 927.
Database 'ABC123' running the upgrade step from version 927 to version 928.
Database 'ABC123' running the upgrade step from version 928 to version 929.
Database 'ABC123' running the upgrade step from version 929 to version 930.
Database 'ABC123' running the upgrade step from version 930 to version 931.
Database 'ABC123' running the upgrade step from version 931 to version 932.
Database 'ABC123' running the upgrade step from version 932 to version 933.
Database 'ABC123' running the upgrade step from version 933 to version 934.
Database 'ABC123' running the upgrade step from version 934 to version 935.
Database 'ABC123' running the upgrade step from version 935 to version 936.
Database 'ABC123' running the upgrade step from version 936 to version 937.
Database 'ABC123' running the upgrade step from version 937 to version 938.
Database 'ABC123' running the upgrade step from version 938 to version 939.
Database 'ABC123' running the upgrade step from version 939 to version 940.
Database 'ABC123' running the upgrade step from version 940 to version 941.
Database 'ABC123' running the upgrade step from version 941 to version 942.
Database 'ABC123' running the upgrade step from version 942 to version 943.
Database 'ABC123' running the upgrade step from version 943 to version 944.
Database 'ABC123' running the upgrade step from version 944 to version 945.
Database 'ABC123' running the upgrade step from version 945 to version 946.
Database 'ABC123' running the upgrade step from version 946 to version 947.
Database 'ABC123' running the upgrade step from version 947 to version 948.
Database 'ABC123' running the upgrade step from version 948 to version 949.
Database 'ABC123' running the upgrade step from version 949 to version 950.
Database 'ABC123' running the upgrade step from version 950 to version 951.
Database 'ABC123' running the upgrade step from version 951 to version 952.
Database 'ABC123' running the upgrade step from version 952 to version 953.
Database 'ABC123' running the upgrade step from version 953 to version 954.
Database 'ABC123' running the upgrade step from version 954 to version 955.
Database 'ABC123' running the upgrade step from version 955 to version 956.
Database 'ABC123' running the upgrade step from version 956 to version 957.
Msg 1105, Level 17, State 2, Server 3175be0690ba, Line 1
Could not allocate space for object 'sys.sysschobjs'.'nc2' in database 'ABC123' because the 'PRIMARY' filegroup is full due to lack of storage space or database files reaching the maximum allowed size. Note that UNLIMITED files are still limited to 16TB. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Msg 3013, Level 16, State 1, Server 3175be0690ba, Line 1
RESTORE DATABASE is terminating abnormally.

Erland Sommarskog 124.9K Reputation points MVP Volunteer Moderator

2025-08-22T21:57:20.0866667+00:00

This sounds strange. If you restore a database like that, you should not be able to query any tables. This is the error message I would expect you to get:

Msg 927, Level 14, State 2, Line 69 Database 'ABC123' cannot be opened. It is in the middle of a restore.

And also, in this particular case, the database apparently comes from an older version of SQL Server that you are running. This is why you see all these upgrade steps. Without running these upgrade steps, it is even more impossible for your instance of SQL Server to read that database.

So when you say that you can read the data, I think you are reading somewhere else.

So you need to fix that error that causes the RESTORE to fail - that is, you need to get more disk space for your container.

By the way, the purpose of the NORECOVERY option is when you want to apply a differential backup or a log backup after having loaded a full backup.
dave 0 Reputation points

2025-08-25T17:18:57.2+00:00

Apologies - that's correct, and the tables are not available with NORECOVERY. I was meant to modify my question after I posted, but I was not able to.

I have a bunch of bak files that i want to restore the data from each. However, some fail to restore due to the error above; i.e., the upgrades are unable to complete the steps. I have enough space on my machine, so I am not sure why it fails. However, I discovered that the MDF file is capped, and since the upgrades require more space, they fail.

After the failure, a database is created that I can see the tables and query the data. However, I am unsure if I can trust this data or not. (I only need the data, and after that, I will need to discard the DB and the server in the container as well.) Since the restore operation fails during upgrades, can I assume that these upgrades will not affect the data itself, but only the metadata and some other system tables that i do not care about?

1 answer

Your answer

Erland Sommarskog 124.9K Reputation points MVP Volunteer Moderator

2025-08-22T21:57:20.0866667+00:00

This sounds strange. If you restore a database like that, you should not be able to query any tables. This is the error message I would expect you to get:

Msg 927, Level 14, State 2, Line 69 Database 'ABC123' cannot be opened. It is in the middle of a restore.

And also, in this particular case, the database apparently comes from an older version of SQL Server that you are running. This is why you see all these upgrade steps. Without running these upgrade steps, it is even more impossible for your instance of SQL Server to read that database.

So when you say that you can read the data, I think you are reading somewhere else.

So you need to fix that error that causes the RESTORE to fail - that is, you need to get more disk space for your container.

By the way, the purpose of the NORECOVERY option is when you want to apply a differential backup or a log backup after having loaded a full backup.
dave 0 Reputation points

2025-08-25T17:18:57.2+00:00

Apologies - that's correct, and the tables are not available with NORECOVERY. I was meant to modify my question after I posted, but I was not able to.

I have a bunch of bak files that i want to restore the data from each. However, some fail to restore due to the error above; i.e., the upgrades are unable to complete the steps. I have enough space on my machine, so I am not sure why it fails. However, I discovered that the MDF file is capped, and since the upgrades require more space, they fail.

After the failure, a database is created that I can see the tables and query the data. However, I am unsure if I can trust this data or not. (I only need the data, and after that, I will need to discard the DB and the server in the container as well.) Since the restore operation fails during upgrades, can I assume that these upgrades will not affect the data itself, but only the metadata and some other system tables that i do not care about?

Answer 1

Erland Sommarskog 124.9K MVP Volunteer Moderator

To check that I understand this correctly:

You do RESTORE WITH RECOVERY.
This fails during the upgrade.
After this, you can query tables and data.

You would definitely be in an unsupported situation, and you could run into funny situations where operations fail with mysterious errors because the database format is not what SQL Server thinks it should be.

One thing you could try is to raise the cap of the mdf file, and then detach the database to then re-attach it. Maybe SQL Server then detects that the format is wrong and runs the remaining upgrade steps.

Else I think you should try to install the same SQL Server version of the source, restore the database on that version, raise the cap, take a new backup and restore on the version you want to work with.

Then again, if you are only going to run queries in the database and don't create any objects, you could take your chances and work with you have now. But be prepared for surprises.

And, oh, by the way, if the plan is to bring back the database to the customer, you should definitely run on the same version of SQL Server as they have. You cannot restore a backup from a higher version of SQL Server on a lower.

dave 0 Reputation points

2025-08-26T17:33:15.5233333+00:00
I think the detach-and-reattach hack did it!

I restored using the below and got the error

RESTORE DATABASE [ABC] FROM DISK = 'ABC.bak' WITH MOVE 'ABC.mdf' TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\ABC.mdf', MOVE 'ABC.ldf' TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\ABC.ldf', REPLACE, STATS = 5;

then checked the produced mdf and ldf files having 102400KB and 32448 KB sizes, respectively

then removed the cap from the mdf using

ALTER DATABASE [ABC] MODIFY FILE ( NAME = N'ABC.mdf', MAXSIZE = UNLIMITED );

4.then detached the db using

EXEC sp_detach_db 'ABC';

5.then reattached using the below and realized the sizes increased: mdf to 103424 KB, and ldf stayed the same - so looks like the upgrades resumed and hence the increased size of the mdf

CREATE DATABASE [ABC] ON ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\ABC.mdf' ), ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\ABC.ldf' ) FOR ATTACH;

To validate the procedure, I repeated the above steps, skipping the step of increasing/removing the cap, and found that when I attempt to reattach, the original error (Could not allocate space for object 'sys.sysschobjs'.'nc2' in database) reappears.

Thanks for your help
Erland Sommarskog 124.9K Reputation points MVP Volunteer Moderator

2025-08-26T21:21:42.4933333+00:00

That's great to hear!

I took the liberty to rearrange the thread so that my last post is the only Answer, and your very useful post that proves the theory as a comment. If you want to accept my answer as the Answer to your original post, please do so.
Erland Sommarskog 124.9K Reputation points MVP Volunteer Moderator

2025-08-27T21:02:13.0733333+00:00
By the way, you can run this query to verify the database version:

SELECT databasepropertyex(name, 'Version'), name FROM sys.databases

It should be the same for all databases!

Share via

Can I safely use NORECOVERY option when restoring a database by a BAK file?

1 answer

Your answer