Can I TLog Backup From LDF File?
Mar 21, 2007
I am considering some disaster recovery scenarios.
Lets assume my MDF is gone - the disks are dead.
The LDF is on a different disk channel. Lets assume its fine.
Can I make a "final" TLog backup from the "good" LDF file?
Maybe copy some-earlier-MDF file into place, would that enable a TLog backup from the LDF file?
'Coz if I can then I could use that as a route to getting a zero-loss recovery - make a final TLog backup, and then restore the whole lot from last FULL + All TLog backups thereafter.
Kristen
View 1 Replies
ADVERTISEMENT
Feb 25, 2015
I have a SQL Server 2012 DB in Full recovery mode that has never had a TLOG backup. the log is huge 200+GB.
I tried doing a transaction log backup but there is not enough space on the Disk.
How can I reclaim this log space in SQL Server 2012?
View 6 Replies
View Related
Mar 15, 2007
If I create an adhoc db backup that takes, say 30 miuntes to complete, should I suspend the tran log backups that run every 10 minutes, until the full backup is complete?
Drew
View 9 Replies
View Related
Apr 9, 2008
Hello,
I have an SQL 2005 maintenance plan that backups up the t-logs of my production db every 15 minutes. I want to begin log shipping over to a warm-standby secondary server. The network share, log shipping folder is not the same folder location as my maintenance plan folder. My question is, do I need to disable the maintenance plan for tlog backups in order for log shipping to be sucessful? Or will the dual backups to seperate backup folder locations cause a failure in the secondary server restore process?
I suspect the latter but wanted a second opinion.
Thanks.
View 4 Replies
View Related
Apr 30, 2008
i have a very big database and number of people are working on it.. it's log file size is increaseing very day too much.. i am taking log back every 30mint...
i dont' know that wethare i need to truncate the log file after taking the log file backup or not.. i am taking differentail backup every day and full backup every week...
Please tell me do i need to trucate the log file to reduce the file size or i hv to leave as it....
Thanks..
View 26 Replies
View Related
Nov 21, 2014
I've already shrunk the tlog from 350 GB to 313.My DB Server (2008 R2 Sp2) cannot be restarted and the db cannot go offline or detach due to company policy.My DB after changing from full to simple mode still has 313GB tlog file and when I run DBCC OPENTRAN I get Transaction information for database 'DB'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (2882:26:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Which means this DB is participating in a High Availability process like replication, mirroring or log shipping.
So I run EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1.
This is useful when there are replicated transactions in the transaction log that are no longer valid and you want to truncate the log.
But I get an error:
Msg 18757, Level 16, State 1, Procedure sp_repldone, Line 1
Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication.
There are currently 9 connections and all are sleeping.What else can i try in order to shrink the tlog file?
View 6 Replies
View Related
Jan 3, 2008
I have a 14GB database whose data content is legacy and is described as static. The log file is significantly large and continues to change size mostly increasing by 2-5GB a day (~60GB now) I have observed over the past two days; it shrank once unexpectly by a few GB. The instance is hosting other databases such as: EnterpriseVaultDirectory, EnterpriseVaultMonitoring, EnterpriseVaultStore, and NetPerfMon - might these seemingly unrelated data sources be involved?
I am trying to a trace to find traffic against the tables, no such luck.
Web applications are playing against it for queries but there should be no UPDATEs beign applied. I can only suspect that other unknown applications are performing operations but have yet to find unexplained connections.
Are there any other reasons why this type of log file activity would happen merely due to queries or stored procedure calls?
Lets also state, "mirroring, indexing, replication" are not at play. I know logging "Full" is not necessary as "Simple" should suffice but I am still hunting down why UPDATEs might be getting through. I realize I might adjust the migrated SQL 2000 security model to deny updates to find what breaks but would rather not take that iniative yet.
The installation is a fresh SQL 2005 Standard setup with SP2 applied; the databases were upgraded.
View 6 Replies
View Related
Jul 11, 2007
Using SQL Server 2005 Server Management Studio, I attempted to back up a database, and received this error:
Backup failed: System.Data.SqlClient.SqlError: Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the satement after the current backup or file manipulation is completed (Microsoft.SqlServer.Smo)
Program location:
at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)
at Microsoft.SqlServer.Management.SqlManagerUI.BackupPropOptions.OnRunNow(Object sender)
Backup Options were set to:
Back up to the existing media set
Overwrite all existing backup sets
I am fairly new to SQL 2005. Can someone help me get past this issue? What other information do I need to provide?
View 11 Replies
View Related
Dec 5, 2006
What is the difference between "Files aned File Groups" backup and Partial Backup?
Looks like both are same.. Please comment.
View 3 Replies
View Related
Jul 9, 2002
I am getting periodic failures of my Transaction log. SQL
Server 7.0 Service Pack 3 on NT 4.0. IT is not occurring
in a set pattern. I have attached the error. I am unable
to find anything on the Internet or n TechNet can someone
help me out.
Thanks;
Message is as follows:
Microsoft (R) SQLMaint Utility (Unicode),
Version [Microsoft SQL-DMO (ODBC SQLState: HYT00)]
Error 0: [Microsoft][ODBC SQL Server Driver]Timeout expired
View 1 Replies
View Related
Dec 15, 2000
Hi,
My Transaction log has 0% Log Space available. I have performed a dump transaction with no_log but to no effect. I have also extended the log size from
100MB to 200MB and this has not made any inmpression either after several dump database statements also.
The Data Device was 500MB and I extended it to 800MB and again performed a backup. Has anyone any idea how to recover from this situation ?
I can make queries on the data fine but the several backups(and Truncate log) I have made are still not creating any space for my Transaction log.
Please help !
Thanks in advance,
Alan
View 3 Replies
View Related
Jul 26, 2007
Hi all
Is there anyway to find out using TSQL for the used space VS what is available for TLOG file? Cause the GUI won't allow me [Database] > Report > Disk Usage. It said that Unable to display the report becuase the database has a compatibility level of 80 ?!?!
Thanks
View 13 Replies
View Related
Sep 8, 2015
I got full backup on daily schedule its taking more space on Drive because each file has more than 25GB.I am using SLQ server 2008R2 so I'm looking to take the backup with compression instead of uncompressed Backup. What are the impacts of compressed backup. Is there any problems with compressed backup while restoring the backup file.
View 8 Replies
View Related
Apr 1, 2008
I should restore a SQL Server 2005 Database from backup. The backup contains three files, named user.bak0, user.bak1 and user.bak2.
How is the syntax of the restore filelistonly and the restore database ... ?
I usualy write
restore filelistonly from disk = 'path and filenam.bak'
restore database. zy
from disk = 'path and filename.bak'
with replace,
move.....
move....
This works but I cannot use it with a splitted backup file. The files are much too big to put together to one file.
Thanks in advance for any help.
View 3 Replies
View Related
Aug 22, 2003
recently i noticed that my trans Log size is not increasing(it is constant) , but the databse size is increasing at high speed.
why is it so? earlier the reverse used to happen i.e databse size increased only after a certain period. and log size increased at const. rate.
View 5 Replies
View Related
Jun 3, 2007
With Migrating from SQL 2000 to SQL 2005 I realise that there are a few options available.
A SQL 2000 backup is able to be restored into 2005 and is workable however there are some issues with internals that have changed. I have identified some DDL which would need to be modified to work correctly.
If the Backup is restored and left in a recoverable state does 2005 allow for 2000 TLog backups to be restored as well ? I am assuming that as you can restore a full db backup that this is allowed.
I am asking the question as i have a very short and fixed deadline of moving from 2000 to 2005. A new datacentre is being moved to and the old DC is being turned off. all new hardware is being placed in the new DC so i need to move from the old to the new with very little downtime to the DB or app.
My approach is as follows
1. Full DB Backup (2000) copied across to 2005 server and loaded being left in a recoverable state.
2. Tlog backups copied across on scheduled basis and loaded to a point in time.
3. Test new 2005 DB with app/web frontend
4. Bring both in sync again and then perform 1 final Tlog backup, copy and load bringing app online again with very little downtime.
Has anybody tried this approach ? does anybody see any issues with my approach ? all comments welcome on this.
Thx
View 4 Replies
View Related
Nov 18, 2014
I am in the process of formulating recommendations with respect to the purchase of additional storage for our current SQL 2012 SharePoint (2013) instance. My recommendation is to purchase separate storage (i.e, 15k disks) for the TempDB and Tlogs respectively (two sets of raid 10 disks). Currently, this server is hosting several instances, including SP, using two arrays (one for database and the other for Tlogs).
I am attempting to find information/recommendations on how to go about projecting the amount of storage for each of these while factoring in for growth.
Additional Details:
how to best formulate a reasonable estimate. Our largest content database belongs to IT and is currently ~80GB. That said, this is currently an outlier. The remaining content databases are less than 10GB (most are less than 2-3 GB). However, SharePoint will be used for digital document imaging in addition to, eventually, replacing file shares as our primary document storage medium once we roll it out.
Our current tempDB is ~400MB, but the instance was recently started a few days ago, as we had to failover to our backup server for hardware maintenance. I do not have any historical data on TempDB growth at this time. Also, I don't know how useful this would be given we have not fully deployed yet.
View 0 Replies
View Related
Nov 8, 2015
I have a vendor database that has a stored procedure that runs a long time.Eventually, the database runs out of log space.
Setting the database to FULL and doing frequent log backups does not work.
The log does not get truncated during this log backups.
The stored procedure in question has SET XACT_ABORT ON statement at the beginning.
View 4 Replies
View Related
Jun 11, 2015
My customer got a total hard drive failure.After sending it to drive recovery specialist we were able to recover the LDF log file (MyDB_0.LDF).But the MDF file was completely destroyed (MyDB.MDF).They have a good full backup from a month ago.
1) Installed SQL Server 2012 on a new PC
2) Created a new database of same name (MyDB) - with same MDF and LDF file names as original
3) Took the new database offline
4) deleted the MDF and LDF files of the new database
5) put "MyDB_0.LDF" in the place of the LDF file I just deleted
6) put the database back on-line
7) after hitting F5 to refresh databases - it shows "MyDB (Recovery Pending)"
8) tried to do Tail Log Backup with this command
BACKUP LOG [MyDB] TO DISK = N'C:BACKUPMyDB_TailLog.bak'
WITH NO_TRUNCATE
And I get this error...
Msg 3447, Level 16, State 1, Line 3
Could not activate or scan all of the log files for database 'MyDB'.
The sad thing is I know we can get this data back using ApexSQL-Log. I can see all the transactions since the last full backup in this program - so the log file is not damaged. But my client doesn't want to pay the $2000 fee for this software.There has to be a way to restore this data, without having to purchase a third party tool.
View 13 Replies
View Related
May 8, 2015
I checked quickly one network and I ran into the folders; I found out the folder MSSQL backup, nothing strange so far. Within that folder, anyway, I found out several backup and one very huge file (datawarehouse) with extension FILE. I am wondering what can be? I got datawarehouse mdf of course and datawarehouse log but what is that huge file (1 TB)?
View 6 Replies
View Related
Jul 12, 2006
Hi,
I tried to backup the master key by the following syntax :
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mypassword'
BACKUP MASTER KEY TO FILE = 'c: empmaster' ENCRYPTION BY PASSWORD = 'mypassword'
but it failed and i got the following message:
Cannot write into file 'c: empmaster'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.
NB: I am using the "sa" user to execute this command.
I know that we have a security permission issue , but where and how ?
Regards,
Tarek Ghazali
SQL Server MVP
View 12 Replies
View Related
Nov 8, 2001
Hi there,
After a bad server crash, the only remnant we have of our SQL server database are the .mdf and .ldf files in the MSSQL7/Data folder. Can we restore this database from either of these files and if so, what is the procedure? Sorry but I'm an SQL server newbie.
Thanks in advance,
Paul
View 3 Replies
View Related
Nov 13, 2007
Hi,
I dropped a SPROC, and was in the process of reacreating it when I lost power. Now I don't have the SPROC except for in an old backup file.
Is there anyway to get this without restoring the DB ? If not, can I easily restore to a different DB name and then delete it? My backups are on my live server and I don't want to overwrite my current DB with my backup :)
thanks for any suggestions!!
mike123
View 4 Replies
View Related
Mar 28, 2008
I rigth click my project , i choose add -> new item -> sql database
and i write
SqlConnection cc = new SqlConnection(@"Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|DataData.mdf;Integrated Security=True;User Instance=True");
when i code
cmd.CommandText = "Backup database Data to disk = 'C:\123.bak'";
i recieve Error
Could not locate entry in sysdatabases for database 'Data'. No entry found with that name. Make sure that the name is entered correctly.
BACKUP DATABASE is terminating abnormally.
help me to solve this problem please!!!
Thanks.
View 7 Replies
View Related
Sep 25, 2004
One of my databases is approxiamtely 1.5 GB's but when I run a backup the backup file explodes to 38GB's.
What is the proper usage of Shrink Database, is this safe or is there another method to reduce the size?
View 2 Replies
View Related
Aug 23, 2001
My database's log file is full. i want to backup it .
First i create a backup device named aa.
Than i use Enterprise to backup trasaction log.
But it popup an error messagebox.
The title is "Microsoft SQL-DMO(ODBC SQL state:42000)
The content is "write on 'aa' failed,status=112. See the SQL error log for more details.Backup or Restore operation terminating abnormally.
What's the matter ?
View 5 Replies
View Related
May 12, 2000
I am using SQL Server 7 and have about 5 databases. One of them has a data file of about 10 Meg, and most of the others are larger. I do a nightly backup to both a local and mapped drive. On both, the size of the backup file for this database is more than 500 Meg, but the rest appear to be an appropriate size. Does anyone know why this would be happening? The database works fine, it does not get a lot of insert/delete activity and I run DBCC every weekend. If anyone has any ideas I would sure like to hear from them.
View 1 Replies
View Related
Dec 19, 2000
Hi,
How can we create a new D/B from a D/B Backup file (DataBase.Bak).
Thanks,
Princy
View 3 Replies
View Related
Oct 16, 2000
Hi,
I am new to SqlServer (I am using 7.0) and I have just recieved a database.bak file from one of our clients and I want to create a new D/B from this back up file ,I have already installed the MS SqlServer. Can you tell me how to do this.
Thanks,
Princy
View 1 Replies
View Related
Apr 25, 2002
Anyone have anything hints on getting the option of DELBKUP to work. I have a maintenance job that backs up the database. Also set to delete backups older than 1 day. This doesn't seem to be working.
Any thoughts.
Thanking you in advance.
View 1 Replies
View Related
Jul 11, 2002
Is the name of the most recent backup file for each database stored anywhere in SQL2K? I want execute a SQA job periodically that takes the BAK from database A and restores it over database B (using the T-SQL RESTORE DATABASE procedure), but I need to know the exact name of the .BAK file; i.e. I need to know the yyyymmddhhmm value at the end of that file.
TIA,
Al
View 1 Replies
View Related
Jul 22, 2002
Is the name of the last backup file recorded anywhere in SQL2K? I do backups every 2 hours, and want to restore backups from database A to database B once a day using the latest backup. Unfortunately the backup files don't have the time the backup started (e.g. 1600), but have the time they ended (I presume); e.g. 1604.
I want to automate the process; any suggestions on how to find the name of the last database file?
TIA,
Al
View 4 Replies
View Related
Oct 10, 2001
Can you backup individual files within a file group.?
View 1 Replies
View Related
