December 28, 2005 at 4:03 am
Hi,
i try to create a script that drops a database and import a backup to create it again.
Now, i get a stupid error message, i think i missed something in sql syntax..
here is what i created :
==================================
DECLARE @DB_Name varchar(128)
DECLARE @DB_Bak_file varchar(128)
DECLARE @DB_Import_file varchar(128)
DECLARE @DB_Datas_Path varchar(260)
DECLARE @DB_Logs_Path varchar(260)
DECLARE @DB_Login varchar(128)
DECLARE @DB_Password varchar(128)
DECLARE @SQLString nvarchar (2000)
-- Nom de la base de données à créer --
SET @DB_Name = 'PROD'
-- Chemin du backup temporaire de l'ancienne base --
SET @DB_Bak_file = 'd:\mssql2000\MSSQL\BACKUP\tmp_DB_DAISY-BAK_PROD.bak'
-- Chemin du fichier de backup à importer --
SET @DB_Import_file = 'd:\mssql2000\MSSQL\BACKUP\DB_DAISY_PROD.bak'
-- Chemin du fichier des DATAS de la base --
SET @DB_Datas_Path = 'd:\mssql2000\MSSQL\data\PROD_Datas.MDF'
-- Chemin du fichier des DATAS de la base --
-- SET @DB_Logs_Path = 'e:\Logs\PROD_Logs.LDF'
SET @DB_Logs_Path = 'd:\mssql2000\MSSQL\log\PROD_Logs.LDF'
-- Login utilisé par l'accès via l'espace client --
SET @DB_Login = 'usr_app_eclient'
-- Nom de la base de données à créer --
SET @DB_Password = 'ZePa$$w0rd'
USE master
GO
-- Suppression de la base de données actuelle
SET @SQLString = 'DROP DATABASE ' + @DB_Name
PRINT @SQLString
EXECUTE(@SQLString)
GO
============================================
Here is the error message (sorry, it's in French...):
============================================
Serveur : Msg 137, Niveau 15, État 2, Ligne 2
La variable '@DB_Name' doit être déclarée.
Serveur : Msg 137, Niveau 15, État 1, Ligne 3
La variable '@SQLString' doit être déclarée.
Serveur : Msg 137, Niveau 15, État 1, Ligne 4
La variable '@SQLString' doit être déclarée.
============================================
Thanks for your help, i'm really a beginner at SQL scripting...
Florent
December 28, 2005 at 4:25 am
USE master
GO
DECLARE @DB_Name varchar(128)
DECLARE @DB_Bak_file varchar(128)
DECLARE @DB_Import_file varchar(128)
DECLARE @DB_Datas_Path varchar(260)
DECLARE @DB_Logs_Path varchar(260)
DECLARE @DB_Login varchar(128)
DECLARE @DB_Password varchar(128)
DECLARE @SQLString nvarchar (2000)
-- Nom de la base de données à créer --
SET @DB_Name = 'PROD'
-- Chemin du backup temporaire de l'ancienne base --
SET @DB_Bak_file = 'd:\mssql2000\MSSQL\BACKUP\tmp_DB_DAISY-BAK_PROD.bak'
-- Chemin du fichier de backup à importer --
SET @DB_Import_file = 'd:\mssql2000\MSSQL\BACKUP\DB_DAISY_PROD.bak'
-- Chemin du fichier des DATAS de la base --
SET @DB_Datas_Path = 'd:\mssql2000\MSSQL\data\PROD_Datas.MDF'
-- Chemin du fichier des DATAS de la base --
-- SET @DB_Logs_Path = 'e:\Logs\PROD_Logs.LDF'
SET @DB_Logs_Path = 'd:\mssql2000\MSSQL\log\PROD_Logs.LDF'
-- Login utilisé par l'accès via l'espace client --
SET @DB_Login = 'usr_app_eclient'
-- Nom de la base de données à créer --
SET @DB_Password = 'ZePa$$w0rd'
-- Suppression de la base de données actuelle
SET @SQLString = 'DROP DATABASE ' + @DB_Name
PRINT @SQLString
EXECUTE(@SQLString)
GO
Try it out
December 28, 2005 at 4:35 am
ok, it worked, now i get a new error few lines after :
-- Creation de la nouvelle base de données
SET @SQLString = 'CREATE DATABASE ' + @DB_Name + ' ON ( NAME = PROD_datas, FILENAME = ' + @DB_Datas_Path + ' ) LOG ON ( NAME = PROD_logs, FILENAME = ' + @DB_Logs_Path + ' )'
PRINT @SQLString
EXECUTE(@SQLString)
GO
What is the rule concerning the ' character ?
thanks
Florent
December 28, 2005 at 4:49 am
SET @SQLString = 'CREATE DATABASE ' + @DB_Name + ' ON ( NAME = PROD_datas, FILENAME = ' + QUOTENAME(@DB_Datas_Path,'''') + ' ) LOG ON ( NAME = PROD_logs, FILENAME = ' + QUOTENAME(@DB_Logs_Path,'''') + ' )'
PRINT @SQLString
EXECUTE(@SQLString)
GO
Try it out and you you know about the Single quote
December 28, 2005 at 4:59 am
DECLARE @DBFileName VARCHAR(100)
DECLARE @SQLString VARCHAR(500)
SELECT @DBFileName = 'D:\SQL2000\Data\xxx.mdf'
SELECT @SQLString = 'FILE = '+@DBFileName
PRINT @SQLString
-- The Syntax is FILE = 'D:\SQL2000\Data\xxx.mdf'
-- Hence
SELECT @SQLString = 'FILE = '''+@DBFileName+''''
-- or
SELECT @SQLString = 'FILE = '+QUOTENAME(@DBFileName,'''')
PRINT @SQLString
December 28, 2005 at 11:01 am
Very strange, there is something i must miss somewhere :
USE master
GO
DECLARE @DB_Name varchar(128)
DECLARE @DB_Bak_file varchar(128)
DECLARE @DB_Import_file varchar(128)
DECLARE @DB_Datas_Path varchar(260)
DECLARE @DB_Logs_Path varchar(260)
DECLARE @DB_Login varchar(128)
DECLARE @DB_Password varchar(128)
DECLARE @SQLString nvarchar (2000)
-- Nom de la base de données à créer --
SET @DB_Name = 'PROD'
-- Chemin du backup temporaire de l'ancienne base --
SET @DB_Bak_file = 'd:\mssql2000\MSSQL\BACKUP\tmp_DB_DAISY-BAK_PROD.bak'
-- Chemin du fichier de backup à importer --
SET @DB_Import_file = 'd:\mssql2000\MSSQL\BACKUP\DB_DAISY_PROD.bak'
-- Chemin du fichier des DATAS de la base --
SET @DB_Datas_Path = 'd:\mssql2000\MSSQL\data\PROD_Datas.MDF'
-- Chemin du fichier des DATAS de la base --
-- SET @DB_Logs_Path = 'e:\Logs\PROD_Logs.LDF'
SET @DB_Logs_Path = 'd:\mssql2000\MSSQL\log\PROD_Logs.LDF'
-- Login utilisé par l'accès via l'espace client --
SET @DB_Login = 'usr_app_eclient'
-- Nom de la base de données à créer --
SET @DB_Password = 'ZePa$$w0rd'
-- Suppression de la base de données actuelle
SET @SQLString = 'DROP DATABASE ' + @DB_Name
PRINT @SQLString
EXECUTE(@SQLString)
GO
-- Creation de la nouvelle base de données
SET @SQLString = 'CREATE DATABASE ' + @DB_Name + ' ON ( NAME = PROD_datas, FILENAME = ' + QUOTENAME(@DB_Datas_Path,'''') + ' ) LOG ON ( NAME = PROD_logs, FILENAME = ' + QUOTENAME(@DB_Logs_Path,'''') + ' )'
PRINT @SQLString
EXECUTE(@SQLString)
GO
Won't work and asks me to declare the @DB_Name variable...
My goal was to declare this at the top of the script only 1 time.
Now, if i add the dECLARE lines and the SET lines just before the "SET @SQLString = 'CREATE DATABASE " line, all is ok...
Any idea ?
December 28, 2005 at 11:08 am
When you use GO, the script changes 'scope'. Your @DB_Name is no longer declared in the new scope.
Refer to the BOL, use the Index tab, enter Variables, then select the option Transact SQL. Then in the pop-up window, select Transact SQL Variables.
-SQLBill
December 28, 2005 at 11:12 am
Actually, upon re-reading, I didn't state it very correctly. Your issue is that a variable is only good for one batch or stored procedure.
This is one batch:
-- Suppression de la base de données actuelle
SET @SQLString = 'DROP DATABASE ' + @DB_Name
PRINT @SQLString
EXECUTE(@SQLString)
GO
This is a second batch:
-- Creation de la nouvelle base de données
SET @SQLString = 'CREATE DATABASE ' + @DB_Name + ' ON ( NAME = PROD_datas, FILENAME = ' + QUOTENAME(@DB_Datas_Path,'''') + ' ) LOG ON ( NAME = PROD_logs, FILENAME = ' + QUOTENAME(@DB_Logs_Path,'''') + ' )'
PRINT @SQLString
EXECUTE(@SQLString)
GO
-SQLBill
December 28, 2005 at 11:15 am
Sorry Bill, i'm not sure to understand.
My goa lis t ocreate 1 only sql script that will perform many tasks in order to:
delete a nexisting Database
Create a new daztabase with the same name
restaure that database using a dump created on another server
create correct access logins/rights to new created database....
My goal was to have variables only at the top of the script in order to be able to quickly change one or more variable for the entire script (as i do in vb script for example)
Florent
December 28, 2005 at 11:19 am
ok, i understood, i just have removed all the "GO" statments and all seems to work now ;-))
But should i add a "GO" anywhere then ?
December 28, 2005 at 12:39 pm
No. A GO isn't really necessary. It just allows you to break a long script into separate batches. One way to look at it...a GO is similar to a COMMIT. When SQL Server comes to a GO, it commits that transaction and starts the next one.
-SQLBill
December 29, 2005 at 5:34 am
ok, now that i understand the syntax, could someone please help me giving me the order / commands to run to :
create a new database using a dump made on another server (*.bak file) and forcing the location + name of *.mdf and *.ldf file.
thanks a lot.
Here is my actual script :
============================
USE master
GO
set nocount on
DECLARE @DB_Name varchar(128)
DECLARE @DB_Bak_file varchar(128)
DECLARE @DB_Import_file varchar(128)
DECLARE @DB_Datas_Path varchar(260)
DECLARE @DB_Logs_Path varchar(260)
DECLARE @DB_Login varchar(128)
DECLARE @DB_Password varchar(128)
DECLARE @SQLString nvarchar (2000)
-- Nom de la base de données à créer --
SET @DB_Name = 'PROD'
-- Chemin du backup temporaire de l'ancienne base --
SET @DB_Bak_file = 'd:\mssql2000\MSSQL\BACKUP\tmp_DB_DAISY-BAK_PROD.bak'
-- Chemin du fichier de backup à importer --
SET @DB_Import_file = 'd:\mssql2000\MSSQL\BACKUP\DB_DAISY_PROD.bak'
-- Chemin du fichier des DATAS de la base --
SET @DB_Datas_Path = 'd:\mssql2000\MSSQL\data\PROD_Datas.MDF'
-- Chemin du fichier des DATAS de la base --
-- SET @DB_Logs_Path = 'e:\Logs\PROD_Logs.LDF'
SET @DB_Logs_Path = 'd:\mssql2000\MSSQL\log\PROD_Logs.LDF'
-- Login utilisé par l'accès via l'espace client --
SET @DB_Login = 'usr_app_eclient'
-- Nom de la base de données à créer --
SET @DB_Password = 'ZePa$$w0rd'
-- Restriction d'accès au DBO et sysadmins seulement
SET @SQLString = 'ALTER DATABASE ' + @DB_Name + ' SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE'
PRINT @SQLString
EXECUTE(@SQLString)
-- Suppression de la base de données actuelle
SET @SQLString = 'DROP DATABASE ' + @DB_Name
PRINT @SQLString
EXECUTE(@SQLString)
-- Creation de la nouvelle base de données
SET @SQLString = 'CREATE DATABASE ' + @DB_Name + ' ON ( NAME = PROD_datas, FILENAME = ' + QUOTENAME(@DB_Datas_Path,'''') + ' ) LOG ON ( NAME = PROD_logs, FILENAME = ' + QUOTENAME(@DB_Logs_Path,'''') + ' )'
PRINT @SQLString
EXECUTE(@SQLString)
-- Restriction d'accès au DBO et sysadmins seulement
SET @SQLString = 'ALTER DATABASE ' + @DB_Name + ' SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE'
PRINT @SQLString
EXECUTE(@SQLString)
-- Restauration de la liste des fichiers associés à la base
SET @SQLString = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@DB_Import_file,'''')
PRINT @SQLString
EXECUTE(@SQLString)
-- Restauration des datas de la base
SET @SQLString = 'RESTORE DATABASE ' + @DB_Name + ' FROM DISK = ' + QUOTENAME(@DB_Import_file,'''') + ' WITH RECOVERY, MOVE ''PROD_datas'' TO ' + QUOTENAME(@DB_Datas_Path,'''') + ' , MOVE ''PROD_logs'' TO ' + QUOTENAME(@DB_Logs_Path,'''')
PRINT @SQLString
EXECUTE(@SQLString)
GO
============================
Here is the output of this actual script :
============================
ALTER DATABASE PROD SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE PROD
Suppression du fichier de base de données 'd:\mssql2000\MSSQL\log\PROD_Logs.LDF'.
Suppression du fichier de base de données 'd:\mssql2000\MSSQL\data\PROD_Datas.MDF'.
CREATE DATABASE PROD ON ( NAME = PROD_datas, FILENAME = 'd:\mssql2000\MSSQL\data\PROD_Datas.MDF' ) LOG ON ( NAME = PROD_logs, FILENAME = 'd:\mssql2000\MSSQL\log\PROD_Logs.LDF' )
Le processus CREATE DATABASE alloue 0.75 Mo sur le disque 'PROD_datas'.
Le processus CREATE DATABASE alloue 1.00 Mo sur le disque 'PROD_logs'.
ALTER DATABASE PROD SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
RESTORE FILELISTONLY FROM DISK = 'd:\mssql2000\MSSQL\BACKUP\DB_DAISY_PROD.bak'
RESTORE DATABASE PROD FROM DISK = 'd:\mssql2000\MSSQL\BACKUP\DB_DAISY_PROD.bak' WITH RECOVERY, MOVE 'PROD_datas' TO 'd:\mssql2000\MSSQL\data\PROD_Datas.MDF' , MOVE 'PROD_logs' TO 'd:\mssql2000\MSSQL\log\PROD_Logs.LDF'
Serveur : Msg 3234, Niveau 16, État 2, Ligne 1
Le fichier logique 'PROD_datas' ne fait pas partie de la base de données 'PROD'. Utilisez RESTORE FILELISTONLY pour lister les noms de fichier logiques.
Serveur : Msg 3013, Niveau 16, État 1, Ligne 1
RESTORE DATABASE s'est terminé anormalement.
============================
Florent
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply