« MSSQL » : différence entre les versions
De TwisterWiki
Aucun résumé des modifications |
Aucun résumé des modifications |
||
(14 versions intermédiaires par le même utilisateur non affichées) | |||
Ligne 1 : | Ligne 1 : | ||
= | == RESTAURATION DE BASES == | ||
* la base master : il faut exactement les mêmes version de sqlserveur et les même SP | * la base master : il faut exactement les mêmes version de sqlserveur et les même SP | ||
stopper puis lancer sqlservr.exe -m afin de lancer la resto en single user | stopper sqlserver puis le lancer avec la commande suivante | ||
sqlservr.exe -m afin de lancer la resto en single user | |||
Lancer sqlcmd | |||
RESTORE DATABASE master FROM "jeux_de_sauvegarde" | |||
Ensuite il faut aller dans l'entreprise manager et clique droit sur le serveur sql | |||
et choisir propriété. | et choisir propriété. | ||
Aller dans sécurité et modifier le mot de passe du compte utilisé pour lancer SQL et il vous est demandé de fournir le compte admin avec le password | |||
== COMMNANDES UTILES == | |||
*Backup du dernier journal de transaction après un crash de la base | |||
BACKUP LOG $BDD TO DISK = N'Fichier.trn' WITH NO RECOVERY; | BACKUP LOG $BDD TO DISK = N'Fichier.trn' WITH NO RECOVERY; | ||
* déconnecter les users d'une base avec rollback des dérnières transaction | *déconnecter les users d'une base avec rollback des dérnières transaction | ||
ALTER DATABASE $BDD RESTRICTED_USER WITH ROLLBACK IMMEDIATE; | ALTER DATABASE $BDD RESTRICTED_USER WITH ROLLBACK IMMEDIATE; | ||
* Création d'un snapshot | * Création d'un snapshot | ||
CREATE DATABASE snapshot_heure ON | |||
(NAME = 'logical_file_name_source', FILENAME='os_file_name_destination.ss' ) | |||
AS SNAPSHOT OF source_database_name; | |||
* restauration à partir d'un snapshot | |||
RESTORE DATABASE base_a_resto FROM DATABASE_SNAPSHOT = 'snapshot_heure' | |||
sp_configure 'show advanced options',1 | |||
RECONFIGURE | |||
sp_configure | |||
== Utiliser une connexion DAC (Dedicated Administrator Connection ) == | |||
# In SQL Server Management Studio, with no other DACs open, on the toolbar, click Database Engine Query. | |||
# In the Connect to Database Engine dialog box, in the Server name box, type ADMIN: followed by the name of the server instance. For example, to connect to a server instance named ACCT\PAYABLE, type ADMIN:ACCT\PAYABLE. | |||
# Complete the Authentication section, providing credentials for a member of the sysadmin group, and then click Connect. | |||
The connection is made. | |||
If the DAC is already in use, the connection will fail with an error indicating it cannot connect. | |||
== CREATION D'UN MIRROIR == | |||
* Création de la base désirée sur le serveur master | |||
* Création des comptes pour acceder à cette base | |||
* Mise en place du mirroir | |||
* Création de la procédure sp_help_revlogin | |||
----- Begin Script, Create sp_help_revlogin procedure ----- | |||
USE master | |||
GO | |||
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL | |||
DROP PROCEDURE sp_hexadecimal | |||
GO | |||
CREATE PROCEDURE sp_hexadecimal | |||
@binvalue varbinary(256), | |||
@hexvalue varchar(256) OUTPUT | |||
AS | |||
DECLARE @charvalue varchar(256) | |||
DECLARE @i int | |||
DECLARE @length int | |||
DECLARE @hexstring char(16) | |||
SELECT @charvalue = '0x' | |||
SELECT @i = 1 | |||
SELECT @length = DATALENGTH (@binvalue) | |||
SELECT @hexstring = '0123456789ABCDEF' | |||
WHILE (@i <= @length) | |||
BEGIN | |||
DECLARE @tempint int | |||
DECLARE @firstint int | |||
DECLARE @secondint int | |||
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) | |||
SELECT @firstint = FLOOR(@tempint/16) | |||
SELECT @secondint = @tempint - (@firstint*16) | |||
SELECT @charvalue = @charvalue + | |||
SUBSTRING(@hexstring, @firstint+1, 1) + | |||
SUBSTRING(@hexstring, @secondint+1, 1) | |||
SELECT @i = @i + 1 | |||
END | |||
SELECT @hexvalue = @charvalue | |||
GO | |||
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL | |||
DROP PROCEDURE sp_help_revlogin | |||
GO | |||
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS | |||
DECLARE @name sysname | |||
DECLARE @xstatus int | |||
DECLARE @binpwd varbinary (256) | |||
DECLARE @txtpwd sysname | |||
DECLARE @tmpstr varchar (256) | |||
DECLARE @SID_varbinary varbinary(85) | |||
DECLARE @SID_string varchar(256) | |||
IF (@login_name IS NULL) | |||
DECLARE login_curs CURSOR FOR | |||
SELECT sid, name, xstatus, password FROM master..sysxlogins | |||
WHERE srvid IS NULL AND name <> 'sa' | |||
ELSE | |||
DECLARE login_curs CURSOR FOR | |||
SELECT sid, name, xstatus, password FROM master..sysxlogins | |||
WHERE srvid IS NULL AND name = @login_name | |||
OPEN login_curs | |||
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd | |||
IF (@@fetch_status = -1) | |||
BEGIN | |||
PRINT 'No login(s) found.' | |||
CLOSE login_curs | |||
DEALLOCATE login_curs | |||
RETURN -1 | |||
END | |||
SET @tmpstr = '/* sp_help_revlogin script ' | |||
PRINT @tmpstr | |||
SET @tmpstr = '** Generated ' | |||
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */' | |||
PRINT @tmpstr | |||
PRINT '' | |||
PRINT 'DECLARE @pwd sysname' | |||
WHILE (@@fetch_status <> -1) | |||
BEGIN | |||
IF (@@fetch_status <> -2) | |||
BEGIN | |||
PRINT '' | |||
SET @tmpstr = '-- Login: ' + @name | |||
PRINT @tmpstr | |||
IF (@xstatus & 4) = 4 | |||
BEGIN -- NT authenticated account/group | |||
IF (@xstatus & 1) = 1 | |||
BEGIN -- NT login is denied access | |||
SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + '''' | |||
PRINT @tmpstr | |||
END | |||
ELSE BEGIN -- NT login has access | |||
SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + '''' | |||
PRINT @tmpstr | |||
END | |||
END | |||
ELSE BEGIN -- SQL Server authentication | |||
IF (@binpwd IS NOT NULL) | |||
BEGIN -- Non-null password | |||
EXEC sp_hexadecimal @binpwd, @txtpwd OUT | |||
IF (@xstatus & 2048) = 2048 | |||
SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')' | |||
ELSE | |||
SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')' | |||
PRINT @tmpstr | |||
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT | |||
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name | |||
+ ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = ' | |||
END | |||
ELSE BEGIN | |||
-- Null password | |||
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT | |||
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name | |||
+ ''', NULL, @sid = ' + @SID_string + ', @encryptopt = ' | |||
END | |||
IF (@xstatus & 2048) = 2048 | |||
-- login upgraded from 6.5 | |||
SET @tmpstr = @tmpstr + '''skip_encryption_old''' | |||
ELSE | |||
SET @tmpstr = @tmpstr + '''skip_encryption''' | |||
PRINT @tmpstr | |||
END | |||
END | |||
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd | |||
END | |||
CLOSE login_curs | |||
DEALLOCATE login_curs | |||
RETURN 0 | |||
GO | |||
----- End Script ----- | |||
CREATE | * Exécution de la procédure sp_help_revlogin | ||
** Cette procédure liste les comptes dans mssql avec leur ID. Il faut créer les comptes sur le slave avec les mêmes informations. | |||
CREATE LOGIN [$login] WITH PASSWORD = $password_en_hexa HASHED, SID = $sid_en_hexa, DEFAULT_DATABASE = [$default_database], CHECK_POLICY = [ON/OFF], CHECK_EXPIRATION = [ON/OFF] | |||
[[category:Basededonnées]] | [[category:Basededonnées]] | ||
[[category:Windows]] | [[category:Windows]] |
Dernière version du 15 octobre 2012 à 10:18
RESTAURATION DE BASES
- la base master : il faut exactement les mêmes version de sqlserveur et les même SP
stopper sqlserver puis le lancer avec la commande suivante
sqlservr.exe -m afin de lancer la resto en single user
Lancer sqlcmd
RESTORE DATABASE master FROM "jeux_de_sauvegarde"
Ensuite il faut aller dans l'entreprise manager et clique droit sur le serveur sql
et choisir propriété.
Aller dans sécurité et modifier le mot de passe du compte utilisé pour lancer SQL et il vous est demandé de fournir le compte admin avec le password
COMMNANDES UTILES
- Backup du dernier journal de transaction après un crash de la base
BACKUP LOG $BDD TO DISK = N'Fichier.trn' WITH NO RECOVERY;
- déconnecter les users d'une base avec rollback des dérnières transaction
ALTER DATABASE $BDD RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
- Création d'un snapshot
CREATE DATABASE snapshot_heure ON (NAME = 'logical_file_name_source', FILENAME='os_file_name_destination.ss' ) AS SNAPSHOT OF source_database_name;
- restauration à partir d'un snapshot
RESTORE DATABASE base_a_resto FROM DATABASE_SNAPSHOT = 'snapshot_heure'
sp_configure 'show advanced options',1
RECONFIGURE
sp_configure
Utiliser une connexion DAC (Dedicated Administrator Connection )
- In SQL Server Management Studio, with no other DACs open, on the toolbar, click Database Engine Query.
- In the Connect to Database Engine dialog box, in the Server name box, type ADMIN: followed by the name of the server instance. For example, to connect to a server instance named ACCT\PAYABLE, type ADMIN:ACCT\PAYABLE.
- Complete the Authentication section, providing credentials for a member of the sysadmin group, and then click Connect.
The connection is made.
If the DAC is already in use, the connection will fail with an error indicating it cannot connect.
CREATION D'UN MIRROIR
- Création de la base désirée sur le serveur master
- Création des comptes pour acceder à cette base
- Mise en place du mirroir
- Création de la procédure sp_help_revlogin
----- Begin Script, Create sp_help_revlogin procedure ----- USE master GO IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL DROP PROCEDURE sp_hexadecimal GO CREATE PROCEDURE sp_hexadecimal @binvalue varbinary(256), @hexvalue varchar(256) OUTPUT AS DECLARE @charvalue varchar(256) DECLARE @i int DECLARE @length int DECLARE @hexstring char(16) SELECT @charvalue = '0x' SELECT @i = 1 SELECT @length = DATALENGTH (@binvalue) SELECT @hexstring = '0123456789ABCDEF' WHILE (@i <= @length) BEGIN DECLARE @tempint int DECLARE @firstint int DECLARE @secondint int SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) SELECT @firstint = FLOOR(@tempint/16) SELECT @secondint = @tempint - (@firstint*16) SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1) SELECT @i = @i + 1 END SELECT @hexvalue = @charvalue GO IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL DROP PROCEDURE sp_help_revlogin GO CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS DECLARE @name sysname DECLARE @xstatus int DECLARE @binpwd varbinary (256) DECLARE @txtpwd sysname DECLARE @tmpstr varchar (256) DECLARE @SID_varbinary varbinary(85) DECLARE @SID_string varchar(256) IF (@login_name IS NULL) DECLARE login_curs CURSOR FOR SELECT sid, name, xstatus, password FROM master..sysxlogins WHERE srvid IS NULL AND name <> 'sa' ELSE DECLARE login_curs CURSOR FOR SELECT sid, name, xstatus, password FROM master..sysxlogins WHERE srvid IS NULL AND name = @login_name OPEN login_curs FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd IF (@@fetch_status = -1) BEGIN PRINT 'No login(s) found.' CLOSE login_curs DEALLOCATE login_curs RETURN -1 END SET @tmpstr = '/* sp_help_revlogin script ' PRINT @tmpstr SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */' PRINT @tmpstr PRINT PRINT 'DECLARE @pwd sysname' WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN PRINT SET @tmpstr = '-- Login: ' + @name PRINT @tmpstr IF (@xstatus & 4) = 4 BEGIN -- NT authenticated account/group IF (@xstatus & 1) = 1 BEGIN -- NT login is denied access SET @tmpstr = 'EXEC master..sp_denylogin + @name + ' PRINT @tmpstr END ELSE BEGIN -- NT login has access SET @tmpstr = 'EXEC master..sp_grantlogin + @name + ' PRINT @tmpstr END END ELSE BEGIN -- SQL Server authentication IF (@binpwd IS NOT NULL) BEGIN -- Non-null password EXEC sp_hexadecimal @binpwd, @txtpwd OUT IF (@xstatus & 2048) = 2048 SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')' ELSE SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')' PRINT @tmpstr EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT SET @tmpstr = 'EXEC master..sp_addlogin + @name + , @pwd, @sid = ' + @SID_string + ', @encryptopt = ' END ELSE BEGIN -- Null password EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT SET @tmpstr = 'EXEC master..sp_addlogin + @name + , NULL, @sid = ' + @SID_string + ', @encryptopt = ' END IF (@xstatus & 2048) = 2048 -- login upgraded from 6.5 SET @tmpstr = @tmpstr + skip_encryption_old ELSE SET @tmpstr = @tmpstr + skip_encryption PRINT @tmpstr END END FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd END CLOSE login_curs DEALLOCATE login_curs RETURN 0 GO ----- End Script -----
- Exécution de la procédure sp_help_revlogin
- Cette procédure liste les comptes dans mssql avec leur ID. Il faut créer les comptes sur le slave avec les mêmes informations.
CREATE LOGIN [$login] WITH PASSWORD = $password_en_hexa HASHED, SID = $sid_en_hexa, DEFAULT_DATABASE = [$default_database], CHECK_POLICY = [ON/OFF], CHECK_EXPIRATION = [ON/OFF]