Backup error on format of ticks

  • DECLARE @Path NVarchar(500)

    SET @Path = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2K8R2\MSSQL\Backup\Restores'

    --Backs up version DB that was just upgraded. This should always be one version back

    BACKUP DATABASE [Current_LastNAInitialVersion] TO DISK = N''+@Path+'\Current_LastNAInitialVersion.bak' WITH NOFORMAT, INIT, NAME = N'Current_LastNAInitialVersion-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    I can't seem to get the ticks correct to make this work. I want the directory to be a variable and the file hard coded.

  • Put it all in a variable

    DECLARE @Path NVarchar(500), @Syntax nvarchar(max)

    SET @Path = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2K8R2\MSSQL\Backup\Restores'

    --Backs up version DB that was just upgraded. This should always be one version back

    set @Syntax = 'BACKUP DATABASE [poc] TO DISK = N'''+@Path+'\Current_LastNAInitialVersion.bak''

    WITH NOFORMAT, INIT, NAME = N''Current_LastNAInitialVersion-Full Database Backup'',

    SKIP, NOREWIND, NOUNLOAD, STATS = 10'

    exec sp_ExecuteSQL @Syntax

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • What's wrong with doing it this way?

    BACKUP DATABASE [Current_LastNAInitialVersion]

    TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2K8R2\MSSQL\Backup\Restores\Current_LastNAInitialVersion.bak'

    WITH NOFORMAT,

    INIT,

    NAME = N'Current_LastNAInitialVersion-Full Database Backup',

    SKIP,

    NOREWIND,

    NOUNLOAD,

    STATS = 10

    GO

  • The reason I want the variable with the path is because there are about 20 backups and restores. These backups and restores reside in a .sql file. I will be calling the .sql file from a .cmd file. I would like an example of how to call a .SQL file with a parameter and then that parameter is used to substitute the path. We are using this for our unit tests across our products.

  • Then try this as a script first:

    DECLARE @Path NVARCHAR(500);

    SET @Path = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2K8R2\MSSQL\Backup\Restores'

    --Backs up version DB that was just upgraded. This should always be one version back

    BACKUP DATABASE [Current_LastNAInitialVersion]

    TO DISK = @Path + N'\Current_LastNAInitialVersion.bak'

    WITH NOFORMAT,

    INIT,

    NAME = N'Current_LastNAInitialVersion-Full Database Backup',

    SKIP,

    NOREWIND,

    NOUNLOAD,

    STATS = 10

    GO

  • Have you tried using SQLCmd?

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • I tried to have SQLCMD respect a variable in a .SQL file but it does not seem to allow that. So I decided to go another way. I thought I could do this in my cmd

    SQLCMD -S %Server% -q"DROP DATABASE [725_XX]"

    SQLCMD -S %Server% -q"DROP DATABASE [733SP2_XX]"

    SQLCMD -S %Server% -q"DROP DATABASE [734_XX]"

    Then I would restore doing the same thing in the cmd file

    SQLCMD -S %Server% -d master -Q"RESTORE DATABASE [725_XX] FROM DISK = '%RestoreScripts%\Restores\725_XX.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10"

    SQLCMD -S %Server% -d master -Q"RESTORE DATABASE [733SP2_XX] FROM DISK = '%RestoreScripts%\Restores\733SP2_XX.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10"

    SQLCMD -S %Server% -d master -Q"RESTORE DATABASE [734_XX] FROM DISK = '%RestoreScripts%\Restores\734_XX.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10"

    It does not seem to work though. It comes up and says 1> and sits there. Am I running the SQL CMD incorrectly?

  • JKSQL (4/18/2012)


    I tried to have SQLCMD respect a variable in a .SQL file but it does not seem to allow that. So I decided to go another way. I thought I could do this in my cmd

    SQLCMD -S %Server% -q"DROP DATABASE [725_XX]"

    SQLCMD -S %Server% -q"DROP DATABASE [733SP2_XX]"

    SQLCMD -S %Server% -q"DROP DATABASE [734_XX]"

    Then I would restore doing the same thing in the cmd file

    SQLCMD -S %Server% -d master -Q"RESTORE DATABASE [725_XX] FROM DISK = '%RestoreScripts%\Restores\725_XX.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10"

    SQLCMD -S %Server% -d master -Q"RESTORE DATABASE [733SP2_XX] FROM DISK = '%RestoreScripts%\Restores\733SP2_XX.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10"

    SQLCMD -S %Server% -d master -Q"RESTORE DATABASE [734_XX] FROM DISK = '%RestoreScripts%\Restores\734_XX.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10"

    It does not seem to work though. It comes up and says 1> and sits there. Am I running the SQL CMD incorrectly?

    In your first code block you are using a lower case "q", that does not exit SQLCMD upon completion. In the second block of code you are using the upper case "Q", which will exit SQLCMD upon completion.

    Try the "Q" in the first code block.

  • Using the idea of a directory / folder being passed into SQLCMD I have done this in the past. The reason I try to use a local declared variable within the script to accept the passed variable is that the script can be tested without SQLCMD first using a standard value assignment

    declare @path varchar(50) = '$(passPath)'

    declare @sql varchar(100)

    set @sql = 'backup database master to disk = ' + '''' + @path + '\master.bak' + ''''

    exec(@sql)

    print 'Path specified is : ' + @path

    and called it using:

    sqlcmd -i RunThis.sql -v passPath="c:\directoryname"

    Fitz

  • Hey just wanted to thank you guys. I actually incorporated all your suggestions. Here is what the code looks like now:

    UpgradeBuild.cmd "C:\SourceDirectory" "Server\Instance" "C:Upgrade files in TFS"

    That called this upgrade.cmd that I have in source control

    :: ----------------------------------------------------------------------------

    :: PARAMETERS

    :: ----------------------------------------------------------------------------

    ::

    :: %1 sets %SourcePath% (i.e. C:\SourceDirectory)

    :: %2 sets %Server% (i.e. Server\Instance)

    :: %3 sets %DBScripts% (i.e. C:Upgrade files in TFS)

    :: Initialize variables

    set SourcePath=%~1

    set Server=%2

    set DBScripts=%~3

    echo Source Path = %SourcePath%

    echo Server = %Server%

    echo DB Script Location = %DBScripts%

    echo.

    echo Running Restores of previous DB versions

    echo SQLCMD -S %Server% -d "Master" -i SSRestore.sql -v passPath="%SourcePath%\"

    SQLCMD -S %Server% -d "Master" -i SSRestore.sql -v passPath="%SourcePath%\"

    SSRestore.Sql:

    declare @path varchar(1000) = '$(passPath)'

    print 'Path specified is : ' + @path

    DECLARE @Backup Varchar(100), @Restore NVarchar(1100)

    --declare @path varchar(1000)

    --SET @path = 'C:Restores\'

    SET @Backup = '725_XX.bak'

    SET @Restore = @path+@Backup

    RESTORE DATABASE [725_XX] FROM DISK = @Restore

    WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10

    --Repeated across the 17 datasets

    I then proceed to use our utility to upgrade the DB to the current version of our branch. This is so we can house historic branched DBs in one place and use this data for multiple unit tests for our DBA team. We need to be able to support all DBs using scripts to upgrade them.

Viewing 10 posts - 1 through 9 (of 9 total)

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