February 7, 2014 at 6:10 am
Just so you know, it's kind of dangerous to be using queries like this if you're not sure what they actually do (just saying). This being said, the SUBSTRING() function is quite simple, it takes 3 parameters: 1) the "String" you wish to parse, 2) the starting position, and 3) how many characters to go for (i.e. length).
So in your case, you have "xxxdb_backup_2014_02_06_073328_1849272.trn'" - this string is 42 characters long. You want to start at the 14th character and stop after 25 characters.
What you need is SELECT SUBSTRING('xxxdb_backup_2014_02_06_073328_1849272.trn'', 14, 25)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 7, 2014 at 7:56 am
MyDoggieJessie (2/7/2014)
Just so you know, it's kind of dangerous to be using queries like this if you're not sure what they actually do (just saying). This being said, the SUBSTRING() function is quite simple, it takes 3 parameters: 1) the "String" you wish to parse, 2) the starting position, and 3) how many characters to go for (i.e. length).So in your case, you have "xxxdb_backup_2014_02_06_073328_1849272.trn'" - this string is 42 characters long. You want to start at the 14th character and stop after 25 characters.
What you need is
SELECT SUBSTRING('xxxdb_backup_2014_02_06_073328_1849272.trn'', 14, 25)
Thanks for finishing that up.
That said, if the xxx is variable in length (meaning it is not always 3 characters) the parsing of this backup name will produce unwanted results.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 7, 2014 at 7:58 am
You can also use this script (there are many at this site by the way)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 7, 2014 at 9:45 am
Hey everyone! It's ugly code Friday!!! :hehe:
Here's the ugliest way around the issue (as pointed out by SQLRNNR)D-ECLARE @String varchar(250)
SET @String = 'xxxdb_backup_2014_02_06_073328_1849272.trn'
SELECT LEFT(@String, CHARINDEX('_', @string, 7)) + REVERSE(SUBSTRING(REVERSE(@String), 5, 25)) + '.trn' "7" is chosen to find the next occurrence of the underscore being used in the string, otherwise it will pick up the first one at position 6 in the string.
Is it 5 o'clock yet?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 7, 2014 at 11:05 am
MyDoggieJessie (2/7/2014)
Hey everyone! It's ugly code Friday!!! :hehe:Here's the ugliest way around the issue (as pointed out by SQLRNNR)
D-ECLARE @String varchar(250)SET @String = 'xxxdb_backup_2014_02_06_073328_1849272.trn'
SELECT LEFT(@String, CHARINDEX('_', @string, 7)) + REVERSE(SUBSTRING(REVERSE(@String), 5, 25)) + '.trn' "7" is chosen to find the next occurrence of the underscore being used in the string, otherwise it will pick up the first one at position 6 in the string.
Is it 5 o'clock yet?
Oh boy. I'm sure we could really come up with uglier code than that. 😉
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 7, 2014 at 10:15 pm
Hi All,
My requirement is to pick the TRN's automatically and restore them in the right sequence keeping the DB in stand by mode. I used this but it is not worked out since it is using the particular named TRN only ("xxxdb_backup_2014_02_06_073328_1849272.trn'").
So for time being I am used the below script which will pick the latest TRN in a folder and restore that continuously keeps the database in stand by mode. so the users can read the database.
Any suggestions let me know
Declare @FileName varChar(255)
Declare @cmdText varChar(255)
Declare @bkfolder varchar(255)
set @FileName = null
set @cmdText = null
set @bkfolder = 'D:\SQL SERVER\INSTONE\DB1\BACKUP\'
DECLARE @FileList table (
FileName varchar(255),
DepthFlag int,
FileFlag int
)
--get all the files and folders in the backup folder and put them in temporary table
insert into @FileList exec xp_dirtree @bkfolder,0,1
--select * from @filelist
--get the latest backup file name
select top 1 @FileName = @bkfolder + FileName from @FileList where Filename like '%.trn' order by filename desc
select @filename
--kick off current users/processes
-- Kill all the Processes of a Database
DECLARE @DatabaseName nvarchar(50)
-- Set the Database Name
SET @DatabaseName = N'DB2'
-- Select the current Daatbase
-- SET @DatabaseName = DB_NAME()
DECLARE @SQL varchar(max)
SET @SQL = ''
SELECT @SQL = @SQL + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId
-- You can see the kill Processes ID
-- SELECT @SQL
--Kill the Processes
exec('RESTORE LOG [DB2]
FROM DISK = ''' + @filename + '''
WITH STANDBY = N''D:\SQL SERVER\INSTTWO\BACKUP\ROLLBACK_UNDO_DB2.BAK'', NOUNLOAD, STATS = 10')
go
February 8, 2014 at 8:59 am
You can use this snippet of code I found online (I believe it came from mssqltips.com), this will give you the proper files to load since your last full backupDECLARE @databaseName sysname
DECLARE @backupStartDate datetime
DECLARE @backup_set_id_start int
DECLARE @backup_set_id_end int
-- set database to be used
SET @databaseName = 'DATABASENAME'
SELECT
@backup_set_id_start = MAX(backup_set_id)
FROM
msdb.dbo.backupset
WHERE
database_name = @databaseName
AND type = 'D'
SELECT
@backup_set_id_end = MIN(backup_set_id)
FROM
msdb.dbo.backupset
WHERE
database_name = @databaseName
AND type = 'D'
AND backup_set_id > @backup_set_id_start
IF @backup_set_id_end IS NULL
SET @backup_set_id_end = 999999999
SELECT
backup_set_id,
'RESTORE DATABASE ' + @databaseName + ' FROM DISK = ''' + mf.physical_device_name
+ ''' WITH STANDBY = N''<<YOUR PATH>>'
FROM
msdb.dbo.backupset b,
msdb.dbo.backupmediafamily mf
WHERE
b.media_set_id = mf.media_set_id
AND b.database_name = @databaseName
AND b.backup_set_id = @backup_set_id_start
UNION
SELECT
backup_set_id,
'RESTORE LOG ' + @databaseName + ' FROM DISK = ''' + mf.physical_device_name + ''' WITH STANDBY = N''<<YOUR PATH>>'
FROM
msdb.dbo.backupset b,
msdb.dbo.backupmediafamily mf
WHERE
b.media_set_id = mf.media_set_id
AND b.database_name = @databaseName
AND b.backup_set_id >= @backup_set_id_start
AND b.backup_set_id < @backup_set_id_end
AND b.type = 'L'
UNION
SELECT
999999999 AS backup_set_id,
'RESTORE DATABASE ' + @databaseName + 'WITH STANDBY = N''<<YOUR PATH>>'
ORDER BY
backup_set_id
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 8, 2014 at 10:32 am
My understanding of the requirements now is as follows:
You want to find the latest log backup in a series of backups that has not been restored to a different server
You want to restore that latest log backup to this different server
You want this secondary server to be up all the time so users can run reports from it
The users running reports should not experience an interruption in the restore??
If you have this server in standby already, why not use the logshipping mechanism that comes with SQL Server? Essentially your requirement for this thread is for somebody to write a script for you to replace that functionality.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 8, 2014 at 10:47 am
I do agree with you but the second server is in different domain. And the second server is a DR\UAT server. there is no domain level server in between these two. So we tried log shipping but it is not supporting with above reasons. Now we are here searching for alternative.
February 8, 2014 at 3:47 pm
Frankly, I think you need to hire a consultant. A consultant can get logshipping to work in your current environment. A consultant could also write some procedures for you to replace SQL Server Logshipping for you.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply