SQL Server 2000 Backup / Restore Bug

Nov 24, 2005

Hello!

I have a bug in SQL Server 2000 that I would like to discuss.

The bug is described briefly on http://support.microsoft.com/kb/821334.

"The potential for inconsistency in the backup history tables backupset and backupmediafamily is resolved. This issue may cause the restore process to point to the wrong backup files."

According to my experiences the bug is extremely serious. SQL Server backup and restore operations are not fully reliable. If you have a disaster situation there is a possibility that you can not perform a restore.

Technically a media_family_id is generated in a backup operation and inserted into msdb.dbo.backupmediafamily. What could happen is that a media_family_id, that already exists in backupmediafamily, is generated.

The bug is fixed from version 8.00.0859, but only if you have an special undocumented trace flag (3003) enabled.

Here is the information that I have got from Microsoft about this trace flag.
"Trace flag 3003 changes how SQL generates media_set_id for new backup files. It will guarantee that new media_set_id values in backupset table are unique when you create a new file with your backup. This is the only place in code where TF 3003 is used so it will not impact your SQL Server operations."

I have a SQL Server with 80 databases. I have a maintenance plan with full backup every 24 hours and log backup every 5 minutes.

The error happened on my server a few times every 24 hours. I believe it's a general problem.

Best regards

Ola Hallengren

View 9 Replies


ADVERTISEMENT

How To Backup And Restore Sql Server 2000

Mar 28, 2008

Hai friends.....
I am doing project in ASP.NET(vb) my backend is sql server 2000...in my PC..
I have another pc....where sql server 2000 is installed.....
i have nearly 50 tables...in sql server 2000. in my PC..
I want to backup these tables from my pc and another pc...
can u tell me the steps...
I will try immediately....
Ambrose......

View 31 Replies View Related

SQL Server 2000 Backup + Restore Question

Sep 29, 2005

Of course I'll try this before I go live, but I figured I'd askhere first. I've read the docs, and the answer is unclear.My current backup strategy is:Take a full database backup occasionally (like, every two weeks).Back up logs every five minutes.This works, but restoring the database all the way through cantake a while, as I've sometimes spun through 800+ log restores.I'd like to change toTake a full database backup occasionally (like, every two weeks).Take a differential backup every day.Back up logs every five minutes.So, I'd have this set of backups after day twoFULLLOGS 1-100DIFFERENTIALLOGS 101-200When I go to restore, do I always have to doFULLDIFFERENTIALLOGS 101-200or can I doFULLLOGS 1-100LOGS 101-200?I guess what I'm really asking is: is there any reason to keepthe "old" logs around (in this case 1-100), if I assume I alwayshave the latest differential?I suppose I *might* need them to do a "point in time" restore,i.e.FULLLOGS 1-53.but assuming I never want to do that, can I discard the logsthat are earlier than my latest differential?

View 1 Replies View Related

Restore Clustered SQL Server 2000 From Backup On Local Drive

Aug 1, 2007

We are attempting to restore one of our databases from a backup that went to a local drive on the server.
We see the backupset in the list but receive an error that it is not available when we try to use it. When we try to restore from device and select the files the drive letter is not available. When we attempt to enter the path to the file it can't locate it. We don't have space on our SAN to copy the backup there and we can't add the local drive to the cluster resources.

Is there a way to get this backup done?

View 1 Replies View Related

How To Restore Sql Server 2000 Db Backup Into Sql Server 2005 Express Edition

Jun 22, 2007

Hi Friends,I have installed SQL server 2005 Express Edition and SQL Server managementI have a SQL server 2000 db backup file. I try create a new database in my SQL server 2005 express Edition and try restore that backup file from device, it only searching for file with *.bak and *.tm extension! I tried generate backup file with .bak extension and tried restore into SQl server 2005 express edition but still it is not allowing to do so! I also tried copy my database's data file and log file and paste it under SQL server 2005 express edition Data folder and still not able to read the tables.Is that any ways to do restoring for this SQL server 2000 backup file into SQL server 2005 express edition! Anybody can help me on this please...:eek:

View 2 Replies View Related

Backup File Of SQL Server 2000 Is Restore In SQL Server 2005

Feb 11, 2008

Hi Dear,

May Any one guide me?
I have a backup file of database which is in SQL Server 2000. it has no Extension.I want to restore this backupfile or this database in my SQL Server 2005.
I have tried to Attach Database or attach this backup file in Sql Server2005 but it also fails .
First I have created New database name as is on the backupfile and then I have also tried to rename this file with .bak extension and then try to restore again it fails.
Plese Guide me urgently........
:eek: :eek: :eek: :eek: :eek:

