if the datafile is corrupted but log file is there how do I restore the
database with latest transaction from log file in sql server
*** Sent via Developersdex http://www.codecomments.com ***Backup the log file (using NO_TRUNCATE), restore latest database backup, and
all subsequent log
backups (including this last one).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jaydip Das" <jaydip_j@.hotmail.com> wrote in message news:ezGvukroFHA.2444@.tk2msftngp13.phx.
gbl...
> if the datafile is corrupted but log file is there how do I restore the
> database with latest transaction from log file in sql server
> *** Sent via Developersdex http://www.codecomments.com ***
Showing posts with label via. Show all posts
Showing posts with label via. Show all posts
Friday, March 30, 2012
Wednesday, March 21, 2012
Restore Sql Express (2005) to Sql Server 2000
Well, my understanding is this should be possible and easy -- use a backup created by Sql Express (via Management Studio Express), copy the backup to the Sql 2000 server, select Database Restore through EM, specify Restore from Device, point to the Sql Express generated backup file as the *only* device, and launch.
|||
Doesn't work.
When I launch the restore, I get the following error dialog:
Too many backup devices for backup or restore. Only 64 are allowed. <yada-yada> .... OK
Only got one backup device selected. Is this possible or not?
I have resorted to sql script generation with data in the past, but that's taxes the server resources heavily.
TIA,
Rick
No ... its not possible.. you can't restore SQL Server 2005 backup in SQL Server 2000. You may follow these steps
(a) Create Script of Database Objects from 2005
(c) Create new database in sql server 2000
(d) run the objects script in 2000
(e) use DTS to transfer data from 2005 to 2000
Madhu
|||Thanks. I assume you have to use DTS provided with full blown Sql Server 2005 (which ain't provided with SqlExpress)?|||
Yes , SSIS(DTS of 2005) is not provided with Express edition. you can use sql server 2000 DTS and pull data from 2005 to 2000.
Madhu
|||Madju,Okay ... so I've created the database objects in Sql Server 2000, created a System DSN to connect to the Sql Express database (different server).
Next, when I use 2000's DTS and specify SQL Native Client I am able to connect successfully to the Sql Express database. However, after only several seconds into the transer, I get the follow error from DTS:
Cannot perform requested task because full-text memory manager is not initialized
Not sure what that pertains to.
(later)
Okay, I've found it. It's the MSSearch service on the server that it needs running. I've started the service, stopped and restarted MS Sql Server serice, dependent services and EA says full-text support is running now. Guess what? DTS still yields the same error message! Any other idea, anyone? I'm running DTS from my workstation. I shouldn't have to run it from the server, should I?
(later)
For whatever reason, have to run DTS from the Sql Server 2000 server and not a client workstation. Done deal now.
|||Thanks. This was really helpful. I tried a similar approach by exporting the table defs and data from SQL Express 2005 using the data publishing wizard, but i ran into an error recreating the database on the SQL Server 2000 due to the limit of 64k for command lengths.
This method worked much smoother. I also had to run the DTS on the server itself for it to work.
Restore Sql Express (2005) to Sql Server 2000
Well, my understanding is this should be possible and easy -- use a backup created by Sql Express (via Management Studio Express), copy the backup to the Sql 2000 server, select Database Restore through EM, specify Restore from Device, point to the Sql Express generated backup file as the *only* device, and launch.
|||
Doesn't work.
When I launch the restore, I get the following error dialog:
Too many backup devices for backup or restore. Only 64 are allowed. <yada-yada> .... OK
Only got one backup device selected. Is this possible or not?
I have resorted to sql script generation with data in the past, but that's taxes the server resources heavily.
TIA,
Rick
No ... its not possible.. you can't restore SQL Server 2005 backup in SQL Server 2000. You may follow these steps
(a) Create Script of Database Objects from 2005
(c) Create new database in sql server 2000
(d) run the objects script in 2000
(e) use DTS to transfer data from 2005 to 2000
Madhu
|||Thanks. I assume you have to use DTS provided with full blown Sql Server 2005 (which ain't provided with SqlExpress)?|||
Yes , SSIS(DTS of 2005) is not provided with Express edition. you can use sql server 2000 DTS and pull data from 2005 to 2000.
Madhu
|||Madju,Okay ... so I've created the database objects in Sql Server 2000, created a System DSN to connect to the Sql Express database (different server).
Next, when I use 2000's DTS and specify SQL Native Client I am able to connect successfully to the Sql Express database. However, after only several seconds into the transer, I get the follow error from DTS:
Cannot perform requested task because full-text memory manager is not initialized
Not sure what that pertains to.
(later)
Okay, I've found it. It's the MSSearch service on the server that it needs running. I've started the service, stopped and restarted MS Sql Server serice, dependent services and EA says full-text support is running now. Guess what? DTS still yields the same error message! Any other idea, anyone? I'm running DTS from my workstation. I shouldn't have to run it from the server, should I?
(later)
For whatever reason, have to run DTS from the Sql Server 2000 server and not a client workstation. Done deal now.
|||Thanks. This was really helpful. I tried a similar approach by exporting the table defs and data from SQL Express 2005 using the data publishing wizard, but i ran into an error recreating the database on the SQL Server 2000 due to the limit of 64k for command lengths.
This method worked much smoother. I also had to run the DTS on the server itself for it to work.
restore single table
Hi All ,
Is it possible to restore just a single table from a particulare db's backup
?
kindly advise
tks & rdgs
Message posted via http://www.droptable.comHi ,
I believe i can put that particular table into its own file group and
therefore, if i restore using file group i am just restoring that particular
table
am i correct on the above ?
and is thre anyother solution to this ?
tks & rdgs
maxzsim wrote:
>Hi All ,
>Is it possible to restore just a single table from a particulare db's backu
p ?
>kindly advise
>tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1|||Such a restore has to be done into a new database, and using the PARTIAL opt
ion of the RESTORE
command. There are other restrictions as well, see the RESTORE command and t
he PARTICL option in
Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim via droptable.com" <u14644@.uwe> wrote in message news:5fb1558785963@.uwe...eagreen">
> Hi ,
> I believe i can put that particular table into its own file group and
> therefore, if i restore using file group i am just restoring that particul
ar
> table
> am i correct on the above ?
> and is thre anyother solution to this ?
> tks & rdgs
> maxzsim wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200605/1|||Alternatively you could restore the entire database to a database of another
name then use TABLEDIFF
(http://www.replicationanswers.com/TableDiff2005.asp) or Redgate's
Datacompare to create the synchronization script.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||tks ppl . basically i want to backup/restore certain important table fast a
s
the whole db is quite huge
Paul Ibison wrote:
>Alternatively you could restore the entire database to a database of anothe
r
>name then use TABLEDIFF
>(http://www.replicationanswers.com/TableDiff2005.asp) or Redgate's
>Datacompare to create the synchronization script.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1
Is it possible to restore just a single table from a particulare db's backup
?
kindly advise
tks & rdgs
Message posted via http://www.droptable.comHi ,
I believe i can put that particular table into its own file group and
therefore, if i restore using file group i am just restoring that particular
table
am i correct on the above ?
and is thre anyother solution to this ?
tks & rdgs
maxzsim wrote:
>Hi All ,
>Is it possible to restore just a single table from a particulare db's backu
p ?
>kindly advise
>tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1|||Such a restore has to be done into a new database, and using the PARTIAL opt
ion of the RESTORE
command. There are other restrictions as well, see the RESTORE command and t
he PARTICL option in
Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim via droptable.com" <u14644@.uwe> wrote in message news:5fb1558785963@.uwe...eagreen">
> Hi ,
> I believe i can put that particular table into its own file group and
> therefore, if i restore using file group i am just restoring that particul
ar
> table
> am i correct on the above ?
> and is thre anyother solution to this ?
> tks & rdgs
> maxzsim wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200605/1|||Alternatively you could restore the entire database to a database of another
name then use TABLEDIFF
(http://www.replicationanswers.com/TableDiff2005.asp) or Redgate's
Datacompare to create the synchronization script.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||tks ppl . basically i want to backup/restore certain important table fast a
s
the whole db is quite huge
Paul Ibison wrote:
>Alternatively you could restore the entire database to a database of anothe
r
>name then use TABLEDIFF
>(http://www.replicationanswers.com/TableDiff2005.asp) or Redgate's
>Datacompare to create the synchronization script.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1
restore single table
Hi All ,
Is it possible to restore just a single table from a particulare db's backup ?
kindly advise
tks & rdgs
--
Message posted via http://www.sqlmonster.comHi ,
I believe i can put that particular table into its own file group and
therefore, if i restore using file group i am just restoring that particular
table
am i correct on the above ?
and is thre anyother solution to this ?
tks & rdgs
maxzsim wrote:
>Hi All ,
>Is it possible to restore just a single table from a particulare db's backup ?
>kindly advise
>tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Such a restore has to be done into a new database, and using the PARTIAL option of the RESTORE
command. There are other restrictions as well, see the RESTORE command and the PARTICL option in
Books Online.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message news:5fb1558785963@.uwe...
> Hi ,
> I believe i can put that particular table into its own file group and
> therefore, if i restore using file group i am just restoring that particular
> table
> am i correct on the above ?
> and is thre anyother solution to this ?
> tks & rdgs
> maxzsim wrote:
>>Hi All ,
>>Is it possible to restore just a single table from a particulare db's backup ?
>>kindly advise
>>tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Alternatively you could restore the entire database to a database of another
name then use TABLEDIFF
(http://www.replicationanswers.com/TableDiff2005.asp) or Redgate's
Datacompare to create the synchronization script.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||tks ppl . basically i want to backup/restore certain important table fast as
the whole db is quite huge
Paul Ibison wrote:
>Alternatively you could restore the entire database to a database of another
>name then use TABLEDIFF
>(http://www.replicationanswers.com/TableDiff2005.asp) or Redgate's
>Datacompare to create the synchronization script.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1
Is it possible to restore just a single table from a particulare db's backup ?
kindly advise
tks & rdgs
--
Message posted via http://www.sqlmonster.comHi ,
I believe i can put that particular table into its own file group and
therefore, if i restore using file group i am just restoring that particular
table
am i correct on the above ?
and is thre anyother solution to this ?
tks & rdgs
maxzsim wrote:
>Hi All ,
>Is it possible to restore just a single table from a particulare db's backup ?
>kindly advise
>tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Such a restore has to be done into a new database, and using the PARTIAL option of the RESTORE
command. There are other restrictions as well, see the RESTORE command and the PARTICL option in
Books Online.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message news:5fb1558785963@.uwe...
> Hi ,
> I believe i can put that particular table into its own file group and
> therefore, if i restore using file group i am just restoring that particular
> table
> am i correct on the above ?
> and is thre anyother solution to this ?
> tks & rdgs
> maxzsim wrote:
>>Hi All ,
>>Is it possible to restore just a single table from a particulare db's backup ?
>>kindly advise
>>tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Alternatively you could restore the entire database to a database of another
name then use TABLEDIFF
(http://www.replicationanswers.com/TableDiff2005.asp) or Redgate's
Datacompare to create the synchronization script.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||tks ppl . basically i want to backup/restore certain important table fast as
the whole db is quite huge
Paul Ibison wrote:
>Alternatively you could restore the entire database to a database of another
>name then use TABLEDIFF
>(http://www.replicationanswers.com/TableDiff2005.asp) or Redgate's
>Datacompare to create the synchronization script.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1
Wednesday, March 7, 2012
Restore of database via Veritas 9.1 fails with communication error
We are running SQL 2000 sp4, only one user database (SAP) the database is 63 GB.
The ldf files were trashed so I went to tape to restore.
We are using veritas backup exec 9.1 sp4. Veritas is on the same server. I can restore the master db and the msdb fine. However when we attempt to restore the DEV db it runs for 2 hours and then fails with a communication error from veritas.
When we restore the master the DEV db comes up suspect as there are no files for it to attach to) so we cannot attach to it to restore, so I have deleted it and attempted to restore - same communication error. I have recreated a db named DEV with the mdf etc created to the size needed plus some to be sure, no good same behavior (behavior described below).
The job starts and it creates all the folders for the database (there are 1 MDF, 5 NDF, and 4 LDF files each in its own folder). Then it begins to create teh individual files it gets ~half way through at about an hour-hour and fifteen. During this time there are a large number of writes being performed by SQL (I assume it is creating the structures). Then it switches over to reading from tape a large number of read by beengine for another 45-1.5 hours then the job fails with the error unable to communicate with the device. I ahve noticed that once it starts reading from tape the db is gone from enterprise manager, and the mdf etc. files that were being created are now gone.
Help this has gone on for three days!!!I advise my clients NEVER to use Veritas to backup databases. I have seen many occasions where the clients thought they were getting backups, but in fact were not.
My standard procedure is to use SQL Server tools to backup the databases and logs (that's what they were designed for...), and then use Veritas to backup the resulting files.
Call Veritas and see if they can help.|||I have been on the phone for two days off and on with them. We will be changing our procedure when we get past this. For 6 years we have had no problems backing up and restoring our databases...always a first time.:eek:|||SQL Server backups are so easy and reliable when scheduled through SQL Server Agent, I honestly don't know why anybody would use 3rd party software to do this.|||Point taken, however that is no help with where I am at now.|||Veritas sold you on using their backup tools, and the errors you are getting appear to be coming from Veritas, so I would keep tossing the ball back into their court.
When you get through this, I and others on the forum would be happy to advise you on setting up a more robust data recovery system.|||Thanks, we will be proceeding in that direction shortly after we get done...first some sleep though...hmmm and maybe a beer or two!!|||We do use LiteSpeed here blindman. :) It actually has worked wonders here. It still creates backup files, but they are 30% of the size and are produced twice as quick. Plus,the backups are encrypted when they are shipped off-site.
Veritas has burned me more times than I care to remember. I would NEVER use it to backup SQL Server. Also, I wouldn't use any third-party service that tries to backup the actual .mdf and .ldf files. As you stated, it's better to back them up using the Agent or something like LiteSpeed. Then, move those backup files to tape with Veritas, etc.|||Issue resolved, thanks for the comments. We will be looking into SQL backups starting next week.
Just in case anyone else hits this issue:
The problem was veritas was timing out attempting create the structures for the database. It uses VDI as the default connection to a db. The VDI is set to 25 minutes for a time out by default. CHange to named pipes and it works fine. HKEY Local machine\software\veritas\backupexec\engine\SQL Server\Force Named Pipe Backup/restore set this to 1 and restart services.
Follow on question:
THis was a 63 GB db and took 6 hours to create structures and restore data; ok it is a development system so not a huge issue. However our production SAP database is over 180 GB! What is the best way IYO to backup and restore a DB of this size - or should we be going with some sort of mirroring?|||mirrorring with some kind of raided drive is usually good but does not solve the problem of what happens when the office burns down.
without knowing more about your situation, I would say you should use a combination of full, differential and transaction log backups to another peice of hardware on your network and not directly to tape. This will be a little faster and will not be such a resource burden. Then you move these backups on this seperate machine to a tape drive connected to it.
It really all depends on your business needs and you should do a little reading on disaster recovery, but I would do a full backup on the weekends, maybe a differential every night during the witching hour and T-Log backups during the day to ensure full recovery.|||I would say make an investment in SQL LiteSpeed by Quest. It will allow your backups to run much faster and they will be much smaller. We have loved it since we purchased it.
Also, you might want to look at buying a faster disk set to backup on. If you had a seperate drive array that was 2 RAID 0 or 4 RAID 10 disks, you would find your backup speed go to minutes instead of hours. We backup hundreds of GB in less time than you take for the one database. We have a backup server with four RAID-10 drives. All backups on the network go to that share. Even with pushing them over the network, we are much faster than your solution.|||nanny nanny boo boo to you too
The ldf files were trashed so I went to tape to restore.
We are using veritas backup exec 9.1 sp4. Veritas is on the same server. I can restore the master db and the msdb fine. However when we attempt to restore the DEV db it runs for 2 hours and then fails with a communication error from veritas.
When we restore the master the DEV db comes up suspect as there are no files for it to attach to) so we cannot attach to it to restore, so I have deleted it and attempted to restore - same communication error. I have recreated a db named DEV with the mdf etc created to the size needed plus some to be sure, no good same behavior (behavior described below).
The job starts and it creates all the folders for the database (there are 1 MDF, 5 NDF, and 4 LDF files each in its own folder). Then it begins to create teh individual files it gets ~half way through at about an hour-hour and fifteen. During this time there are a large number of writes being performed by SQL (I assume it is creating the structures). Then it switches over to reading from tape a large number of read by beengine for another 45-1.5 hours then the job fails with the error unable to communicate with the device. I ahve noticed that once it starts reading from tape the db is gone from enterprise manager, and the mdf etc. files that were being created are now gone.
Help this has gone on for three days!!!I advise my clients NEVER to use Veritas to backup databases. I have seen many occasions where the clients thought they were getting backups, but in fact were not.
My standard procedure is to use SQL Server tools to backup the databases and logs (that's what they were designed for...), and then use Veritas to backup the resulting files.
Call Veritas and see if they can help.|||I have been on the phone for two days off and on with them. We will be changing our procedure when we get past this. For 6 years we have had no problems backing up and restoring our databases...always a first time.:eek:|||SQL Server backups are so easy and reliable when scheduled through SQL Server Agent, I honestly don't know why anybody would use 3rd party software to do this.|||Point taken, however that is no help with where I am at now.|||Veritas sold you on using their backup tools, and the errors you are getting appear to be coming from Veritas, so I would keep tossing the ball back into their court.
When you get through this, I and others on the forum would be happy to advise you on setting up a more robust data recovery system.|||Thanks, we will be proceeding in that direction shortly after we get done...first some sleep though...hmmm and maybe a beer or two!!|||We do use LiteSpeed here blindman. :) It actually has worked wonders here. It still creates backup files, but they are 30% of the size and are produced twice as quick. Plus,the backups are encrypted when they are shipped off-site.
Veritas has burned me more times than I care to remember. I would NEVER use it to backup SQL Server. Also, I wouldn't use any third-party service that tries to backup the actual .mdf and .ldf files. As you stated, it's better to back them up using the Agent or something like LiteSpeed. Then, move those backup files to tape with Veritas, etc.|||Issue resolved, thanks for the comments. We will be looking into SQL backups starting next week.
Just in case anyone else hits this issue:
The problem was veritas was timing out attempting create the structures for the database. It uses VDI as the default connection to a db. The VDI is set to 25 minutes for a time out by default. CHange to named pipes and it works fine. HKEY Local machine\software\veritas\backupexec\engine\SQL Server\Force Named Pipe Backup/restore set this to 1 and restart services.
Follow on question:
THis was a 63 GB db and took 6 hours to create structures and restore data; ok it is a development system so not a huge issue. However our production SAP database is over 180 GB! What is the best way IYO to backup and restore a DB of this size - or should we be going with some sort of mirroring?|||mirrorring with some kind of raided drive is usually good but does not solve the problem of what happens when the office burns down.
without knowing more about your situation, I would say you should use a combination of full, differential and transaction log backups to another peice of hardware on your network and not directly to tape. This will be a little faster and will not be such a resource burden. Then you move these backups on this seperate machine to a tape drive connected to it.
It really all depends on your business needs and you should do a little reading on disaster recovery, but I would do a full backup on the weekends, maybe a differential every night during the witching hour and T-Log backups during the day to ensure full recovery.|||I would say make an investment in SQL LiteSpeed by Quest. It will allow your backups to run much faster and they will be much smaller. We have loved it since we purchased it.
Also, you might want to look at buying a faster disk set to backup on. If you had a seperate drive array that was 2 RAID 0 or 4 RAID 10 disks, you would find your backup speed go to minutes instead of hours. We backup hundreds of GB in less time than you take for the one database. We have a backup server with four RAID-10 drives. All backups on the network go to that share. Even with pushing them over the network, we are much faster than your solution.|||nanny nanny boo boo to you too
Subscribe to:
Posts (Atom)