SQL Server Staging and MSSQL Upgrade

[degauser]

Hi all.

In my environment I've a VM that runs MSSQL 16 with 3 databases. My Veeam Server's MSSQL is SQL Express 2012.

What I want to achieve is restore database items like a table or record but now whole database. So that's called SQL Server Staging right? https://helpcenter.veeam.com/docs/backu ... l?ver=95u4

Everytime I tried to stage, I got this error:



Reason is obivious. Therefore I've to upgrade SQL Express 2012. But what should I do before upgrading? I've got a few questions about this process.

[*]I've backed up my Veeam configuration. But do I have to backup the Veeam database from SQL Express too?
[*]After upgrading to SQL Express 2016, are there any further things for me to do? Like editing some connection settings or Veeam database migrations etc.
[*]What will happen my currently running jobs (like a full backup job or transactional log backup jobs) during upgrade?
[*]What is the proper steps to upgrade?
[*]Is Veeam still shippingwith SQL Express 2012? If so, why?


Another problem is I've a database over 10 GB so SQL Express 2016 won't cut it. Therefore I'll upgrade to a enterprise version of SQL Server. I've all the questions above for this scenario too (except the last one of course).

Regards.

[HannesK]

Hello,
and welcome to the forums.

yes, there are some operations that require a staging server. But in most situations (e.g. normal database restore), you don't need it. Well, restore of schema (your table restore) is one situation where you need it.

Please keep in mind that you don't need to use the Veeam SQL server as staging server. You can also use a remote staging server.

1) you can do both. But configuration backup is fine. It includes everything from the SQL database (keep in mind to enable encryption. otherwise it will not backup the passwords for security reasons)
2) as long as you only upgrade, no changes are required
3) all veeam services must be stopped during upgrade of the database
4) Microsoft should have a documentation. I don't remember anything specific when I did it some years ago
5) No. It ships with 2016. But old installations are not upgraded.

Enterprise edition of SQL is not required. Standard is also fine

Best regards,
Hannes

[degauser]

Thanks for your answer.

Restoring a full database (size approx 1TB) takes up to 10-12 hours. If I'd be able to restore a single table with staging, how much time should I expect to gain?

[HannesK]

hmm, I have no idea how I should seriously estimate that. Even if you have me the size of your table, it would be impossible to make a serious estimation.

[degauser]

The point that I've asked that question, I was curious about table restore process.

For example, if a table restore requires to restore whole database for once and than do some changes and push new database to server, that would mean nothing to me. I need to be able to restore single object without waiting full restoration time.

[HannesK]

it won't restore the full database before. so you can expect it to be faster than a full database restore.