Automating backups with TSM

I was wondering if anyone can share the procedure(s) used in setting up an automated MS SQL database backup through tivoli Storage Manager
( 5.2.7 )........?I'd settle for getting Tivoli to back up files 50% or more of the time. I'd consider getting Tivoli to do reliable, repeatable backups to be a miracle.

-PatPsql

Automatics restore script

I am setting up a job to automatically restore the standby database.
This is to test our tape backups. I need to passon the latest full
backup and log file names to the tape system to extract the files. How
do I get the file names? I would like to store these file names in temp
table and pass on to tape system one by one to extract it to local
disk. Do I need to query the msdb of primary or backup directory?
Please suggest sql to achieve this.
Thanks
Tram
If you do SQL Backups then you shouldn't need to know the file names because
the backup is referenced by database name. Are you doing file system
backups? That's not recommended unless you detach the database first.
Query the sysfiles system table for the physical file names.
David Portas
SQL Server MVP
|||A I need to restore from third party tape subsyste, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.
|||A I need to restore from third party tape subsystem, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.
|||A I need to restore from third party tape subsystem, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.
|||Hi
If it is 3rd party, ask the 3rd party vendor how you can script a restore
using their command line tools.
If SQL Server did not back the data up itself (to disk or a backup device
that it can control) it can't get the data back for you by itself.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"tram" <tram_e@.hotmail.com> wrote in message
news:1117887893.908844.130200@.f14g2000cwb.googlegr oups.com...
>A I need to restore from third party tape subsystem, the enterprise
> manager doen't recognize the restore fle anmes. I have to develop the
> script to retiriev the latest backup and logs therafter.
>
|||Thirty party vendor has command to resore the files. I have to passon
the filename as parameter, that is latest bak file and corresponding
trn files.
|||Hi
Have you looked at the backupfile and backupset tables in msdb to see if it
has what you need?
John
"tram" <tram_e@.hotmail.com> wrote in message
news:1117889003.683361.325430@.g43g2000cwa.googlegr oups.com...
> Thirty party vendor has command to resore the files. I have to passon
> the filename as parameter, that is latest bak file and corresponding
> trn files.
>

Automatics restore script

I am setting up a job to automatically restore the standby database.
This is to test our tape backups. I need to passon the latest full
backup and log file names to the tape system to extract the files. How
do I get the file names? I would like to store these file names in temp
table and pass on to tape system one by one to extract it to local
disk. Do I need to query the msdb of primary or backup directory?
Please suggest sql to achieve this.
Thanks
TramIf you do SQL Backups then you shouldn't need to know the file names because
the backup is referenced by database name. Are you doing file system
backups? That's not recommended unless you detach the database first.
Query the sysfiles system table for the physical file names.
--
David Portas
SQL Server MVP
--|||A I need to restore from third party tape subsyste, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.|||A I need to restore from third party tape subsystem, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.|||A I need to restore from third party tape subsystem, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.|||Hi
If it is 3rd party, ask the 3rd party vendor how you can script a restore
using their command line tools.
If SQL Server did not back the data up itself (to disk or a backup device
that it can control) it can't get the data back for you by itself.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"tram" <tram_e@.hotmail.com> wrote in message
news:1117887893.908844.130200@.f14g2000cwb.googlegroups.com...
>A I need to restore from third party tape subsystem, the enterprise
> manager doen't recognize the restore fle anmes. I have to develop the
> script to retiriev the latest backup and logs therafter.
>|||Thirty party vendor has command to resore the files. I have to passon
the filename as parameter, that is latest bak file and corresponding
trn files.|||Hi
Have you looked at the backupfile and backupset tables in msdb to see if it
has what you need?
John
"tram" <tram_e@.hotmail.com> wrote in message
news:1117889003.683361.325430@.g43g2000cwa.googlegroups.com...
> Thirty party vendor has command to resore the files. I have to passon
> the filename as parameter, that is latest bak file and corresponding
> trn files.
>sql