View 9 Replies View Related

How To Restore A Sql Server 2005 Database Backup To Sql Server 2000

Oct 17, 2006

Hi All

This is a very urgent requirement of my client having 200 stores and one Head office. the HO has just installed SQL-Server 2005, The HO sends small backups of the data related to stock transfer which needs to be restored at stores. It was working find when both the HO and stores were SQL-Server 2000. but the process has come to a dead halt as the HO has SQL-Server 2005. It is not possible to convert the stores immediately. please help me out.



Thanks

Raoshan

View 3 Replies View Related

Restore Backup From SQL Server 2000 In SQL Server 2005?

Jan 29, 2008

I was running Small Business Server 2003 with MS SQL Server 2000. I put backups of all my SQL Server 2000 databases on hard disk d: and the operating system and applications on c:/. I then reformatted c:/ (but left d:/ intact) and installed Windows XP Home. Then I installed MS SQL Server 2005 Developer's Edition.

I would like to now restore the backups of the SQL Server 2000 databases (still intact on drive d:/) to SQL Server 2005, but I get a variety of error messages.

I suspect that I am going about this the wrong way. I have done several hours of research in the documentation without success. I cannot attach the SQL Server 2000 database to SQL Server 2005 because it is a backup, not a database. I cannot migrate the databases as part of the installation of SQL Server 2005 because (a) they are back-ups, and (b) I don't have SQL Server 2000 installed when I install SQL Server 2005.

Is there a solution?

TIA

Mark Schreiner

View 6 Replies View Related

SQL 2000 DB Backup Restore To SQL 2005 DB

Jun 10, 2008

Friends -

Is it possible to restore user databases in SQL Server 2005 with user database backup copy of SQL Server 2000.

Please advise on this.

Cheers:)
VSH

View 11 Replies View Related

SQL 2005 Backup To Restore In SQL 2000 SP3

Feb 15, 2007

Hi All
I have a problem in transferring a 2005 backed up database to SQL Server 2000 SP3.
i got an error message, crux of it is
"too many backup devices specified only 64 allowed"

the i browsed MICROSOFT help, downloaded SQL 2000 SP4 - mentioned as a fix for the above error.

Now get a message,crux of it is
"current version of Database in 611 and destination version is 536"

Is there a solution????
should i download any other patches or
Should i tweak the SQL 2005 backup process???
ASAP Plz

View 1 Replies View Related

Backup SQL 2000 SP4 And Restore On SQL 2005

May 26, 2006

Does anyone know if this works to where the data is viewable/usable?



I have an application that takes datasets out of the SQL database. We decommisioned our old SQL 2000 SP4 machine and brought in a SQL 2005 Server. What we did was backed up the old database and then restored it on the new Server.

Now the application does not see the old datasets but will collect new ones that are viewable. We can import older datasets but we have nearly 27 GB of data, which our DBA can see the old tables in Enterprise Manager but our applciation can't see it.

I'm thinking it might be a schema issue or the way that 2005 translate the odler 2000 SP4 database. Does anyone know, or have they run a back up on a older 2000 Server and then restored it to a 2005?

Thank you.

David

View 4 Replies View Related

SQL 2005 Backup To 2000 Restore

Jul 9, 2007

I need to restore a backup of database taken from SQL 2005 in SQLServer 2000.



I tried to restore from backup file; It says "Error 3205: Too many backup devices specified for backup or restore; Only 64 are allowed" .



I tried to attach mdf file, it too failed.



Can anyone help me in the procedure to restore 2005 backup in 2000?



View 4 Replies View Related

Backup MS SQL 2005 -&&> Restore MS SQL 2000

Feb 9, 2007

hi there

i've made a backup from a MS SQL Server 2005 database and i tried to restore this database to a MS SQL Server 2000. Unfortunately, I got following error

The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrad this database.

what can i do ? is it possible to restore the MS SQL Server 2005 DB on a MS SQL Server 2000 ?

Philipp

View 4 Replies View Related

Bloated Database Wont Shrink After Backup/restore SQL 2000

Jul 20, 2005

