More Intelligent Backup and Restore

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Vince Iacoboni » More Intelligent Backup and Restore

45 posts, Page 1 of 5

1 2 3 4 5 »»»

More Intelligent Backup and Restore

Rate Topic

Display Mode

Topic Options

Author

Message

viacoboni

Posted Tuesday, October 25, 2005 4:43 PM

Say Hey Kid

Group: General Forum Members
Last Login: Friday, May 03, 2013 11:50 AM
Points: 712, Visits: 410

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/vIacoboni/moreintelligentbackupandrestore.asp

Post #232234

Ian Yates

Posted Monday, November 07, 2005 6:13 PM

Ten Centuries

Group: General Forum Members
Last Login: Sunday, September 16, 2012 3:26 AM
Points: 1,038, Visits: 443

Nice article and useful procs

Any thoughts on adding transaction log backups to the mix?

Post #235617

viacoboni

Posted Tuesday, November 08, 2005 6:43 AM

Say Hey Kid

Group: General Forum Members
Last Login: Friday, May 03, 2013 11:50 AM
Points: 712, Visits: 410

Ian,

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

Vince

Post #235698

moncel

Posted Tuesday, November 08, 2005 6:49 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, February 20, 2007 12:02 PM
Points: 1, 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

Post #235699

Luis Cabrera-257608

Posted Tuesday, November 22, 2005 12:44 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, July 23, 2007 2:31 PM
Points: 16, 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.

Post #238964

viacoboni

Posted Tuesday, November 22, 2005 12:57 PM

Say Hey Kid

Group: General Forum Members
Last Login: Friday, May 03, 2013 11:50 AM
Points: 712, Visits: 410

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

Post #238966

Luis Cabrera-257608

Posted Tuesday, November 22, 2005 1:15 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, July 23, 2007 2:31 PM
Points: 16, 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.

Post #238972

viacoboni

Posted Tuesday, November 22, 2005 2:13 PM

Say Hey Kid

Group: General Forum Members
Last Login: Friday, May 03, 2013 11:50 AM
Points: 712, Visits: 410

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

Post #238983

Luis Cabrera-257608

Posted Tuesday, November 22, 2005 2:34 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, July 23, 2007 2:31 PM
Points: 16, 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?

Post #238985

viacoboni

Posted Tuesday, November 22, 2005 2:41 PM

Say Hey Kid

Group: General Forum Members
Last Login: Friday, May 03, 2013 11:50 AM
Points: 712, Visits: 410

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...

Post #238987

« Prev Topic | Next Topic »

45 posts, Page 1 of 5

1 2 3 4 5 »»»

Permissions