Automatic Restore

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,
hjThis 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:
> 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
> >|||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:
> > 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)|||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|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:upB3v4VmGHA.2120@.TK2MSFTNGP05.phx.gbl...
> 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
>
BTW, this is what we used to do.
Though, if possible what works better if the source server is doing
transaction log backups is to do a full backup/restore and then restore only
the logs every N hours. This is quicker and can reduce your disaster
recovery time.
(google for log shipping for more info. It's possible to roll your own.)|||You can't do wildcards in the restore command. I suggest you go the path suggested by Tracy. Also
consider Log Shipping as suggested by Greg.
--
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:1151350714.948572.273570@.r2g2000cwb.googlegroups.com...
> 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:
>> 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
>> >
>