Round out --use scenario (add "use" sub-command, add local file, add .bacpac / .mdf & .ldf / compressed file support)

[stuartpa]

DRAFT: Not ready for code review yet.

---

This PR rounds out the "use" scenario for containers, to allow the casual user who doesn't know containers/linux well, to be able to get going quickly with already existing databases:

1. --use now supports local and remote files sqlcmd create mssql --use [<local file>|<remote file>]

The T/SQL database name by default becomes the --use file name (without extension). This can be overridden with a ",", e.g.

sqlcmd create mssql --use https://aka.ms/AdventureWorksLT.bak,adventure_works

This will result in the restored database being named [adventure_works] instead of [AdventureWorksLT]

2. --use now supports .mdf/.bacpac and .bak files sqlcmd create mssql --use [.mdf | .bacpac | *.bak]

3. --use now supports compressed file formats (this also allows a .mdf/.ldf file pair) sqlcmd create mssql --use [.7z | .zip | .tar] # containing any of the above (inc. a .mdf/.ldf pair)

4. the new sqlcmd use subcommand allows all of the above, to add a database to an already existing container sqlcmd use [<local file>|<remote file>]

This PR also enables the open sub-command e.g. sqlcmd open ads to be leveraged directly in sqlcmd create, e.g.

sqlcmd create mssql --use https://aka.ms/AdventureWorksLT.bak --open ads --open-file https://aka.ms/AdventureWorksLT.sql

This enables a T/SQL repro (including database and T/SQL script file) to be shared in a single command line

Examples:

sqlcmd install mssql --accept-eula

# Restore a backup (override database name to be [adventure_works], instead of [AdventureWorksLT])
sqlcmd use https://aka.ms/AdventureWorksLT.bak,adventure_works
sqlcmd use c:\Users\alias\Downloads\AdventureWorksLT.bak,adventure_works

# Restore a Dacfx .bacpac
sqlcmd use https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImportersDW-Full.bacpac

# Attach a compressed .mdf/.ldf
sqlcmd use https://downloads.brentozar.com/StackOverflow2010.7z

[shueybubbles]

What about supporting non-public URLs (SAS etc)? Does it download the file then discover its type similar to how you'd have to examine a zip to know its content?

[shueybubbles]

Could you create a couple mermaid diagrams in the wiki? One would show the relationship between components like ingest and extract and container, and one would show how the contents of the bak/ldf/dacpac file would move from their source to the database for each environment we support (sql in a container, sql on localhost, sql on a remote host, sql in the cloud).

graph TD;
a -- label --> b

[stuartpa]

What about supporting non-public URLs (SAS etc)? Does it download the file then discover its type similar to how you'd have to examine a zip to know its content?

Doesn't the non-pupil SAS URL contain the filename still (before the ?), e.g.:

https://blahblah.blob.core.windows.net/database/AdventureWorks.bak?sv=2021-10-04&st=2023-04-07T06%3A12%3A15Z&se=2023-04-08T06%3A12%3A15Z&sr=b&sp=r&sig=<REDACTED>

Are there URL types that don't container the filename (e.g .bak or .7z?)

[shueybubbles]

A URL can be anything. eg "aka.ms/ssmsfullsetup" is a pointer to an EXE.

---

In reply to: 1499995103

[stuartpa]

This will work. If the mechanism can't be determined from the file extension, then the --use-mechanism flag can be used (restore, dacfx, attach etc.). I'll write unit tests around all these scenarios

---

In reply to: 1499460199

[stuartpa]

sure

---

In reply to: 1499673629

[stuartpa]

There is a --use-mechanism flag, that is required if we can't determine the mechanism from the file extension.

---

In reply to: 1500333712