question

KevinHarris-8196 avatar image
0 Votes"
KevinHarris-8196 asked KevinHarris-8196 commented

Why Can't I get sql 2019 to restore from backup files

When we do the restore for the SQL 2019 DB, when selecting the device and going to the DB Backup file and add it. it says no Backup set selected. what could we be doing wrong. It worked great for a long time , but now noting. And we have tried different DB BU files and even ones i have use to restore before.

sql-server-general
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Can you post some screenshots with the error?

0 Votes 0 ·

I get them as soon as i can

0 Votes 0 ·

Rather than using the UI, why not use a T-SQL command directly?

0 Votes 0 ·

Not really sure on how to do that. Sorry newby here

0 Votes 0 ·
Yufeishao-msft avatar image
0 Votes"
Yufeishao-msft answered KevinHarris-8196 commented

Hi @KevinHarris-8196

It's best to show the error screenshot to locate the error
For your description, No Backupset Selected to be Restore maybe because:
119751-1.png

1、you are trying to restore your database to a lower version of sql server

2、The backup file is unreadable or corrupted,you can use the following script to confirm
119697-2.png

3、it may also be the permission of your account, please check your account permissions,

https://stackoverflow.com/questions/12119891/no-backupset-selected-to-be-restored-sql-server-2012



1.png (9.7 KiB)
2.png (1.7 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes this is exactly the problem its doing in your pic above. Saying no backupset. I use the BD.bak files in test BD's we have and they work so don't think they are corrupt. These Backups are coming from a 2008r2 SQL to a 2019 version. But have been working fine before a couple weeks ago, then just started to this. I will do # 2 and 3 to see if this is a fix thanks you!

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

The simple version of RESTORE is:

RESTORE DATABASE db FROM DISK = 'C:\somepath\somefile.bak'

If you are taking a copy of a database from a different server, the path for the files may not work, and in this case, you get an error. In this case, you need to use the WITH MOVE clause:

RESTORE DATABASE db FROM DISK = 'C:\somepath\somefile.bak'
WITH MOVE 'db' TO 'D:\somdatapath\db.mdf',
    MOVE 'db_log' TO 'D:\somelogpath\db.ldf',

In this command 'db' is the logical name of the data file. This often the same as the database name, but not always. 'db_log' is the logical name of the log file, and again this is often the database name with _log tacked on, but not always.

If these names do not work, you can use this command:

RESTORE FILELISTONLY FROM DISK = 'C:\somepath\somefile.bak'

And you will have the logical files in the first column.

When it comes to the paths for the database files (those that follows TO), you may or may not where to put them. When in doubt, run sp_helpdb on another database and copy the paths for this database and changes. The file-name part should preferably agree with the database name and with .mdf for the data file and .ldf for the log file.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.