November 11, 2009 at 12:33 pm
I have been provided a SQL Safe (idera) script by my manager and he wants me to either use SSIS or create a SQL job for it. Unfortunatly I don't have access to any testing environments so I need to have this all worked out in the implementation cognitively (no trial and error). I was told this script works fine when running it from through Query Analyzer (i.e. SSMS) it needs to be created and scheduled as a job. I have pasted the script below for your reference.
If I created a SQL job and added this as a step my assumption is that it would run under the login I specify the job step and if it was run created on and run under the eSitedb1 server instance - that contains the database to backup - it should be able to call the [master].[dbo].[xp_ss_backup] procedure and pass in the specified parameters. I don't see why there should be a need for the ":CONNECT eSitedb1" line entry in the script? I have never seen this ":CONNECT" command before but assume it serves the same purpose as USE databasename would?
The first part of the script instructs the xp_ss_backup procedure what database to backup and where to copy the backup to (i.e. \\esitedb2\eSiteMonthEndLogBackups\eSiteProductionCopy.safe'), and the name to give the backup. From this I have question:
1) It is my understanding that the copy process to a share location should be seemless if the job is run with a integrated windows login that has permissions to that network share location? However, if it is run under a SQL Login wouldn't something like a device or linked server need to be created and configured?
The second part of the script is supposed to restore the copied database to another a different instance on another server.
Any insights and suggestions is greatly appreciated. Second day on new job as a Application System Analyst and this is what my manager tasks me with...
-- Start of script... --
:CONNECT eSitedb1
GO
DECLARE @Returncode int
EXEC @Returncode = [master].[dbo].[xp_ss_backup] @database = 'eSiteProduction'
, @filename = '\\esitedb2\eSiteMonthEndLogBackups\eSiteProductionCopy.safe'
, @overwrite = 1
-- Specifies that the backup does not affect the normal sequence of backups (SQL Server 2005 Only). Allowed values {0|1}.
, @copyonly = 1
if @Returncode != 0
begin
Print 'Backup failed: ' + convert(nvarchar(5),@Returncode)
return
end
GO
:CONNECT eSitedb2
DECLARE @DatabaseName nvarchar(255)
DECLARE @ArchivePathAndFilename varchar(255)
DECLARE @Debug int
DECLARE @BackupSet int
DECLARE @DisconnectUsers int
DECLARE @WindowsUsername nvarchar(255)
DECLARE @EncryptedWindowsPassword nvarchar(255)
DECLARE @NoStatus int
DECLARE @withMoveData nvarchar(1024)
DECLARE @withMoveLog nvarchar(1024)
DECLARE @Replace int
DECLARE @RecoveryMode nvarchar(10)
DECLARE @UndoFile nvarchar(1024)
DECLARE @StopAt nvarchar(255)
DECLARE @StopAtMark nvarchar(255)
DECLARE @StopBeforeMark nvarchar(255)
DECLARE @After nvarchar(255)
DECLARE @ContinueAfterError int
DECLARE @RestoreReturncode int
SET @DatabaseName = 'eSiteProductionCopy'
SET @ArchivePathAndFilename = '\\esitedb2\eSiteMonthEndLogBackups\eSiteProductionCopy.safe'
SET @Debug = 1
SET @Replace = 1
Set @DisconnectUsers = 1
Set @withMoveData = 'esiteproduction_data F:\Program Files\Microsoft SQL Server\MSSQL\Data\' + @DatabaseName + '_data.mdf'
Set @withMoveLog = 'esiteproduction_log E:\Program Files\Microsoft SQL Server\MSSQL\Logs\' + @DatabaseName + '_log.ldf'
EXEC @RestoreReturncode = [master].[dbo].[xp_ss_restore]
-- *************** Required parameters ***************
-- Name of database to backup.
@database = @DatabaseName,
-- Archive path and filename (where backup set is located).
@filename = @ArchivePathAndFilename,
-- *************** Common Options ***************
-- The backup set to restore (1 based).
@backupset = @BackupSet,
-- Disconnects all users from the target database before the restore operation begins. Allowed values {0|1}.
@disconnectusers = @DisconnectUsers,
-- The database logical filename to move to the physical target filename.
-- '<logical_file_to_move> <physical_target_filename>', e.g., @withmove = 'data c:ewfile.ndf'.
--@withmove = @withMoveData,
--@withmove = @withMoveLog,
-- If true (1), During restore, create the specified database and its related files even if another database already exists with the same name. Allowed values {0|1}.
@replace = @Replace,
-- *************** Security Options ***************
-- The Widnows user that will be used to read the backup archive file on the remote server.
@windowsusername = @WindowsUsername,
-- The password for the Windows user specified in the @windowsusername.
@encryptedwindowspassword = @EncryptedWindowsPassword,
-- *************** Advanced Options ***************
-- Instructs SQL Server to continue the operation despite encountering errors such as invalid checksums (SQL Server 2005 Only). Allowed values {0|1}.
@continueaftererror = @ContinueAfterError,
-- Toggles display of the command line arguments which can be used to invoke SQLsafeCMD from the command line. Allowed values {0|1}.
@debug = @Debug,
-- Prevents status messages from being cached or sent to the Repository. Allowed values {0|1}.
@nostatus = @NoStatus,
-- Specifies the mode in which to leave the database after restore {norecovery | standby}.
@recoverymode = @RecoveryMode,
-- The absolute path to the undo filename (standby recovery mode only).
@undofile = @UndoFile,
-- Specifies that the database be restored to the state it was in as of the specified date and time (log backup type only).
@stopat = @StopAt,
-- Specifies recovery to the specified mark, including the transaction that contains the mark (log backup type only).
@stopatmark = @StopAtMark,
-- Specifies recovery to the specified mark, but does not include the transaction that contains the mark.
@stopbeforemark = @StopBeforeMark,
-- Recovery stops at the first mark having the specified name exactly at or after the datetime (log backup only, stop at/before mark only).
@after = @After
IF @RestoreReturncode != 0
BEGIN
RAISERROR
('Restore failure of database %s.', 16, 1, @DatabaseName)
END
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
November 11, 2009 at 1:33 pm
The ":CONNECT" command is for SQLCMD.exe. If you use it in Management Studio, there's an option for "Use SQLCMD mode". What it does is connect to a server, and it has to have a valid server name or it returns an error. The T-SQL commands after it are run on the server indicated.
I would definitely recommend against running something in production without testing it thoroughly first. If you don't have a test/dev environment, and management won't help you set one up (even if it's as simple as getting a copy of SQL Server Dev Edition for your desktop), I'd definitely look into seeking employment elsewhere ASAP.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 11, 2009 at 1:51 pm
Thanks for the information SSCertifiable. Yes, it is a very awkward position for me to be in. I have never been in a situation where I have no tools to use in creating a solution. I hadn't realized the importance of the principle of trial and error as much as I do now.
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
November 12, 2009 at 6:58 am
Not that it actually matters, but SSCertifiable is a title based on number of posts and points. The username is right above that ("GSquared"). Just like with your posts.
Has your boss explained why he wants this run in production without testing? Just because I can't think of a valid reason for that doesn't mean one doesn't exist. Perhaps it's already been tested and he just wants you to take it live?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 12, 2009 at 1:57 pm
Sorry about that GSquared. I'm pretty sure the reason that he won't provide me access to anything to test things is because he believes I should be able to work it all out in my mind and testing isn't necessary. However, since he has access to the testing and staging servers he might test it prior to implementation in production. I'm more used to working through something in a trial and error fashion and having technology to be able to do that.
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
November 13, 2009 at 6:56 am
If it comes down to it, get a copy of Dev Edition for yourself. It's only about $50, and it's worth it to have.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply