SQL Server 2005 Log Shipping And 3rd Party Tape Backups
May 27, 2008
Hello,
I would like to know if anyone has a recommended method for having backups to tape while running log shipping. For example, is it possible to copy the transaction log backups used for log shipping to tape and apply them to a Full Database Restore from a 3rd Party Backup tool such as Veritas?
My goal is to be able to do a point-in-time restore from tape and still be able to use SQL Server 2005 Log Shipping.
I have a few Log Shipped DBs that are working great.
Currently they are set to fire off every 15 minutes 24/7.
My question is this ... I need to get FULL backups of the source DBs in order to restore them on certain Dev boxes.
If I were to execute the full backup on one of these Log Shipped DBs ... how would it affect the log shipping process?
Is there a special method to accomplish this?
As a side note, what would be some concerns/issues if in being able to create the FULL backups and not interupt log shipping, I were to create the backup using a 3rd party tool like Quest LiteSpeed?
I sure wish we were on Enterprise, then I could create a mirror and then snapshot off it to create my backups BUT ... that is not the case as we stand today.
Has anyone had experience using ‘VERITAS Backup Exec For Windows Servers Agent for MS SQL Server’ to run backup jobs? If so, what are the pros and cons?
I have always preferred using SQL Server Agent (not the maintenance plans) to run backup jobs and don’t know why anyone would want to use 3rd party software to do something SQL Server Agent does perfectly. Any advice?
Perhaps this maybe a silly simple questions SCENERIO: Say i want to make a nightly backup of the Masterdb and the same for a user database. One that kicks off at 8 and the other at 9. For soem reason I am having difficult time with this.. Some help please??
I have a COTS database on MSSQL 6.5, no service packs... I used sp_help_revdatabase to create the same database on a different server, using the same data and log device sizes and paths. I scripted the database, tables, stored procedures, and user defined datatypes, created them on the new server, and tried to restore a tape dump from the previous server to the new database. It would say "restoring", but then "DBLibrary is dead or connection broken", when I click OK to this message, it sais "restore complete" then the database appears grayed out and reads "loading".
In 2007 we plan on moving many of our SQL Servers (2000 & 2005) to a SAN. We will also be investigating the best way to replicate data from our production SAN to a Disaster Recovery SAN. I would like to know if people rely on built-in SQL Server replication / log shipping or third party products like DoubleTake or MirrorView. What's the advantage, if any, to using a third-party product?
We have a heterogenious network of Windows 2003 Server/SQL Server 2005 machines and Linux file servers. For various reasons our HP/Compaq DLT tape drive robots are attached to our Linux file servers.
What options exist for sending backups to remote tape drives that are attached to a Linux host? I'm interested in solutions that have been actually implemented (we've been having plenty of fun with "this should work" scenarios ).
Hi, I back up SQL Server 2000 and SQL server 2005 databases to hard disk using the SQL Server Backup Wizard and maintenance plans. Then, I copy the resulting backups to tape using third party tape backup software and compression by the backup software and hardware. I do not use the SQL Server Agent available for the third party backup software. Is this acceptable, or does the compression performed by the third party backup system introduce opportunities for database corruption or other negative effects?
Can we backup our cluster databases directly to tape using native backups (without using any third party tool) ? It's SQL Server 2012 two node Active/Passive cluster. One of the DB will be huge in size, hence checking if we can directly backup from the cluster instance to a tape.
I have some questions on backups and log shipping.
Before I get to them, though, the goal:
Back up a SQL 2000 database (~5GB in size) completely every day. Back up its transaction log hourly. Maintain a warm backup DB server using log shipping.
Here is what I have done so far, which has led to my questions:
I have stopped using maintenance plans for backups, feeling that then the process would be less 'black boxy'
I first created two jobs to perform the daily db and hourly tlog backups that saved the backups in files using the naming formats <name>_db_<yymmdd>.bak and <name>_tlog_<yymmddnnss>.bak.
Issue 1: doing backups this way means that it's a little difficult for the log shipping jobs to figure out the filenames (esp. the tlog ones), if log shipping is going to use the files generated by the backup jobs.
Issue 2: I thought maybe I'd have log shipping generate its OWN backup files, but would that cause problems with the transaction log? Say the normal tlog backup fires, then fifteen minutes later the shipping tlog backup fires. Would the shipping tlog backup file be missing the transactions that were backed up during the normal log backup?
Issue 3: To try to help with the naming issue, I tried switching the backups from creating new files each time to file devices whose names would be constant. This worked, but since my database is about 5GB in size, that meant that, with expiring backups after 7 days, the database backup device would settle in at about 25GB (I skip weekends) and I'd have to copy or cab-copy-uncab that file over to my warm server every day. That seemed a little inefficient. Does anybody have alternative ideas?
My last question is for general info:
Where does SQL Server keep the information on the backups that exist? Whether I was using individual files or file devices, I was able to go to Database-All Tasks-Restore Database... in Enterprise Manager and it would show me the backups that existed. I imagine these must be stored in a system table somewhere, but I did not see any obvious place to look (no 'sysbackups' table, etc.).
We have a SQL 2012 server instance that has log shipping set up to another SQL 2012 server to provide a warm standby for a forward facing application. The databases on the primary server occasionally are required to be backed up and restored to a development environment, completely different server. Is there a way to schedule full backups with log shipping enabled?
I've got log shipping set up, and everything seems to be working fine, but the log files are not being deleted from the primary server despite configuring log shipping to retain them for 3 days. Â I see no errors concerning the log shipping, but did not configure a monitor. What process is responsible for deleting the older log backups, and how can I look for errors. Â I could simply set up a jog to delete the older files, but that will only mask the issue.
I plan to use Log Shipping on SQL 2000 to have warm standby database. I understand current procedure but have a question wether I can do full normal backups of my database server without screwing up my Log Shipping process? Also can I do transaction log backups as well (separate from the ones used for log shipping)?
I am looking to be able to backup our database automatically on a daily basis. It is for a SQL Server 2005 Express Database. Does the Express edition have automated backups or does it have to be carried out manually?
I completed an install of SQL 2005 SP2 on Windows Server 2003. Everything worked fine I can connect while logged into the box, while connected thru RDP. I can create databases add users without any issue. Until!!!!
I have a third party application (Tririga) which I run the install and indicate the SQL database (in this case local) The application begins to install but when I get to the point of adding the SA password the install fails. "login fails for sa) 18456 sev 8 (wrong password) but it is the same password I use to connect to the DB. So I change the sa assword and try again and it still fails. any suggestions would be nice. by the way on machines on which I have run an upgrade from 2000 (sql) to 2005 (sql) the install does not fail.
I have set up log shipping between two servers with a third serving as a monitoring server. Recently the monitoring for the eight databases that I am log shipping began to indicate that everything was failing. Upon further inventigation of the log_shipping_monitor_history_detail tables on the prinary and secondary everthing appears to working properly but I have been unable to resyncronize the monitoring. I have attempted to use the stored procedure sp_refresh_log_shipping_monitor to no avail, it does not appear to do anything with respect to the data in the dbo.log_shipping_primary_databases and log_shipping_monitor_primary tables on the primary server or the log_shipping_monitor_secondary and dbo.log_shipping_secondary_databases tables on the secondary. I have also manually updated the records in these tables in an attempt to syncronize but after the next sucessful backup, copy and restore this monitoring data is not updating. Does anyone have any ideas as to what I am doing wrong or how I might rectify this situation.
I am getting the following error " The specified servername does not exist" when I run the Log Shipping procedure. There is only a Primary server running Win Server 2003 withe Service pack 2 and SQL Server 2005 with service pack 2 and one secondary server runnung Win Server 2003 R2 with service pack 2 and SQL Server 2005 with service pack 2. All permission setting look ok . Can anyone help
I'm working on a restore procedure for the case where all MDF filesare missing, but the LDF files are all intact. A full backup is doneevery 24 hours, and a log backup is done every 3 hours. Afterrestoring the last full + log backups, is it at all possible to usethe LDF files to recover data from that point up to a newer point intime?I've found a post which explains how to do this on SQL Server 2000<http://groups.google.com/group/comp...s.ms-sqlserver/browse_thread/thread/3ef5c7cbc0a83334/f3b0c70811d35ed7>, but step 4fails with the following error message:BACKUP LOG cannot be performed because there is no current databasebackup.
I have installed SQL Server 2005 and setup a cluster and log shipping. I decided to perform a planned failover and realized that there are no sp_change_primary_role and sp_change_secondary_role stored procedures. I checked on both the SQL Server 2005 installations and could not find them.
My question is :
"Is it safe to get these stored procedures from a SQL Server 2000 database and run them on the SQL Server 2005 database ??"
"Has anyone come across such missing stored procedures in SQL Server 2005 and know the reason why it is so (so that I can avoid it in the future if it due to a faulty installation) ?"
I work for a small state government department. We are about to install SQL Server 2005.
A national government department that runs on SQL Server 2000 are willing to give us a backup of their database (20 gig I think) and then log ship to us regulary. Our aim is to have a copy of the database that we can query from and that is only a day or so out-of-date.
I'm having no luck.
1. I can backup the SQL Server 2000 database.
2. I can restore the SQL Server 2000 database onto the SQL Server 2005 box no worries.
2. BUT, if I attempt to restore the 2000 database with the option 'Leave the database in read-only mode ...' selected (so that I can then restore transaction logs and it's read-only), then I get an error message. ""This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.". I'm doing all this using SQL Server Management Studio.
It appears we will have to use SQL Server 2000 rather than 2005 to get this working.
Q: Has anyone successfully done this from SQL 2000 to SQL 2005 please? If so, how was it achieved please?
emergency help, our server1 is down, we need to restore everything from tape using server2, but when i put the tape in the server2, it said "unreadble media", what should i do to restore everything? How can the tape to be readble? Thanks, thanks.
We are currently trying to restore a copy of our production database to a different server. Unfortunately SQL does not see the tape drives and we are not able to add them. The tape drives are installed and visible in device manager. I have updated all drivers and firmware but still no luck. Any ideas would be appreciated. Thanks
I am trying to use NConstruct to generate my Nhibernate files. i can't connect to the sql server. In the past I have ran into similar problems with MyGeneration and other 3rd party tools. In those cases If I ran the tool as an administrator, it worked out ok. For NConstruct there is no option to run as administrator. I ran the SQL Server Surface Configuration tool to allow local and remote connections for named pipes and tcp already. I read all the blogs written for asp.net, and people often complain about the learning curve for Nhibernate or lack of Tutorials for Nhibernate. What I found in my case is that the steepest learning curve is to get the Microsoft Products Configured and Working Correctly! If anyone can be of any assistance, I would deeply appreciate it
I thought I was successful in linking SQL to a Third Party LDAP in the past, but I cannot get it to work now. It has been about 2 years ago I played with this. The LDAP does require authentication and I think that is where the problem lies, but if anyone can tell me if there is a way to do this and what the steps are I would greatly appreciate it.
Does anyone know if it is possible to have a tape device another server as a tape dump device on SQL. Instead on removing the tape drive and installing it locally ?!?
I have SQL Server 2000 Standard Version backups going to Disk then we have Legato Networker Software to make backups of these backup directories to TAPE.
When we retrieve the file from the TAPE and use to recover the database we get the following errors: (Notice some work and some do not. This is from command 'restore verifyonly from disk='
Server: Msg 3242, Level 16, State 2, Line 1 The file on device '\backupfilersqlbackupsqlserver ecovered_iis_mainsqlbackupsqlserveriss_mainiss_main_tlog_200709210130.TRN' is not a valid Microsoft Tape Format backup set. Server: Msg 3013, Level 16, State 1, Line 1 VERIFY DATABASE is terminating abnormally. The backup set is valid. The backup set is valid. Server: Msg 3242, Level 16, State 1, Line 4 The file on device '\backupfilersqlbackupsqlserver ecovered_iis_mainsqlbackupsqlserveriss_mainiss_main_tlog_200709180130.TRN' is not a valid Microsoft Tape Format backup set. Server: Msg 3013, Level 16, State 1, Line 4 VERIFY DATABASE is terminating abnormally. The backup set is valid. The backup set is valid.
If I take the original backup file that are on disk they are all valid.
Thanks for any help that can lead me to the solution
I have schedule backup job from SQL Server 2000 to Tape. Each Jobs consist of 4 steps which backup different database.
Sometimes I encounter problem. The Message is
Executed as user:dbo. The file on device '\.Tape0' is not a valid Microsoft Tape Format backup set. [SQLSTATE 42000] (Error 3242) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
There is no problem at the command, as it will run normally when i manually trigger the schedule job. Sometimes the error occur only after few days the job run.
Hi, I'm using one report tool by 'AriaCom' and have got such problems with FREE SQL in that tool. Pls advise!
Message: SQLExecDirect>[ODBC 195][37000][Microsoft][ODBC SQL Server Driver][SQL Server]'instr' is not a recognized built-in function name. Message: SQLExecDirect>[ODBC 195][37000][Microsoft][ODBC SQL Server Driver][SQL Server]'mid' is not a recognized built-in function name.
