Docker SQL backup

To back up and migrate your SQL Server database running in a Docker container, you can follow these steps:

1. Take a Backup of the Database

Access the SQL Server Container:
First, you'll need to access the running Docker container:
```
docker exec -it <container_name> /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "Pass@word"
```
Replace <container_name> with the actual name or ID of your SQL Server container.
Run the Backup Command:
Once inside the SQL Server prompt, run the following command to back up your database:
```
BACKUP DATABASE [YourDatabaseName] 
TO DISK = N'/var/opt/mssql/backup/YourDatabaseName.bak' 
WITH NOFORMAT, NOINIT, 
NAME = 'YourDatabaseName-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO
```
Make sure to replace YourDatabaseName with the actual name of your database. This command creates a backup file in the container's /var/opt/mssql/backup directory.
Copy the Backup File to the Host:
After creating the backup, you'll want to copy the .bak file to your host machine:
```
docker cp <container_name>:/var/opt/mssql/backup/YourDatabaseName.bak /path/on/host/YourDatabaseName.bak
```
Replace /path/on/host/ with the path where you want to store the backup file on your host machine.

2. Set Up the New Database Environment

Deploy a New SQL Server Instance:
If you are migrating to a new environment, set up a new Docker container or SQL Server instance similar to the current one.

Example Docker service:

sqldata-new:
  image: mcr.microsoft.com/mssql/server:2019-latest
  environment:
    - SA_PASSWORD=NewPass@word
    - ACCEPT_EULA=Y
  restart: always
  ports:
    - "5434:1433"
  volumes:
    - fsm-sqldata-new:/var/opt/mssql

Copy the Backup File to the New Environment:
Use docker cp to copy the backup file to the new container:

docker cp /path/on/host/YourDatabaseName.bak <new_container_name>:/var/opt/mssql/backup/YourDatabaseName.bak

Restore the Database:
Access the new SQL Server container and restore the database:

docker exec -it <new_container_name> /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "NewPass@word"

Then, run the restore command:

RESTORE DATABASE [YourDatabaseName] 
FROM DISK = N'/var/opt/mssql/backup/YourDatabaseName.bak' 
WITH FILE = 1, 
MOVE N'YourDatabaseName' TO N'/var/opt/mssql/data/YourDatabaseName.mdf', 
MOVE N'YourDatabaseName_log' TO N'/var/opt/mssql/data/YourDatabaseName_log.ldf', 
NOUNLOAD, STATS = 5;
GO

Ensure that the paths for .mdf and .ldf are correct for your new environment.

3. Verify the Migration

Connect to the New Database:
Ensure that the database is accessible from your web application or other clients, and verify that all data has been correctly restored.
Update Application Configuration:
If needed, update your application configuration to point to the new database instance.

By following these steps, you should be able to back up your SQL Server database from one Docker container and restore it in a new environment.