Help,I have a database that has a data file of 2GB and a log file of 31GB.In enterprise manager, when I choose shrink it says there is 30GB ofunused space. When I shrink the database, it does not shrink,(however it says it has completed).I've done a complete backup, tried shrink again, no dice. I thenbacked up the database (which the backup was 1.9GB), deleted thedatabase and made a new database with 2,048MB for the data and samefor the log file.When I restore, the log file jumps up to 31GB again. When I check thespace when I use the shrink, it again says I have 30GB of unusedspace.How on earth do I get this file to shrink?I've been able to shrink other databases, but not this one.TIARob

View 1 Replies View Related

Backup/Restore Vs Attach/Detach 2000 Database To 2005

Jul 15, 2006

I'm trying to see the differences between Backup/Restore and Attach/Detach. I backup and detach a database from SQL Server 2000 SP3 and then attach and restore it to SQL Server 2005 SP1.

The differences I noticed are:

1. The restored database has a much larger initial size (database size is same) for data and log.
2. The attached database has a last backup date
3. If the backup is restored over a database, the restored database is showing owned by the database owner of the database restored over but syslogins and sysusers do not match.

I don't understand why #1 happens.

Are there any other differences between Backup/Restore and Attach/Detach?



Thanks,

Peter

View 2 Replies View Related

RESTORE DATABASE Timeout In SQL 2000 With Large Backup Files

Sep 11, 2007



Hello,

I am attempting to restore the database from within VB.NET application I am making the following 3 calls:

RESTORE FileListOnly FROM DISK = 'C:MyDatabase.dat'

USE Master RESTORE DATABASE MyDatabase FROM DISK = 'C:MyDatabase.dat' WITH NORECOVERY, MOVE 'MyDatabase' TO 'C:Program FilesMicrosoft SQL ServerMSSQLDataMyDatabase.mdf', MOVE 'MyDatabase_log' TO 'C:Program FilesMicrosoft SQL ServerMSSQLDataLDFMyDatabase.ldf', REPLACE

RESTORE DATABASE MyDatabase FROM DISK = 'C:MyDatabase.dat'


using SMO. This logic works fine with small *.dat files, however when using *.dat file of about 4Gb I get an error on the 3d restore database call:



ExecuteNonQuery failed for Database 'master'.

An exception occurred while executing a Transact-SQL statement or batch.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Operator aborted backup or restore. See the error messages returned to the console for more details.

ExecuteNonQuery failed for Database 'master'.

An exception occurred while executing a Transact-SQL statement or batch.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Operator aborted backup or restore. See the error messages returned to the console for more details.



The same program/logic also works fine when I use MS SQL 2005 and it runs fine from MS SQL 2005 Query Analyzer for both 2005 and 2000 databases. There seem to be only problem with MS SQL 2000 from within VB.NET. Anybody has any idea? I'd appreciate any response. Thanks

Eugene

View 6 Replies View Related

SQL Server Admin 2014 :: Restore DB With Full Backup And Transactional Log Backup

Aug 3, 2015

Need to restore database,here's the scenario:

Data got deleted on Friday evening, need to have database restored to FRiday afternoon and also some data has been entered on Monday, which needs to be there.

View 8 Replies View Related

Can Windows 2003 Server Backup Utility Be Used To Backup A SQL 2000 Dbase

Dec 28, 2007

Windows 2003 backup utility uses the shadow copy option that allows it to copy open files.
Therefore, can I use this utility to backup the .mdf and .ldf files for my SQL 2000 database?
I can then attach the .mdf files if I need to restore the database to another server.
Can anyone tell me if this is safe? I've tried it and it worked but I'm worried there maybe some lurking danger in using this approach.

View 4 Replies View Related

Sql Server Backup/restore

Dec 27, 2007

Hi all,
I am new to Sql server backup and restore.
I have an sql server database in my office and I have successfully copied the whole database to my pc at home. For this purpose I used complete database backup.

Now what I want to do is delete all the data from office database(this can be done manually also) and copy daily data to my pc at home.So that only one day data will reside in my office compuer.

I tried this with completed database backup/restore again. But the old data in my home pc got lost. And when I tried the same with differetial backup, it gave an error message lile "the restore process was not stared with NORECOVER or STAND BY option".

Please suggest me how can I do daily backup from office computer and restore the data to my home PC so that the daily data gets appended to the existing database in my home PC.

Thank you in advance.

View 3 Replies View Related

SQL Server: Backup/Restore

Jul 23, 2005

I have SQL Server desktop installed on my laptop. I have a database onthese that i created. but when i try to back it up to disk, i have nochoices available to select either disk or tape. i still try to clickon the 'add' button to select a file and it fails.i checked the settings and have the sys admin permission level as it's astandalone on my laptop. any suggestions???tiawoody

