SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Variable not working
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 04/09/2013 :  10:38:26  Show Profile  Reply with Quote
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

USA
5072 Posts

Posted - 04/09/2013 :  11:01:21  Show Profile  Visit russell's Homepage  Reply with Quote

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 + '];')
Go to Top of Page

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 04/10/2013 :  11:00:24  Show Profile  Reply with Quote
Wonderful Russell, quite ingenious!! Thank you :)
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/10/2013 :  11:10:51  Show Profile  Visit russell's Homepage  Reply with Quote
You're welcome
Go to Top of Page

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 04/11/2013 :  05:54:56  Show Profile  Reply with Quote
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'
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/11/2013 :  06:00:39  Show Profile  Visit russell's Homepage  Reply with Quote
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.
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/11/2013 :  06:03:45  Show Profile  Visit russell's Homepage  Reply with Quote
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
;')

Edited by - russell on 04/11/2013 06:04:34
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 04/11/2013 :  06:13:29  Show Profile  Reply with Quote

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)
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 04/11/2013 :  06:16:44  Show Profile  Reply with Quote
quote:
Originally posted by russell



what is the purpose of square brackets in the file paths

--
Chandu
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/11/2013 :  06:20:01  Show Profile  Visit russell's Homepage  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 04/11/2013 :  06:23:27  Show Profile  Reply with Quote
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
Go to Top of Page

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 04/11/2013 :  06:28:59  Show Profile  Reply with Quote
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



Probably because you were up before 6am? :)
Go to Top of Page

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 04/11/2013 :  07:33:20  Show Profile  Reply with Quote
That "Print" command is pretty handy for a non-Developer like myself, I'm getting quite an education here! :)
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 04/11/2013 :  07:40:25  Show Profile  Reply with Quote
you figured out the solution?
Go to Top of Page

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 04/11/2013 :  08:17:20  Show Profile  Reply with Quote
Yep
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/11/2013 :  09:14:19  Show Profile  Visit russell's Homepage  Reply with Quote
yep, lol. that'll teach me to try to read/write code at that hour!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000