How Do I Automate A Recovery Process With Lots of Transaction Logs

I have to automate a process that will restore the latest BAK file and all the TRN files that occured afterwards on a remote server. I can't use the log shipping process. The BAK file is created nightly, and TRN files are created every 15 mins. I'm using a maintenance plan, so the file names change
Can anyone think of a good way to recover all those files, without all the clicking and typing
Jeff ZuerleinJeff
Create JOB under Management -SQL Server Agent folders with RESTORE DATABASE
..... command.
"Jeff Zuerlein" <anonymous@.discussions.microsoft.com> wrote in message
news:367639E5-7A83-4D3D-8FA4-2507A221F958@.microsoft.com...
> I have to automate a process that will restore the latest BAK file and all
the TRN files that occured afterwards on a remote server. I can't use the
log shipping process. The BAK file is created nightly, and TRN files are
created every 15 mins. I'm using a maintenance plan, so the file names
change.
> Can anyone think of a good way to recover all those files, without all the
clicking and typing?
> Jeff Zuerlein|||Jeff,
I'm interested in why you can't use log shipping. If it is because you're
not using Enterprise Edition, then there are scripts in the Resource Kit to
do it manually for Standard Edition and below, or online there are a few
people who provide them for free:
http://www.sql-server-performance.com/sql_server_log_shipping.asp
HTH,
Paul Ibison|||It's purely political
I'm using a maintenance plan, so the names of the transaction logs change
I think I could code my way out, but I hate to spend the time if there is a better solution
Jeff|||To load logs from a folder you can get the file names into a temp table in
date order (earliest first) using something like this pseudo code
declare @.files int
create table #files(filename varchar(255))
insert #files exec master..xp_cmdshell 'dir /B /A-D /O-D c:\logs\*.trn'
delete #files where filename is null or filename like '%File Not Found%'
select @.files = count(*) from #files
If @.files >0
begin
-- loop through files in a cursor issuing a restore log command
end
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jeff Zuerlein" <anonymous@.discussions.microsoft.com> wrote in message
news:FCFA3898-9DAB-46EA-8BD8-0AF5982572EF@.microsoft.com...
> It's purely political.
> I'm using a maintenance plan, so the names of the transaction logs change.
> I think I could code my way out, but I hate to spend the time if there is
a better solution.
> Jeffsql

How Do I Automate A Recovery Process With Lots of Transaction Logs

I have to automate a process that will restore the latest BAK file and all t
he TRN files that occured afterwards on a remote server. I can't use the lo
g shipping process. The BAK file is created nightly, and TRN files are crea
ted every 15 mins. I'm usi
ng a maintenance plan, so the file names change.
Can anyone think of a good way to recover all those files, without all the c
licking and typing?
Jeff ZuerleinJeff
Create JOB under Management -SQL Server Agent folders with RESTORE DATABASE
..... command.
"Jeff Zuerlein" <anonymous@.discussions.microsoft.com> wrote in message
news:367639E5-7A83-4D3D-8FA4-2507A221F958@.microsoft.com...
> I have to automate a process that will restore the latest BAK file and all
the TRN files that occured afterwards on a remote server. I can't use the
log shipping process. The BAK file is created nightly, and TRN files are
created every 15 mins. I'm using a maintenance plan, so the file names
change.
> Can anyone think of a good way to recover all those files, without all the
clicking and typing?
> Jeff Zuerlein|||Jeff,
I'm interested in why you can't use log shipping. If it is because you're
not using Enterprise Edition, then there are scripts in the Resource Kit to
do it manually for Standard Edition and below, or online there are a few
people who provide them for free:
http://www.sql-server-performance.c...og_shipping.asp
HTH,
Paul Ibison|||It's purely political.
I'm using a maintenance plan, so the names of the transaction logs change.
I think I could code my way out, but I hate to spend the time if there is a
better solution.
Jeff|||To load logs from a folder you can get the file names into a temp table in
date order (earliest first) using something like this pseudo code
declare @.files int
create table #files(filename varchar(255))
insert #files exec master..xp_cmdshell 'dir /B /A-D /O-D c:\logs\*.trn'
delete #files where filename is null or filename like '%File Not Found%'
select @.files = count(*) from #files
If @.files >0
begin
-- loop through files in a cursor issuing a restore log command
end
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jeff Zuerlein" <anonymous@.discussions.microsoft.com> wrote in message
news:FCFA3898-9DAB-46EA-8BD8-0AF5982572EF@.microsoft.com...
> It's purely political.
> I'm using a maintenance plan, so the names of the transaction logs change.
> I think I could code my way out, but I hate to spend the time if there is
a better solution.
> Jeff

How do I attach query results to send mail

I have several tasks that I don't want to use conditional failure on. I have a ON ERROR send mail task right now that works. I want to have that send mail task include query results from a separate query. How do I do that?

Peter Cwik

How about dropping the result of the query into a file usig the Flat File Destination and then attaching that file within Send Mail Task?

Just an idea...

-Jamie

|||SSIS is new to me...how do I do that?|||