View 1 Replies View Related

Backup From One Server, Restore To Another

Jul 23, 2005

Hi there,I've been sent a backup file from a SQL Server 2000 DB and triedrestoring into a blank DB (with the same name) on my SQL Server 2K,only it errors, see below:(I've separated each line with a return, to make it more readable)2005-07-08 11:08:23.93 spid51 BackupIoRequest::WaitForIoCompletion:read failure on backup device 'C:file location to backup'. Operatingsystem error 87(The parameter is incorrect.).2005-07-08 11:08:23.94 spid51 Internal I/O request 0x03BDFCEC: Op:Read, pBuffer: 0x03BDF800, Size: 512, Position: 5136, UMS: Internal:0x103, InternalHigh: 0x0, Offset: 0x1410, OffsetHigh: 0x0, m_buf:0x03BDF800, m_len: 512, m_actualBytes: 0, m_errcode: 87, BackupFile:C:file location to backup2005-07-08 11:08:23.94 spid51 The backup data in 'C:file locationto backup' is incorrectly formatted. Backups cannot be appended, butexisting backup sets may still be usable.2005-07-08 11:08:23.94 spid51 Starting up database 'databizDB'.2005-07-08 11:08:24.02 spid51 Bypassing recovery for database'databizDB' because it is marked IN LOAD.2005-07-08 11:08:24.16 spid51 BackupMedium::ReportIoError: readfailure on backup device 'C:file location to backup'. Operating systemerror 87(The parameter is incorrect.).2005-07-08 11:08:24.16 spid51 Internal I/O request 0x42FC5530: Op:Read, pBuffer: 0x03980000, Size: 512, Position: 5648, UMS: Internal:0x103, InternalHigh: 0x0, Offset: 0x1610, OffsetHigh: 0x0, m_buf:0x03980000, m_len: 512, m_actualBytes: 0, m_errcode: 87, BackupFile:C:file location to backupIt may be something simple as I've never really got that involved withthis side of it before. Hopefully someone out there can shed some lighton it, please?This may be normal, but I had to backup the blank DB locally firstbefore it would let me select a file to restore, so I could change thelocation.Cheers,Rich.

View 4 Replies View Related

Transact SQL :: Backup / Restore Tools Which Can Restore Multiple Environments

Jun 25, 2015

I am looking for a SQL Backup/Restore tools which can restore multiple environments.  Here is high level requirements.

1.  We have 4 DBs, range from 1 TB - 1.5 TB Each Database.  When we restore to QA, DEV, or Staging, we usually restore 4 of them.
2.  I am looking for the speed to complete restoring between 1 - 2 hours for 4 DBs.

I am evaluating the Dephix Software but the setup is very complex and its given us a lot of issues with Windows Authentions, and failure in the middle of the backup.  I used Guess Software many years ago but can't find it on the web site any more. Speed is very important for us mean complete restoring as fast as possible.  We are on SQL 2012 and SQL 2008 R2.We are currently using NETAPP Technology and I have Redgate Backup Tool but I am mainly looking for fast Restore Process.

View 4 Replies View Related

BACKUP And RESTORE SQL SERVER DATABASE

Jun 9, 2004

I want to backup an SQL Server database through a client application written in VB 6.0, and not through the Enterprize Manager.
I use the SQLDMO Objects Library to backup the database to the SQL Server machine through my application,
but there are the following problems:
a. The backup can be done only locally to the SQL Server machine. Is there any way to bypass this restriction?
b. To get the Directories Structure of this machine i use the xp_FixedDrives, xp_Subdirs stored procedures.
That's ok for the Backup operation but i do not have a way to view the FILES to perform Restore operation.
Of cource VB function Dir() cannot be used to view files on the server because of access restrictions.
Is there any workaround for that?

View 2 Replies View Related

Sql Server 2005 Backup && Restore

Dec 27, 2007

Morning guys,
What is the equivalent to the taskpad on sql server 2005?
I want to be able to see when the last backup and restore was performed via transact sql ......
how would you go about that..... ?
what would I search... ?

View 2 Replies View Related

SQL 2012 :: Restore TDE Backup Onto New Server?

Jan 29, 2015

I have TDE backup one serverA but There is no backup of certificates or keys from Server A. And no one knows the password used to create those backups. How do you restore the database XYZ at that time on Server B?

View 9 Replies View Related

Restore Database Backup To New Server

Jul 22, 2013

