We have received a backup file that is 6G in size. The log file is
about 74G while the MDF is about 5 G when we restored. Is there a way
to run a SQL script to restore the .BAK file without restoring the log
file as we do not have enough space on the server.
PS. We are running Sql2K5.
Thanks!
I'm trying to configure log shipping When I retore the full disk dump of my databaseto my standby server, I need to be able to go in and drop and recreate several user logins(so they can do reports, read-only).
Once I do this though, I will be unable to restore T-logs, right?
Anyone know a way to get it back into a read only state and able to accept T-log restores? Thanks.
Pete K.
Hi all,
I am restoring database from Transaction Logs. I followed all the steps
mentioned in the book. Just before the last step there are three options
1. Leave Database Operational. No Additional Logs can be Restored.
2. Leave Database Nonoperational But Able to Restore Additional
Transaction Logs.
3. Leave Database Read_Only And Able To Restore Additional Transaction Logs.
Option 2 and 3 aresupposed to set on the NORECOVERY flag.
I tried both options 2, and 3 , But still got messages that I did not
specify WITH NORECOVERY or WITH STANDBY.
Can anyone please tell me what I am doing wrong?
Thanks in Advance.
Attaullah
I am trying to restore a set of transactions logs on my SQL server and I keep getting the following error 'The preceding restore operation did not specify with NO RECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step. RESTORE LOG is terminating abnormally.
Can someone please show me how to properly restore a set of backup logs with specifying the required recovery option?
Thanks
Hi Gurus,i am having problems with restoring a ms sql database.i have restored the database using veritas to a different location('g:datafiles') in no recover mode.when i view the database through the Enterprise Manager, it shows thedatabase as silver icon (loading).i go to sql analyzer, and put in the restore commandrestore log myDatabasefrom 'mylog'with recoverythis produces an errorRESTORE FILELISTONLYFROM jobsServer: Msg 3206, Level 16, State 1, Line 1No entry in sysdevices for backup device 'mylog'. Update sysdevicesand rerunstatement.i look in master..sysdevices - no entry for mylog, but then no entriesfor the log files for any of the other perfectly working databaseseither.i do have a copy of the log files ('c:logfiles') in another locationi would like the following help if possible:* a way to update sysdevices with the log file i wish to apply to myrestored database so it will let my restore go through properly.* a way to specify to use apply the logs in 'c:logfiles' withouthaving to give the restore statement a logical name for the log files(which naturally won't be in sysdevices!)pls supply transact sqlmy thanksEdwina63(if wish to email please remove h from edwinah@)p.s sp_add_log_file_recover_suspect_db will not work in a partiallyrestored database
View 6 Replies View RelatedI have recently implemented a backup solution that keeps our standby server up-to-date with nightly database backup and restores. Ironed out all the problems with syslogins and orphan users. It's been working very well over the past few weeks.
On the production server we do hourly tran log dumps every hour between 8am and 10pm. I would like to implement some form of "log shipping" to bring the window of vulnerability down to 1 hour. By making some alterations to my current process I been able to incorporate the hourly log dumps in the same process.
However, when I try to restore the log on the standby servre I get the following message,
Server: Msg 4305, Level 16, State 1, Line 1
Specified file 'HODB1SQLBACKUPRace_Prd_T_dump.200101231513' is out of sequence. Current time stamp is Jan 23 2001 2:01PM while dump was from Jan 23 2001 3:01PM.
After a bit of investigation I found the dumptrdate field in the sysdatabases table and it was set to Jan 23 2001 14:01. After updating this field to Jan 23 2001 14:01, I ran the load command again but received the same error message.
Is there something else that needs updating on the standby server? Is what I'm trying to do possible in 6.5? Any help or ideas would be greatly appreciated.
Thanks
Phill
I have scheduled a database backup at 9 pm and 5 Transaction Log backups. The transaction Log backups happen at 11 am, 1 pm, 3 pm and 5 pm (device does not get initialized) and one at 11 pm where i initialize the backup device. now i have another server with the same database on it. I restore these backups on to this server by simple copying the files and then doing Restore ...From Device...Add file...option. I am able to restore the DB backup but when i try to restore the log backups it get the error msg -
'Specified file 'G:...' is out of sequence. current time stamp is Feb 9 2000 3:54 pm while dump was from Feb 9 2000 3 pm.'
When i am restoring the logs i start from the 1st backup buyt still i get this error. Can someone pls let me know what the problem is and how to resolve it. Thanks.
Hey all,
Just went through migrating to SQL 2005 as well and we have a need to apply log shipping between SQL 2000 and SQL 2005. Well not shipping per say in the automated fashion that SQL 2005 offers but rather we need to apply SQL 2000 transaction logs to a SQL 2005 instance. Though the database is still in SQL 2000 version 80 and the secondary database needs to be available in read only. (STANDBY MODE)
When I attempt to restore either a FULL backup or transaction log I get the error:
RESTORE DATABASE is terminating abnormally. This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY
The only way I could restore the FULL was by using the WITH RECOVERY OR NORECOVERY option during the restore process. The issue with this is I need the database in standby by mode in order to continue to append transaction logs.
Anybody have any thoughts how I might be able to get around this?
Thanks
Eric
I need to ensure users can submit SQL queries (readonly) to a database whilst transaction logs are restored (at 15 minutes intervals.)
Is this possible?
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 RelatedI've truncated and shrunk the database and now, it says that the size of the db is 14 mb and 8mb is free. However, on the file system, the mdb and
ldb files together adds up to 14 mb. How can I free up the 8 mb for other usage?
Thanks,
Anthony
Help on Backup of Logs
I get the following errors when I am trying to backup log every 15 minutes. It fails only during the peak hours.Does anyone have any ideas why it happens.
Cannot allow BACKUP LOG because file 'XX_data' has been subjected to nonlogged updates and cannot be rolled forward. Perform a full database, or differential database, backup. [SQLSTATE 42000] (Error 4213) Backup or restore operation terminating abnormally. [SQLSTATE 4200
I am trying to backup my Transaction Logs using T-SQL with the aid of a job. The problem I am running into is that I need it to backup every three hours and retain one days worth of transactional logs. This is the query I got so far.
BACKUP LOG CA001 TO DISK = 'S:AZTARGPCA001ca001_TRN_bkuplog.trn' with retaindays = 1
Any suggestions welcomed.
I've got a Sql server 2000 box that currently backs up to tape in full every night. I also want to back the box up off site but not in full (as 30GB is a little too much to transfer every evening).
So my plan was to do a full backup to tape at 7pm then a differential at 8pm (to transfer off site).
The problem I am having is that after my differential has been done the logs get truncated so if I want to replay them for any reason I need to get that differential back to site.
Anyone have any suggestions please?
Hi:
I have configured maintenace plan to do a full backup of sql server at 5:00 Am followed by transaction log backup every 15 mts.The transaction log backup basically appends to existing backups so there is only one file of transaction log backup.
I want to modify that the transaction logs backup only has transaction logs after 5:00 Am full backup.Right now the transaction backup has backup of all the transaction logs since i have scheduled.
Thanks for your help.
Ganesh
I noticed our log files are getting way too big. I found that a previous
SQL guy had set up the following scheduled job with the TSQL statement:
BACKUP LOG DEV TO DevDailyTranLog with noinit
This runs every 20 minutes.
There's also a Maint Plan to do a Complete backup every night and a
Transaction Backup every hour (could be set to 20 min)
Why do you suppose the BACKUP LOG job exists ? If the maint plan were
set to backup transaction every 20 min, wouldn't the 2 jobs be
duplicating each other ?
Also, I notice that the Tran Logs specified in the database properties
seem awfully big (4 gig), shouldn't they automatically be truncated when
the daily full backup occurs ? (full recovery model)
Feel free to call me and straighten me out or to get more info.
Win2K, SQL 2K
gdunn@taunton.com
203-426-8171 x 374
Hi,
I am in the process of migrating databases from a SQL Server 2000 server to a 2005 server.
I have taken full backups and restored without recovery on the 2005 server. I now need to take transaction log backups and restore with recovery tonight to complete the operation. My question is this:
Some of the transaction logs have grown very large. Is it safe practice to perform a backup tran with truncate only and then run dbcc shrinkfile to reduce the size before I back up? This would make the process much quicker.
Thanks.
In MS SQL 2005 when you do a Full Backup does it also backup andtruncate the transaction logs or do I need to back the transactionlogs up separately?Thanks.Brian
View 4 Replies View RelatedIs there any problem with implementating some kind of replication andbacking up transaction logs at the same time? SQL Server 2000 StandardEditionThe server is configured to back up log files regularly, with thepresenceof a(New) Disaster recovery server and with an inability forstandby(Standard Edition)I am planning to implement some kind ofreplication hopefully still backing up the transaction logs to enablea point in time recovery.Thanks in advance for your great ideasVincento
View 1 Replies View RelatedI've got a couple of backup database tasks setup to run one after the other. The are set up the same, Alldatabases, backup to a device each, and overwrite every time. The task on databases full works fine but the one on logs fails every time, right away with the following rather esoteric error pulled from logging in the event log.
Event Type: Error
Event Source: SQLISPackage
Event Category: None
Event ID: 12550
Date: 29/01/2008
Time: 09:54:11
User: OILCATSsqlservice
Computer: GUNKVBPROC
Description:
Event Name: OnError
Message: The Execute method on the task returned error code 0x80004003 (Object reference not set to an instance of an object.). The Execute method must succeed, and indicate the result using an "out" parameter.
Operator: OILCATSSqlService
Source Name: Back Up Database Task 1
Source ID: {BF117128-FB4C-470D-AC00-4C68797C2C97}
Execution ID: {450BA60C-2EBF-453B-B3E4-80FBC2042F83}
Start Time: 29/01/2008 09:54:11
End Time: 29/01/2008 09:54:11
Data Code: -1073594108
What is this elusive Execute method that is only relevant to the log aspect of this task?
Hi,
Can someone please express their experience and recommendations for the following questions?
1. Do you need to unable SQL-Backup if you already using a 3rd party software utility to backup the SQL data drives?
2. If you do not want to shutdown SQL services for backup can you use Volume Shadow Copy as a solution to open files?
3. What size would you recommended to use for the Transaction Logs DB if my Data DB size is 2GB
I am running Windows 2003 with SQL 2000/SP3. Obviously, I am not an expert in SQL so I appreciate your help.
Thanks
We run a disk backup (full) early in the evening, and then another bull backup to tape later at night. We also keep independent backups of the logs.
It seems that any full backup "resets" the ability to apply logs when restoring?
I want to be able to restore a full backup, and apply logs forward possibly more than 1 day, in case a backup is corrupted and not usable. But it seems I can only apply logs up until the time of the next full backup.
Is this true, and is there a way around this?
Hi guys, just wanna ask about the backup and restore database method. What's the best way for database and restore which able to view all the transaction logs after the database being restored. Currently I backup my database daily for recovery purposes. However, if I restore the backup file at another server and use SQL log application for viewing the prefer database's transaction log, it shows all the previous log had been truncated.
Therefore, I want to know is there any way that able to get the transaction logs after restore from a database backup file? Hope able to get any assistance here as soon as possible. Thank you.
Best Regards,
Hans
HI everybody,
I have a problem in restoring a backup. In the restore command there is one option called <file> = 'file_number'. Where do we get this 'file_number'?.
tks in advance
Srini
Hi Everyone, I was prototyping a DB and made a backup of all the files in the data directory of sql server, thinking that I would be able to restore the system from another installation when I made it to the US. I have the cd with the archives on and a full backup of the DB I want but I have no idea how to get sql server to initialise it.
Any ideas???
Steve
Guys, i've 1.6 GB of backup size and it takes 1 hour for this backup to get restored over a SCSII 10,000 rpm.
Is it normal. How can i make the restore faster.
It takes only 5-10 mins to take the backup however.
I am restoring the full backup, no differential or log file.
Howdy!
I Have a full database backup of the database aaa ON a server xxxx. I want to restore the same back upto as a database aaa ON another server yyyy.For to achieve this do I need to make the database aaa ON server xxxx.
How can I do that.Please help me in this issue.
Thanks.
I used backup and restore to upgrade a database from sql 2000 to sql 2005. Is it necessary to create the mdf and ldf on the new server at the time of restore under options or should I copy them to the new server in the data folder? I am new and not quite sure what the log files hold.
Thanks
Hi,
I am using SQL server 2000. When I want to restore a backup database from the disk. I use the Enterprise Manager tool to restore database. From there I checked Restore database and select from the first backup to restore. Do I need to backup the transaction log after? Or this does all of it?
Hi,
I have a problem. I create a backup from a DB named Prueba (in full mode). But when I try to restore it in a new server the Prueba backup is loaded, and appear a message that said so. But in the DB list next to his name(Prueba) appear a "(Restoring)" ... The DB is not so big (i didnt know why is taking so long or whats the prblem) and i cannot do anything whit it because it doesn t show the tables or any info.
Thanks for the help ..
I really thanks.
Hernan
Hi there, I'm from Argentina. I have an SQL backup made with SQL Server 2005 Developer Edition. I want to restore this backup into a Server with SQL Server 2005 Express Edition. Can this be possible? Thanks.
View 3 Replies View RelatedHi,I am working in a sql server 2000 environment but my customer uses sql server 7. Is it possible to use backup and restore to transfer the database or should I set up a sql server 7 for development?The database only contains tables and views...ThanksKoen
View 1 Replies View RelatedPls explain how to backup and restore data on SQL Server with a CD drive instead of tape drive.
This is urgent.
Thanks.