Automatics restore script

I am setting up a job to automatically restore the standby database.
This is to test our tape backups. I need to passon the latest full
backup and log file names to the tape system to extract the files. How
do I get the file names? I would like to store these file names in temp
table and pass on to tape system one by one to extract it to local
disk. Do I need to query the msdb of primary or backup directory?
Please suggest sql to achieve this.
Thanks
TramIf you do SQL Backups then you shouldn't need to know the file names because
the backup is referenced by database name. Are you doing file system
backups? That's not recommended unless you detach the database first.
Query the sysfiles system table for the physical file names.
David Portas
SQL Server MVP
--|||A I need to restore from third party tape subsyste, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.|||A I need to restore from third party tape subsystem, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.|||A I need to restore from third party tape subsystem, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.|||Hi
If it is 3rd party, ask the 3rd party vendor how you can script a restore
using their command line tools.
If SQL Server did not back the data up itself (to disk or a backup device
that it can control) it can't get the data back for you by itself.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"tram" <tram_e@.hotmail.com> wrote in message
news:1117887893.908844.130200@.f14g2000cwb.googlegroups.com...
>A I need to restore from third party tape subsystem, the enterprise
> manager doen't recognize the restore fle anmes. I have to develop the
> script to retiriev the latest backup and logs therafter.
>|||Thirty party vendor has command to resore the files. I have to passon
the filename as parameter, that is latest bak file and corresponding
trn files.|||Hi
Have you looked at the backupfile and backupset tables in msdb to see if it
has what you need?
John
"tram" <tram_e@.hotmail.com> wrote in message
news:1117889003.683361.325430@.g43g2000cwa.googlegroups.com...
> Thirty party vendor has command to resore the files. I have to passon
> the filename as parameter, that is latest bak file and corresponding
> trn files.
>

Automatic Purge of Transaction Log Data?

Hello,

I have a database that I am setting up in SQL Server 2005. Initially, I am doing very large imports of data. Every time I run an import, I am having the increase the size of my transaction logs, and now they are approaching 2 GB. Should these be purging themselves? I have to keep increasing the max size of the log so that I can get my data in. While this will work for now, it is not a long term solution, because I can see the log size growing quite large and the amount of space on the server obviously isn't infinite. Is there a setting that I can change so they will automatically purge? If not, how do I purge this information myself?

Thanks so much!

Christine

The management of the transaction log depends on the recovery model used in your database. There is a good overview on http://msdn2.microsoft.com/en-us/library/ms189275.aspx about the available recovery models.

With a full recovery model, you will need to backup your transaction log to reclaim its space. With a simple recovery model, the system will do this for you, but there is a greater risk for data loss - be sure to make the right tradeoff for your database!

For the problem of large imports filling up the log space, you might also want to look into switching to bulk-logged recovery for the duration of the imports, although you might not need to do this if the one of the other models works for you after the log is under control (reclaimed by the system or by regular backups).

Another SQL BOL reference that will probably come in handy is Truncating the Transaction Log at http://msdn2.microsoft.com/en-us/library/ms189085.aspx, which explains a bit more about how log truncation works.

|||

Here are some good resources that will help you better understand and control the Transaction Log:

FileSize -How to stop the log file from growing
http://www.support.microsoft.com/?id=873235

FileSize -Log file filling up
http://www.support.microsoft.com/?id=110139

FileSize -Log File Grows too big
http://www.support.microsoft.com/?id=317375

FileSize -Log File issues
http://www.nigelrivett.net/TransactionLogFileGrows_1.html

FileSize -Shrinking Log in SQL Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=272318

And especially, this article about why cautions about reducing the Log file size is worth reading:

FileSize -DB Shrink Issues
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

|||Thank you very much Marcelo and Arnie. I appreciate all of the information you have provided. I will spend this afternoon and tomorrow morning looking into it! :0)

Automate the configuration of the Query Log

Hi

I am looking at automating an AS 2005 build which will include setting up the Query Log. Can this step be automated.