I have a .bak full database backup from a computer. Now I installed new SQL Server 2005 in another computer and would like to restore the .bak database here. How it can be done?

When I right click database and select restore database, it opens a window where it asks to put "destination for restore". This is a new SQL Server, how can I restore then?

View 3 Replies View Related

SQL Server 2005 TR With IU/QUS - Restore From A Backup

Feb 22, 2007

Hello,

SQL Server 2005 TR with IU/QUS - Restore from a backup

Im trying to set-up the above using sp1. It all works fine until the final step, creating a subscription through sp_addarticle and Im getting the following error:

Msg 8152, Level 16, State 10, Procedure sp_MSget_synctran_commands, Line 198
String or binary data would be truncated.

This worked fine on my laptop with a small table with a couple of columns.Now Ive looked at this error and its like

select * from #art_commands

The table only has 49 columns in it, so its not huge.

What are my options for solving/getting around this problem:

1. Try and fix sp_MSget_synctran_commands, from memory, #art_commands has a nvarchar(max) column and Im wondering if this is related. must admit, havent really looked solidly at this sp yet.

2. SP2 doesnt look like it fixes this problem.

3. Create a bespoke method to replicate.

4. anymore for anymore?

It will be impossible to synchronize the databases so this restore from backup was my only option. Im a bit stuck now, can anyone help?

many thanks,

John P

View 3 Replies View Related

Transact SQL :: Restore Backup From A Different Server

Nov 23, 2015

I am trying to restore a DB from FULL backup onto a server (ServB) but that backup is located on a different server (ServA) within the same network. I know that using SSMS 2008, i cannot restore using file from a different server. Due to space restrictions, I cannot copy the bak file (on ServA) to the server where the DB will be restored (ServB). How to restore the FULL db using sql. Basically, the end result is to setup mirroring with the principal server. so i need to restore full and tlog backup 

View 2 Replies View Related

SQL Server 2005 For TFS Backup&&Restore

Oct 31, 2006



Hi, Our Sharepoint of TFS was down a couple of days ago. By luck, we had fully backed up SQL database. Now, the problem is, I uninstalled everything and tried to move TFS to a new server machine on the domain. I installed freshly everything and tried to restore all the databases (with tables also). Now the problems are these:

1 - Since there are lots of different backup-restore procedures telling on the Internet from some forums and this forum also: What should i do basically to make the configuration of Sharepoint and Reporting Services to make them work correctly?

2 - Secondly, even if i fail in the problem above, I, at least, want to save the source control files and sharepoint files. Where can I find them and how can I take them back to my new TFS installation?

3 - If these problems exist and Suppose on a new project on the new installation, i create new WI's, documents (with new Version Control information), and new SPS configuration. And on a new release,the problems are resolved. Is there some way that I can merge the former Backup with the new installation's database?

View 1 Replies View Related

SQl Server 2000 Backup And Win2k Backup

Nov 29, 2001

I want to know how people are backup up their win2k system and SQL 2000 server. If you want a run backup once a night and first backup win2k file system and then run backup using sql agent will that work?
Thanks.

View 2 Replies View Related

Restore Backup To Remote Hosted Server

Aug 21, 2006

Sorry if this is elementary, but I have searched and cannot find an answer.
I have a backup file of a sql server 2000 database. I want to restore it to a remote hosted sql server 2005.
Using sql server management studio, it seems to me that I cannot access the files stored local on my computer.
Does anybody have a solution?
What is the best way to deploy my backup file to the remote server?
Thanks for any suggestions!

View 1 Replies View Related

Restore SQL Server Database From A File Backup

Mar 19, 2001

Hello,

I have a user database which has 1 data file and 1 logfile.
I did a complete backup of the database to a file on the disk drive, using Enterprise manager. Due to some reason, I had to drop the database. The only way I could restore it was by,
1. Create the database with the same name.
2. Restore the complete backup from the file device.

While doing so,
I get the message "Backup set holds the backup of daatabase other than the existing 'userdbname' database. BAckup terminating."

Is it because the database was dropped and recreated.
If I choose the option to overwrite, it restores successfully.

Is this normal and right way to do it? Is there any thing else that I need to take care of, while backup or restore?

Any suggestions welcome.
Thanks,
MMS

View 3 Replies View Related

Restore Backup File On SQL70 Server

Jul 24, 2001

I need to restore a backup file which resites on a network share to my local Sql70 server. Is it possible to restore a database from a backup file on the network share? Thanks!

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved