SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


More Intelligent Backup and Restore


More Intelligent Backup and Restore

Author
Message
vince.iacoboni@db.com
vince.iacoboni@db.com
SSC Eights!
SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)

Group: General Forum Members
Points: 937 Visits: 552
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/vIacoboni/moreintelligentbackupandrestore.asp



Ian Yates
Ian Yates
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1822 Visits: 445

Nice article and useful procs

Any thoughts on adding transaction log backups to the mix?





vince.iacoboni@db.com
vince.iacoboni@db.com
SSC Eights!
SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)

Group: General Forum Members
Points: 937 Visits: 552
Ian,

Not more than a passing thought at this point, but I'll think about how to integrate it some more.

Vince



moncel
moncel
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 1

Very good article!

I've been struggle for a month or two to write 2 scripts for restoring an SQL Lite Speed full backup and another script for restoring the transaction logs.

I didn't have time to look over your whole script in details but I had difficulties in building the restore string with move option because it exceeded the number of characters (8000).Finally I used a temporary table with a text column which stores the whole string (more than 9000 of characters).This is string is bulked copied into another script which is run with master.dbo.xp_cmdshell .

Congratulations and I guess a script for restoring of the transaction logs will complete the excellent article.

Miki


Luis Cabrera-257608
Luis Cabrera-257608
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 1

Excellent article indeed!

I just had an issue running the following script "sp_ABFixUserLoginLinks.sql" in SQL Server 2000. It seems like the script was written back in 1998 for SQL7 and does not work in SQL 2000?

I tried to fix the issue myself but my little brain did not cooperate.


vince.iacoboni@db.com
vince.iacoboni@db.com
SSC Eights!
SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)

Group: General Forum Members
Points: 937 Visits: 552
Luis,

If you give me a little more information on the error and the contents of syslogins and sysusers I'd be happy to look at it.

Vince



Luis Cabrera-257608
Luis Cabrera-257608
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 1

Thanks for your prompt reply!

Basically I opened Query Analyzer and tried to run the sp_ABFixUserLoginLinks.sql script. The following errors poped up:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Configuration option 'allow updates' changed from 1 to 1. Run the RECONFIGURE statement to install.
Server: Msg 207, Level 16, State 3, Procedure sp_ABFixUserLoginLinks, Line 37
Invalid column name 'Suid'.
Server: Msg 207, Level 16, State 1, Procedure sp_ABFixUserLoginLinks, Line 37
Invalid column name 'suid'.
Server: Msg 207, Level 16, State 1, Procedure sp_ABFixUserLoginLinks, Line 64
Invalid column name 'suid'.
Server: Msg 207, Level 16, State 1, Procedure sp_ABFixUserLoginLinks, Line 64
Invalid column name 'suid'.
Server: Msg 207, Level 16, State 1, Procedure sp_ABFixUserLoginLinks, Line 64
Invalid column name 'suid'.
Server: Msg 207, Level 16, State 1, Procedure sp_ABFixUserLoginLinks, Line 64
Invalid column name 'suid'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.


vince.iacoboni@db.com
vince.iacoboni@db.com
SSC Eights!
SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)

Group: General Forum Members
Points: 937 Visits: 552
Well, I goofed. I used a similarly-named stored procedure that was meant for SQL 7, as you suspected. Thanks for finding this, Luis. Here's the proc I meant to include:

USE master
GO

IF OBJECT_ID('sp_abFixUserLogins') IS NULL
EXEC('CREATE PROCEDURE sp_abFixUserLogins AS BEGIN RETURN END')
GO


ALTER PROCEDURE sp_abFixUserLogins

/* Name: sp_abFixUserLogins
*
* Purpose: Resync SQL Server logins after a database load.
*
* Source: SQLServerPerformance.com
*
* Modification History:
* 08/18/2005 VRI Created.
*
*/

AS

DECLARE @UserName nvarchar(255)

DECLARE orphanuser_cur CURSOR FOR
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1
AND sid IS NOT NULL
AND sid <> 0x0
AND suser_sname(sid) IS NULL
AND EXISTS (SELECT *
FROM master.dbo.syslogins
WHERE name = sysusers.name)
ORDER BY name

OPEN orphanuser_cur
FETCH NEXT
FROM orphanuser_cur
INTO @UserName

WHILE (@@fetch_status = 0)
BEGIN
PRINT @UserName + ' user name being resynced'

EXEC sp_change_users_login 'Update_one', @UserName, @UserName

FETCH NEXT
FROM orphanuser_cur
INTO @UserName
END

CLOSE orphanuser_cur
DEALLOCATE orphanuser_cur
go


GO

GRANT EXEC ON sp_abFixUserLogins TO PUBLIC
GO



Luis Cabrera-257608
Luis Cabrera-257608
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 1

You're the man!

One more thing. When I installed the FUNCTION fn_Split I got the following:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.
Caution: Changing any part of an object name could break scripts and stored procedures.

Is this correct?


vince.iacoboni@db.com
vince.iacoboni@db.com
SSC Eights!
SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)

Group: General Forum Members
Points: 937 Visits: 552
Yeah, that's just because we're installing it as a system function. Had to turn "allow updates" on to do that. You can safely ignore the spewage...



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search