February 14, 2013 at 3:11 am
Hi,
What is the database refresh. how it will work.
How to do the database refresh in sql server real time environment.
Thanks in advance.
February 14, 2013 at 3:15 am
Could you explain what you mean by database refresh?
February 14, 2013 at 3:21 am
Modifying the users attribute causes back-end sessions to be refreshed automatically.
Actually some one asked me about this.
February 14, 2013 at 3:42 am
Ask the person what they meant, because your explanation does not have enough info. What users of what, what attributes, what back-end, what sessions?
The common meaning for 'database refresh' that I've seen is copying a production database to dev or test to create a fresh copy there.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2013 at 4:26 am
K thank u
February 15, 2013 at 8:15 am
Hi
Database refresh in the sense you have to take the backup of source server database ( Prod or any) and restore with replace on Destination server.
If you are using T-sql script make sure Logical names, Physical location and Physical Names.
Before doing this please run Sp_help_revlogin for scriptout the login on destination server after restore the database you have to sync the logins. this is called refresh the database if you need Sp_help_revlogin script findout in online or ping me. 🙂
March 7, 2013 at 10:29 pm
Hi
Database refresh in the sense you have to take the backup of source server database ( Prod or any) and restore with replace on Destination server.
If you are using T-sql script make sure Logical names, Physical location and Physical Names.
Before doing this please run Sp_help_revlogin for scriptout the login on destination server after restore the database you have to sync the logins. this is called refresh the database if you need Sp_help_revlogin script findout in online or ping me. [Smile]
Thank you
Whether We need to run the Sp_help_revlogin script before restore the database in destination server or after restoring
Can you please provide the script
If we run the script orphan user problem will solve or not
March 8, 2013 at 8:16 am
SP_HELP_revlogin will give sql logins script with encripted password and SID. so, you have to run before restore. logins script you have to run after restore.
in this you are getting logins script when you restore the db source users overwrite on destination with this befrore restore you will get logins script right. after restore if you run the logins script it will sync up back.
below is the script
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_hexadecimal] Script Date: 10/09/2012 14:50:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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 <= @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
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_help_revlogin] Script Date: 9/10/2012 6:23:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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 <> '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 <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group
SET @tmpstr = 'IF NOT EXISTS (SELECT * From syslogins WHERE loginname = '''+ ( @name ) + ''' )' + char(13) +
'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 = 'IF NOT EXISTS (SELECT * From syslogins WHERE loginname = '''+ ( @name ) + ''' )' + char(13) +
' 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
March 8, 2013 at 9:17 pm
Here first script we need to run in source sever.
After restoring the data in destination we need to run second script
it is right.
March 8, 2013 at 11:07 pm
NO, the script will create 2 store procs on your master db
step1: create proc's on destination server
step2: exec sp_help_revlogin in destination server
save the result set in some where(create login script is the result set)
step3: take the backup on source server
step4: restore on the destination server
step5: run the login script on destination server(result set of sp_help_revlogin)
note: when you restore the db source server users will over write on the destinaton database, we have to resync the logins. so, thats why we are taking logins script with sid valuve & encrypted PW
March 9, 2013 at 12:34 am
Thank you
March 19, 2015 at 4:33 am
How do we refresh the new updated fresh database with the old database without replacing the database?
Old Database would be the client database and we cannot the directly replace the database on the client database as it will consist live data.
Our requirement is to refresh new changes on on the old database without hampering the client live data.
Its likely to be an project upgrade.Can someone assist me on this,how can we achieve this without using any third party tool.
I am aware how to achieve this by making manual script. But is there any other way that we can implement this more smoothly,as it will be happening very often and running
manual script will be time consuming.
March 27, 2015 at 2:20 pm
Database refresh:
When someone asks you to refresh a database, please be sure to ask them the below source and destination server information...
Source Server:
Server Name: PROD\Inst1,12000
Database Name: SQLSERVER
ENVIRONMENT: PROD
Destination Server:
Server Name: TEST\Inst1,12000
Database Name: SQLSERVER
ENVIRONMENT: STAGING
Remember when you restore database on staging server...
1. First thing would be to extract the permissions.
2. Restore the database on test server from most recent available backup from PROD.
3. Re-apply the permissions which you have extracted in first step.
You could find Script to extract the permissions here.....
http://www.sqlservercentral.com/scripts/Security/71562/
Hope this helps 🙂
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply