SET NOCOUNT ON;

DECLARE @DatabaseName sysname;
DECLARE @BackupFileName nvarchar(260);

DECLARE @DataPath nvarchar(4000);
DECLARE @LogPath nvarchar(4000);
DECLARE @BackupPath nvarchar(4000);

DECLARE @BackupFile nvarchar(4000);
DECLARE @DataFile nvarchar(4000);
DECLARE @LogFile nvarchar(4000);

DECLARE @SQL nvarchar(max);

/*
    Alleen deze twee waarden aanpassen.
*/
SET @DatabaseName = N'UpdateXXXXXX';
SET @BackupFileName = N'updateXXXXXX.bak';


/*
    Standaard data- en logpaden van de SQL Server-instance.
*/
SET @DataPath =
    CONVERT(nvarchar(4000),
            SERVERPROPERTY('InstanceDefaultDataPath'));

SET @LogPath =
    CONVERT(nvarchar(4000),
            SERVERPROPERTY('InstanceDefaultLogPath'));


/*
    Standaard backuppad van SQL Server 2017.
*/
EXEC master.dbo.xp_instance_regread
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'BackupDirectory',
    @BackupPath OUTPUT;


/*
    Controleren of alle paden gevonden zijn.
*/
IF NULLIF(@DataPath, N'') IS NULL
BEGIN
    RAISERROR(
        'Het standaard datapad van de SQL Server-instance is niet gevonden.',
        16, 1
    );
    RETURN;
END;

IF NULLIF(@LogPath, N'') IS NULL
BEGIN
    RAISERROR(
        'Het standaard logpad van de SQL Server-instance is niet gevonden.',
        16, 1
    );
    RETURN;
END;

IF NULLIF(@BackupPath, N'') IS NULL
BEGIN
    RAISERROR(
        'Het standaard backuppad van de SQL Server-instance is niet gevonden.',
        16, 1
    );
    RETURN;
END;


/*
    Indien nodig een backslash aan de paden toevoegen.
*/
IF RIGHT(@DataPath, 1) NOT IN (N'\', N'/')
    SET @DataPath = @DataPath + N'\';

IF RIGHT(@LogPath, 1) NOT IN (N'\', N'/')
    SET @LogPath = @LogPath + N'\';

IF RIGHT(@BackupPath, 1) NOT IN (N'\', N'/')
    SET @BackupPath = @BackupPath + N'\';


/*
    Volledige bestandsnamen samenstellen.
*/
SET @BackupFile =
    @BackupPath + @BackupFileName;

SET @DataFile =
    @DataPath + @DatabaseName + N'.mdf';

SET @LogFile =
    @LogPath + @DatabaseName + N'_log.ldf';


/*
    Niet per ongeluk een bestaande database overschrijven.
*/
IF DB_ID(@DatabaseName) IS NOT NULL
BEGIN
    RAISERROR(
        'Database %s bestaat al. De restore is niet uitgevoerd.',
        16, 1, @DatabaseName
    );
    RETURN;
END;


/*
    Tonen welke bestanden gebruikt gaan worden.
*/
SELECT
    @DatabaseName AS DatabaseName,
    @BackupFile AS BackupFile,
    @DataFile AS DataFile,
    @LogFile AS LogFile;


/*
    Eerst controleren of de backup leesbaar is.
*/
SET @SQL =
    N'RESTORE VERIFYONLY ' +
    N'FROM DISK = N''' +
    REPLACE(@BackupFile, N'''', N'''''') +
    N''';';

EXEC sys.sp_executesql @SQL;


/*
    Database herstellen.

    zwork en zwork_log zijn de logische namen
    van de bestanden in de backup.
*/
SET @SQL =
    N'RESTORE DATABASE ' + QUOTENAME(@DatabaseName) +
    N' FROM DISK = N''' +
    REPLACE(@BackupFile, N'''', N'''''') +
    N''' WITH ' +
    N'MOVE N''zwork'' TO N''' +
    REPLACE(@DataFile, N'''', N'''''') +
    N''', ' +
    N'MOVE N''zwork_log'' TO N''' +
    REPLACE(@LogFile, N'''', N'''''') +
    N''', ' +
    N'RECOVERY, STATS = 10;';

EXEC sys.sp_executesql @SQL;
