<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 Administration
»
Database Refresh
11 posts, Page 1 of 2
1
2
»»
Database Refresh
Rate Topic
Display Mode
Topic Options
Author
Message
New persopn
New persopn
Posted Thursday, February 14, 2013 3:11 AM
Valued Member
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:56 AM
Points: 56,
Visits: 163
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.
Post #1419912
DBArrr!
DBArrr!
Posted Thursday, February 14, 2013 3:15 AM
Valued Member
Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:23 PM
Points: 70,
Visits: 54
Could you explain what you mean by database refresh?
Post #1419916
New persopn
New persopn
Posted Thursday, February 14, 2013 3:21 AM
Valued Member
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:56 AM
Points: 56,
Visits: 163
Modifying the users attribute causes back-end sessions to be refreshed automatically.
Actually some one asked me about this.
Post #1419919
GilaMonster
GilaMonster
Posted Thursday, February 14, 2013 3:42 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 4:15 PM
Points: 37,651,
Visits: 29,903
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 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1419931
New persopn
New persopn
Posted Thursday, February 14, 2013 4:26 AM
Valued Member
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:56 AM
Points: 56,
Visits: 163
K thank u
Post #1419968
AAKR
AAKR
Posted Friday, February 15, 2013 8:15 AM
SSC Journeyman
Group: General Forum Members
Last Login: Thursday, May 09, 2013 11:14 AM
Points: 81,
Visits: 283
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.
Post #1420572
New persopn
New persopn
Posted Thursday, March 07, 2013 10:29 PM
Valued Member
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:56 AM
Points: 56,
Visits: 163
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
Post #1428387
AAKR
AAKR
Posted Friday, March 08, 2013 8:16 AM
SSC Journeyman
Group: General Forum Members
Last Login: Thursday, May 09, 2013 11:14 AM
Points: 81,
Visits: 283
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
Post #1428598
New persopn
New persopn
Posted Friday, March 08, 2013 9:17 PM
Valued Member
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:56 AM
Points: 56,
Visits: 163
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.
Post #1428841
AAKR
AAKR
Posted Friday, March 08, 2013 11:07 PM
SSC Journeyman
Group: General Forum Members
Last Login: Thursday, May 09, 2013 11:14 AM
Points: 81,
Visits: 283
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
Post #1428853
« Prev Topic
|
Next Topic »
11 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
