January 9, 2004 at 3:57 pm
Greetings,
This is a complicated question, if I need to break it into parts please let me know and I will
be happy to do so. I attempted to refresh a test copy of our sqlserver 2000 database using a
backup of our production server. If anyone can point me to specific instructions on how to clone
a database I would be most appreciative. I moved a backup of the application database, the backup
was made out of a maintenance plan, from the test to the production server and using the enterprise
manager asked it to restore the database. The database seems fine, I see the production data in
the test database but the application which uses the database is unable to connect. I worked
with the venor but have not been able to determine why. It was suggested I should try to restore
the master database so I put the database in single user mode and when I attempted the restore
using the enterprise manager I am presented a dialogue box that says it can't do the restore
because it was created by a different version of the server (nnnnnnn) than the server (xxxxxx).
I don't know what this means, if anyone can explain it to me and tell me if there is a way around it
I would be grateful. I do have backups of the master, msdb and application databases made using
a maintenance plan prior to my starting this whole process. Can some one give me guidance in
restoring this test database back to it's original state.
Thanks.
January 9, 2004 at 4:27 pm
Cloning a database is easy in SQL Server. Really. In fact, it looks like you've already succeeded. The problem is in the master database, but restoring the master database is an overkill solution to this problem.
Most likely, your users can't connect simply because the logins in use by the database on the first server are not defined in the master database on the second server. On the second server, run this batch, replacing "mydatabase" with the actual name of the database in question:
exec mydatabase..sp_change_users_login 'REPORT'
Any users that show up in this report are "orphaned," meaning that the user definitions inside the "mydatabase" database do not have any corresponding login defined in the master database. If an orphaned login is a Windows login, just grant access to that same Windows login on the second server, and you should be good to go. If it's a SQL-authenticated login, you will need to recreate the login with the same password that the application is expecting.
I have a script I use when I need to transfer logins from one server to another, when I don't know the logins' passwords. But I won't post that unless you ask for it - hopefully you can get the passwords you need. There is also a DTS task that can help with this.
Hope this helps!
Chris
January 11, 2004 at 4:33 pm
In addition to Chris's comments, this page may help - http://support.microsoft.com/default.aspx?scid=kb;en-us;246133
HTH
------------
Ray Higdon MCSE, MCDBA, CCNA
January 12, 2004 at 5:06 pm
THanks to both of you for your replies. CHris, I would be very interested in seeing the script you spoke of. Thank you.
January 13, 2004 at 9:18 am
Did you read the post Ray mentioned? Our procedure is based almost entirely on the sp_help_revlogin procedure, which is available at http://support.microsoft.com/default.aspx?kbid=246133 (the same link Ray mentioned). Compared to the source code I started with, I believe that all I have done so far is to include code that will generate the same default database for the user. What our code still doesn't do (and I was just discussing this with one of our DBAs as a potential enhancement) is to also recreate fixed system roles for the login on the target server.
Basically, this thing just recreates a little SQL that you can run on the target server. It's very easy to use.
/****** Object: Stored Procedure dbo.sp_its_help_revlogin Script Date: 5/5/2003 4:26:34 PM ******/
CREATE PROCEDURE sp_its_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @dbname 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, db_name(dbid) FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password, db_name(dbid) 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, @dbname
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_its_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_its_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_its_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_its_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
-- Set default database:
PRINT ''
SET @tmpstr = '-- Setting default database:'
PRINT @tmpstr
SET @tmpstr = 'exec sp_defaultdb ' + @name + ', ' + @dbname
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dbname
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
January 14, 2004 at 6:06 pm
Chris, I did see the link referenced. Thanks very much for sharing your code. I do appreciate it.
January 14, 2004 at 8:00 pm
Glad you're finding the kind of info you needed. By the way, I saw that somebody else posted a script that takes fixed server roles into account. It can be found here. I've hardly tested the script, but it appears to provide what my script is missing (plus there is overlap with both scripts providing the default database info). The two scripts could be easily combined, or left as separate so that you could run one without the other if desired.
Cheers,
Chris
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply