Variable not working - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Variable not working

Author

Topic

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2013-04-09 : 10:38:26

Hi all,

I need to rewrite this to use a variable, instead of using Ctrl+h to replace the values, but it keeps erroring out...

Use master
Go
DECLARE @YourDatabaseName varchar(15)
SET @YourDatabaseName = 'northwind'

Alter database YourDatabaseNameHere set offline with rollback immediate
Go
Alter database YourDatabaseNameHere set online
Go
--Drop Database YourDatabaseNameHere

Cheers,

JB

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-04-09 : 11:01:21

[code]
Use master
Go
DECLARE @YourDatabaseName varchar(15)
SET @YourDatabaseName = 'northwind'

EXEC ('Alter database [' + @YourDatabaseName + '] set offline with rollback immediate;')
EXEC ('Alter database [' + @YourDatabaseName + '] set online;')
--EXEC ('Drop Database [' + @YourDatabaseName + '];')[/code]

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2013-04-10 : 11:00:24

Wonderful Russell, quite ingenious!! Thank you :)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-04-10 : 11:10:51

You're welcome

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2013-04-11 : 05:54:56

Russ, I thought I'd apply your wonderful techniques to my usual restoration script, but clearly I can't charm SQL the way you can, could you have a look at the syntax for this??

-----------------------------------------------------------------------------------
--------------ALP5DB07 RESTORATION SCRIPT----------------------------------------
------------------------------------------------------------------------------------

Use Master
Go
DECLARE @YourDatabaseName varchar(15)
DECLARE @YourBackupFileLocation varchar(15)
SET @YourDatabaseName = 'AVALONS_777_ART' (--PASTE THE DB NAME BETWEEN THE APOSTROPHES)
SET @YourBackupFileLocation = 'U:\' (--PASTE THE .BAK FILE LOCATION BETWEEN THE APOSTROPHES)

--Create new windows directories (if restoring brand new db)
--Exec master..xp_cmdshell 'MKDIR "U:\Archives\[' + @YourDatabaseName + ']\DATA"'

EXEC
(
'Restore Database [' + @YourDatabaseName + ']
From
Disk = '[' + @YourBackupFileLocation + ']\[' + @YourDatabaseName + '].BAK'

WITH
MOVE 'DCM[' + @YourDatabaseName + ']' TO 'U:\Archives\[' + @YourDatabaseName + ']\DATA\[' + @YourDatabaseName + '].mdf',
MOVE 'DCM[' + @YourDatabaseName + ']_log' TO 'W:\Archives\Log\[' + @YourDatabaseName + ']_log.ldf',
--Replace --(if restoring over an existing DB)
STATS = 1
;')

--Delete your backup file (if space an issue)
--Exec master..xp_cmdshell ' DEL /Q [' + @YourBackupFileLocation + ']\[' + @YourDatabaseName + '].BAK'

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-04-11 : 06:00:39

Couple of quick things...

- Get rid of the brackets [] in the xp_cmdshell script.
- Might want to append .bak to the backup filenames.

Change EXEC to PRINT and run the script. This allows you to see the SQL you're generating so you can see what's wrong and test the various parts then modify your script as necessary.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-04-11 : 06:03:45

Also, you needed to escape your single quotes by doubling 'em up. Like below

DECLARE @YourDatabaseName varchar(15)
DECLARE @YourBackupFileLocation varchar(15)
SET @YourDatabaseName = 'AVALONS_777_ART'-- (--PASTE THE DB NAME BETWEEN THE APOSTROPHES)
SET @YourBackupFileLocation = 'U:\' --(--PASTE THE .BAK FILE LOCATION BETWEEN THE APOSTROPHES)

--Create new windows directories (if restoring brand new db)
--Exec master..xp_cmdshell 'MKDIR "U:\Archives\[' + @YourDatabaseName + ']\DATA"'

print
(
'Restore Database [' + @YourDatabaseName + ']
From
Disk = ''[' + @YourBackupFileLocation + ']\[' + @YourDatabaseName + '].BAK''

WITH
MOVE ''DCM[' + @YourDatabaseName + ']'' TO ''U:\Archives\[' + @YourDatabaseName + ']\DATA\[' + @YourDatabaseName + '].mdf'',
MOVE ''DCM[' + @YourDatabaseName + ']_log'' TO ''W:\Archives\Log\[' + @YourDatabaseName + ']_log.ldf'',
--Replace --(if restoring over an existing DB)
STATS = 1
;')

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-11 : 06:13:29

[code]
DECLARE @YourDatabaseName varchar(15)
DECLARE @YourBackupFileLocation varchar(15)
SET @YourDatabaseName = 'AVALONS_777_ART' --PASTE THE DB NAME BETWEEN THE APOSTROPHES)
SET @YourBackupFileLocation = 'U:' --PASTE THE .BAK FILE LOCATION BETWEEN THE APOSTROPHES)

--Create new windows directories (if restoring brand new db)
--Exec master..xp_cmdshell 'MKDIR "U:\Archives\[' + @YourDatabaseName + ']\DATA"'

DECLARE @sql VARCHAR(1000)=
'Restore Database [' + @YourDatabaseName + ']
From
Disk = ''' + @YourBackupFileLocation + '\' + @YourDatabaseName + '.BAK''

WITH
MOVE ''DCM'+ @YourDatabaseName + ''' TO ''U:\Archives\' + @YourDatabaseName + '\DATA\' + @YourDatabaseName + '.mdf'',
MOVE ''DCM' + @YourDatabaseName + '_log'' TO ''W:\Archives\Log\' + @YourDatabaseName + '_log.ldf'',
STATS = 1
;'
PRINT @sql
EXEC(@sql)
[/code]

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-11 : 06:16:44

quote:
Originally posted by russell

what is the purpose of square brackets in the file paths

--
Chandu

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-04-11 : 06:20:01

quote:
Originally posted by bandi

quote:
Originally posted by russell

what is the purpose of square brackets in the file paths

--
Chandu

There is none and they shouldn't be there. Only in the logical name of the database. Purpose is in case there are spaces or special characters in the db name (or it the name is a reserved word).

I should've edited them out of the paths, but didn't look close enough

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-11 : 06:23:27

quote:
Originally posted by russell
There is none and they shouldn't be there. Only in the logical name of the database. Purpose is in case there are spaces or special characters in the db name (or it the name is a reserved word).

I should've edited them out of the paths, but didn't look close enough

--
Chandu

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2013-04-11 : 06:28:59

quote:
Originally posted by russell

quote:
Originally posted by bandi

quote:
Originally posted by russell

what is the purpose of square brackets in the file paths

--
Chandu

There is none and they shouldn't be there. Only in the logical name of the database. Purpose is in case there are spaces or special characters in the db name (or it the name is a reserved word).

I should've edited them out of the paths, but didn't look close enough