April 14, 2022 at 10:21 am
Hello,
I need to create a script to create some database : 1 to 3.
I wrote the script with a variable called @NB_APP
This variable should be asked to the user that runs the script.
For now i run it like this in a command prompt:
sqlcmd -v NB_APP="3" -i c:\CreateABA.sql
Ultimately, i would like to click on the cmd file and the first question should be how many DB you need. That number is NB_APP variable.
But I get an error saying :
Must declare the scalar variable "@NB_APP".
Can someone have a look at this please?
Thanks
DECLARE @NB_APP INT;
RESTORE DATABASE ABA_01
FROM DISK = 'D:\SQL\BACKUP\ABA_01.bak'
WITH MOVE 'ABA_01_Data' TO 'D:\SQL\DATA\ABA_01_Data.mdf',
MOVE 'ABA_01_log' TO 'D:\SQL\LOG\ABA_01_log.ldf'
GO
IF @NB_APP <= 2
RESTORE DATABASE ABA_01
FROM DISK = 'D:\SQL\BACKUP\ABA_01.bak'
WITH MOVE 'ABA_01_Data' TO 'D:\SQL\DATA\ABA_02_Data.mdf',
MOVE 'ABA_01_log' TO 'D:\SQL\LOG\ABA_02_log.ldf'
GO
IF @NB_APP <= 3
RESTORE DATABASE ABA_01
FROM DISK = 'D:\SQL\BACKUP\ABA_01.bak'
WITH MOVE 'ABA_01_Data' TO 'D:\SQL\DATA\ABA_03_Data.mdf',
MOVE 'ABA_01_log' TO 'D:\SQL\LOG\ABA_03_log.ldf'
GO
April 14, 2022 at 3:29 pm
I am pretty proud of myself. I wrote this script and it worked.
Now I need to know how to make the variable in the CMD.
DECLARE @NBAPP INT;
DECLARE @i INT;
DECLARE @DBSource VARCHAR(250);
DECLARE @DBTarget VARCHAR(250);
DECLARE @NewDBTarget VARCHAR(250);
DECLARE @NewDataDBTarget VARCHAR(250);
DECLARE @NewLOGDBTarget VARCHAR(250);
DECLARE @PathSourceDB VARCHAR(250);
DECLARE @PathSourceDBData VARCHAR(250);
DECLARE @PathSourceDBLog VARCHAR(250);
DECLARE @PathFullSource VARCHAR(250)
DECLARE @SourceDataDB VARCHAR(250);
DECLARE @SourceLogDB VARCHAR(250);
-------------------------------------
-- The lines below need to be update
SET @NBAPP = 5; --You need to update the number of apps you need.
SET @DBSource = 'ABA_01' --You need to update this with the name of the database in your source bak file. If you do not know it, then do a manual restore and it will show up
SET @DBTarget = 'ABA_' -- You can update that with the name
SET @PathSourceDB = 'D:\SQL\BACKUP\' -- Here you put the path of the directory where the SOURCE BAK file is. Do not forget the \ at the end
SET @PathSourceDBData = 'D:\SQL\DATA\' -- Here you put the path of the directory where the TARGET DATA file is going to be. Do not forget the \ at the end
SET @PathSourceDBLog = 'D:\SQL\LOG\' -- Here you put the path of the directory where the TARGET LOG file is going to be. Do not forget the \ at the end
-- No need to update below
-------------------------------------
SET @i = 1;
SET @PathFullSource = @PathSourceDB + @DBSource + '.bak'
SET @SourceDataDB = @DBSource + '_Data'
SET @SourceLogDB = @DBSource + '_log'
WHILE @i <= @NBAPP
BEGIN
SET @NewDBTarget = @DBTarget + convert(varchar(2),@i)
SET @NewDataDBTarget = @PathSourceDBData + @DBTarget + convert(varchar(2),@i) + '_Data.mdf'
SET @NewLOGDBTarget = @PathSourceDBLog + @DBTarget + convert(varchar(2),@i) + '_Log.ldf'
PRINT @NewDBTarget
PRINT @NewDataDBTarget
PRINT @NewLOGDBTarget
PRINT @PathFullSource
RESTORE DATABASE @NewDBTarget
FROM DISK = @PathFullSource
WITH MOVE @SourceDataDB TO @NewDataDBTarget,
MOVE @SourceLogDB TO @NewLOGDBTarget
SET @i = @i +1
END
April 14, 2022 at 6:02 pm
Have you read this link:
It's pretty clear how to do this.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 15, 2022 at 2:04 pm
Hello,
I need to create a script to create some database : 1 to 3. I wrote the script with a variable called @NB_APP This variable should be asked to the user that runs the script. For now i run it like this in a command prompt: sqlcmd -v NB_APP="3" -i c:\CreateABA.sql Ultimately, i would like to click on the cmd file and the first question should be how many DB you need. That number is NB_APP variable.
But I get an error saying : Must declare the scalar variable "@NB_APP".
Can someone have a look at this please?
Thanks
DECLARE @NB_APP INT;
RESTORE DATABASE ABA_01
FROM DISK = 'D:\SQL\BACKUP\ABA_01.bak'
WITH MOVE 'ABA_01_Data' TO 'D:\SQL\DATA\ABA_01_Data.mdf',
MOVE 'ABA_01_log' TO 'D:\SQL\LOG\ABA_01_log.ldf'
GO
IF @NB_APP <= 2
RESTORE DATABASE ABA_01
FROM DISK = 'D:\SQL\BACKUP\ABA_01.bak'
WITH MOVE 'ABA_01_Data' TO 'D:\SQL\DATA\ABA_02_Data.mdf',
MOVE 'ABA_01_log' TO 'D:\SQL\LOG\ABA_02_log.ldf'
GO
IF @NB_APP <= 3
RESTORE DATABASE ABA_01
FROM DISK = 'D:\SQL\BACKUP\ABA_01.bak'
WITH MOVE 'ABA_01_Data' TO 'D:\SQL\DATA\ABA_03_Data.mdf',
MOVE 'ABA_01_log' TO 'D:\SQL\LOG\ABA_03_log.ldf'
GO
Your problem in the code above is the batch separator GO. Your declaration of @NB_APP is only valid for the first bath (first restore).
April 15, 2022 at 4:41 pm
My question would be...
Why are you storing such a script on the root of the C: where some users could tamper with it or delete it? This should be in a stored procedure, not in an unprotected SQLCMD script?
--Jeff Moden
April 29, 2022 at 4:08 pm
totally correct - a procedure would be a quite good approach - you could check if any databases already exist with an option to replace them or generate more database e.g. ABA_04, ABA_05 and so on.
I would prefer a powershell script for the task - with parameters like sqlInstance, databaseName, backupPath,... and you could do it with or without SqlServer Cmdlets (dotnet methods, SMO, dacpac, ...). With a powershell script you have a very broad flexibility...
April 29, 2022 at 4:56 pm
I have the same concerns about a PoSh script as I do with an SQLCMD script.
--Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply