I've made a stored procedure restoredatabase
that returns code to restore a database. The output script looks like this:
restore database [DB] from disk='F:\FULL1.bak' with norecovery
restore database [DB] from disk='F:\DIFF1.bak' with norecovery
restore log [DB] from disk='F:\log1.bak' with norecovery
restore log [DB] from disk='F:\log2.bak' with norecovery
...
restore log [DB] from disk='F:\logN.bak' with norecovery
restore database [DB] with recovery
Througout the SP, the lines get added to a parameter @sql varchar(max)
which, at the end, gets printed with print(@sql)
, and if the user specified the right flag, gets executed with exec(@sql)
. Both work perfectly. However, when calling the SP with sqlcmd like this:
set restoresql=exec restoredatabase @DB='Databasename', @Execute='y'
sqlcmd -h-1 -S SRV\T2 -U sa -P sa -d master -Q "%restoresql%" -o output.txt
The output file output.txt
will always have a maximum of 4002 characters.
I've tried adding -y 8000
but that doesn't help. How can I get the output file to give me the full generated script?
Best Answer
To be clear, the issue is really with the
PRINT
command and not theSQLCMD
utility.I am guessing that your variable is actually
NVARCHAR(MAX)
, notVARCHAR(MAX)
since thePRINT
command is limited to only 4000 characters usingNCHAR
/NVARCHAR
. Otherwise it can output up to 8000 characters usingNVARCHAR
/CHAR
. To see thatVARCHAR
does go beyond 4000 characters, but not beyond 8000, run the following:If you need more than 8000 characters printed, then you need to iterate over the variable in chunks of up to 8000 characters (the
PRINT
command will only display up to 8000CHAR
/VARCHAR
characters at a time). But you don't want to simply chop it into even 8000 character chunks as that could be right in the middle of something that should not be split, like a word / object name / number, etc. Since thePRINT
command will always include a newline character at the end, it is best to print up to the last newline character of each 8000 character chunk. The result is that the only lines to get split across multiple displayed lines are those that are over 8000 characters before the next newline character, and there is really nothing that can be done about that.Try the following proc in place of your
print(@sql)
command (FYI: you don't need parenthesis around@sql
forPRINT
like you do forEXEC
). Please note that finding the last newline character in a string requires creating aLastIndexOf
function since one is not built into T-SQL. You can create a T-SQL UDF to do this. You can also use SQLCLR in which case you can code it yourself or simply get the Free version of the SQL# library (which I am the author of).Tests:
NOTES:
CHARINDEX
function to look for each next newline, and in this respect it would function almost like using a string splitting function. The difference between what is needed here and doing a simple split on newlines is that any element returned that is over 8000 characters will still need to be split into chunks of no more than 8000 characters.NVARCHAR(MAX)
instead ofVARCHAR(MAX)
, use the code that I posted to PasteBin: T-SQL Stored Proc to PRINT NVARCHAR(MAX) valuesUtil_Print
, in the SQL# library that handlesNVARCHAR(MAX)
and has some functionality beyond the T-SQL code shown above, but that is only available in the Full (i.e. paid for) version, not in the Free version. But the code shown here will handle most cases.