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

autocontained selects

Dear gurus,
select 'insert into cargafrecuencias([id],tipo,horas) values(' +
convert(char(2),[id]) + ',
''' + rtrim(ltrim(tipo)) + ''',
' + convert(char(5), horas) + ')'
from cargafrecuencias
That query returns fine INSERTS and ready for be executed:
insert into cargafrecuencias([id],tipo,horas) values(1 , 'Semanal',
168 )
insert into cargafrecuencias([id],tipo,horas) values(2 , 'Mensual',
720 )
insert into cargafrecuencias([id],tipo,horas) values(3 , 'Diaria',
24 )
insert into cargafrecuencias([id],tipo,horas) values(4 , 'Cuando llegue
el fichero', 8640 )
It's easy and useful without a doubt. But can you imagine do the same with a
table with 80 columns?
I would like do something like that (it would be gorgeous):
select 'insert into table(f1,f2,f3... fn) values (sp_help table)'
from table
On the other hand is very annonying.
Does anyone have any ideas or thoughts?
Thanks in advance and regards,CREATE PROCEDURE SPInserttest
(
@.Test varchar(50)
)
AS
BEGIN
PRINT 'Do something with the test varchar(50)'
Select 'Jens'
UNION
SELECT 'BOB'
END
CREATE TABLE #Tabname
(
Names varchar(50)
)
INSERT INTo #Tabname
EXEC('SPInserttest ''Test''')
Select * from #Tabname
DROP Procedure SPInserttest
DROP Table #Tabname
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Enric" wrote:

> Dear gurus,
> select 'insert into cargafrecuencias([id],tipo,horas) values(' +
> convert(char(2),[id]) + ',
> ''' + rtrim(ltrim(tipo)) + ''',
> ' + convert(char(5), horas) + ')'
> from cargafrecuencias
>
> That query returns fine INSERTS and ready for be executed:
> insert into cargafrecuencias([id],tipo,horas) values(1 , 'Semanal',
> 168 )
> insert into cargafrecuencias([id],tipo,horas) values(2 , 'Mensual',
> 720 )
> insert into cargafrecuencias([id],tipo,horas) values(3 , 'Diaria',
> 24 )
> insert into cargafrecuencias([id],tipo,horas) values(4 , 'Cuando llegue
> el fichero', 8640 )
>
> It's easy and useful without a doubt. But can you imagine do the same with
a
> table with 80 columns?
> I would like do something like that (it would be gorgeous):
> select 'insert into table(f1,f2,f3... fn) values (sp_help table)'
> from table
> On the other hand is very annonying.
> Does anyone have any ideas or thoughts?
> Thanks in advance and regards,
>|||Try this one.
http://vyaskn.tripod.com/code/generate_inserts.txt
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:1BD06558-758F-490C-B228-7090641DA685@.microsoft.com...
> Dear gurus,
> select 'insert into cargafrecuencias([id],tipo,horas) values(' +
> convert(char(2),[id]) + ',
> ''' + rtrim(ltrim(tipo)) + ''',
> ' + convert(char(5), horas) + ')'
> from cargafrecuencias
>
> That query returns fine INSERTS and ready for be executed:
> insert into cargafrecuencias([id],tipo,horas) values(1 , 'Semanal',
> 168 )
> insert into cargafrecuencias([id],tipo,horas) values(2 , 'Mensual',
> 720 )
> insert into cargafrecuencias([id],tipo,horas) values(3 , 'Diaria',
> 24 )
> insert into cargafrecuencias([id],tipo,horas) values(4 , 'Cuando
> llegue
> el fichero', 8640 )
>
> It's easy and useful without a doubt. But can you imagine do the same with
> a
> table with 80 columns?
> I would like do something like that (it would be gorgeous):
> select 'insert into table(f1,f2,f3... fn) values (sp_help table)'
> from table
> On the other hand is very annonying.
> Does anyone have any ideas or thoughts?
> Thanks in advance and regards,
>|||Great, thanks a lot, it works very well
"Roji. P. Thomas" wrote:

> Try this one.
> http://vyaskn.tripod.com/code/generate_inserts.txt
>
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:1BD06558-758F-490C-B228-7090641DA685@.microsoft.com...
>
>|||XXXXing life. That doesn't works with my table, it is too large in terms of
columns.
"Roji. P. Thomas" wrote:

> Try this one.
> http://vyaskn.tripod.com/code/generate_inserts.txt
>
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:1BD06558-758F-490C-B228-7090641DA685@.microsoft.com...
>
>