What I just descibed is slightly advanced because it required the use of expressions. I don't want to try and cover those before you know the basics.

If I were you I would build the bit that populates a flat file first. You'll need a new data-flow. Drop an OLE DB Source Adapter into your data flow. Paste your query into the OLE DB Source Adapter.

Then, drop a Flat File Destination Adapter onto the data-flow. Drag a data path between your source adapter and your destination adapter. Open up the destination adapter editor and accept all the defaults - including building your flat file connection manager.

If none of that makes any sense then i recommend you work your way through the SSIS tutorial provided off the SQL Server install CD.

-Jamie

|||

That part makes sense.

Thanks!

How do I attach database when I just have express edition

I'm trying to attach a database to sql server using the sp_attach_db
stored procedure. However, I don't see management studio on my
computer. I do have Visual Basic 2005, which is supposed to install
the express version of SQL Server. How do I run the command to attach
the database given that I don't have a paid-for version of sql server?
Thanks,
MarvinTry using the utility SQLCMD.EXE
How to: Connect to the Database Engine Using sqlcmd.exe
http://msdn2.microsoft.com/en-us/library/ms188247.aspx
AMB
"COHENMARVIN@.lycos.com" wrote:
> I'm trying to attach a database to sql server using the sp_attach_db
> stored procedure. However, I don't see management studio on my
> computer. I do have Visual Basic 2005, which is supposed to install
> the express version of SQL Server. How do I run the command to attach
> the database given that I don't have a paid-for version of sql server?
> Thanks,
> Marvin
>|||Hi
For SQL Server 2005 Express Edition you can download a management studio
and you will not pay for that , pease search on interent
<COHENMARVIN@.lycos.com> wrote in message
news:2e4245c5-ad3a-444d-882d-61eacf58aa4d@.i29g2000prf.googlegroups.com...
> I'm trying to attach a database to sql server using the sp_attach_db
> stored procedure. However, I don't see management studio on my
> computer. I do have Visual Basic 2005, which is supposed to install
> the express version of SQL Server. How do I run the command to attach
> the database given that I don't have a paid-for version of sql server?
> Thanks,
> Marvin|||> For SQL Server 2005 Express Edition you can download a Management Studio
> and you will not pay for that, please search on internet
Microsoft SQL Server Management Studio Express
http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796
Brief Description
Microsoft SQL Server Management Studio Express (SSMSE) is a free,
easy-to-use
graphical management tool for managing SQL Server 2005 Express Edition and
SQL Server 2005 Express Edition with Advanced Services.
Tom
http://kbupdate.info/

How do I attach database when I just have express edition

I'm trying to attach a database to sql server using the sp_attach_db
stored procedure. However, I don't see management studio on my
computer. I do have Visual Basic 2005, which is supposed to install
the express version of SQL Server. How do I run the command to attach
the database given that I don't have a paid-for version of sql server?
Thanks,
Marvin
Try using the utility SQLCMD.EXE
How to: Connect to the Database Engine Using sqlcmd.exe
http://msdn2.microsoft.com/en-us/library/ms188247.aspx
AMB
"COHENMARVIN@.lycos.com" wrote:

> I'm trying to attach a database to sql server using the sp_attach_db
> stored procedure. However, I don't see management studio on my
> computer. I do have Visual Basic 2005, which is supposed to install
> the express version of SQL Server. How do I run the command to attach
> the database given that I don't have a paid-for version of sql server?
> Thanks,
> Marvin
>
|||Hi
For SQL Server 2005 Express Edition you can download a management studio
and you will not pay for that , pease search on interent
<COHENMARVIN@.lycos.com> wrote in message
news:2e4245c5-ad3a-444d-882d-61eacf58aa4d@.i29g2000prf.googlegroups.com...
> I'm trying to attach a database to sql server using the sp_attach_db
> stored procedure. However, I don't see management studio on my
> computer. I do have Visual Basic 2005, which is supposed to install
> the express version of SQL Server. How do I run the command to attach
> the database given that I don't have a paid-for version of sql server?
> Thanks,
> Marvin

How do I associate a sql server login with the SQLAgentUserRole?

Currently a 2005 login has been granted sysadmin because I can not figure out how to grant the same login the SqlAgentUserRole. I thought I would use SSMS but there must be a trick. The role only exists in the msdb database, which the login has not been granted explicit access to. And the login does not appear in the list of Role Members or its sub windows. This should be real easy and intuitive . . .

Thanks!

Michael

You cannot associate a login with a database role. What you can do is map the login to a database user (CREATE USER/sp_adduser) and then make that user a role member (sp_addrolemember).

For understanding the distinction that exists between logins and users, have a look at: http://blogs.msdn.com/lcris/archive/2007/03/23/basic-sql-server-security-concepts-logins-users-and-principals.aspx.

Thanks

Laurentiu

|||

I was rather loose with my definitions. But thinking about it caused me to create a User in msdb for the login in question and was then able to associate the User in msdb as a Member of the fixed database role SQLAgentUserRole. I still don't know yet if this will allow me to demote the login from sysadmin and still allow the User to set-up and run his own SqlServerAgent jobs. But that is another day!

