More Intelligent Backup and Restore

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

  • Nice article and useful procs

    Any thoughts on adding transaction log backups to the mix?

  • Ian,

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

    Vince

  • 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

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

  • 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

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

  • 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

  • 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?

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

  • Yup. It all works as expected now. Thank you very much.

    Let me ask you one more question

    When I run EXEC sp_ABRestoreDb 'Newluis', 'C:\temp\08-23-05-staffingdb' the database is created successfully and the DB files (MDF and LDF) are both created under the default backup directory.

    How can I tell your script to use a different location for the physical files? In other words, instead of creating the following:

    C:\Program Files\Microsoft SQL Server\MSSQL\Data\Newluis_data.MDF

    C:\Program Files\Microsoft SQL Server\MSSQL\Data\Newluis_log.LDF

    I want the following:

    C:\temp\Newluis_data.MDF

    C:\temp\Newluis_log.LDF

    Where temp is a directory I pass as a parameter. Is this possible?

  • Not directly. My suggestion would be to use the @DryRun=1 parameter, which will generate the statements to restore the database but not execute them. You can then edit the statement to direct the files to your temp directory.

    Hope that helps.

  • Just as an FYI, I sent Steve Jones an update to my .ZIP to fix the similarly-named but wrong file I included initially. Sorry for any troubles that oversight caused anyone.

    Vince

  • I just ran across your set of stored procs when looking to upgrade our backup processes.

    I also could use transaction log ability.

    I stripped out the main components of the script to quickly generate a transaction log backup. Right now I am contemplating running this as a seperate stored proc or reintegrating it as another parameter.

    I don't forsee needing to worry about modifying the restore procedure to be able to restore transaction logs, at lease in our case. The only way we will be doing transaction log restores is in a DR situation and we will be wearing kid gloves then so we won't be using automated scripts anyway.

    Maybe someone doing log shipping could use that function, but I would be willing to bet there are plenty of log shipping scripts out there that already handle this...

    If I do reintegrate the tlog backups, let me know if you want the updated stored procs...

    Thank You,

    Kevin

  • Another thought, we centrally locate all our SQL backups on one UNC server. We are in the stage where we have more than one SQL server, but don't have a SAN.

    So in our backup enviornments we backup like so:

    \\UNC\SHARE\SERVERNAME\DBNAME\DBNAME_db_YYYMMDDHHMM.bak

    It would be nice to pull the {SERVERNAME} parameter automatically and stuff that in as another option. Most people probalby don't need that though, so that might be overkill.

Viewing 15 posts - 1 through 15 (of 44 total)

You must be logged in to reply to this topic. Login to reply