SQL Server 2005 Log Shipping And Subsequent FULL Backups That Are Needed
Dec 4, 2007
Afternoon,
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.
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 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.
Regarding backups. The first available time to do a full backup is at 11:00pm which also applies to diff. backups. How often does the trans. logs need a back up?
The space allocated to the Log in question is 180 GB. During this time period I was running TLog backups every 5 minutes, yet the log continued to chew through to 80 GB used, even after the process was complete and a final TLog backup had been taken. It continued to stay very large until the Full backup was complete -- or something else that I'm unaware of completed. Like every other DBA I typically take a TLog backup to shrink the log, but what appeared to be the case here was the Full completed and it released the used log space. All said, will Transaction Log backups not free up the log during Full backups?
Hello, Background: I am a Software Engineer - not a DBA - with limited SQL knowledge (I know the SQL but not the configuration stuff)... I am sure others need this too - I tried but cannot find the answers online... please help me with a few questions:
Task: I need to find an way to install a database via Installshield 2008/command-line (silently), that is accessible locally and remotely regardless of what was already installed, and accessible only to our program - not users, so I am thinking use SA and a strong password since windows authentication may not apply with multiple users accessing this DB and they do not need to login on to the server - only our program does - sound right to use Mixed?).
Questions: Can SQL 2005 Express be installed when SQL 2005 Full edition is already present?
Can SQL 2005 Express be installed without an instance name? I think I read an instance name is required in Express, that if I do not provide it defaults to SQLEXPRESS - So, if the prior question's answer is 'no, they cannot both exist'... prompting the next question:
If the Full version is already installed, should/can I use it - AND - can I add a New Instance silently via command line or ???
Any other ideas on why I can install 2005Express on one system, and not on two others (all 3 are development machines: VS 2005, SQL Express and that's about it)? Currently I have figured out a command-line call to install - the command I came up with during testing is: start /wait C: empSQLEXPR32.EXE /qb INSTANCENAME=CINST ADDLOCAL=ALL SECURITYMODE=SQL SAPWD=STR2PWD4SA SQLBROWSERACCOUNT="NT AUTHORITYNETWORK SERVICE" SQLACCOUNT="NT AUTHORITYNETWORK SERVICE" AGTACCOUNT="NT AUTHORITYNETWORK SERVICE" SQLBROWSERAUTOSTART=1 SQLAUTOSTART=1 AGTAUTOSTART=1 DISABLENETWORKPROTOCOLS=0 ERRORREPORTING=1 ADDUSERASADMIN=1 This looks like it might work for SQL Express 2005 - I do not have the full edition installed, yet - thus, this post. The errors I got, shown in the summary.txt as 'Error 10' on one system with no other info. My other system reports: Microsoft SQL Server 2005 9.00.3042.00 ============================== OS Version : Microsoft Windows XP Professional Service Pack 2 (Build 2600) Time : Fri Mar 21 16:45:25 2008
Machine : TONELSON Product : Microsoft SQL Server Setup Support Files (English) Product Version : 9.00.3042.00 Install : Successful Log File : c:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0007_TONELSON_SQLSupport_1.log -------------------------------------------------------------------------------- SQL Server Setup failed. For more information, review the Setup log file in %ProgramFiles%Microsoft SQL Server90Setup BootstrapLOGSummary.txt. ...not much to go on as to why it is failing - maybe the hotfix: C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9Express_Hotfix_KB921896_SQLEXPR.EXE in the directory is blocking the install?
The goal is for my application to use the same connection strings regardless of 2005 full or 2005 express.
Need to figure this out - Thank you, Todd
Software Engineer/Developer... learning a lot about SQL
I make two full backups on Oct 1 and Oct 10. I want to restore the server to a state in Oct 5. So I just do as follows:
1.Perform a transaction log backup on the server on Oct 23. I have never backup transaction log in the past. 2. Restore the server with Oct 1 full backup with NORECOVERY option. 3.Try to restore to the point at Oct 5 12:00, with the transaction log.
But the restore fails and SQL Server said the transaction log does not contain the point. The point is too early. Why? Also my .LDF file is about 13G, but the transaction log backup is only 200MB. Why?
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.).
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)?
WE ARE DOWNLOADING THE BACKUP FILE FROM OTHER BRANCH OFFICE AND THE FILE SIZE IS GROWING LIKE HELL SO I AM CHANGING THE BACKUP POLICY TO DOWNLOAD THE DIFFERENTIAL BACKUP FILES EVERYDAY. BUT EVEN THE FILE SIZE GROWS DAY BY DAY UNTILL WE TAKE THE FULL BACKUP THERE. SO I WANT TO TAKE A DIIFERNETIAL BACKUP AND THEN IMMIDIATELY A FULL BACKUP AT THE REMOTE SERVER END. SO MY NEXT DIFFERNTIAL BACKUP WILL HAVE THE DATA OF ONLY PREVIOUS DAY. FOR THIS I AM SCHEDULING A JOB WHICH FIRST TAKES A DIFFERENTIAL BACKUP AND THEN IMMIDIATELY FULL BACKUP EVERYDAY AT 12.00AM. BUT I HAVE A CONCERN.. WHILE TAKING THE DIFFERENTIAL BACKUP ANY TRANSACTIONS THAT ARE TO BE COMMITED WILL NOT COMMIT UNTIL THE DIFFERENTIAL BACKUP COMPLETES. BUT IMMIDIATELY I AM STARTING FULL BACKUP. IS THERE ANY CHANCE THAT A TRANSACTION GETTING COMMITTED IN BETWEEN THESE TWO STEPS. IF SO IS THERE ANY WAY .. NOT RELEASE THE DATABASE UNTILL THESE 2 BACKUPS ARE COMPLETED?? I AM RUNNING IT AS A JOB.. ANY SUGGESTION??
I have daily scheduled full backups of each database and log backups scheduled for every 2 hours. My question is should they be scheduled for overwriting or appending? I have always had them set as overwrite, but I don't know if that is correct? Any recommendations would be appreciated
Does the full and differential backups have to be in the same location or can I do a once a week full backup on one drive and everyday differential backups on a different drive?
Every Sunday morning (a different time each week) there is a full backup created for every database on the server. The backup is not a scheduled backup from any maintenance plan or SQL Agent job. The backup set is unrestorable and has a strange name in the format of 'DBNAME_00_12c95fb7_399d_41ce_9a0d_b5728b6a00ba_'
Because this backup is listed as the last full backup and will not restore, it is causing a problem with our disaster recovery plans as nothing will restore from this point forward to the next full backup.
Does anyone know how a backup record like this can get created, and/or how to find the source. The backups are listed in the backupsets table in msdb - are there other system tables that may hold some clues as to the source of these backups?
I make two full backups on Oct 1 and Oct 10. I want to restore the server to a state in Oct 5. So I just do as follows:
1.Perform a transaction log backup on the server on Oct 23. I have never backup transaction log in the past. 2. Restore the server with Oct 1 full backup with NORECOVERY option. 3.Try to restore to the point at Oct 5 12:00, with the transaction log.
But the restore fails and SQL Server said the transaction log does not contain the point. The point is too early. Why? Also my .LDF file is about 13G, but the transaction log backup is only 200MB. Why?
But i have one issue bothering me, it will be great if someone can help me out on this
1. I take full backup on Monday 8 pm which ends at 9.30 PM 2. Diff Backup starts at 10 PM every 2 hrs till Tuesday 7.59 PM 3. Transactional Log backup is happening every 10 min till tueday 7.59 PM
Now when i restore it in the same order i get error that Log in the set is too late to apply. I have recemmonded by team to stop log backup on the time they start full backups and take transactional log backup only after differentials are restored. Now is this a correct suggestion???
Also appendin the differential backup is a good idea or overwriting them is a better approach if Database is backedup every night and Differential occurs every 2 hrs.
I am running a full backup quarterly using "with init" to overwrite the existing file if it is still on the server. Then I run a differential back weekly using "with init". Finally I run a transaction log backup nightly using "with init".
My question is should I be using "with init" for the transaction log backup or allow it to accumulate until the next differential backup?
Hi sql server experts. This is a beginners question.
Let's say, I run a full backup at 4:30 am it takes usually 35 minutes to finish.
I want to schedule a differential backup to run every hour.
Should I start this job at 5:10 after the full backup finishes? or it doesn't matter? I am confused about timing.
Let's say my full back up takes 1 hour to finish, that means that when the differential backup runs the full backup will be still running, so in case I need to restore the database, can I use this differential from 5:10 or the differential from 6:10.
Same issue with the transactional, the job runs every 30 minutes, should I started after the differential is done?
I am so confused, what happens with the backups jobs that run while other backups are running?
Is there a way to suppress or re-direct the messages that result from a tran. log dump? We're running a tran. log dump every 15 minutes in prod. and the errorlog is really cumbersome to look at.
(In another RDBMS I worked with all backup messages went to a separate error/message log.)
I am in plan to implement following for backup of one of our database Enable Full recovery mode
1- Create full backup nightly 2- Create transaction log backup after every 25 min
as I am taking full backup every night, I think I can remove transaction log file backups at the time of full backup, as we can apply transaction log backup over full backup.My question is regarding removal of transaction log backups.
-Should I remove all transaction log backups and then execute full backup? -Should I execute full backup and remove all transaction log backup older than 24Hrs ? -Do I have to consider SCN or related info before deleting any transaction log backup ?
I make two full backups on Oct 1 and Oct 10. I want to restore the server to a state in Oct 5. So I just do as follows:
Perform a transaction log backup on the server on Oct 23. I have never backup transaction log in the past. Restore the server with Oct 1 full backup with NORECOVERY option.Try to restore to the point at Oct 5 12:00, with the transaction log.
But the restore fails and SQL Server said the transaction log does not contain the point. The point is too early. Why? Also my .LDF file is about 13G, but the transaction log backup is only 200MB. Why?
We are having full backup every day and hourly transactional log backups during the working hours in our production server which is running sql server2008 R2 as a clustered instance. For the Db's under simple recovery model we are having full backups. Now we want to implement transaction log shipping to a remote server in another site. I understand that log shipping involves the restoration of a full backup initially in the remote server and then restoring the transactional log backups which are shipped to it ,on a no recovery basis.
My question is whether we can continue taking the full backups every day in the production sever which is given for offsite storage. Will the full backups taken in the primary server, after the log shipping has been implemented, affect the log backups which are restored into the remote server. Will the chain of log backups which are restored into the secondary server be affected in any manner if a full backup is taken in the primary?
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 am setting up Availability Groups and I want to use the secondary replica to perform the full copy_only backups to reduce the load on the primary replica.But what is the best way to check for successful full backups on Availability Group databases?
Previously I could check the system table msdb.dbo.backupset but this is not available for copy_only backups.So I wonder how people are monitoring that their full backups have been successful?
Do you just check that the SQL Agent job that runs the backup was successful?
Or do you search the SQL Server Error Log for entries like "Database backed up. Database: xxx" where database xxx is in an Availability Group?
I just want to display all the data of a Single table into a Data Grid, I know that we can drag and drop the table on to a form and datagrid is generated, but here I want to retrive those values through my code, how should i do that
I am getting following errors while running the program Error 1) Error No. 28037, MS SQL Server 2005 Evrywhere Edition Error: A request to send data to the computer running IIS has failed. For more information see HRESULT Error 2) Error No. 0, SQL Server 2005 Evrywhere Edition ADO.Net Data Provider Error: The specified table does not exist [ JobLists ].
Can anybody please tell me, where I went wrong ??? In this code anywhere else????
Note: While adding a Data Source of SQL Server 2005 Mobile Edition, I have added that .sdf file into my project, thats why I have written the Data Source as : .DbFile.sdf
@"Data Source = .DbDotNetCF.sdf";
The code is as follows:
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.Data.SqlServerCe;
namespace DeviceApplication1 { public partial class Form1 : Form { string filename = @".DbDotNetCF.sdf";
private DataSet dsJobLists;
public Form1() { InitializeComponent(); }
private void DeleteDB() { if (System.IO.File.Exists(filename)) { System.IO.File.Delete(filename); } }
private void Sync() { SqlCeReplication repl = new SqlCeReplication();
if (DbDotNetCFDataSetUtil.DesignerUtil.IsRunTime()) { // TODO: Delete this line of code to remove the default AutoFill for 'dbDotNetCFDataSet.JobLists'. this.jobListsTableAdapter.Fill(this.dbDotNetCFDataSet.JobLists); } } } }
I have created a merge replication correctlly( I suppose, there were no errros) Please help
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) ?"
