Automatic Restore

This should get you started:
DECLARE @.sql nvarchar(300), @.d char(8)
SET @.d = CONVERT(char(8), CURRENT_TIMESTAMP, 112)
SET @.sql = 'RESTORE DATABASE db_name from DISK=''c:\backup\db_name_' + @.d +
'.bak'''
SELECT @.sql
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
news:1151340039.332861.226090@.u72g2000cwu.googlegroups.com...
> Hi,
> I have a database .BAK file on disk that I want to resotre everynight.
> RESTORE DATABASE db_name from DISK='c:\backup\db_name_yyyymmddhh'
> If I want to automate this everyday, how can I create yyyymmdd
> variable?
> Thanks,
> hj
>Hitesh Joshi wrote:
> Hi,
> I have a database .BAK file on disk that I want to resotre everynight.
> RESTORE DATABASE db_name from DISK='c:\backup\db_name_yyyymmddhh'
> If I want to automate this everyday, how can I create yyyymmdd
> variable?
> Thanks,
> hj
>
Instead of assuming a given filename/format, why not just check to see
what the most recent backup was, and restore that? The following should
work for you, add whatever switches you need to the restore command:
DECLARE @.DBName VARCHAR(50)
DECLARE @.LastBackupFileName VARCHAR(255)
DECLARE @.Command VARCHAR(1024)
SELECT @.DBName = 'YourDBName'
SELECT TOP 1 @.LastBackupFileName = backupmediafamily.physical_device_name
FROM msdb.dbo.backupset backupset
INNER JOIN msdb.dbo.backupmediafamily backupmediafamily
ON backupset.media_set_id = backupmediafamily.media_set_id
WHERE backupset.type = 'D'
AND backupset.database_name = @.DBName
ORDER BY backupset.backup_start_date DESC
SELECT @.Command = 'RESTORE DATABASE ' + RTRIM(@.DBName) + '_New FROM
DISK=''' + @.LastBackupFileName + ''''
EXEC (@.Command)|||Hi,
I have a database .BAK file on disk that I want to resotre everynight.
RESTORE DATABASE db_name from DISK='c:\backup\db_name_yyyymmddhh'
If I want to automate this everyday, how can I create yyyymmdd
variable?
Thanks,
hj|||This should get you started:
DECLARE @.sql nvarchar(300), @.d char(8)
SET @.d = CONVERT(char(8), CURRENT_TIMESTAMP, 112)
SET @.sql = 'RESTORE DATABASE db_name from DISK=''c:\backup\db_name_' + @.d +
'.bak'''
SELECT @.sql
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
news:1151340039.332861.226090@.u72g2000cwu.googlegroups.com...
> Hi,
> I have a database .BAK file on disk that I want to resotre everynight.
> RESTORE DATABASE db_name from DISK='c:\backup\db_name_yyyymmddhh'
> If I want to automate this everyday, how can I create yyyymmdd
> variable?
> Thanks,
> hj
>|||Hitesh Joshi wrote:
> Hi,
> I have a database .BAK file on disk that I want to resotre everynight.
> RESTORE DATABASE db_name from DISK='c:\backup\db_name_yyyymmddhh'
> If I want to automate this everyday, how can I create yyyymmdd
> variable?
> Thanks,
> hj
>
Instead of assuming a given filename/format, why not just check to see
what the most recent backup was, and restore that? The following should
work for you, add whatever switches you need to the restore command:
DECLARE @.DBName VARCHAR(50)
DECLARE @.LastBackupFileName VARCHAR(255)
DECLARE @.Command VARCHAR(1024)
SELECT @.DBName = 'YourDBName'
SELECT TOP 1 @.LastBackupFileName = backupmediafamily.physical_device_name
FROM msdb.dbo.backupset backupset
INNER JOIN msdb.dbo.backupmediafamily backupmediafamily
ON backupset.media_set_id = backupmediafamily.media_set_id
WHERE backupset.type = 'D'
AND backupset.database_name = @.DBName
ORDER BY backupset.backup_start_date DESC
SELECT @.Command = 'RESTORE DATABASE ' + RTRIM(@.DBName) + '_New FROM
DISK=''' + @.LastBackupFileName + ''''
EXEC (@.Command)|||Hi,
Thank you for the suggestion... it works awesome with file name like
db_name_yyyymmdd.bak but my files have name something like
db_name_yyyymmddhhmm.bak.
and last hhmm is not always same. is there a way I can use wildchars?
SET @.sql = 'RESTORE DATABASE db_name from DISK=''c:\backup\db_name_' +
@.d + '*.bak'''
I tried this but did not work
Thanks in advance.
Tibor Karaszi wrote:[vbcol=seagreen]
> This should get you started:
> DECLARE @.sql nvarchar(300), @.d char(8)
> SET @.d = CONVERT(char(8), CURRENT_TIMESTAMP, 112)
> SET @.sql = 'RESTORE DATABASE db_name from DISK=''c:\backup\db_name_' + @.d
+ '.bak'''
> SELECT @.sql
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
> news:1151340039.332861.226090@.u72g2000cwu.googlegroups.com...|||This solution looks very interesting but I am getting bak'ed up db
files from a third server and I do not have access to msdb on that
server...
Tracy McKibben wrote:
> Hitesh Joshi wrote:
>
> Instead of assuming a given filename/format, why not just check to see
> what the most recent backup was, and restore that? The following should
> work for you, add whatever switches you need to the restore command:
> DECLARE @.DBName VARCHAR(50)
> DECLARE @.LastBackupFileName VARCHAR(255)
> DECLARE @.Command VARCHAR(1024)
> SELECT @.DBName = 'YourDBName'
> SELECT TOP 1 @.LastBackupFileName = backupmediafamily.physical_device_name
> FROM msdb.dbo.backupset backupset
> INNER JOIN msdb.dbo.backupmediafamily backupmediafamily
> ON backupset.media_set_id = backupmediafamily.media_set_id
> WHERE backupset.type = 'D'
> AND backupset.database_name = @.DBName
> ORDER BY backupset.backup_start_date DESC
> SELECT @.Command = 'RESTORE DATABASE ' + RTRIM(@.DBName) + '_New FROM
> DISK=''' + @.LastBackupFileName + ''''
> EXEC (@.Command)|||Hitesh Joshi wrote:
> This solution looks very interesting but I am getting bak'ed up db
> files from a third server and I do not have access to msdb on that
> server...
>
Create a linked server on your restore server that points to the "third"
server you mentioned. Then query the backupset and backupmediafamily
tables using their fully-qualified names:
SERVERNAME.msdb.dbo.backupset
SERVERNAME.msdb.dbo.backupmediafamily|||Hi,
Thank you for the suggestion... it works awesome with file name like
db_name_yyyymmdd.bak but my files have name something like
db_name_yyyymmddhhmm.bak.
and last hhmm is not always same. is there a way I can use wildchars?
SET @.sql = 'RESTORE DATABASE db_name from DISK=''c:\backup\db_name_' +
@.d + '*.bak'''
I tried this but did not work
Thanks in advance.
Tibor Karaszi wrote:[vbcol=seagreen]
> This should get you started:
> DECLARE @.sql nvarchar(300), @.d char(8)
> SET @.d = CONVERT(char(8), CURRENT_TIMESTAMP, 112)
> SET @.sql = 'RESTORE DATABASE db_name from DISK=''c:\backup\db_name_' + @.d
+ '.bak'''
> SELECT @.sql
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
> news:1151340039.332861.226090@.u72g2000cwu.googlegroups.com...|||Hitesh Joshi wrote:
> This solution looks very interesting but I am getting bak'ed up db
> files from a third server and I do not have access to msdb on that
> server...
>
Create a linked server on your restore server that points to the "third"
server you mentioned. Then query the backupset and backupmediafamily
tables using their fully-qualified names:
SERVERNAME.msdb.dbo.backupset
SERVERNAME.msdb.dbo.backupmediafamily