Thanks

There are a couple of server properties for QueryLog settings (you can see all of them with SQL Management Studio: right click on the server item, chose 'Properties' and look for 'Log \ QueryLog' items). The query log settings are per entire server.

To automate setting up query log settings, here are some options:

1. Script the Server Properties dialog (from SQL Management Studio - the dialog has a 'Script' button at the top) and then run the script with an Integration Services task or with AMO. You can remove from the script all the other server properties that you don't want to touch (keeping the script small, clear and safe).

To run the script with AMO, this is sample C# code:

(in your C# project, add a reference to Microsoft.AnalysisServices.DLL from '%ProgramFiles%\Microsoft SQL Server\90\SDK\Assemblies')

...
using Microsoft.AnalysisServices;
...

Server s = new Server();

s.Connect("localhost");

try
{
XmlaResultCollection results = s.Execute("Put here the script copy-pasted from SQL Management Studio");

foreach (XmlaResult result in results)
{
foreach( XmlaMessage message in result.Messages )
{
Console.WriteLine(message.Description);
if (message is XmlaError)
{
// FAILURE: the script failed, you need to do something here
}
}
}
}
finally
{
s.Disconnect();
}

2. Use AMO to change the server properties. Sample code:

...
using Microsoft.AnalysisServices;
...

Server s = new Server();

s.Connect("localhost");

try
{
ServerProperty sp;

// setup the query log file path
sp = s.ServerProperties[@."Log\QueryLog\QueryLogFileName"]; // this throws exception if lookup fails
sp.Value = @."c:\MyQueryLog.txt";

// setup the query log file size
sp = s.ServerProperties[@."Log\QueryLog\QueryLogFileSize"]; // this throws exception if lookup fails
sp.Value = "10"; // 10 MB

// Now save everything; the properties we set don't require server restart.
s.Update();
}
finally
{
s.Disconnect();
}

Adrian Dumitrascu

|||

Adrian

Cheers i'll give it a try.

Automate setting permissions to databases (SQL 2005)?

Here is a bunch of links for issues in that area:
http://www.sqlservercentral.com/scr...utions/1598.asp Script
Roles and Permissions
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
system dbs 2005
http://www.databasejournal.com/feat...cle.php/3379901 Moving
system DB's 2000
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/col...se
s.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=320125 Moving a Diagram
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
Andrew J. Kelly SQL MVP
"AdamM" <adam@.nospam.com> wrote in message
news:un9nntilGHA.1272@.TK2MSFTNGP03.phx.gbl...
>I set a long list of granular user database permissions with every
>reinstall of my development database and I would like to automate this work
>with a script instead of doing this manually in SQL Server Management
>Studio.
> Is there a security settings export to file option? Or maybe some script
> samples that show how to set user permissions?
> I saw this example, but it requires both old and new databases to be
> running at once. I reinstall the same PC repeatedly so this doesn't work
> for me:
> http://www.sql-server-performance.c...ion_scripts.asp
> Thanks!
> Adam
>
>I set a long list of granular user database permissions with every reinstall
of my development database and I would like to automate this work with a
script instead of doing this manually in SQL Server Management Studio.
Is there a security settings export to file option? Or maybe some script
samples that show how to set user permissions?
I saw this example, but it requires both old and new databases to be running
at once. I reinstall the same PC repeatedly so this doesn't work for me:
http://www.sql-server-performance.c...ion_scripts.asp
Thanks!
Adam|||Here is a bunch of links for issues in that area:
http://www.sqlservercentral.com/scr...utions/1598.asp Script
Roles and Permissions
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
system dbs 2005
http://www.databasejournal.com/feat...cle.php/3379901 Moving
system DB's 2000
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/col...se
s.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=320125 Moving a Diagram
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
Andrew J. Kelly SQL MVP
"AdamM" <adam@.nospam.com> wrote in message
news:un9nntilGHA.1272@.TK2MSFTNGP03.phx.gbl...
>I set a long list of granular user database permissions with every
>reinstall of my development database and I would like to automate this work
>with a script instead of doing this manually in SQL Server Management
>Studio.
> Is there a security settings export to file option? Or maybe some script
> samples that show how to set user permissions?
> I saw this example, but it requires both old and new databases to be
> running at once. I reinstall the same PC repeatedly so this doesn't work
> for me:
> http://www.sql-server-performance.c...ion_scripts.asp
> Thanks!
> Adam
>
>

Automate Oracle import to SQL

We are looking at setting up a SQL report server which automates the task of importing Oracle tables. However it seems to be an unknown how to reliablily and automatically import tables from Oracle into SQL. The total size is 500Meg now but will probably grow to a couple of gigs before long. Importing needs to not take too long and would be good if people could still query the data while the update is occuring. So I believe we need to append tables only.

Any suggestions for this noob.

Thanks,Well, the basic approach would be to create a set of stored procedures which would append the SQL tables from the Oracle tables based on simple outer joins. They could be kicked off periodically as a SQL Server Agent job.

DTS (Data Transformation Services) allows you do do the same thing, but gives you a nice GUI for configuring everything. You add a server connection for the Oracle server, a server connection for your SQL Server, and define individual transformation steps for each of your tables. You can customize the source SQL to include only new rows (again, outer joins based on table keys), and transform the data along the way if youd like. Again, the DTS package can be kicked off on a schedule by SQL Server Agent.

I haven't done a lot with DB Replication, but I think it's pretty much SQL to SQL, so I'm not sure how much that would help. The first two approaches would give you everything you are looking for, I would think.
I use the first approach to warehouse data from nine Informix servers every 5 minutes. Works pretty well.

-b|||Well ... the best approach would be to create a linked server and a repository of the tables you will need to transfer. Then you can write a stored procedure to dynamically make queries to transfer the data from oracle to SQL server.

Autogrow problem

We are seeing an issue with the autogrow setting on some of our production
databases. Occasionally we will see a database fill up, processes trying to
write to the db will show page latch waits and the database will not grow for
up to five hours. The last time it occured, the database only needed to grow
400MB but it did not for hours. Once it did grow most of the writes which
had been waiting did not occur. Their is plenty of space on the disk and the
server was not at capacity. Any ideas?
Thank You.Autogrow is a necessary evil...you should be maintaining your data and log
file sizes based on the expected amount of transactions and altering them
during maintenance hours. Autogrow ought to be a failsafe only due to the
performance impact...
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Len Gustafson" <LenGustafson@.discussions.microsoft.com> wrote in message
news:E6CD323D-2F34-42F6-8EF4-926D2FC69730@.microsoft.com...
> We are seeing an issue with the autogrow setting on some of our production
> databases. Occasionally we will see a database fill up, processes trying
> to
> write to the db will show page latch waits and the database will not grow
> for
> up to five hours. The last time it occured, the database only needed to
> grow
> 400MB but it did not for hours. Once it did grow most of the writes which
> had been waiting did not occur. Their is plenty of space on the disk and
> the
> server was not at capacity. Any ideas?
> Thank You.

Autogrow problem

We are seeing an issue with the autogrow setting on some of our production
databases. Occasionally we will see a database fill up, processes trying to
write to the db will show page latch waits and the database will not grow for
up to five hours. The last time it occured, the database only needed to grow
400MB but it did not for hours. Once it did grow most of the writes which
had been waiting did not occur. Their is plenty of space on the disk and the
server was not at capacity. Any ideas?
Thank You.
Autogrow is a necessary evil...you should be maintaining your data and log
file sizes based on the expected amount of transactions and altering them
during maintenance hours. Autogrow ought to be a failsafe only due to the
performance impact...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Len Gustafson" <LenGustafson@.discussions.microsoft.com> wrote in message
news:E6CD323D-2F34-42F6-8EF4-926D2FC69730@.microsoft.com...
> We are seeing an issue with the autogrow setting on some of our production
> databases. Occasionally we will see a database fill up, processes trying
> to
> write to the db will show page latch waits and the database will not grow
> for
> up to five hours. The last time it occured, the database only needed to
> grow
> 400MB but it did not for hours. Once it did grow most of the writes which
> had been waiting did not occur. Their is plenty of space on the disk and
> the
> server was not at capacity. Any ideas?
> Thank You.

Autogrow problem

We are seeing an issue with the autogrow setting on some of our production
databases. Occasionally we will see a database fill up, processes trying to
write to the db will show page latch waits and the database will not grow fo
r
up to five hours. The last time it occured, the database only needed to gro
w
400MB but it did not for hours. Once it did grow most of the writes which
had been waiting did not occur. Their is plenty of space on the disk and th
e
server was not at capacity. Any ideas?
Thank You.Autogrow is a necessary evil...you should be maintaining your data and log
file sizes based on the expected amount of transactions and altering them
during maintenance hours. Autogrow ought to be a failsafe only due to the
performance impact...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Len Gustafson" <LenGustafson@.discussions.microsoft.com> wrote in message
news:E6CD323D-2F34-42F6-8EF4-926D2FC69730@.microsoft.com...
> We are seeing an issue with the autogrow setting on some of our production
> databases. Occasionally we will see a database fill up, processes trying
> to
> write to the db will show page latch waits and the database will not grow
> for
> up to five hours. The last time it occured, the database only needed to
> grow
> 400MB but it did not for hours. Once it did grow most of the writes which
> had been waiting did not occur. Their is plenty of space on the disk and
> the
> server was not at capacity. Any ideas?
> Thank You.

Auto time stamp in Sql. Express

Is there a property setting in SqlEX. That automatically inserts the date and time in to a field (timestamp) in the dB, when a record is created. If so can someone please show me how this is done.

Thanks in advance

You can set up the default value for the column as getdate() in the design view of the table.

Auto increment

How do I do to give a column an integer type and setting it to auto increment
so that I don't have to give this value every time I insert something in the
table?
You might want to read about identity columns on the BOL.
Aramid
On Wed, 6 Apr 2005 02:59:08 -0700, "Joachim"
<Joachim@.discussions.microsoft.com> wrote:

>How do I do to give a column an integer type and setting it to auto increment
>so that I don't have to give this value every time I insert something in the
>table?

Auto increment

How do I do to give a column an integer type and setting it to auto incremen
t
so that I don't have to give this value every time I insert something in the
table?You might want to read about identity columns on the BOL.
Aramid
On Wed, 6 Apr 2005 02:59:08 -0700, "Joachim"
<Joachim@.discussions.microsoft.com> wrote:

>How do I do to give a column an integer type and setting it to auto increme
nt
>so that I don't have to give this value every time I insert something in th
e
>table?

Auto increment

How do I do to give a column an integer type and setting it to auto incremen
t
so that I don't have to give this value every time I insert something in the
table?Hi
See an IDENTITY property
CREATE TABLE #Test
(
col1 INT NOT NULL IDENTITY(1,1),
col2 CHAR(1)
)
INSERT INTO #Test (col2) VALUES ('A')
"Joachim" <Joachim@.discussions.microsoft.com> wrote in message
news:319993D2-2CE4-47BE-A8C7-BB2064E6EFB0@.microsoft.com...
> How do I do to give a column an integer type and setting it to auto
increment
> so that I don't have to give this value every time I insert something in
the
> table?

Auto increment

How do I do to give a column an integer type and setting it to auto increment
so that I don't have to give this value every time I insert something in the
table?You might want to read about identity columns on the BOL.
Aramid
On Wed, 6 Apr 2005 02:59:08 -0700, "Joachim"
<Joachim@.discussions.microsoft.com> wrote:
>How do I do to give a column an integer type and setting it to auto increment
>so that I don't have to give this value every time I insert something in the
>table?