August 10, 2011 at 8:00 am
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
August 10, 2011 at 8:08 am
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
August 10, 2011 at 8:10 am
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).
August 10, 2011 at 8:11 am
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.
August 10, 2011 at 8:24 am
How can that possibly work with a local temp table (seriously asking)?
August 10, 2011 at 8:25 am
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"'
August 10, 2011 at 8:35 am
Good, I was thinking I was getting senile! 😀
August 10, 2011 at 10:19 am
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?
August 10, 2011 at 11:17 am
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)
August 10, 2011 at 1:17 pm
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....
August 10, 2011 at 1:28 pm
Linked server to text file could do it (never tried)
bcp out.
August 10, 2011 at 1:39 pm
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 '
August 11, 2011 at 2:12 am
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.
August 11, 2011 at 6:10 am
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.
August 11, 2011 at 8:23 am
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply