Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

More Intelligent Backup and Restore Expand / Collapse
Author
Message
Posted Tuesday, October 25, 2005 4:43 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 07, 2014 10:46 AM
Points: 716, Visits: 447
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/vIacoboni/moreintelligentbackupandrestore.asp


Post #232234
Posted Monday, November 07, 2005 6:13 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444

Nice article and useful procs

Any thoughts on adding transaction log backups to the mix?




Post #235617
Posted Tuesday, November 08, 2005 6:43 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 07, 2014 10:46 AM
Points: 716, Visits: 447
Ian,

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

Vince



Post #235698
Posted Tuesday, November 08, 2005 6:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, November 22, 2005 12:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Tuesday, November 22, 2005 12:57 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 07, 2014 10:46 AM
Points: 716, Visits: 447
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
Posted Tuesday, November 22, 2005 1:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Tuesday, November 22, 2005 2:13 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 07, 2014 10:46 AM
Points: 716, Visits: 447
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
Posted Tuesday, November 22, 2005 2:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Tuesday, November 22, 2005 2:41 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 07, 2014 10:46 AM
Points: 716, Visits: 447
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 »

Add to briefcase 12345»»»

Permissions Expand / Collapse