MSSQL

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 )

1. In SQL Server Management Studio, with no other DACs open, on the toolbar, click Database Engine Query.
2. 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.
3. 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]