----------start of the script-------------
-- this script is used to generate logins in your sql server instance
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 -----
Wednesday, December 22, 2010
Tuesday, December 21, 2010
Database Virtualization
I guess this has already been implemented in some or the other DBMS, latest when I searched in Google, I've seen this being implemented first by Delphi.
But of course everyone is catching up with Cloud.
So I thought why not Sql Server and so are my thoughts on this:
I would like to see the following features in a virtualized database:
1) To restore a database, just drag the source database and drop it on the destination, the system alerts you if you want to replace the destination database and on clicking yes, it should just replace the destination with source
2) drag and drop the database to create a new copy on the destination
3) Create a virtual database with data/log files etc all virtual
4) implement other solutions like mirroring, log shipping and clustering on the virtual databases.
But of course everyone is catching up with Cloud.
So I thought why not Sql Server and so are my thoughts on this:
I would like to see the following features in a virtualized database:
1) To restore a database, just drag the source database and drop it on the destination, the system alerts you if you want to replace the destination database and on clicking yes, it should just replace the destination with source
2) drag and drop the database to create a new copy on the destination
3) Create a virtual database with data/log files etc all virtual
4) implement other solutions like mirroring, log shipping and clustering on the virtual databases.
BCP all the tables in your database
Declare @query nvarchar(100)
Declare @tbl_name varchar(50)
Declare Del_arch_tbls cursor for
Select table_name from information_schema.tables where table_schema='schema2'
Open del_arch_tbls
Fetch next from del_arch_tbls into @tbl_name
While @@fetch_status=0
begin
Set @query = 'xp_cmdshell ' +char(39) +
'bcp databaseA.schema2.'+@tbl_name+
' out f:\ppte\schema2-' + @tbl_name+'.dat -SserverA -T -n'
+char(39)
print @query
exec Sp_executesql @query
Fetch next from del_arch_tbls into @tbl_name
end
Close del_arch_tbls
Deallocate del_arch_tbls
I am updating this post after I found that there's an easy way of doing this.
simply use the below to query all the tables in your database.
sp_msforeachtable ' sp_spaceused [?]'
so easy
Declare @tbl_name varchar(50)
Declare Del_arch_tbls cursor for
Select table_name from information_schema.tables where table_schema='schema2'
Open del_arch_tbls
Fetch next from del_arch_tbls into @tbl_name
While @@fetch_status=0
begin
Set @query = 'xp_cmdshell ' +char(39) +
'bcp databaseA.schema2.'+@tbl_name+
' out f:\ppte\schema2-' + @tbl_name+'.dat -SserverA -T -n'
+char(39)
print @query
exec Sp_executesql @query
Fetch next from del_arch_tbls into @tbl_name
end
Close del_arch_tbls
Deallocate del_arch_tbls
I am updating this post after I found that there's an easy way of doing this.
simply use the below to query all the tables in your database.
sp_msforeachtable ' sp_spaceused [?]'
so easy
Query to list all the backups
use msdb
go
select f.file_size/(1024*1024) 'file_size',s.backup_size/(1024*1024) 'backup_size',s.backup_finish_date,s.database_name,f.logical_name from
backupset s inner join backupfile f on s.backup_set_id=f.backup_set_id
where s.database_name='rar' and s.type='D'
order by s.backup_finish_date desc
go
select f.file_size/(1024*1024) 'file_size',s.backup_size/(1024*1024) 'backup_size',s.backup_finish_date,s.database_name,f.logical_name from
backupset s inner join backupfile f on s.backup_set_id=f.backup_set_id
where s.database_name='rar' and s.type='D'
order by s.backup_finish_date desc
Query to backup all databases
There are other easy ways to take backup, but this is just one of the other ways.
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'e:\Program Files\Microsoft SQL Server\backup-CR30581-20100507'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
--WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
--master.xp_fixeddrives
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'e:\Program Files\Microsoft SQL Server\backup-CR30581-20100507'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
--WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
--master.xp_fixeddrives
SQL Server - inventories
I've been thinking and thinking to dig out how sql server was built and how the storage engine works in sql server.
I almost became an instant fan of Paul S Randal by reading his blogs on the concepts.
So I started my own blog.
Here are some features in MSSQL on which I have little or no knowledge.
1) Status/Status2 columns in sysdatabases table in Master database, I checked with Paul on this and most of the statuses apart from the usual 1,2,4,8.... are undocumented and hence the results/descriptions vary after every release
2) Implementing Database in RAM, there is ofcourse Ramdisk which is available as free download where you can make some part of your RAM as a hard drive and put your user databases in that.
I am yet to try this
3) DB Cluster in the same instance - kind of wierd thought but y not??
Lets suppose we have two databases in the same instance, one acting as an active DB and the other as a passive DB, Now I'll create a virtual DB to which users have access.
Users don't know that there are two physical databases.
So at any point of time only one Database is online and the other will be readonly/standby mode same as the secondary database in log shipping.
And the t-log backups should be applied continuously on the secondary.
so its like we have log shipping in place between two different databases with the same name on the same instance.
Now the next question is what happens in case of failover.
when Database A goes down, all the connections will be redirected to Database B with all the incomplete transactions starting from the beginning on B.
This seems a bit of work.
Also we can configure the failover like this:
Do the failover if any of the following conditions are true:
1) If Tlog is full and no further disk space to grow
2) If file group is full and the corresponding disk is full as well
3) DB going to Suspect mode
Is this ideally possible ?? I don't have an answer for this.
I almost became an instant fan of Paul S Randal by reading his blogs on the concepts.
So I started my own blog.
Here are some features in MSSQL on which I have little or no knowledge.
1) Status/Status2 columns in sysdatabases table in Master database, I checked with Paul on this and most of the statuses apart from the usual 1,2,4,8.... are undocumented and hence the results/descriptions vary after every release
2) Implementing Database in RAM, there is ofcourse Ramdisk which is available as free download where you can make some part of your RAM as a hard drive and put your user databases in that.
I am yet to try this
3) DB Cluster in the same instance - kind of wierd thought but y not??
Lets suppose we have two databases in the same instance, one acting as an active DB and the other as a passive DB, Now I'll create a virtual DB to which users have access.
Users don't know that there are two physical databases.
So at any point of time only one Database is online and the other will be readonly/standby mode same as the secondary database in log shipping.
And the t-log backups should be applied continuously on the secondary.
so its like we have log shipping in place between two different databases with the same name on the same instance.
Now the next question is what happens in case of failover.
when Database A goes down, all the connections will be redirected to Database B with all the incomplete transactions starting from the beginning on B.
This seems a bit of work.
Also we can configure the failover like this:
Do the failover if any of the following conditions are true:
1) If Tlog is full and no further disk space to grow
2) If file group is full and the corresponding disk is full as well
3) DB going to Suspect mode
Is this ideally possible ?? I don't have an answer for this.
Subscribe to:
Posts (Atom)