SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** Object:  SQL_STORED_PROCEDURE  prc_save_text_to_file       Script Date: 2026-05-15 19:31:09.403 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[prc_save_text_to_file]')) DROP PROCEDURE [dbo].[prc_save_text_to_file];
GO


CREATE PROCEDURE [dbo].[prc_save_text_to_file] (
  @text VARCHAR(MAX), 
  @filename VARCHAR(265))
AS
BEGIN
/*
SQLServer Stored Procedure

PURPOSE
Write text variable to ansi file.

OLE Automation Procedures has to be enabled.
Existing files will be overwritten.
If path does not exist, no error is raised. Check path before with function fun_FolderExist.
If file could not be created, no error is raised. Check file afterwards with function fun_FileExist.
Character set Windows-1252 is used, 1 byte per character.
Character set Windows-1252 includes ascii extended characters dec 128-159, except dec 129, 141, 143, 144 and 157.
Character set iso-8859-1 does not handle ascii extended characters dec 128-159 in a proper way, so this character set is unusable here.

Parameters
1 @text       VARCHAR(MAX)  = (nonbinary) text
2 @filename   VARCHAR(265)  = filespec

HISTORY
2017-01-25 - Created procedure
2017-01-31 - iso-8859-1 (Latin-1) character set is used
2025-06-06 - Windows-1252 character set is used

TAGS
<program>
  <description>Write text variable to ansi file</description>
  <generic>1</generic>
  <author>Gerrit Mantel</author>
  <created>2017-01-25</created>
  <lastmodified>2025-06-06</lastmodified>
</program>
*/
  DECLARE @objtoken INT

  EXEC sp_OACreate 'ADODB.Stream', @objtoken OUTPUT;
  EXEC sp_OASetProperty @objtoken, 'Type', 2;
  EXEC sp_OASetProperty @objtoken, 'ContentType', 'text/plain'
  EXEC sp_OASetProperty @objtoken, 'Encoding', 'quoted-printable'
  EXEC sp_OASetProperty @objtoken, 'Charset', 'Windows-1252'
  EXEC sp_OAMethod @objtoken, 'Open';
  EXEC sp_OAMethod @objtoken, 'WriteText', NULL, @text;
  EXEC sp_OAMethod @objtoken, 'SaveToFile', NULL, @filename, 2;
  EXEC sp_OAMethod @objtoken, 'Close';
  EXEC sp_OADestroy @objtoken;
END


GO

/****** Object:  SQL_STORED_PROCEDURE  prc_dump_survive_script       Script Date: 2026-05-15 19:31:09.403 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[prc_dump_survive_script]')) DROP PROCEDURE [dbo].[prc_dump_survive_script];
GO

CREATE PROCEDURE [dbo].[prc_dump_survive_script] (
  @dump_path VARCHAR(255) = '')
AS
BEGIN
/*
SQLServer Stored Procedure

PURPOSE
Dump scriptfile to create TMP_SURVIVE database.

The output filename is survive_script.sql and is dumped to the given dump_path.
The dump_path must exist and will be checked.

With the generated script, it is possible to create a temporarily database TMP_SURVIVE,
which holds all necessary procedures to restore user databases from backup files.

PARAMETERS
 - 1 @dump_path VARCHAR(255)   Path to dump file to.

USED OBJECTS
 - dbo.prc_restore_database (SP in local database) + referenced procedures and functions
 - dbo.prc_save_text_to_file (SP in local database)
 - dbo.fun_FolderExist (UDF in local database)
 - sys.all_objects
 - sys.sql_modules
 - sys.sql_expression_dependencies

SQLSERVER CONFIGURATION
 - OLE Automation has to be enabled.

EXAMPLES
EXEC dbo.prc_dump_survive_script @dump_path='C:\Data\MSSQL\survive'

HISTORY
2026-04-09 - Created procedure
2026-04-10 - Renamed procedure
           - Changed output filename
2026-05-12 - Bug fix: Included wrong procedure name in survive_script

TAGS
<program>
  <description>Dump scriptfile to create SURVIVE database</description>
  <generic>0</generic>
  <minversion>12</minversion>
  <author>Gerrit Mantel</author>
  <created>2026-04-09</created>
  <lastmodified>2026-05-12</lastmodified>
</program>
*/
  SET NOCOUNT ON

  DECLARE @message VARCHAR(512)
  DECLARE @crlf VARCHAR(2)
  DECLARE @body NVARCHAR(MAX)
  DECLARE @dump_file VARCHAR(265)

  DECLARE @programs TABLE (
    [name] NVARCHAR(128),
    [object_id] INT,
    [definition] NVARCHAR(MAX),
    [referenced] BIT)
  DECLARE @name NVARCHAR(128)
  DECLARE @definition NVARCHAR(MAX)

  SET @crlf = CHAR(13)+CHAR(10);

  -- Dump file name
  SET @dump_file = @dump_path + '\survive_script.sql';

  -- Get main programs
  INSERT INTO @programs ([name], [object_id], [definition], [referenced])
  SELECT t1.[name], t1.[object_id], t2.[definition], 0
  FROM sys.all_objects t1
  INNER JOIN sys.sql_modules t2 ON t2.[object_id] = t1.[object_id]
  WHERE t1.[name] IN ('prc_restore_database','prc_restore_userdb_script_only');

  -- Get referenced programs
  INSERT INTO @programs ([name], [object_id], [definition], [referenced])
  SELECT DISTINCT t3.[Name], t3.[object_id], t4.[definition], 1
  FROM @programs t1
  INNER JOIN sys.sql_expression_dependencies t2 ON t2.[referencing_id] = t1.[object_id]
  INNER JOIN sys.all_objects t3 ON t3.[object_id] = t2.[referenced_id]
  INNER JOIN sys.sql_modules t4 ON t4.[object_id] = t3.[object_id];

  -- Make script
  SET @body = N'SET ANSI_NULLS ON' + @crlf + 'GO' + @crlf+
  'SET QUOTED_IDENTIFIER ON' + @crlf + 'GO' + @crlf + @crlf +
  'CREATE DATABASE [TMP_SURVIVE]' + @crlf + 'GO' + @crlf+
  'USE [TMP_SURVIVE]' + @crlf+ 'GO' + @crlf;
  
  DECLARE my_cur1 CURSOR LOCAL READ_ONLY FOR
    SELECT [name], [definition]
    FROM @programs
    ORDER BY [referenced] DESC, [name] ASC;

  OPEN my_cur1
  WHILE 1 < 2
  BEGIN
    FETCH NEXT FROM my_cur1 INTO @name, @definition
    IF @@FETCH_STATUS <> 0 BREAK

    SET @body = @body + @definition + @crlf + 'GO' + @crlf + @crlf;
  END
  CLOSE my_cur1;
  DEALLOCATE my_cur1;
  
  -- Dump script
  EXEC [dbo].[prc_save_text_to_file] @body, @dump_file;
END
GO