January 2, 2015 at 12:54 pm
Hello --
I am going through the motions of implementing MaintenanceSolution sql scripts. The first script I am working on is the DatabaseBackup.sql file. I am modifying it to run some tests. Currently, I am trying to back up a single database to a local drive, and do verify the backup file. The modifications that I have made to the file are the following:
@Databases = '02May2013',
@Directory = 'M:\Backups\Full and Differential',
@BackupType = 'FULL',
@verify = 'Y',
@CleanupTime int = NULL,
@CleanupMode nvarchar(max) = 'AFTER_BACKUP',
@Compress nvarchar(max) = NULL,
@CopyOnly nvarchar(max) = 'N',
@ChangeBackupType nvarchar(max) = 'N',
@BackupSoftware nvarchar(max) = NULL,
@checksum nvarchar(max) = 'N',
@BlockSize int = NULL,
@BufferCount int = NULL,
@MaxTransferSize int = NULL,
@NumberOfFiles int = NULL,
@CompressionLevel int = NULL,
@Description nvarchar(max) = NULL,
@Threads int = NULL,
@Throttle int = NULL,
@Encrypt nvarchar(max) = 'N',
@EncryptionAlgorithm nvarchar(max) = NULL,
@ServerCertificate nvarchar(max) = NULL,
@ServerAsymmetricKey nvarchar(max) = NULL,
@EncryptionKey nvarchar(max) = NULL,
@ReadWriteFileGroups nvarchar(max) = 'N',
@OverrideBackupPreference nvarchar(max) = 'N',
@NoRecovery nvarchar(max) = 'N',
@URL nvarchar(max) = NULL,
@Credential nvarchar(max) = NULL,
@MirrorDirectory nvarchar(max) = NULL,
@MirrorCleanupTime int = NULL,
@MirrorCleanupMode nvarchar(max) = 'AFTER_BACKUP',
@LogToTable nvarchar(max) = 'N',
@Execute nvarchar(max) = 'Y'
When I execute the script, within Studio, the following error message appears:
Msg 102, Level 15, State 1, Procedure DatabaseBackup, Line 3
Incorrect syntax near '='.
There are also a whole series scaler variable messages as well, which I am pretty sure are all related to the syntax error.
What is the correct syntax to use here?
Thanks.
January 2, 2015 at 3:00 pm
If you're declaring the variables and want to set them on the same line it's something like this
DECLARE @MyInt INT = 0
If the variable is already declared earlier in the script, then you just set the variable like this
SET @MyInt = 0
It looks like you have a mixture of both in your example.
January 2, 2015 at 3:26 pm
One other thing I would look at are your data types. It looks like all your string types are NVARCHAR(max). Do you really need the strings to be capable of hold 2^32 - 1 characters?
Especially if all the need is to hold a 'N' or 'Y'. In this case a char(1) or nchar(1) would be just as good.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply