Very cool problem -- with Dymamic SQL, Backups/Restores, and variables (maybe)

  • This is a really cool solution to a common problem: this script auto-generates exactly the right RESTORE commands, so you can restore a database from *all* the backup files without having to manually figure everything out. But I really want to save the result to a text file, and I can't figure out how to do it. I get the error Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    Here's an example of the (extremely helpful!) output. I would like to write this to a text file and save it with the backup files once a day, so it is available even if msdb gets corrupted...

    RESTORE DATABASE [AdventureWorksDW] FROM DISK = 'C:\SQLBackups\AdventureWorksDW\AdventureWorksDW_backup_2011_08_08_000503_5033627.bak' WITH FILE = 1, NORECOVERY

    RESTORE DATABASE [AdventureWorksDW] FROM DISK = 'C:\SQLBackups\AdventureWorksDW\AdventureWorksDW_backup_2011_08_08_130050_5752615.bak' WITH FILE = 1, NORECOVERY

    RESTORE LOG [AdventureWorksDW] FROM DISK = 'C:\SQLBackups\AdventureWorksDW\AdventureWorksDW_backup_2011_08_08_130222_6029775.trn' WITH FILE = 1, NORECOVERY

    RESTORE LOG [AdventureWorksDW] FROM DISK = 'C:\SQLBackups\AdventureWorksDW\AdventureWorksDW_backup_2011_08_09_000505_6410167.trn' WITH FILE = 1, NORECOVERY

    RESTORE LOG [AdventureWorksDW] FROM DISK = 'C:\SQLBackups\AdventureWorksDW\AdventureWorksDW_backup_2011_08_09_103703_3337679.trn' WITH FILE = 1, NORECOVERY

    RESTORE LOG [AdventureWorksDW] FROM DISK = 'C:\SQLBackups\AdventureWorksDW\AdventureWorksDW_backup_2011_08_10_000506_7147955.trn' WITH FILE = 1, NORECOVERY

    RESTORE DATABASE [AdventureWorksDW] WITH RECOVERY

    Here's the script, just copy and paste into Management Studio and run it. There are 3 options near the bottom, I commented out the second two options so you can just run it and see how useful the output is!

    Any help appreciated... thanks!

    /*

    This is adapted from this article http://www.mssqltips.com/tip.asp?tip=1243

    in the Next Steps section where it says:

    "Here is another version from Chris Gelhaus. This updated version handles

    differential backups as well as multiple backups written to the same file."*/

    SET NOCOUNT ON

    DECLARE @backup_set_id_Full INT

    DECLARE @Full_Backup_Set_Date DATETIME

    DECLARE @backup_set_id_Diff INT

    DECLARE @backup_set_id_Tlog INT

    DECLARE @backup_set_id_end INT

    DECLARE @Last_Backup_Set_Date DATETIME

    DECLARE @databaseName SYSNAME

    DECLARE @RestoreString NVARCHAR(MAX)

    SET @databasename = 'AdventureWorksDW'-- <--Change the target database here.

    -- Get the ID of the most recent full backup for the database

    SELECT @backup_set_id_Full = MAX(backup_set_id)

    FROM msdb.dbo.backupset

    WHERE database_name = @databaseName

    AND type = 'D'

    -- Get the ID for the most recent DIFF backup (if there is one)

    SELECT @backup_set_id_Diff = MAX(backup_set_id)

    FROM msdb.dbo.backupset

    WHERE database_name = @databaseName

    AND type = 'I'

    AND backup_set_id > (@backup_set_id_Full)

    -- If no DIFF backup exists, then set the DIFF ID to the full backup ID

    IF @backup_set_id_Diff IS NULL

    BEGIN

    SET @backup_set_id_Diff = @backup_set_id_Full

    END

    ELSE

    BEGIN

    -- Set the Last Backup Date to the most recent Differential

    SET @Last_Backup_Set_Date = @Full_Backup_Set_Date

    END

    -- Set a maximum backup set ID to make sure this is at the bottom of the list

    SET @backup_set_id_end = 999999999

    --DROP the temp table if it already exists.

    IF OBJECT_ID('[tempdb].[dbo].[#RestoreCommands]',N'U') IS NOT NULL

    DROP TABLE [dbo].#RestoreCommands

    --I don't know how else to do this -- we need *only* the final set of restore commands, and they *must* be in order

    --(which is done by ORDER BY backup_set_id), but we do not want to return either the backup_set_id or Backup Type columns.

    --So we insert into the temp table, using an ORDER BY clause, then just do a SELECT from the temp table.

    CREATE TABLE [dbo].#RestoreCommands

    (

    [RestoreCommands_ID] INT IDENTITY(1,1) NOT NULL,

    [BackupSetID] INT,

    [BackupType] NVARCHAR(200),

    [RestoreCommand] NVARCHAR(1000) NULL

    )

    --This actually inserts the entire set of restore commands into the temporary table.

    INSERT INTO #RestoreCommands

    --This is the main select that UNIONs all the statements (from that mssqltips article)...

    SELECT

    backup_set_id,

    'Full' AS 'Backup Type',

    'RESTORE DATABASE [' + @databaseName + '] FROM DISK = ''' + mf.physical_device_name + ''' WITH ' + 'FILE = ' + convert(varchar(10), b.position) + ', NORECOVERY' AS 'SQL Restore commands'

    FROM msdb.dbo.backupset b, msdb.dbo.backupmediafamily mf

    WHERE b.media_set_id = mf.media_set_id

    AND b.database_name = @databaseName

    AND b.backup_set_id = @backup_set_id_Full

    UNION

    SELECT

    backup_set_id,

    'Diff',

    'RESTORE DATABASE [' + @databaseName + '] FROM DISK = ''' + mf.physical_device_name + ''' WITH ' + 'FILE = ' + convert(varchar(10), b.position) + ', NORECOVERY'

    FROM msdb.dbo.backupset b, msdb.dbo.backupmediafamily mf

    WHERE b.media_set_id = mf.media_set_id

    AND b.database_name = @databaseName

    AND b.backup_set_id = @backup_set_id_Diff

    UNION

    SELECT

    backup_set_id,

    'TLog',

    'RESTORE LOG [' + @databaseName + '] FROM DISK = ''' + mf.physical_device_name + ''' WITH ' + 'FILE = ' + convert(varchar(10), b.position) + ', NORECOVERY'

    FROM msdb.dbo.backupset b,

    msdb.dbo.backupmediafamily mf

    WHERE b.media_set_id = mf.media_set_id

    AND b.database_name = @databaseName

    AND b.backup_set_id >= @backup_set_id_Diff AND b.backup_set_id < @backup_set_id_end

    AND b.type = 'L'

    UNION

    SELECT

    999999999 AS backup_set_id,

    'Final RECOVERY command',

    'RESTORE DATABASE [' + @databaseName + '] WITH RECOVERY'

    ORDER BY backup_set_id --This actually sorts the results in the *required* order for a restore, as they are being inserted into the temp table.

    --OPTION 1: This does work, but will not allow you to save to a file:

    SELECT [RestoreCommand] FROM [dbo].#RestoreCommands

    --OPTION 2: This does *not* work, and gives this error:

    -- Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    --SET @RestoreString = ( SELECT [RestoreCommand] FROM [dbo].#RestoreCommands )

    --OPTION 3: This does not generate an error, but it only sets the very last command into the @RestoreString variable:

    --SELECT @RestoreString = [RestoreCommand] FROM [dbo].#RestoreCommands --This sets the variable.

    --SELECT @RestoreString --This shows what the variable holds, but it only holds the last line, not all of the restore commands!

    --This is what I really want to do, save the string to a text file (I just can't get the string into the variable!)...

    DECLARE @cmd sysname

    SET @cmd = 'echo ' + @RestoreString + ' > c:\SavedRestoreCommands.txt'

    EXEC master..xp_cmdshell @cmd

    --Cleanup

    DROP TABLE [dbo].#RestoreCommands

  • Option 2 will never work, it has to return only 1 row. To concatenate you can do something like this, but I would personally just look + print or output to text rather than query.

    SELECT

    T.name,

    STUFF((

    SELECT

    ',' + name

    FROM

    sys.columns C

    WHERE C.object_id = T.object_id

    ORDER BY

    name

    FOR

    XML PATH('')

    ) , 1 , 1 , '') As Columns_

    FROM sys.tables T

    ORDER BY name

  • P.S. The Restore gui can generate that whole restore script using the same tables.

    I love having a 2nd option. My personal choice what to use the file names available on drive to rebuild the script (in case msdb is corrupt or the list doesn't match what's available).

  • jpSQLDude (8/10/2011)


    --This is what I really want to do, save the string to a text file (I just can't get the string into the variable!)...

    The reason you can't set the "string" into a variable is because you're attempting to insert a table into an NVARCHAR. From another viewpoint, it's like you're grabbing an array of values and stamping them into a variable. Of course all you get returned is the last result.

    Try something like this: -

    --EDIT-- Deleted, my mistake.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • How can that possibly work with a local temp table (seriously asking)?

  • Ninja's_RGR'us (8/10/2011)


    How can that possibly work with a local temp table (seriously asking)?

    Indeed, my bad. Either would need a staging table or to sit the entire script where I entered the SELECT.

    Corrected: -

    EXEC master..xp_cmdshell 'osql.exe -S YourServerName -U sa -P yourpassword -Q "

    DECLARE @backup_set_id_Full INT

    DECLARE @Full_Backup_Set_Date DATETIME

    DECLARE @backup_set_id_Diff INT

    DECLARE @backup_set_id_Tlog INT

    DECLARE @backup_set_id_end INT

    DECLARE @Last_Backup_Set_Date DATETIME

    DECLARE @databaseName SYSNAME

    DECLARE @RestoreString NVARCHAR(MAX)

    SET @databasename = ''AdventureWorksDW''-- <--Change the target database here.

    -- Get the ID of the most recent full backup for the database

    SELECT @backup_set_id_Full = MAX(backup_set_id)

    FROM msdb.dbo.backupset

    WHERE database_name = @databaseName

    AND type = ''D''

    -- Get the ID for the most recent DIFF backup (if there is one)

    SELECT @backup_set_id_Diff = MAX(backup_set_id)

    FROM msdb.dbo.backupset

    WHERE database_name = @databaseName

    AND type = ''I''

    AND backup_set_id > (@backup_set_id_Full)

    -- If no DIFF backup exists, then set the DIFF ID to the full backup ID

    IF @backup_set_id_Diff IS NULL

    BEGIN

    SET @backup_set_id_Diff = @backup_set_id_Full

    END

    ELSE

    BEGIN

    -- Set the Last Backup Date to the most recent Differential

    SET @Last_Backup_Set_Date = @Full_Backup_Set_Date

    END

    -- Set a maximum backup set ID to make sure this is at the bottom of the list

    SET @backup_set_id_end = 999999999

    --DROP the temp table if it already exists.

    IF OBJECT_ID(''[tempdb].[dbo].[#RestoreCommands]'',N''U'') IS NOT NULL

    DROP TABLE [dbo].#RestoreCommands

    --I don''t know how else to do this -- we need *only* the final set of restore commands, and they *must* be in order

    --(which is done by ORDER BY backup_set_id), but we do not want to return either the backup_set_id or Backup Type columns.

    --So we insert into the temp table, using an ORDER BY clause, then just do a SELECT from the temp table.

    CREATE TABLE [dbo].#RestoreCommands

    (

    [RestoreCommands_ID] INT IDENTITY(1,1) NOT NULL,

    [BackupSetID] INT,

    [BackupType] NVARCHAR(200),

    [RestoreCommand] NVARCHAR(1000) NULL

    )

    --This actually inserts the entire set of restore commands into the temporary table.

    INSERT INTO #RestoreCommands

    --This is the main select that UNIONs all the statements (from that mssqltips article)...

    SELECT

    backup_set_id,

    ''Full'' AS ''Backup Type'',

    ''RESTORE DATABASE ['' + @databaseName + ''] FROM DISK = '''' + mf.physical_device_name + '''' WITH '' + ''FILE = '' + convert(varchar(10), b.position) + '', NORECOVERY'' AS ''SQL Restore commands''

    FROM msdb.dbo.backupset b, msdb.dbo.backupmediafamily mf

    WHERE b.media_set_id = mf.media_set_id

    AND b.database_name = @databaseName

    AND b.backup_set_id = @backup_set_id_Full

    UNION

    SELECT

    backup_set_id,

    ''Diff'',

    ''RESTORE DATABASE ['' + @databaseName + ''] FROM DISK = '''' + mf.physical_device_name + '''' WITH '' + ''FILE = '' + convert(varchar(10), b.position) + '', NORECOVERY''

    FROM msdb.dbo.backupset b, msdb.dbo.backupmediafamily mf

    WHERE b.media_set_id = mf.media_set_id

    AND b.database_name = @databaseName

    AND b.backup_set_id = @backup_set_id_Diff

    UNION

    SELECT

    backup_set_id,

    ''TLog'',

    ''RESTORE LOG ['' + @databaseName + ''] FROM DISK = '''' + mf.physical_device_name + '''' WITH '' + ''FILE = '' + convert(varchar(10), b.position) + '', NORECOVERY''

    FROM msdb.dbo.backupset b,

    msdb.dbo.backupmediafamily mf

    WHERE b.media_set_id = mf.media_set_id

    AND b.database_name = @databaseName

    AND b.backup_set_id >= @backup_set_id_Diff AND b.backup_set_id < @backup_set_id_end

    AND b.type = ''L''

    UNION

    SELECT

    999999999 AS backup_set_id,

    ''Final RECOVERY command'',

    ''RESTORE DATABASE ['' + @databaseName + ''] WITH RECOVERY''

    ORDER BY backup_set_id --This actually sorts the results in the *required* order for a restore, as they are being inserted into the temp table.

    SELECT [RestoreCommand] FROM [dbo].#RestoreCommands

    --Cleanup

    DROP TABLE [dbo].#RestoreCommands

    " -o "C:\output.txt"'


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Good, I was thinking I was getting senile! 😀

  • Ninja's_RGR'us (8/10/2011)


    P.S. The Restore gui can generate that whole restore script using the same tables.

    I love having a 2nd option. My personal choice what to use the file names available on drive to rebuild the script (in case msdb is corrupt or the list doesn't match what's available).

    I think the GUI option only does that if the database is still Attached, meaning the server still has to be up, the storage has to still be functional, etc. I am trying to prepare for the worst-case scenario.

    My real idea was to have the backup files automatically copied to a remote file share, so even if the entire datacenter goes away, I could restore all my databases to the last TLog backup, very quickly. They have nearly 100 SQL Servers where I'm currently a DBA, and maybe 450 databases total. With all the restore scripts always sitting right with the backup files, restoring all the databases would be so much easier. Yes, that sounds a bit like Log shipping, and we plan to do that for a few of the mission critical databases, but just syncing all backup SAN volumes to a remote share is so much easier than setting up Log Shipping for a zillion databases. So that's why I want text files with the restore scripts sitting right with the backup files.

    So using a script to automatically generate your RESTORE commands from the actual backup files. sounds awesome! Have you seen such a script? How would it handle Diffs?

  • Something like this?

    Full backup (I don't verify here because I do a test restore + checkdb())

    DECLARE @Today DATETIME

    DECLARE @BackupTime DATETIME

    DECLARE @BkName AS VARCHAR(250)

    DECLARE @Cmd NVARCHAR(1000)

    SET @Today = DATEADD(D , 0 , DATEDIFF(D , 0 , GETDATE()))

    SET @BackupTime = DATEADD(n , DATEDIFF(n , @Today , GETDATE()) , @Today)

    SET @BkName = '\PROD-FORDIA-FULL ' + CONVERT(VARCHAR(25) , @BackupTime , 120)

    SET @BkName = 'Q:\Backups SQL' + REPLACE(LEFT(@BkName , LEN(@BkName) - 3) , ':' , 'H') + '.bak'

    BACKUP DATABASE [PROD-FORDIA] TO DISK = @BkName WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM

    Tran logs (I choose every 15 minutes 24 / 7)

    I do verify here because I only test those once / day with a restore / checkdb().

    DECLARE @Today DATETIME

    DECLARE @BackupTime DATETIME

    DECLARE @BkName AS VARCHAR(250)

    DECLARE @Cmd NVARCHAR(1000)

    SET @Today = DATEADD(D , 0 , DATEDIFF(D , 0 , GETDATE()))

    SET @BackupTime = DATEADD(n , DATEDIFF(n , @Today , GETDATE()) , @Today)

    SET @BkName = '\PROD-FORDIA-TransactionLogs ' + CONVERT(VARCHAR(25) , @BackupTime , 120)

    SET @BkName = 'Q:\Backups SQL' + REPLACE(LEFT(@BkName , LEN(@BkName) - 3) , ':' , 'H') + '.bak'

    BACKUP LOG [PROD-FORDIA] TO DISK = @BkName WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM

    DECLARE @backupSetId AS INT

    SELECT @backupSetId = position

    FROM msdb..backupset

    WHERE database_name = N'PROD-FORDIA'

    AND backup_set_id = (

    SELECT MAX(backup_set_id)

    FROM msdb..backupset

    WHERE database_name = N'PROD-FORDIA'

    )

    IF @backupSetId IS NULL

    BEGIN

    RAISERROR ( N'Verify failed. Backup information for database ''PROD-FORDIA'' not found.'

    , 16 , 1 )

    END

    RESTORE VERIFYONLY FROM DISK = @BkName WITH FILE = @backupSetId , NOUNLOAD , NOREWIND

    Then server-sensitive restore (the EXEC() are commented out so you can't shoot yourself in the foot the first time you run this) :

    SET IMPLICIT_TRANSACTIONS OFF

    SET DATEFORMAT YMD

    SET STATISTICS IO OFF

    SET NOCOUNT ON

    --------Instructions----------

    -- SET datetime of restore

    -- You can exclude full backup file if they are corrupted at the line "Backup exclude list". Uncomment the line and enter the full filename to exclude

    -- set query output to text

    -- execute the script

    -- copy / paste the result of the query to a new ssms window

    -- The script is generated so that you can't accidentally erase the prod DB

    -- To erase the prod DB : Erase or comment the line that starts with : , MOVE N'PROD-FOR

    -- Then do a search / replace ([PROD-FORDIA_test_restore] >>>> [PROD-FORDIA]

    -- Execute the script

    --------Instructions----------

    --What date / time to restore the db to?

    DECLARE @PIT AS DATETIME

    --SET @PIT = '2011-06-16 23:30:00.000'

    SET @PIT = GETDATE()

    DECLARE @RestoreToDBName VARCHAR(128)

    SET @RestoreToDBName = CASE @@SERVERNAME

    WHEN 'FORDIASQL' THEN 'PROD-FORDIA_test_restore'

    WHEN 'NAVIGATION' THEN 'PROD-FORDIA-X'

    --WHEN 'NAVTEST' THEN 'Test whole universe'

    --WHEN 'NAVTEST' THEN 'Test Upgrade Dynamics'

    WHEN 'NAVTEST' THEN 'Test Moulinette'

    WHEN 'FORDIARPT' THEN 'PROD-FORDIA-REPORTING'

    ELSE NULL

    END

    DECLARE @Path VARCHAR(1024)

    SET @Path = CASE @@SERVERNAME

    WHEN 'FORDIASQL' THEN 'Q:\Test restore\'

    WHEN 'NAVIGATION' THEN 'D:\NAVISION\'

    WHEN 'NAVTEST' THEN 'H:\Databases\'

    WHEN 'FORDIARPT' THEN 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\'

    ELSE NULL

    END

    SELECT @Path AS [Path], @@SERVERNAME AS SvrName, @RestoreToDBName AS RestoreToDBName

    IF OBJECT_ID('tempdb..#out') > 0 DROP TABLE #out

    IF OBJECT_ID('tempdb..#cmds') > 0 DROP TABLE #cmds

    IF OBJECT_ID('tempdb..#Exec') > 0 DROP TABLE #Exec

    CREATE TABLE #out (t VARCHAR(500) NULL, dt DATETIME NULL, TYPE CHAR(1))

    INSERT INTO #out (t)

    EXEC xp_cmdshell 'dir /B /OD "\\FORDIASQL\Backups SQL\*FORDIA*.bak"'

    UPDATE #out SET t = '\\FORDIASQL\Backups SQL\' + t, dt = REPLACE(LEFT(RIGHT(REPLACE(t, ' - DO NOT DELETE - REMI', ''), 20), 16), 'H', ':'), TYPE = CASE WHEN LOWER(t) LIKE '%full%' THEN 'D' ELSE 'L' END

    DELETE t FROM #out t WHERE t.t NOT LIKE '%.bak' OR t IS NULL

    --Exclusion list

    --DELETE t FROM #out t WHERE TYPE = 'D' AND t IN ('Q:\PROD-FORDIA-FULL 2011-06-06 11H33.bak')

    --Start FROM oldest backup

    --Comment this line to start from the most recent available

    --DELETE t FROM #out t WHERE TYPE = 'D' AND t NOT IN ('\\FORDIASQL\Backups SQL\PROD-FORDIA-FULL 2011-07-09 05H00 - DO NOT DELETE - REMI.bak')

    --SELECT * FROM #out ORDER BY t

    ;

    WITH CTE_BK (t, dt, TYPE)

    AS

    (

    SELECT TOP 1 t, dt, TYPE FROM #out WHERE [TYPE] = 'D' AND dt <= @PIT

    ORDER BY dt DESC

    )

    SELECT --t, dt, TYPE ,

    CASE dtRestore_seq.[TYPE]

    WHEN 'D' THEN 'PRINT ''

    Restoring...' + t + '''

    RESTORE DATABASE [' + @RestoreToDBName + '] FROM DISK = N''' + t + ''' WITH FILE = 1

    , MOVE N''PROD-FORDIA_Data'' TO N''' + @Path + @RestoreToDBName + '.mdf'', MOVE N''PROD-FORDIA_Log'' TO N''' + @Path + @RestoreToDBName + '_1.ldf'', MOVE N''PROD-FORDIA_1_Data'' TO N''' + @Path + @RestoreToDBName + '_2.ndf''

    , NORECOVERY, REPLACE, STATS = 5'

    WHEN 'L' THEN 'PRINT ''

    Restoring...' + t + '''

    RESTORE LOG [' + @RestoreToDBName + '] FROM DISK = N''' + t + ''' WITH FILE = 1, NORECOVERY, STATS = 25

    '

    WHEN 'STOP_AT' THEN 'PRINT ''

    Restoring...' + t + '''

    RESTORE LOG [' + @RestoreToDBName + '] FROM DISK = N''' + t + ''' WITH FILE = 1, STATS = 10, STOPAT = N''' + CONVERT(VARCHAR(100), @PIT, 126) + ''''

    WHEN 'MissLogs' THEN 'PRINT ''

    STOP_AT later than last log file... recovering database...''

    RESTORE DATABASE [' + @RestoreToDBName + '] WITH RECOVERY

    '

    ELSE NULL

    END AS [--cmd] --self commenting line in copy/paste

    , dt

    INTO #Exec

    FROM (

    SELECT t, dt, TYPE FROM CTE_BK

    UNION ALL

    SELECT t, dt, TYPE FROM #out WHERE TYPE = 'L' AND dt > (SELECT dt FROM CTE_BK) AND dt <= @PIT

    UNION ALL

    SELECT TOP 1 t, dt, 'STOP_AT' AS TYPE FROM #out WHERE TYPE = 'L' AND dt > @PIT AND EXISTS (SELECT * FROM CTE_BK) ORDER BY dt

    UNION ALL

    SELECT NULL, '9999-12-31', 'MissLogs' AS TYPE WHERE NOT EXISTS (SELECT TOP 1 t, dt, 'STOP_AT' AS TYPE FROM #out WHERE TYPE = 'L' AND dt > @PIT AND EXISTS (SELECT * FROM CTE_BK) ORDER BY dt)

    ) dtRestore_seq

    ORDER BY dt

    DECLARE @Exec VARCHAR(8000)

    SET @Exec = NULL ;

    DECLARE Exec_cursor CURSOR FOR

    SELECT [--cmd] FROM #Exec ORDER BY dt

    OPEN Exec_cursor

    FETCH NEXT FROM Exec_cursor

    INTO @Exec

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT ' '

    PRINT @Exec

    --EXEC (@Exec)

    FETCH NEXT FROM Exec_cursor

    INTO @Exec

    END

    CLOSE Exec_cursor

    DEALLOCATE Exec_cursor

    IF OBJECT_ID('tempdb..#out') > 0 DROP TABLE #out

    IF OBJECT_ID('tempdb..#cmds') > 0 DROP TABLE #cmds

    IF OBJECT_ID('tempdb..#Exec') > 0 DROP TABLE #Exec

    SET @Exec = '

    DBCC CHECKDB(''' + @RestoreToDBName + ''') WITH ALL_ERRORMSGS, NO_INFOMSGS

    USE master;

    --DROP DATABASE [' + @RestoreToDBName + ']'

    PRINT @Exec

    --EXEC (@Exec)

  • Thank you Ninja's_RGR'us, it will take me a while to dig thru that!

    But basically what y'all are saying is there is no way to take the output of a SQL statement and write it to a text file? That just seems really strange. Presumably this is what SSIS is for, but I thought for sure it could be done with SQL directly. Hmmmmm....

  • Linked server to text file could do it (never tried)

    bcp out.

  • You could try osql or bcp. You would just need to put your results into a table to output.

    --osql.

    declare @cmd varchar(1000)

    select @cmd = 'osql -U -P -S -Q"select * from master..sysobjects" -o"c:\osqloutput.txt" -w500'

    exec master..xp_cmdshell @cmd

    --bcp

    master..xp_cmdshell 'bcp master..sysobjects out c:\file.bcp -S -U -P -c '

    :cool:

    "There are no problems! Only solutions that have yet to be discovered!"

  • bopeavy (8/10/2011)


    You could try osql or bcp. You would just need to put your results into a table to output.

    --osql.

    declare @cmd varchar(1000)

    select @cmd = 'osql -U -P -S -Q"select * from master..sysobjects" -o"c:\osqloutput.txt" -w500'

    exec master..xp_cmdshell @cmd

    --bcp

    master..xp_cmdshell 'bcp master..sysobjects out c:\file.bcp -S -U -P -c '

    I already showed osql here.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • skcadavre (8/11/2011)


    I already showed osql here.

    I understand you showed that here but----

    jpSQLDude (8/10/2011)


    But basically what y'all are saying is there is no way to take the output of a SQL statement and write it to a text file? That just seems really strange. Presumably this is what SSIS is for, but I thought for sure it could be done with SQL directly. Hmmmmm

    When he said that "what y'all are saying is there is no way to take the output of a SQL statement " is not true if you build the statement right it is possible. So that is why I said something about osql along with bcp.

    :cool:

    "There are no problems! Only solutions that have yet to be discovered!"

  • How about this code you can tweak it to do what you want:

    create table ##fileTable (id int identity(1,1),Files varchar(max))

    insert into ##fileTable

    exec master.dbo.xp_cmdshell 'dir C:\Test\'

    --select * from ##fileTable

    DELETE from ##fileTable WHERE Files LIKE '%Dir%'

    DELETE from ##fileTable WHERE Files LIKE '%Volume%'

    DELETE from ##fileTable WHERE Files IS NULL

    DELETE from ##fileTable WHERE Files LIKE '%Fil%'

    update ##fileTable set Files = right(files,18)from ##fileTable

    declare @var varchar(150)

    select top 1 @var = left(files,4) from ##fileTable

    select @var

    update ##fileTable set Files = 'RESTORE DATABASE ['+ @var+'] FROM DISK = ''C:\Test\'+Files+''' WITH FILE = 1, NORECOVERY' from ##fileTable where Files like ('%.bak')

    update ##fileTable set Files = 'RESTORE LOG ['+ @var+'] FROM DISK = ''C:\Test\'+Files+''' WITH FILE = 1, NORECOVERY' from ##fileTable where Files like ('%.trn')

    insert into ##fileTable (Files) values ('RESTORE DATABASE '+@var+' WITH RECOVERY')

    --select files from ##fileTable order by id

    exec master..xp_cmdshell 'bcp "select files from tempdb..##fileTable order by id" queryout c:\Test\file.sql -T -c '

    drop table ##fileTable

    :cool:

    "There are no problems! Only solutions that have yet to be discovered!"

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply