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