Backup error on format of ticks

Question

Post reply

Backup error on format of ticks

JKSQL

SSCrazy Eights

Points: 8481
More actions
April 17, 2012 at 1:25 pm

#259616

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.

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

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

Mike01 SSChampion Points: 11395 More actions · Answer 1

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/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

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

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 3

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

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

Mike01 SSChampion Points: 11395 More actions · Answer 5

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/

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 6

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?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

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.

Mark Fitzgerald-331224 SSCrazy Eights Points: 8489 More actions · Answer 8

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

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 9

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.