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...
>
>

Auto generate String

I have an ID Field in a table ContactInformation. This ID field is char(4)
type. And for this I want to auto generate strings starting from '0001'
onwards:
0001
0002
9998
9999
A001
A002
Z999
Thanks.You can't auto generate strings like an Identity() does. You have to create
a stored procedure that you call to get the next value and use that in the
Insert.
Andrew J. Kelly SQL MVP
"SQL Newbie" <SQL Newbie@.discussions.microsoft.com> wrote in message
news:97A8948F-934A-4DE9-A3F4-125C1847C3AD@.microsoft.com...
>I have an ID Field in a table ContactInformation. This ID field is char(4)
> type. And for this I want to auto generate strings starting from '0001'
> onwards:
> 0001
> 0002
> 9998
> 9999
> A001
> A002
> Z999
> Thanks.|||This seems like a very loopy and bizarre numbering scheme.
If you could allow A000 instead of jumping from 9999 to A001, there might be
an easier way to do this (if you also allow a peripheral IDENTITY column),
e.g.
create table dbo.foo
(
int_id int identity(1,1),
id as convert(char(4),
CASE WHEN int_id < 10000 THEN RIGHT('0000'+RTRIM(int_id),4)
ELSE CHAR(CONVERT(INT,LEFT(RTRIM(int_id),2))+
54) +
RIGHT('000'+RTRIM(int_id),3)
END)
)
SET NOCOUNT ON;
WHILE 1=1
BEGIN
BEGIN TRAN
INSERT foo DEFAULT VALUES
COMMIT
END
Don't forget to stop the above loop after a few minutes! Then run SELECT *
FROM foo and you will see that it closely matches your requirements, until
you hit somewhere around 36,000 rows. If you can't have the extra column
then you could easily use a stored procedure to handle inserts into the
eventual table, by first inserting into this table and then grabbing the row
that matches scope_identity(), and inserting into the "real" table. However
that would serialize inserts, would break on a multi-insert statement (or
BULK INSERT or bcp), and would require you to prevent ad hoc direct inserts
to the table. In addition, I have no idea what you expect to do once you
get past ~36,000 rows, where your CHAR(4) "numbering" scheme breaks. It
will loop around and perform lower case a000 -> z999 and then, at some
point, the upper bound will exceed the capacity of CHAR() and the inserts
will stop working. You can stop it at Z999 (and produce errors thereafter,
at least for the next 36,000 rows) if you apply a unique constraint to the
id column (provided you are in a case insensitive collation).
Have fun. Glad I'm not directly involved, I would probably move on to a
different gig. Your requirements seem to have been drafted by someone who
does not understand how databases work and, in particular, the weaknesses of
rolling your own string generator to create the same kind of meaningless
surrogate key that could easily be handled by identity alone, where the
system justtakes care of itself and you don't have any of these constraints,
extra space requirements and logical headaches...
"SQL Newbie" <SQL Newbie@.discussions.microsoft.com> wrote in message
news:97A8948F-934A-4DE9-A3F4-125C1847C3AD@.microsoft.com...
>I have an ID Field in a table ContactInformation. This ID field is char(4)
> type. And for this I want to auto generate strings starting from '0001'
> onwards:
> 0001
> 0002
> 9998
> 9999
> A001
> A002
> Z999
> Thanks.|||Hi,
I would fully agree with Aaron that you will pretty soon reach your limit. I
believe one thing that you might wanna do is to increase char(4) to a higher
value depending on how many inserts you want. This way you can also avoid an
additional over head of including alphabets in your ID field.
Its is always a good idea to conduct a capacity planning (as to how many
such id you would need) well in advance before coding such things.
I believe you need this particular format (i.e. to say that ID should have
to have these many characters which is not a very uncommon situation.) and
padding the left side characters with zero is often time used. But once agai
n
these do have limits to the max number they can avhieve.
Hope this helps.
"SQL Newbie" wrote:

> I have an ID Field in a table ContactInformation. This ID field is char(4)
> type. And for this I want to auto generate strings starting from '0001'
> onwards:
> 0001
> 0002
> 9998
> 9999
> A001
> A002
> Z999
> Thanks.|||Why? Do these values have a special meaning in your data model?
ML|||> I believe you need this particular format (i.e. to say that ID should have
> to have these many characters which is not a very uncommon situation.) and
> padding the left side characters with zero is often time used.
But aside from the weird rollover scheme used here (rolling to A -> Z after
the first 9,999 rows), this could easily be achieved through a view or
stored procedure, without storing this unnecessary padding in the database.
Let's say we could get by with just
00001
00002
...
00578
...
09999
...
99999
Then you could easily use an IDENTITY column and then create a view like:
CREATE VIEW dbo.bar
AS
SELECT RIGHT('00000'+RTRIM(id_column)) FROM some_table|||Thank you very much Aaron.
"Aaron Bertrand [SQL Server MVP]" wrote:

> But aside from the weird rollover scheme used here (rolling to A -> Z afte
r
> the first 9,999 rows), this could easily be achieved through a view or
> stored procedure, without storing this unnecessary padding in the database
.
> Let's say we could get by with just
> 00001
> 00002
> ...
> 00578
> ...
> 09999
> ...
> 99999
> Then you could easily use an IDENTITY column and then create a view like:
> CREATE VIEW dbo.bar
> AS
> SELECT RIGHT('00000'+RTRIM(id_column)) FROM some_table
>
>