Re: TDP for SQL: does the id absolutely require SA priv?
I would guess that the TDP account for SQL Server just has to be a member of the db_backupoperator role in each database. Once you give it this role in each database (including model) it will automatically have this role in any new database (because new databases start off as copies of model). Hope this helps. -Chris ___ Chris Leonard, The Database Guy http://www.databaseguy.com ___
Re: Multiple SQL Instances through the TDP
Del said...
> Maybe some subscribers on this list with this SQL Server configuration
> will share what they have done in their environment?
We don't currently have many SQL Servers that are multi-instance, but
here's what we've done on the ones that are. So far it seems to work
just fine. We capture the SERVERNAME or SERVERNAME\INSTANCENAME
combination into a variable which our T-SQL script then passes in as the
/SQLServer parameter, as Del suggests. However, in our case we only use
one TSM nodename. By the way, for those times when somebody wants to
use the TDPSQL GUI application, we built a separate icon for each
instance, adding the appropriate /SQLServer parameter value to each
icon. That way it's easy to pull up a GUI TDPSQL console for the
specific instance you want to work with.
Basically, our script (which is a stored procedure, really) does
something like the following. Sorry for the heavy snipping, but there
should be enough here to make sense to T-SQL folks:
create procedure usp_its_tdpsqlc_backup
@bkupType varchar(4)= NULL, -- Valid choices are 'FULL',
'LOG', or 'DIFF' for backups or NULL for usage notes.
@dbList varchar(8000) = '*' -- List of databases to back up,
or '*' for all databases
as
declare @servername varchar(500)
set @servername = cast(ServerProperty('ServerName') as varchar)
set @bkupCmd = @tdpsqlPath + '\tdpsqlc.exe backup ' + @dbName + '
' + @bkupType
+ ' /TSMPassword="' + @pwd + '" /MountWait /SQLServer="' +
@servername + '"'
-- We used to print @bkupCmd, but it contained the /TSMPassword.
Since this output is piped to a log file,
--we decided not to do that any more (!).
print ''
print '***'
print '*** ' + cast (getdate() as varchar)
print '*** About to perform ' + @bkupType + ' backup for database
' + @dbName
print '***'
raiserror ('', 10, 1) with nowait -- flush io cache
exec @rc = master..xp_cmdshell @bkupCmd
if @rc = 0
set @successes = @successes + 1
else
...
Hope that's enough to help somebody!
Cheers,
Chris
___
Chris Leonard, University of Iowa ITS
Institutional Data and Database Management
300 USB / 319-384-0801
MCSE, MCDBA, MCT, OCP, CIW
___
The Database Guy
http://www.databaseguy.com
Brainbench MVP for Oracle Administration
http://www.brainbench.com
___
Re: Backing up a 150GB Oracle DB on a datawarehouse server
Not to nitpick, but you'd be better off putting the tablespaces into
backup mode one at a time in your shell script, because Oracle has to do
additional (larger) logging when a tablespace is in backup mode.
Basically it has to log OS block images instead of DB page or data
images. Anyway, if you happen to hit a period when a tablespace is
backup mode and someone is loading into it, you can generate a terrific
amount of redo log. So rather than put all your tablespaces into backup
mode at once, it's better to have your shell script do something like
this pseudo-code:
For each tablespace t {
put t into backup mode;
backup t using OS commands;
take t out of backup mode;
}
backup your archive logs, pfiles, spfiles, pwfiles, etc.;
RMAN is a great help with hot database backups as well as the
incremental copies that have already been mentioned, by the way.
Regardless of whether you use RMAN, though, hot backups will require
that the database be run in ARCHIVELOG mode (shutdown immediate; startup
mount; alter database archivelog; alter database open;) and that means
that you need to set up archive log backups also.
Hope this helps,
Chris
| Date:Mon, 28 Mar 2005 17:12:16 +0100
| From:Andreas Almroth <[EMAIL PROTECTED]>
| Subject: Re: Backing up a 150GB Oracle DB on a datawarehouse server
|
| Luc Beaudoin wrote:
|
| >Hi all
| >
| >I have to take a new Oracle server (AIX), it's our new Datawarehouse
| >server
| >
| >I know that I have to stop the Oracle DB before the backup
| and restart it
| >after. ..
| >
| >
| Well, you really don't need to take if offline at all,
| depending on how
| much work you are willing to spend on configuring the backup.
| Most of the DWH systems I have worked on had to be up at all time, as
| they constantly processed new inputs from other systems, so you would
| probably need to do hot backups. Unless you can have a backup window
| that is.
|
| >My question is .. the DB has to be up and running as fast as
| it could ...
| >so what should be the best way to take that Oracle DB in
| backup (incr,
| >image, snapshot or ???)
| >
| >
| If you have the TDP for Oracle, it is easy, use Oracle RMAN
| to do online
| incremental 0 and 1 backups, with archive logs. The database
| will never
| have to be taken offline.
|
| If you don't have TDP, well then you can do the good ole'
| Oracle backup
| using some shell scripting. Essentially you would use a
| prebackup script
| to put all tablespaces in backup mode, then backup all files
| and archive
| log files, and the postbackup script would put all tablespaces back in
| normal mode.
|
| Regardless of method you will not have to take the database offline.
|
| /A
|
Re: SQL database restore question
Moses, You need to name each file shown in the metadata in the relocate parameter only if you don't want the location listed in the metadata for that file to be used by the restore command. For example, several of the files listed a location on the E: drive (sorry, don't have the details in front of me any more), meaning they were on the source server's E: drive someplace. If you want those files to be restored to the *same* location on the *target* server, then you can omit them from the /RELOCATE and /TO clauses. Another way of putting this is that, for each file, the metadata supplies the file location that will be used by a restore unless you override that specific file's location with the /RELOCATE and /TO clauses. Make sense? Hope so. :o) -Chris _ From: Moses Show [mailto:[EMAIL PROTECTED] Sent: Friday, August 27, 2004 9:23 AM To: Leonard, Christopher Subject: Re: SQL database restore question Does this mean I have to include each of the files shown in meta data in the relocate parameter and then for each of these files specify a location for them individually on the to parameter. (i.e have the locations I would like the files to be restored to tbe set for each of the files which showed up under meta data listing so the relocate and to parameters should look something like this /relocate=DWH_Data,DWH_Log,DWH_1_Data,DHWPROD_Log,DWH_2_Data, DWHPROD_Log1 /to=E:MSSQL\Data\DWHPROD.mdf,E:\MSSQL\Data\DWHPROD_Log.ldf,E:\MSSQL\Data\DWH PROD1.mdf,E:\MSSQL\Data\DHWPROD_Log.ldf,E:\MSSQL\Data\DWHPROD_Log1_Log.ldf "Leonard, Christopher" <[EMAIL PROTECTED]> Sent by: "ADSM: Dist Stor Manager" <[EMAIL PROTECTED]> 27/08/2004 15:05 Please respond to "ADSM: Dist Stor Manager" <[EMAIL PROTECTED]> To [EMAIL PROTECTED] cc Subject Re: SQL database restore question Moses, Your metadata shows the following 6 files: 3 data files named DWH_Data, DWH_1_Data, and DWH_2_Data and 3 log files named DWH_Log, DWHPROD_Log, and DWHPROD_Log1. However, your TDPSQLC RESTORE command is only relocating two of them, DWH_Data and DWH_Log. Therefore, the other 4 files are trying to restore themselves to locations that are determined from the metadata stored with the backup set. In the case of DHWPROD_Log, the file was located on the F: drive on the original SQL Server, as you can see from your /FILEINFO query. Therefore, since your TDPSQLC RESTORE command does not RELOCATE the file, TDPSQLC is trying to restore it on the F: drive on the target server as well. The solution is that you need to RELOCATE _all_ the files that should have different physical names on the target server. Hope this helps, Chris _ Chris Leonard MCSE, MCDBA, OCP, CIW _ The Database Guy http://www.databaseguy.com Brainbench MVP for Oracle Admin http://www.brainbench.com _ == This communication, together with any attachments hereto or links contained herein, is for the sole use of the intended recipient(s) and may contain information that is confidential or legally protected. If you are not the intended recipient, you are hereby notified that any review, disclosure, copying, dissemination, distribution or use of this communication is STRICTLY PROHIBITED. If you have received this communication in error, please notify the sender immediately by return e-mail message and delete the original and all copies of the communication, along with any attachments hereto or links herein, from your system. == The St. Paul Travelers e-mail system made this annotation on 08/27/2004, 10:24:50 AM. smime.p7s Description: S/MIME cryptographic signature
Re: SQL database restore question
Moses, Your metadata shows the following 6 files: 3 data files named DWH_Data, DWH_1_Data, and DWH_2_Data and 3 log files named DWH_Log, DWHPROD_Log, and DWHPROD_Log1. However, your TDPSQLC RESTORE command is only relocating two of them, DWH_Data and DWH_Log. Therefore, the other 4 files are trying to restore themselves to locations that are determined from the metadata stored with the backup set. In the case of DHWPROD_Log, the file was located on the F: drive on the original SQL Server, as you can see from your /FILEINFO query. Therefore, since your TDPSQLC RESTORE command does not RELOCATE the file, TDPSQLC is trying to restore it on the F: drive on the target server as well. The solution is that you need to RELOCATE _all_ the files that should have different physical names on the target server. Hope this helps, Chris _ Chris Leonard MCSE, MCDBA, OCP, CIW _ The Database Guy http://www.databaseguy.com Brainbench MVP for Oracle Admin http://www.brainbench.com _ smime.p7s Description: S/MIME cryptographic signature
Re: Restoring an SQL database
Hi Moses, The way I do this is as follows. First of all, we use PasswordAccess=Prompt in our dsm.opt. If it's possible to do this using Generate, I'd appreciate somebody chiming in, because I could not figure that out. Having said that, assume that I want to restore a backup made from MSSQLA onto MSSQLB. Suppose also that the TDPSQL client is installed on both MSSQLA and MSSQLB. In that case, I get a command prompt on SQLServerB and run commands such as the following: To query the tsm catalog for information about database backups made on MSSQLA, run this on MSSQLB (all one line, of course - I'm just splitting lines here for legibility): tdpsqlc query tsm /TSMNode="" /TSMPassword="" /FromSQLServer="" To run the command, replace the tokens in angle brackets with the appropriate values for your environment (but do not include the angle brackets themselves). To restore from SQLServerA to SQLServerB, run this on SQLServerB (again, all on one line): tdpsqlc restore /TSMNode="" /TSMPassword="" /FromSQLServer="" An alternative approach would be to use copies of each server's config files, and then use the appropriate parameters to select the correct config file for use each time you run tdpsqlc. In the future, we might do that, but right now our configurations are similar enough across servers that we can get by with just specifying the paramters that we need to override from our otherwise-identical config files. Make sense? You may need to use other options also (we frequently need to use /RELOCATE and /TO when restoring, for example) but this should be enough to get you started. Good luck, Chris | -- | | Date:Wed, 18 Aug 2004 17:03:49 +0100 | From:Moses Show <[EMAIL PROTECTED]> | Subject: Restoring an SQL database | | Hi chaps, | Can anybody enlighten me as to whether it is possible | using TDP | for SQL to restore a a backup taken on one SQL server to a | different SQL | server. I have been asked to perform the said task, but if it is not a | workable practice then I would need to find a way around this. | Any help would be gratefully received. | == smime.p7s Description: S/MIME cryptographic signature
Re: Restoring SQL Database Question
Jeff, I didn't see anybody reply with anything better than what I know, so I thought I'd chime in. To do this, you will need to use TDPSQLC to query the TSM database for /ALL backups - not just the active ones. For example, "tdpsqlc query tsm mydatabase /all". Then you need to parse the output, looking at the "Backup Creation Date / Time" to select the backups you require. Then you need to make appropriate calls to tdpsqlc, using the strings listed as the "Database Object Name," so that you can perform the restores. Remember, if you are restoring more than just a single full backup, each restore command except the last one should specify /recovery=no. This prevents the rollback portion of the recovery process so that subsequent backups can be applied to move the recovery forward in time. The last restore command should specify /recovery=yes. This allows the rollback portion of recovery to complete so that the database can be opened for normal use. I don't know of any way to force the /all query to return a subset of objects based on the time you want to recover to, or how to get the output in a more usable format. That would be nice, by the way, Del: a command to show me just the objects required to perform a PIT recovery to a time prior to the currently active backup. It would be even better if the objects came back in a more immediately usable format, such as a comma-separated list of TSM database object names or (to shoot the moon) a list of appropriate TDPSQLC commands. Maybe I can submit a request for that once my AIX friends tell me where to send them. Without such output, doing what Jeff is talking about is a little tedious ... and we haven't written a script yet to parse the "query tsm ... /all" output and automatically produce the correct restore commands. Has anybody else written an automatable script that can do this? Cheers, Chris | -- | | Date:Mon, 16 Aug 2004 17:25:36 + | From:Jeff White <[EMAIL PROTECTED]> | Subject: Restoring SQL Database Question | | Hi, | | TSM Server 5.1.5.2 | TDP/SQL 2.2.1 | TSM Client 5.1.5.0 | | What is the full command syntax, that i would enter from the | SQL/TDP command line client, to recover a database, but not | the latest database. | The user's guide shows the command syntax for recovering the | latest, but i have a DB Administrator who has asked that i | recover the backup PRIOR to the latest. This would be fine | via the GUI, but he asked if it was possible to do this on a | regular basis, overnight, so that it is recovered for him | prior to starting work. I was going to put this into a batch | file and run it via a schedule. | | Any suggestions would be appreciated. | | Jeff White | [EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
Re: ADSM-L Digest - 1 Aug 2004 to 2 Aug 2004 (#2004-204)
Thanks Del. I will try to do just that. The number of SQL Server instances and databases we have, plus the hardware we currently have available (not to mention the admistrative issues associated with restoring from one SQL Server node onto another in sqltdp!) make doing "actual" restores of each backup a fairly uninviting scenario right now. I mean, we can periodically restore backups, but I don't know if we have the bandwidth available to test them all! VERIFYONLY is nice, since it gives you a high level of assurance that everything is OK in between the actual test restores. Also, I've never met anyone who actually had the verify succeed only to have a subsequent restore fail. But then again, maybe I'm just leading a sheltered life. :o) The frustrating thing is that it seems that this would be such an easy component to add to the interface, and it saves so much time versus setting up the restore-to-another-server scenario. Thanks again. I'll ask our systems people who our IBM representative is, and forward the request to them. Chris > Date:Mon, 2 Aug 2004 10:10:23 -0400 > From:Del Hoobler <[EMAIL PROTECTED]> > Subject: Re: TDP for SQL - VERIFYONLY? > > Chris, > > I remember you asking about this last year as well. > You are correct that Data Protection for SQL does not have > a "VERIFYONLY" function at this time. To be completely honest > we have only heard this request from one or two customers. > > Since it means that you need to read and restore all of the data from the > backup set anyway, many people have decided to just do the actual > restore to an alternate server to do a *real* verification that their > data will restored and actually see it work, versus a message that > says "The backup set is valid." > > I encourage you to submit an official requirement through your > IBM representative that you need this function added to Data Protection > for SQL. If the requirement is heard from enough customers, the priority > will rise and it has a much better chance of being implemented. > > Thanks, > > Del smime.p7s Description: S/MIME cryptographic signature
TDP for SQL - VERIFYONLY?
As long as I'm posting, here's a question for Del. There is one glaring omission in the TDP client for SQL Server, namely the lack of any command that would expose Microsoft's RESTORE VERIFYONLY command. This command saves tons of time in verifying backups, since it greatly simplifies the process. Are there any plans by IBM to build something into the TDP Client for SQL Server that will let us run this command? It really doesn't seem like it should be very hard to do, and it's a big enough omission that initially we considered scrapping TDP for SQL because of it - and we still consider this from time to time. It's really an integral part of the T-SQL backup and restore API. Thanks for any response, Chris smime.p7s Description: S/MIME cryptographic signature
TDP SQL errors backing up SQLSERVER Cluster on Windows2003
I am a SQL-Head (as Mark so elegantly put it), and one with more experience than most. The fact that the DBAs can do a backup from SQL Enterprise Manager (or whatever) does *not* prove that this is not a SQL Server error. It may, sadly, give substantial evidence that they are stubborn or relatively inexperienced DBAs if that's how they diagnose things. I have seen this error pop up in different circumstances, and I urge you to try the workaround that Del mentions below (which is the same as the one from the MS KB article). If this works, then it *is* most definitely a Microsoft problem that can be resolved either by a Hotfix or by using the tcp:servername syntax when registering or connecting to a SQL Server. The first time I saw this error, my intuition told me it was "probably not a SQL thing," but note the word "probably." When I took the time to replicate the connectivity environment of the client (which your SQL-Heads appear not to have done), I saw the error, which led me to the KB. We don't use SQL failover clusters at my current job (and I've only used mostly DTC-bound clusters at other jobs) ... yet ... and this is the first job I've had where we've used TDP for SQL Server. Since we are likely to start using failover clusters for SQL Server in the fairly near future, I'd really appreciate it if you could post the resolution of this problem to the list. Thanks, Chris _ Chris Leonard MCSE, MCDBA, OCP, CIW _ The Database Guy http://www.databaseguy.com Brainbench MVP for Oracle Admin http://www.brainbench.com _ | Date:Fri, 30 Jul 2004 16:45:03 -0400 | From:Del Hoobler <[EMAIL PROTECTED]> | Subject: Re: TDP SQL errors backing up SQLSERVER Cluster on | Windows2003 | | Bill, | | Did you try this? | |tdpsqlc backup dbname full /sqlserver=tcp:server-name.abc.com | | for example: | |tdpsqlc backup master full /sqlserver=tcp:couples.endicott.ibm.com | | What happens? | | If you can't get it working, please ask that the PMR be | forwarded to IBM Level 2 support. Level 2 can gather traces | and work with Microsoft support to identify the problem. | | Thanks, | | Del | smime.p7s Description: S/MIME cryptographic signature
Re: TDP for MSSQL 5.2.1 performance issues and questions.
John, This may not help you right now, but in case you're wanting to throw SQL Server out the Window because of this "feature," I thought I'd mention that this will change in SQL Server 2005. As others have mentioned, SQL Server 2000 (and prior) must initialize all of the database pages before proceeding with a restore. This is because the restore only writes out pages that are in use, so you can't rely on the restore to initialize all the pages for you. As I understand it, in SQL Server 2005, SQL Server still must allocate the file space before doing a database restore, but the time-consuming initialization of database pages is no longer required. This should considerably alleviate, or perhaps even practically eliminate, the problem you are experiencing. Like I said, although this might not help you now, I thought it might be good to know as you look down the road. Cheers, Chris _ Chris Leonard MCSE, MCDBA, OCP, CIW _ The Database Guy http://www.databaseguy.com Brainbench MVP for Oracle Admin http://www.brainbench.com _ | Date:Wed, 30 Jun 2004 16:07:51 -0400 | From:Del Hoobler <[EMAIL PROTECTED]> | Subject: Re: TDP for MSSQL 5.2.1 performance issues and questions. | | John, | | This is normal. | SQL Server is pre-formatting the files in preparation for the restore. | Data Protection for SQL restores a small bit of information that | the SQL Server requires to properly set up and preformat the files. | After that, Data Protection for SQL must wait around until the | SQL Server starts requesting more data to fill the files. | There is a README entry and a User's Guide note about this: | | We recommend that the COMMTIMEOUT value on the TSM Server be | set to at least 600 seconds. The SQL Server must preformat | the database | files prior to restoring the database in those files. This occurs | after a small amount of data has already been restored from the | TSM Server. When restoring very large databases, it may be necessary | to set COMMTIMEOUT to even larger values than 600. Failure to do | so may result in restores that fail due to a severed TCP/IP | connection | caused by the TSM Server thinking that DP for SQL is hung. | | Thanks, | | Del smime.p7s Description: S/MIME cryptographic signature
