Archive for the Category: ' MSSQL '

Статья написана автором Галиев Руслан

MS SQL 2000 Shrink/remove transaction log

На практике столкнулся с такой ситуацией в древнем Ms SQL Server 2000. Товарищ отказывался настойчиво обрезать транзакт лог до минимального размера 100mb. И занимал порядка 50Гб.

Решение:

1
2
3
4
5
6
7
8
9
10
USE master
GO
dump TRANSACTION <Имя_Базы_Данных> WITH no_log
GO
USE
GO
DBCC SHRINKFILE (<Имя_Базы_Данных>, 100)
GO
-- После того как лог обрезался, проверьте БД на ошибки
dbcc checkdb(<Имя_Базы_Данных>)
Статья написана автором Галиев Руслан

MS SQL 200 database backup на сетевой диск

В MS SQL 2000 чтобы произвести резервное копирование на сетевой диск до строки самого бэкапа нужно добавить две строки: первая удаляет сетевой диск, вторая заново его монтирует

1
2
3
exec master..xp_cmdshell 'net use z: /d /y'
exec master..xp_cmdshell 'net use z: \\192.168.0.5\Backup /user:USER PASSWORD'
BACKUP DATABASE [BASE] TO  DISK = N'Z:\BASE.BAK' WITH  INIT ,  NOUNLOAD ,  NAME = N'BASE backup',  NOSKIP ,  STATS = 10,  NOFORMAT

Рассмотрим несколько вариантов восстановления БД, в зависимости от того, насколько повреждены файлы БД зависит успешность того или иного метода. Все описанные способы были лично мной проверены на практике и все случаи восстановления, за исключением одного, были успешны. Используйте данное руководство на свой страх и риск, за совершенные вами действия ответственность несете, вы сами.

Итак, во-первых останавливаем службу SQL Server и копируем файлы базы данных (*.mdf и *.ldf) в другую папку, чтобы можно было восстановить их в случае неудачи.

Если у вас есть свежий актуальный бэкап, то дальше можете не читать, а просто восстановите БД из него, тем самым сэкономите драгоценное время, далее я приведу алгоритмы восстановления для разных версий SQL Server. Надеюсь вам это поможет, как в свое время помогло и мне.

Для всех версий SQL Server подойдет следующий вариант: делаем Detach database(отсоединить базу данных), удаляем журнал транзакций(файл с расширением ldf) и делаем Attach database(присоединить базу данных). В мастере выбираем наш mdf файл и жмем ОК.

Далее »

Статья написана автором Галиев Руслан

MS SQL 2000 Shrink log file

Вырос лог файл 1С Бухгалтерии до 70 гигов. После шринка стал 2мб.

Выполняем две операции:

1.

1
BACKUP LOG pubs WITH TRUNCATE_ONLY

2.

1
DBCC SHRINKFILE(Buh_log, 2)
Статья написана автором Галиев Руслан

Загрузка изображение в MS SQL

Необходимо было загрузить изображение в БД MS SQL 2005. В таблице dbo.org_Organization поле Logo имеет тип Image

1
2
3
UPDATE [CALLCENTER].[dbo].[org_Organization]
SET [Logo]=(SELECT * FROM OPENROWSET(BULK 'C:\image.jpg', SINGLE_BLOB) AS [File])
WHERE [ID]=1
Статья написана автором Галиев Руслан

Перенос логинов MS SQL 2005

Скрипт был найден на просторах интернета, и предназначается для переноса логинов БД MS SQL 2005, из одного сервера БД в другой, мной был применён в случае переустановки сервера баз данных. Таким образом позволил избежать геморроя создавать заново пользователей и прописывать им пароли (Все пароли сохраняются , вернее сохраняется их хэш). Единственное что остаётся сделать это прописать у логинов права к базам данных.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
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 (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
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 &lt;= @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 @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR

SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name &lt;&gt; 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
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 ''
WHILE (@@fetch_status &lt;&gt; -1)
BEGIN
IF (@@fetch_status &lt;&gt; -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
Статья написана автором Галиев Руслан

MSSQL sp_who

Предоставляет сведения о текущих пользователях, сеансах и процессах.

Синтаксис

1
sp_who [ [ @loginame = ] 'login' | session ID | 'ACTIVE' ]

Далее »