auto-increment nvarchar column

Hi experts!

The situation:I have a table containing the MemberID and related member's info. The MemberID is the pk and should be incremented for every new member. However, the ID is in "nvarchar" type, since it consists of an "IM" prefix. I used the following stored procedure to insert new member to the table.

CREATE PROCEDURE dbo.spInsertNewMember
@.parMemberTitle nvarchar(2),
@.parMemberFirstName nvarchar(40),
@.parMemberLastName nvarchar(40)
AS
DECLARE @.LastMemberID AS nvarchar(16)
DECLARE @.NextMemberID AS nvarchar(16)
SET @.LastMemberID = (SELECT ISNULL(MAX(MemberID),'IM000000') FROM MemberInfo (UPDLOCK))
SET @.xx = SET @.NextMemberID = 'IM' + RIGHT(1000000 + (CAST(RIGHT(@.LastMemberID,6) AS INT) + 1), 6)
INSERT INTO MemberInfo (MemberID, MemberTitle, MemberFirstName, MemberLastName) VALUES (@.NextMemberID, @.parMemberTitle, @.parMemberFirstName, @.parMemberLastName)
RETURN

I want to ask if this sp can handle any concurrent insert to the MemberID table.

Thanks.

Hi,

why arent you doing the evaluation in the INSERT statement rather than separate ? This would minimize the concurrency effect.

(SELECT ISNULL(MAX(MemberID),'IM000000') FROM MemberInfo (UPDLOCK))
SET @.xx = SET @.NextMemberID = 'IM' + RIGHT(1000000 + (CAST(RIGHT(@.LastMemberID,6) AS INT) + 1), 6)
INSERT INTO MemberInfo (MemberID, MemberTitle, MemberFirstName, MemberLastName)
SELECT 'IM'+ CAST(RIGHT(ISNULL(MAX(MemberID),'IM000000'),6) + 1 AS CHAR(6)),
@.parMemberTitle,
@.parMemberFirstName,
@.parMemberLastName
FROM MemberInfo

If you really want to be sure about this you have to specify a TABLOCK.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens,

Thanks for your suggestion...

Since I have been using mysql for quite a while and thus not familiar with mssql.

what I am really looking for is something similar to "SELECT .... FOR UPDATE" in mysql.

I will try your suggest that later.

Thanks