Thanks

sql

How do I assign nos for column

I have existing data in a table and would like to
assign auto number to a column.
How can I do?
Thank you for your help in advance!
-Kim
To add an identity column to your existing table you can issue the following
command:
alter table yourtable add newcol int identity
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Kim" <anonymous@.discussions.microsoft.com> wrote in message
news:00af01c49045$a6f16e80$a401280a@.phx.gbl...
> I have existing data in a table and would like to
> assign auto number to a column.
> How can I do?
> Thank you for your help in advance!
> -Kim
|||Greg,
I don't want to add a column, I already have a column,
I want to assign sequence numbers to this column.
Sequence numbers will depend on another field, if
the field value = 'A' it will have one sequence,
if the field value = 'B' then it will have another
sequence, so on...
here is the example -
Col1 Col2 Col3 ...
T123 Test rec1 A
F001 Test Rec2 B
S0001 Test Rec3 A
P001 Test Rec4 A
it should have the following values ...
Col1 Col2 Col3 ...
A00001 Test rec1 A
B00001 Test Rec2 B
A00002 Test Rec3 A
A00003 Test Rec4 A
Hope this helps!
Thank you,
-Kim

>--Original Message--
>To add an identity column to your existing table you can
issue the following
>command:
>alter table yourtable add newcol int identity
>--
>----
--
>----
--
>-
>Need SQL Server Examples check out my website
>http://www.geocities.com/sqlserverexamples
>
>"Kim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:00af01c49045$a6f16e80$a401280a@.phx.gbl...
>
>.
>
|||Kim wrote:
> Greg,
> I don't want to add a column, I already have a column,
> I want to assign sequence numbers to this column.
> Sequence numbers will depend on another field, if
> the field value = 'A' it will have one sequence,
> if the field value = 'B' then it will have another
> sequence, so on...
> here is the example -
> Col1 Col2 Col3 ...
> T123 Test rec1 A
> F001 Test Rec2 B
> S0001 Test Rec3 A
> P001 Test Rec4 A
> it should have the following values ...
>
> Col1 Col2 Col3 ...
> A00001 Test rec1 A
> B00001 Test Rec2 B
> A00002 Test Rec3 A
> A00003 Test Rec4 A
> Hope this helps!
> Thank you,
> -Kim
If you know the number of possible col3 values beforehand, you can write
a T-SQL script to move through the table, grab each row, one at a time,
check the col3 value, increment the corresponding counter value in the
script, and update the row using the counter value.
Pseudo-Code Here:
Start all counters at 0
Loop through a cursor on the table (do this off-hours)
Get col3 value
If col3 = 'A' then
CounterA = CounterA + 1
NewCol1 = col3 + Right('0000' + CAST(CounterA as varchar(5)), 5)
If col3 = 'B' Then
CounterB = CounterB + 1
etc.
Update Table
Set Col1 = NewCol1
Where PKVal = WhateverThePKValueIs
David G.
|||David,
I will implement your suggestion - thanks.
In the meantime wanted to find out from you how
can I create sequences for each of the series for future
use? Like for 'A' ... 'A000001' onwards,
for 'B' ... 'B000001' onwards.
'cause after I update my database with these numbers
I would like it to autogenerate while creating records
for each of the series.
Thank you for your help!
-Kim

>--Original Message--
>Kim wrote:
>
>If you know the number of possible col3 values
beforehand, you can write
>a T-SQL script to move through the table, grab each row,
one at a time,
>check the col3 value, increment the corresponding
counter value in the
>script, and update the row using the counter value.
>Pseudo-Code Here:
>Start all counters at 0
>Loop through a cursor on the table (do this off-hours)
> Get col3 value
> If col3 = 'A' then
> CounterA = CounterA + 1
> NewCol1 = col3 + Right('0000' + CAST(CounterA as
varchar(5)), 5)
> If col3 = 'B' Then
> CounterB = CounterB + 1
> etc.
> Update Table
> Set Col1 = NewCol1
> Where PKVal = WhateverThePKValueIs
>--
>David G.
>.
>
|||Kim wrote:[vbcol=seagreen]
> David,
> I will implement your suggestion - thanks.
> In the meantime wanted to find out from you how
> can I create sequences for each of the series for future
> use? Like for 'A' ... 'A000001' onwards,
> for 'B' ... 'B000001' onwards.
> 'cause after I update my database with these numbers
> I would like it to autogenerate while creating records
> for each of the series.
> Thank you for your help!
> -Kim
You can create a trigger on the table or a before trigger if the value
is not part of the PK. You'll have to keep track of the underlying key
values using another table.
I'm not a big fan of these types of intelligent keys because maintance
and implementation are much more difficult than using an indentity
columns. Have you considered using an identity column with the col3 as
the compound PK. That way, you have to do nothing to get the values in
there. You can then create a computed column on the table to return
values to your application in the correct format.
David G.