database problem: files

Showing posts with label files. Show all posts

Wednesday, March 28, 2012

restoring without log

Our sql server 2000 keeps the mdf files on one drive and the ldf files
on another. We lost our data on the ldf drive and had to restore from
a day earlier. Now the mdf and ldf are out of synch and can't access
our data. How do we rebuild our data with just the mdf file?
Thanks,
RickRestore from the most recent SQL Server backup (not file system backups). You can try
sp_attach_single_file_db, but as you didn't detach first, it might not work. If you're still out of
luck, open a case with MS Support and see if they have any tricks up their sleeves.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rick" <rick@.abasoftware.com> wrote in message
news:1124387611.085354.43780@.g47g2000cwa.googlegroups.com...
> Our sql server 2000 keeps the mdf files on one drive and the ldf files
> on another. We lost our data on the ldf drive and had to restore from
> a day earlier. Now the mdf and ldf are out of synch and can't access
> our data. How do we rebuild our data with just the mdf file?
> Thanks,
> Rick
>

restoring without log

Our sql server 2000 keeps the mdf files on one drive and the ldf files
on another. We lost our data on the ldf drive and had to restore from
a day earlier. Now the mdf and ldf are out of synch and can't access
our data. How do we rebuild our data with just the mdf file?
Thanks,
Rick
Restore from the most recent SQL Server backup (not file system backups). You can try
sp_attach_single_file_db, but as you didn't detach first, it might not work. If you're still out of
luck, open a case with MS Support and see if they have any tricks up their sleeves.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rick" <rick@.abasoftware.com> wrote in message
news:1124387611.085354.43780@.g47g2000cwa.googlegro ups.com...
> Our sql server 2000 keeps the mdf files on one drive and the ldf files
> on another. We lost our data on the ldf drive and had to restore from
> a day earlier. Now the mdf and ldf are out of synch and can't access
> our data. How do we rebuild our data with just the mdf file?
> Thanks,
> Rick
>
sql

restoring without log

Our sql server 2000 keeps the mdf files on one drive and the ldf files
on another. We lost our data on the ldf drive and had to restore from
a day earlier. Now the mdf and ldf are out of synch and can't access
our data. How do we rebuild our data with just the mdf file?
Thanks,
RickRestore from the most recent SQL Server backup (not file system backups). Yo
u can try
sp_attach_single_file_db, but as you didn't detach first, it might not work.
If you're still out of
luck, open a case with MS Support and see if they have any tricks up their s
leeves.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rick" <rick@.abasoftware.com> wrote in message
news:1124387611.085354.43780@.g47g2000cwa.googlegroups.com...
> Our sql server 2000 keeps the mdf files on one drive and the ldf files
> on another. We lost our data on the ldf drive and had to restore from
> a day earlier. Now the mdf and ldf are out of synch and can't access
> our data. How do we rebuild our data with just the mdf file?
> Thanks,
> Rick
>

Restoring Tran. Log files

I need to restore a database on my test server from that on my production
server.
Here's what i've done so far:
1) Created a new db on my testing server named 'main_test'
2) Restored 'main_test' using .bak file from my production db.
Now, I have several transaction log files I wan to incorporate as well.
When I use Enterprise Manager and select my first trn log file and select
'Transaction Log' on the 'General' tab, I get the following error:
"The preceeding resotre operation did not specify WITH NORECOVERY or WITH
STANDBY....."
Should I use Query Analyzer for this? What is the syntax?Eric,
After applying the trnasaction log, go to "options" tab and select "Leave
database nonoperational, but able to restore additional transaction logs", o
r
"Leave database operational. No additional transaction logs can be restored"
if you are restoring the last transaction log backup.
AMB
"Eric" wrote:

> I need to restore a database on my test server from that on my production
> server.
> Here's what i've done so far:
> 1) Created a new db on my testing server named 'main_test'
> 2) Restored 'main_test' using .bak file from my production db.
> Now, I have several transaction log files I wan to incorporate as well.
> When I use Enterprise Manager and select my first trn log file and select
> 'Transaction Log' on the 'General' tab, I get the following error:
> "The preceeding resotre operation did not specify WITH NORECOVERY or WITH
> STANDBY....."
> Should I use Query Analyzer for this? What is the syntax?
>|||Correction,

> After applying the trnasaction log, go to "options" tab and select "Leave
Before applying ...
AMB
"Alejandro Mesa" wrote:
> Eric,
> After applying the trnasaction log, go to "options" tab and select "Leave
> database nonoperational, but able to restore additional transaction logs",
or
> "Leave database operational. No additional transaction logs can be restore
d"
> if you are restoring the last transaction log backup.
>
> AMB
> "Eric" wrote:
>

Restoring to a network paths?

I'm getting the error "The file \\xxx... is on a network path that is not supported for database files. File xx cannot be restored to \\xx..... Use WITH MOVE to identify a valid location for the file. Below is my code for the restore. I've even shared the folder on the network but still no success.

Try
Dim restoreToServer As New Server("xxxx")
Dim rest As New Restore
rest.Devices.AddDevice("\\xx\x$\Program Files\Microsoft SQL Server\MSSQL\BACKUP\myData\myData.BAK", DeviceType.File)

rest.Action = RestoreActionType.Database
rest.Database = "myDatabase"
rest.NoRecovery = False
rest.ReplaceDatabase = True

rest.RelocateFiles.Add(New RelocateFile("myData", "\\xx\data\myData.mdf"))
rest.RelocateFiles.Add(New RelocateFile("myData_log", "\\xx\logs\myData.ldf"))

rest.SqlRestore(restoreToServer)
Catch ex As Exception
MessageBox.Show(ex.InnerException.ToString)

End Try

Can you run a database from a network path?

Why would you want to do it?
|||

We are running the vb app on a desktop. To connect to the production db we issue smo commands. Those commands will backup the production database and then we need to do a restore on another database. To connect to any of the two mentioned we connect using \\host-machine\drive$ but this does not work.

|||That what is wrote for placing data files on a network shares some days ago:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=477137&SiteID=1

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Monday, March 26, 2012

restoring the database has failed

hi,
i try to restore a database with this script
RESTORE DATABASE logship
FROM DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\database_LOGSHIP_backup_device .bak'
WITH -- Norecovery,
restricted_user,
STANDBY = 'c:\undo.ldf',
REPLACE,
MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\LOGSHIP_data.mdf',
MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\LOGSHIP_log.ldf'
but i receive a error
Server: Msg 3101, Level 16, State 2, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
i don't understand why it tells this?
thanks for your help.
M'bark
I think the error messages is pretty clear. Someone is using the database, so you need to kick out
all users before the restore can succeed. Use sp_who etc to see what users you have in the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"M'bark BOULOUIRD" <bark.news@.DELETEspam.logaviv.com> wrote in message
news:OUWSM3s0EHA.3900@.TK2MSFTNGP10.phx.gbl...
> hi,
> i try to restore a database with this script
> RESTORE DATABASE logship
> FROM DISK = 'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\database_LOGSHIP_backup_device .bak'
> WITH -- Norecovery,
> restricted_user,
> STANDBY = 'c:\undo.ldf',
> REPLACE,
> MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_data.mdf',
> MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_log.ldf'
> but i receive a error
> Server: Msg 3101, Level 16, State 2, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> i don't understand why it tells this?
> thanks for your help.
> M'bark
>
|||Just to add something to what Tibor said, that user could
be you.
You don't need a connection to that database to perform a
restore.
Peter
"Choose a job you love, and you will never have to work a
day in your life."
Confucius

>--Original Message--
>I think the error messages is pretty clear. Someone is
using the database, so you need to kick out
>all users before the restore can succeed. Use sp_who etc
to see what users you have in the database.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"M'bark BOULOUIRD" <bark.news@.DELETEspam.logaviv.com>
wrote in message[vbcol=seagreen]
>news:OUWSM3s0EHA.3900@.TK2MSFTNGP10.phx.gbl...
database is in use.
>
>.
>
|||As Tibor said, the RESTORE command requires that no-one is connected to
the database. Someone is connected. You can set the database to single
user mode using
ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
then do your restore. or you can do ALTER DATABASE SET RESTRICTED_USER -
look it up in BOL (ALTER DATABASE command)
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
M'bark BOULOUIRD wrote:
> hi,
> i try to restore a database with this script
> RESTORE DATABASE logship
> FROM DISK = 'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\database_LOGSHIP_backup_device .bak'
> WITH -- Norecovery,
> restricted_user,
> STANDBY = 'c:\undo.ldf',
> REPLACE,
> MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_data.mdf',
> MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_log.ldf'
> but i receive a error
> Server: Msg 3101, Level 16, State 2, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> i don't understand why it tells this?
> thanks for your help.
> M'bark
>
|||thank you
M'bark
"Mark Allison" <marka@.no.tinned.meat.mvps.org> a crit dans le message de
news: en9MuWt0EHA.2716@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> As Tibor said, the RESTORE command requires that no-one is connected to
> the database. Someone is connected. You can set the database to single
> user mode using
> ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> then do your restore. or you can do ALTER DATABASE SET RESTRICTED_USER -
> look it up in BOL (ALTER DATABASE command)
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> M'bark BOULOUIRD wrote:
|||A better suggestion would be to take the database offline. If you are restoring it, who cares if it is offline or not.
I prefer this because of Web Server connection pooling and, in some situations, those connections have high privilege. Because of this, SINGLE_USER and RESTRICTED_USER may not successfully lock out users long enough to start up the restore operation. If you use the OFFLINE setting, then no one, not even you, can access that database until restored or brought back online.
ALTER DATABASE MyDB
SET OFFLINE
WITH ROLLBACK IMMEDIATE
Sincerely,
Anthony Thomas

"M'bark BOULOUIRD" <bark.news@.DELETEspam.logaviv.com> wrote in message news:OYrgy%23t0EHA.1392@.TK2MSFTNGP14.phx.gbl...
thank you
M'bark
"Mark Allison" <marka@.no.tinned.meat.mvps.org> a crit dans le message de
news: en9MuWt0EHA.2716@.TK2MSFTNGP14.phx.gbl...
[vbcol=seagreen]
> As Tibor said, the RESTORE command requires that no-one is connected to
> the database. Someone is connected. You can set the database to single
> user mode using
>
> ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
>
> then do your restore. or you can do ALTER DATABASE SET RESTRICTED_USER -
> look it up in BOL (ALTER DATABASE command)
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
>
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
> M'bark BOULOUIRD wrote:

restoring the database has failed

hi,
i try to restore a database with this script
RESTORE DATABASE logship
FROM DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\database_LOGSHIP_backup_device.bak'
WITH -- Norecovery,
restricted_user,
STANDBY = 'c:\undo.ldf',
REPLACE,
MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\LOGSHIP_data.mdf',
MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\LOGSHIP_log.ldf'
but i receive a error
Server: Msg 3101, Level 16, State 2, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
i don't understand why it tells this?
thanks for your help.
M'barkI think the error messages is pretty clear. Someone is using the database, so you need to kick out
all users before the restore can succeed. Use sp_who etc to see what users you have in the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"M'bark BOULOUIRD" <bark.news@.DELETEspam.logaviv.com> wrote in message
news:OUWSM3s0EHA.3900@.TK2MSFTNGP10.phx.gbl...
> hi,
> i try to restore a database with this script
> RESTORE DATABASE logship
> FROM DISK = 'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\database_LOGSHIP_backup_device.bak'
> WITH -- Norecovery,
> restricted_user,
> STANDBY = 'c:\undo.ldf',
> REPLACE,
> MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_data.mdf',
> MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_log.ldf'
> but i receive a error
> Server: Msg 3101, Level 16, State 2, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> i don't understand why it tells this?
> thanks for your help.
> M'bark
>|||Just to add something to what Tibor said, that user could
be you.
You don't need a connection to that database to perform a
restore.
Peter
"Choose a job you love, and you will never have to work a
day in your life."
Confucius
>--Original Message--
>I think the error messages is pretty clear. Someone is
using the database, so you need to kick out
>all users before the restore can succeed. Use sp_who etc
to see what users you have in the database.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"M'bark BOULOUIRD" <bark.news@.DELETEspam.logaviv.com>
wrote in message
>news:OUWSM3s0EHA.3900@.TK2MSFTNGP10.phx.gbl...
>> hi,
>> i try to restore a database with this script
>> RESTORE DATABASE logship
>> FROM DISK = 'C:\Program Files\Microsoft SQL
>> Server\MSSQL\BACKUP\database_LOGSHIP_backup_device.bak'
>> WITH -- Norecovery,
>> restricted_user,
>> STANDBY = 'c:\undo.ldf',
>> REPLACE,
>> MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\LOGSHIP_data.mdf',
>> MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\LOGSHIP_log.ldf'
>> but i receive a error
>> Server: Msg 3101, Level 16, State 2, Line 1
>> Exclusive access could not be obtained because the
database is in use.
>> Server: Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>> i don't understand why it tells this?
>> thanks for your help.
>> M'bark
>>
>
>.
>|||As Tibor said, the RESTORE command requires that no-one is connected to
the database. Someone is connected. You can set the database to single
user mode using
ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
then do your restore. or you can do ALTER DATABASE SET RESTRICTED_USER -
look it up in BOL (ALTER DATABASE command)
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
M'bark BOULOUIRD wrote:
> hi,
> i try to restore a database with this script
> RESTORE DATABASE logship
> FROM DISK = 'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\database_LOGSHIP_backup_device.bak'
> WITH -- Norecovery,
> restricted_user,
> STANDBY = 'c:\undo.ldf',
> REPLACE,
> MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_data.mdf',
> MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_log.ldf'
> but i receive a error
> Server: Msg 3101, Level 16, State 2, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> i don't understand why it tells this?
> thanks for your help.
> M'bark
>|||thank you
M'bark
"Mark Allison" <marka@.no.tinned.meat.mvps.org> a écrit dans le message de
news: en9MuWt0EHA.2716@.TK2MSFTNGP14.phx.gbl...
> As Tibor said, the RESTORE command requires that no-one is connected to
> the database. Someone is connected. You can set the database to single
> user mode using
> ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> then do your restore. or you can do ALTER DATABASE SET RESTRICTED_USER -
> look it up in BOL (ALTER DATABASE command)
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> M'bark BOULOUIRD wrote:
>> hi,
>> i try to restore a database with this script
>> RESTORE DATABASE logship
>> FROM DISK = 'C:\Program Files\Microsoft SQL
>> Server\MSSQL\BACKUP\database_LOGSHIP_backup_device.bak'
>> WITH -- Norecovery,
>> restricted_user,
>> STANDBY = 'c:\undo.ldf',
>> REPLACE,
>> MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\LOGSHIP_data.mdf',
>> MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\LOGSHIP_log.ldf'
>> but i receive a error
>> Server: Msg 3101, Level 16, State 2, Line 1
>> Exclusive access could not be obtained because the database is in use.
>> Server: Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>> i don't understand why it tells this?
>> thanks for your help.
>> M'bark

restoring the database has failed

hi,
i try to restore a database with this script
RESTORE DATABASE logship
FROM DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\database_LOGSHIP_bac
kup_device.bak'
WITH -- Norecovery,
restricted_user,
STANDBY = 'c:\undo.ldf',
REPLACE,
MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\LOGSHIP_data.mdf',
MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\LOGSHIP_log.ldf'
but i receive a error
Server: Msg 3101, Level 16, State 2, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
i don't understand why it tells this?
thanks for your help.
M'barkI think the error messages is pretty clear. Someone is using the database, s
o you need to kick out
all users before the restore can succeed. Use sp_who etc to see what users y
ou have in the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"M'bark BOULOUIRD" <bark.news@.DELETEspam.logaviv.com> wrote in message
news:OUWSM3s0EHA.3900@.TK2MSFTNGP10.phx.gbl...
> hi,
> i try to restore a database with this script
> RESTORE DATABASE logship
> FROM DISK = 'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\database_LOGSHIP_bac
kup_device.bak'
> WITH -- Norecovery,
> restricted_user,
> STANDBY = 'c:\undo.ldf',
> REPLACE,
> MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_data.mdf',
> MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_log.ldf'
> but i receive a error
> Server: Msg 3101, Level 16, State 2, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> i don't understand why it tells this?
> thanks for your help.
> M'bark
>|||Just to add something to what Tibor said, that user could
be you.
You don't need a connection to that database to perform a
restore.
Peter
"Choose a job you love, and you will never have to work a
day in your life."
Confucius

>--Original Message--
>I think the error messages is pretty clear. Someone is
using the database, so you need to kick out
>all users before the restore can succeed. Use sp_who etc
to see what users you have in the database.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"M'bark BOULOUIRD" <bark.news@.DELETEspam.logaviv.com>
wrote in message
>news:OUWSM3s0EHA.3900@.TK2MSFTNGP10.phx.gbl...
database is in use.[vbcol=seagreen]
>
>.
>|||As Tibor said, the RESTORE command requires that no-one is connected to
the database. Someone is connected. You can set the database to single
user mode using
ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
then do your restore. or you can do ALTER DATABASE SET RESTRICTED_USER -
look it up in BOL (ALTER DATABASE command)
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
M'bark BOULOUIRD wrote:
> hi,
> i try to restore a database with this script
> RESTORE DATABASE logship
> FROM DISK = 'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\database_LOGSHIP_bac
kup_device.bak'
> WITH -- Norecovery,
> restricted_user,
> STANDBY = 'c:\undo.ldf',
> REPLACE,
> MOVE 'logship_data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_data.mdf',
> MOVE 'logship_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\LOGSHIP_log.ldf'
> but i receive a error
> Server: Msg 3101, Level 16, State 2, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> i don't understand why it tells this?
> thanks for your help.
> M'bark
>|||thank you
M'bark
"Mark Allison" <marka@.no.tinned.meat.mvps.org> a crit dans le message de
news: en9MuWt0EHA.2716@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> As Tibor said, the RESTORE command requires that no-one is connected to
> the database. Someone is connected. You can set the database to single
> user mode using
> ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> then do your restore. or you can do ALTER DATABASE SET RESTRICTED_USER -
> look it up in BOL (ALTER DATABASE command)
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> M'bark BOULOUIRD wrote:|||A better suggestion would be to take the database offline. If you are resto
ring it, who cares if it is offline or not.
I prefer this because of Web Server connection pooling and, in some situatio
ns, those connections have high privilege. Because of this, SINGLE_USER and
RESTRICTED_USER may not successfully lock out users long enough to start up
the restore operation. If you use the OFFLINE setting, then no one, not ev
en you, can access that database until restored or brought back online.
ALTER DATABASE MyDB
SET OFFLINE
WITH ROLLBACK IMMEDIATE
Sincerely,
Anthony Thomas
--
"M'bark BOULOUIRD" <bark.news@.DELETEspam.logaviv.com> wrote in message new
s:OYrgy%23t0EHA.1392@.TK2MSFTNGP14.phx.gbl...
thank you
M'bark
"Mark Allison" <marka@.no.tinned.meat.mvps.org> a crit dans le message de
news: en9MuWt0EHA.2716@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> As Tibor said, the RESTORE command requires that no-one is connected to
> the database. Someone is connected. You can set the database to single
> user mode using
>
> ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
>
> then do your restore. or you can do ALTER DATABASE SET RESTRICTED_USER -
> look it up in BOL (ALTER DATABASE command)
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
>
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
> M'bark BOULOUIRD wrote:

restoring suspect database

We have some databases whose files are on a SAN. Someone shut the SAN down
before the sql server box and when sql server came back up the databases on
the SAN were marked "suspect". I've been able to reboot the sql server box in
the past and it clears up the problem but it didn't this time.
I don't want to try sp_resetstatus at this time (in the middle of the day)
because there are production databases on the box and I don't want to have to
restart sql server. The last backup that was done was file backup by Veritas.
Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
will work? What does this command do when a file is not specified?
Can I use the restore command if I just have an .mdf file? I thought that I
might be able to do a detach/attach but I guess because the database is in
the "suspect" mode, I can't do that. Would removing the database and creating
a new one by the same name and then doing the detach/attach work?
Thanks,
--
Dan D.Seems you are investigating every possible action except the correct action ;-)
Do a log backup using the NO_TRUNCATE option (the option is required since your database is
suspect). Restore the latest database backup and all subsequent log backups (including this last log
backup). Zero data loss.
> Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> will work? What does this command do when a file is not specified?
No. The command assumes you first did restore of a database of log backup using either NORECOVERY or
STANDBY. It does a very specific thing: do the UNDO work that weren't performed by that last
restore.
> Can I use the restore command if I just have an .mdf file?
No. If you are *very* lucky, you can do attach. As for the rest of the attach alternatives you
mention, I wouldn't even go there. Do it the proper way (as I listed in top of this post).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:BAD5B056-6C16-46EC-A043-2E047C7D6E5E@.microsoft.com...
> We have some databases whose files are on a SAN. Someone shut the SAN down
> before the sql server box and when sql server came back up the databases on
> the SAN were marked "suspect". I've been able to reboot the sql server box in
> the past and it clears up the problem but it didn't this time.
> I don't want to try sp_resetstatus at this time (in the middle of the day)
> because there are production databases on the box and I don't want to have to
> restart sql server. The last backup that was done was file backup by Veritas.
> Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> will work? What does this command do when a file is not specified?
> Can I use the restore command if I just have an .mdf file? I thought that I
> might be able to do a detach/attach but I guess because the database is in
> the "suspect" mode, I can't do that. Would removing the database and creating
> a new one by the same name and then doing the detach/attach work?
> Thanks,
> --
> Dan D.|||Hi
sp_resetstatus is your only option.
Restore will not work as the Db is in suspect mode and not loading mode.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:BAD5B056-6C16-46EC-A043-2E047C7D6E5E@.microsoft.com...
> We have some databases whose files are on a SAN. Someone shut the SAN down
> before the sql server box and when sql server came back up the databases
> on
> the SAN were marked "suspect". I've been able to reboot the sql server box
> in
> the past and it clears up the problem but it didn't this time.
> I don't want to try sp_resetstatus at this time (in the middle of the day)
> because there are production databases on the box and I don't want to have
> to
> restart sql server. The last backup that was done was file backup by
> Veritas.
> Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> will work? What does this command do when a file is not specified?
> Can I use the restore command if I just have an .mdf file? I thought that
> I
> might be able to do a detach/attach but I guess because the database is in
> the "suspect" mode, I can't do that. Would removing the database and
> creating
> a new one by the same name and then doing the detach/attach work?
> Thanks,
> --
> Dan D.|||Thanks.
--
Dan D.
"Tibor Karaszi" wrote:
> Seems you are investigating every possible action except the correct action ;-)
> Do a log backup using the NO_TRUNCATE option (the option is required since your database is
> suspect). Restore the latest database backup and all subsequent log backups (including this last log
> backup). Zero data loss.
>
> > Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> > will work? What does this command do when a file is not specified?
> No. The command assumes you first did restore of a database of log backup using either NORECOVERY or
> STANDBY. It does a very specific thing: do the UNDO work that weren't performed by that last
> restore.
>
> > Can I use the restore command if I just have an .mdf file?
> No. If you are *very* lucky, you can do attach. As for the rest of the attach alternatives you
> mention, I wouldn't even go there. Do it the proper way (as I listed in top of this post).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:BAD5B056-6C16-46EC-A043-2E047C7D6E5E@.microsoft.com...
> > We have some databases whose files are on a SAN. Someone shut the SAN down
> > before the sql server box and when sql server came back up the databases on
> > the SAN were marked "suspect". I've been able to reboot the sql server box in
> > the past and it clears up the problem but it didn't this time.
> >
> > I don't want to try sp_resetstatus at this time (in the middle of the day)
> > because there are production databases on the box and I don't want to have to
> > restart sql server. The last backup that was done was file backup by Veritas.
> >
> > Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> > will work? What does this command do when a file is not specified?
> >
> > Can I use the restore command if I just have an .mdf file? I thought that I
> > might be able to do a detach/attach but I guess because the database is in
> > the "suspect" mode, I can't do that. Would removing the database and creating
> > a new one by the same name and then doing the detach/attach work?
> >
> > Thanks,
> > --
> > Dan D.
>|||Thanks.
--
Dan D.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> sp_resetstatus is your only option.
> Restore will not work as the Db is in suspect mode and not loading mode.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:BAD5B056-6C16-46EC-A043-2E047C7D6E5E@.microsoft.com...
> > We have some databases whose files are on a SAN. Someone shut the SAN down
> > before the sql server box and when sql server came back up the databases
> > on
> > the SAN were marked "suspect". I've been able to reboot the sql server box
> > in
> > the past and it clears up the problem but it didn't this time.
> >
> > I don't want to try sp_resetstatus at this time (in the middle of the day)
> > because there are production databases on the box and I don't want to have
> > to
> > restart sql server. The last backup that was done was file backup by
> > Veritas.
> >
> > Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> > will work? What does this command do when a file is not specified?
> >
> > Can I use the restore command if I just have an .mdf file? I thought that
> > I
> > might be able to do a detach/attach but I guess because the database is in
> > the "suspect" mode, I can't do that. Would removing the database and
> > creating
> > a new one by the same name and then doing the detach/attach work?
> >
> > Thanks,
> > --
> > Dan D.
>
>

restoring suspect database

We have some databases whose files are on a SAN. Someone shut the SAN down
before the sql server box and when sql server came back up the databases on
the SAN were marked "suspect". I've been able to reboot the sql server box i
n
the past and it clears up the problem but it didn't this time.
I don't want to try sp_resetstatus at this time (in the middle of the day)
because there are production databases on the box and I don't want to have t
o
restart sql server. The last backup that was done was file backup by Veritas
.
Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
will work? What does this command do when a file is not specified?
Can I use the restore command if I just have an .mdf file? I thought that I
might be able to do a detach/attach but I guess because the database is in
the "suspect" mode, I can't do that. Would removing the database and creatin
g
a new one by the same name and then doing the detach/attach work?
Thanks,
--
Dan D.Seems you are investigating every possible action except the correct action
;-)
Do a log backup using the NO_TRUNCATE option (the option is required since y
our database is
suspect). Restore the latest database backup and all subsequent log backups
(including this last log
backup). Zero data loss.

> Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> will work? What does this command do when a file is not specified?
No. The command assumes you first did restore of a database of log backup us
ing either NORECOVERY or
STANDBY. It does a very specific thing: do the UNDO work that weren't perfor
med by that last
restore.

> Can I use the restore command if I just have an .mdf file?
No. If you are *very* lucky, you can do attach. As for the rest of the attac
h alternatives you
mention, I wouldn't even go there. Do it the proper way (as I listed in top
of this post).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:BAD5B056-6C16-46EC-A043-2E047C7D6E5E@.microsoft.com...
> We have some databases whose files are on a SAN. Someone shut the SAN down
> before the sql server box and when sql server came back up the databases o
n
> the SAN were marked "suspect". I've been able to reboot the sql server box
in
> the past and it clears up the problem but it didn't this time.
> I don't want to try sp_resetstatus at this time (in the middle of the day)
> because there are production databases on the box and I don't want to have
to
> restart sql server. The last backup that was done was file backup by Verit
as.
> Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> will work? What does this command do when a file is not specified?
> Can I use the restore command if I just have an .mdf file? I thought that
I
> might be able to do a detach/attach but I guess because the database is in
> the "suspect" mode, I can't do that. Would removing the database and creat
ing
> a new one by the same name and then doing the detach/attach work?
> Thanks,
> --
> Dan D.|||Hi
sp_resetstatus is your only option.
Restore will not work as the Db is in suspect mode and not loading mode.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:BAD5B056-6C16-46EC-A043-2E047C7D6E5E@.microsoft.com...
> We have some databases whose files are on a SAN. Someone shut the SAN down
> before the sql server box and when sql server came back up the databases
> on
> the SAN were marked "suspect". I've been able to reboot the sql server box
> in
> the past and it clears up the problem but it didn't this time.
> I don't want to try sp_resetstatus at this time (in the middle of the day)
> because there are production databases on the box and I don't want to have
> to
> restart sql server. The last backup that was done was file backup by
> Veritas.
> Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> will work? What does this command do when a file is not specified?
> Can I use the restore command if I just have an .mdf file? I thought that
> I
> might be able to do a detach/attach but I guess because the database is in
> the "suspect" mode, I can't do that. Would removing the database and
> creating
> a new one by the same name and then doing the detach/attach work?
> Thanks,
> --
> Dan D.|||Thanks.
--
Dan D.
"Tibor Karaszi" wrote:

> Seems you are investigating every possible action except the correct actio
n ;-)
> Do a log backup using the NO_TRUNCATE option (the option is required since
your database is
> suspect). Restore the latest database backup and all subsequent log backup
s (including this last log
> backup). Zero data loss.
>
> No. The command assumes you first did restore of a database of log backup
using either NORECOVERY or
> STANDBY. It does a very specific thing: do the UNDO work that weren't perf
ormed by that last
> restore.
>
> No. If you are *very* lucky, you can do attach. As for the rest of the att
ach alternatives you
> mention, I wouldn't even go there. Do it the proper way (as I listed in to
p of this post).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:BAD5B056-6C16-46EC-A043-2E047C7D6E5E@.microsoft.com...
>|||Thanks.
--
Dan D.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> sp_resetstatus is your only option.
> Restore will not work as the Db is in suspect mode and not loading mode.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:BAD5B056-6C16-46EC-A043-2E047C7D6E5E@.microsoft.com...
>
>

restoring suspect database

We have some databases whose files are on a SAN. Someone shut the SAN down
before the sql server box and when sql server came back up the databases on
the SAN were marked "suspect". I've been able to reboot the sql server box in
the past and it clears up the problem but it didn't this time.
I don't want to try sp_resetstatus at this time (in the middle of the day)
because there are production databases on the box and I don't want to have to
restart sql server. The last backup that was done was file backup by Veritas.
Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
will work? What does this command do when a file is not specified?
Can I use the restore command if I just have an .mdf file? I thought that I
might be able to do a detach/attach but I guess because the database is in
the "suspect" mode, I can't do that. Would removing the database and creating
a new one by the same name and then doing the detach/attach work?
Thanks,
Dan D.
Seems you are investigating every possible action except the correct action ;-)
Do a log backup using the NO_TRUNCATE option (the option is required since your database is
suspect). Restore the latest database backup and all subsequent log backups (including this last log
backup). Zero data loss.

> Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> will work? What does this command do when a file is not specified?
No. The command assumes you first did restore of a database of log backup using either NORECOVERY or
STANDBY. It does a very specific thing: do the UNDO work that weren't performed by that last
restore.

> Can I use the restore command if I just have an .mdf file?
No. If you are *very* lucky, you can do attach. As for the rest of the attach alternatives you
mention, I wouldn't even go there. Do it the proper way (as I listed in top of this post).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:BAD5B056-6C16-46EC-A043-2E047C7D6E5E@.microsoft.com...
> We have some databases whose files are on a SAN. Someone shut the SAN down
> before the sql server box and when sql server came back up the databases on
> the SAN were marked "suspect". I've been able to reboot the sql server box in
> the past and it clears up the problem but it didn't this time.
> I don't want to try sp_resetstatus at this time (in the middle of the day)
> because there are production databases on the box and I don't want to have to
> restart sql server. The last backup that was done was file backup by Veritas.
> Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> will work? What does this command do when a file is not specified?
> Can I use the restore command if I just have an .mdf file? I thought that I
> might be able to do a detach/attach but I guess because the database is in
> the "suspect" mode, I can't do that. Would removing the database and creating
> a new one by the same name and then doing the detach/attach work?
> Thanks,
> --
> Dan D.
|||Hi
sp_resetstatus is your only option.
Restore will not work as the Db is in suspect mode and not loading mode.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:BAD5B056-6C16-46EC-A043-2E047C7D6E5E@.microsoft.com...
> We have some databases whose files are on a SAN. Someone shut the SAN down
> before the sql server box and when sql server came back up the databases
> on
> the SAN were marked "suspect". I've been able to reboot the sql server box
> in
> the past and it clears up the problem but it didn't this time.
> I don't want to try sp_resetstatus at this time (in the middle of the day)
> because there are production databases on the box and I don't want to have
> to
> restart sql server. The last backup that was done was file backup by
> Veritas.
> Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> will work? What does this command do when a file is not specified?
> Can I use the restore command if I just have an .mdf file? I thought that
> I
> might be able to do a detach/attach but I guess because the database is in
> the "suspect" mode, I can't do that. Would removing the database and
> creating
> a new one by the same name and then doing the detach/attach work?
> Thanks,
> --
> Dan D.
|||Thanks.
Dan D.
"Tibor Karaszi" wrote:

> Seems you are investigating every possible action except the correct action ;-)
> Do a log backup using the NO_TRUNCATE option (the option is required since your database is
> suspect). Restore the latest database backup and all subsequent log backups (including this last log
> backup). Zero data loss.
>
> No. The command assumes you first did restore of a database of log backup using either NORECOVERY or
> STANDBY. It does a very specific thing: do the UNDO work that weren't performed by that last
> restore.
>
> No. If you are *very* lucky, you can do attach. As for the rest of the attach alternatives you
> mention, I wouldn't even go there. Do it the proper way (as I listed in top of this post).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:BAD5B056-6C16-46EC-A043-2E047C7D6E5E@.microsoft.com...
>
|||Thanks.
Dan D.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> sp_resetstatus is your only option.
> Restore will not work as the Db is in suspect mode and not loading mode.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:BAD5B056-6C16-46EC-A043-2E047C7D6E5E@.microsoft.com...
>
>

Friday, March 23, 2012

Restoring SQL2000 DB to SQLExpress on different machine

Really having a problem...
I have a full backup in a file where RESTORE FILELISTONLY
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0' shows the results...
DeviceTest E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest. mdf D PRIMARY 27590656 35184372080640 1
0 0 000
00000-0000-0000-0000- 000000000000 0 0 0 512 1 NULL 1740000000
004500003 153EE
4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
DeviceTest_log E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest_log. ldf L NULL 13238272 35184372080640 2 0 0
00
000000-0000-0000-0000- 000000000000 0 0 0 512 0 NULL 0 00000000
-0000-0000-000
0-000000000000 0 0
When I try to restore this database on another machine with the following...
RESTORE DATABASE [DeviceTest]
FILE = N'DeviceTest'
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0'
WITH FILE = 1, NORECOVERY,
MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
STATS = 10
it seems to work with the following output...
Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file 1.
100 percent processed.
RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in 2.447
seconds (8.811 MB/sec).
BUT the newly restored database STAYS in the restoring state and I can't do
a thing with it! What is the secret that I'm missing? I've searched Books on
Line, this forum, product support, Q314546, and nothing seems to help.
Wishing for help...
Dave Gardner"DGardner" <DGardner@.discussions.microsoft.com> wrote in message
news:0916F23F-29B7-4460-A761-235D7A67A9C5@.microsoft.com...
> Really having a problem...
> I have a full backup in a file where RESTORE FILELISTONLY
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0' shows the
> results...
> DeviceTest E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest.mdf D PRIMARY 27590656 35184372080640 1 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 1 NULL 1740000000004500003
> 153EE4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
> DeviceTest_log E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest_log.ldf L NULL 13238272 35184372080640 2 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0
> 00000000-0000-0000-0000-000000000000 0 0
> When I try to restore this database on another machine with the
> following...
> RESTORE DATABASE [DeviceTest]
> FILE = N'DeviceTest'
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0'
> WITH FILE = 1, NORECOVERY,
> MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
> STATS = 10
> it seems to work with the following output...
> Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file
> 1.
> 100 percent processed.
> RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in
> 2.447
> seconds (8.811 MB/sec).
> BUT the newly restored database STAYS in the restoring state and I can't
> do
> a thing with it! What is the secret that I'm missing? I've searched Books
> on
> Line, this forum, product support, Q314546, and nothing seems to help.
>
You left the database in restoring mode by not recovering it.
from BOL RESTORE (Transact-SQL)
Comparison of RECOVERY and NORECOVERY
Roll back is controlled by the RESTORE statement through the [ RECOVERY
|
NORECOVERY ] options:
NORECOVERY specifies that roll back not occur. This allows roll forward to
continue with the next statement in the sequence.
In this case, the restore sequence can restore other backups and roll them
forward.
RECOVERY (the default) indicates that roll back should be performed after
roll forward is completed for the current backup.
Recovering the database requires that the entire set of data being restored
(the roll forward set) is consistent with the database. If the roll forward
set has not been rolled forward far enough to be consistent with the
database and RECOVERY is specified, the Database Engine issues an error.
David|||"David Browne" wrote:
> You left the database in restoring mode by not recovering it.
> from BOL RESTORE (Transact-SQL)
> Comparison of RECOVERY and NORECOVERY
> Roll back is controlled by the RESTORE statement through the [ RECOVER
Y |
> NORECOVERY ] options:
> NORECOVERY specifies that roll back not occur. This allows roll forward to
> continue with the next statement in the sequence.
> In this case, the restore sequence can restore other backups and roll them
> forward.
>
> RECOVERY (the default) indicates that roll back should be performed after
> roll forward is completed for the current backup.
> Recovering the database requires that the entire set of data being restore
d
> (the roll forward set) is consistent with the database. If the roll forwar
d
> set has not been rolled forward far enough to be consistent with the
> database and RECOVERY is specified, the Database Engine issues an error.
> David
>
>
David,
Sorry, but that's not the problem. Been there, done that. Using RECOVERY or
NORECOVERY still keeps the database in RECOVERING status with no hope of
doing anything.
--Dave G.

Restoring SQL2000 DB to SQLExpress on different machine

Really having a problem...
I have a full backup in a file where RESTORE FILELISTONLY
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\DeviceTest_db_200608060200' shows the results...
DeviceTest E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest.mdf D PRIMARY 27590656 35184372080640 1 0 0 00000000-0000-0000-0000-000000000000 0 0 0 512 1 NULL 1740000000004500003 153EE4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
DeviceTest_log E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest_log.ldf L NULL 13238272 35184372080640 2 0 0 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 0
When I try to restore this database on another machine with the following...
RESTORE DATABASE [DeviceTest]
FILE = N'DeviceTest'
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\DeviceTest_db_200608060200'
WITH FILE = 1, NORECOVERY,
MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
STATS = 10
it seems to work with the following output...
Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file 1.
100 percent processed.
RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in 2.447
seconds (8.811 MB/sec).
BUT the newly restored database STAYS in the restoring state and I can't do
a thing with it! What is the secret that I'm missing? I've searched Books on
Line, this forum, product support, Q314546, and nothing seems to help.
Wishing for help...
Dave Gardner"DGardner" <DGardner@.discussions.microsoft.com> wrote in message
news:0916F23F-29B7-4460-A761-235D7A67A9C5@.microsoft.com...
> Really having a problem...
> I have a full backup in a file where RESTORE FILELISTONLY
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Backup\DeviceTest_db_200608060200' shows the
> results...
> DeviceTest E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest.mdf D PRIMARY 27590656 35184372080640 1 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 1 NULL 1740000000004500003
> 153EE4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
> DeviceTest_log E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest_log.ldf L NULL 13238272 35184372080640 2 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0
> 00000000-0000-0000-0000-000000000000 0 0
> When I try to restore this database on another machine with the
> following...
> RESTORE DATABASE [DeviceTest]
> FILE = N'DeviceTest'
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Backup\DeviceTest_db_200608060200'
> WITH FILE = 1, NORECOVERY,
> MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
> STATS = 10
> it seems to work with the following output...
> Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file
> 1.
> 100 percent processed.
> RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in
> 2.447
> seconds (8.811 MB/sec).
> BUT the newly restored database STAYS in the restoring state and I can't
> do
> a thing with it! What is the secret that I'm missing? I've searched Books
> on
> Line, this forum, product support, Q314546, and nothing seems to help.
>
You left the database in restoring mode by not recovering it.
from BOL RESTORE (Transact-SQL)
Comparison of RECOVERY and NORECOVERY
Roll back is controlled by the RESTORE statement through the [ RECOVERY |
NORECOVERY ] options:
NORECOVERY specifies that roll back not occur. This allows roll forward to
continue with the next statement in the sequence.
In this case, the restore sequence can restore other backups and roll them
forward.
RECOVERY (the default) indicates that roll back should be performed after
roll forward is completed for the current backup.
Recovering the database requires that the entire set of data being restored
(the roll forward set) is consistent with the database. If the roll forward
set has not been rolled forward far enough to be consistent with the
database and RECOVERY is specified, the Database Engine issues an error.
David|||"David Browne" wrote:
> You left the database in restoring mode by not recovering it.
> from BOL RESTORE (Transact-SQL)
> Comparison of RECOVERY and NORECOVERY
> Roll back is controlled by the RESTORE statement through the [ RECOVERY |
> NORECOVERY ] options:
> NORECOVERY specifies that roll back not occur. This allows roll forward to
> continue with the next statement in the sequence.
> In this case, the restore sequence can restore other backups and roll them
> forward.
>
> RECOVERY (the default) indicates that roll back should be performed after
> roll forward is completed for the current backup.
> Recovering the database requires that the entire set of data being restored
> (the roll forward set) is consistent with the database. If the roll forward
> set has not been rolled forward far enough to be consistent with the
> database and RECOVERY is specified, the Database Engine issues an error.
> David
>
>
David,
Sorry, but that's not the problem. Been there, done that. Using RECOVERY or
NORECOVERY still keeps the database in RECOVERING status with no hope of
doing anything.
--Dave G.sql

Restoring SQL server from log files

I am using SQL serve. We have a probelm after our log backup have been damaged.
Problem is we have only log files for the daily backups. ie., after the
entire datbase files were backed up few days ago, it has been a daily
routine of
- backup log file with NT backup
- backup log with SQL 'BACKUP log" command
The SQL log backup is toast now.
The dates on the log files are different on the first and last day.
Replacing the log with the last days log files doesn't seem to work
spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
invalid..
spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
(database ID 7) could not recover. Contact Technical Support...
Is there a way we can just restore the daily log file copies from the first
day and roll forward the database ? Is there a 'RESTORE log.. WITH
NORECOVERY" option without using an archive? Or can I do roll forward by
restarting the server each time replacing the log file?
Any help is appreciated.
-JBThe only way to do any type if "incremental" restore is to restore from a database backup, then
restore an unbroken chain of log backups. I didn't quite follow your scenario, but that is what you
need to do.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"bertiew" <bertiew@.discussions.microsoft.com> wrote in message
news:3E26B852-C0D5-4424-A423-D1A2AC411873@.microsoft.com...
>I am using SQL serve. We have a probelm after our log backup have been damaged.
> Problem is we have only log files for the daily backups. ie., after the
> entire datbase files were backed up few days ago, it has been a daily
> routine of
> - backup log file with NT backup
> - backup log with SQL 'BACKUP log" command
> The SQL log backup is toast now.
> The dates on the log files are different on the first and last day.
> Replacing the log with the last days log files doesn't seem to work
> spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
> invalid..
> spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
> (database ID 7) could not recover. Contact Technical Support...
> Is there a way we can just restore the daily log file copies from the first
> day and roll forward the database ? Is there a 'RESTORE log.. WITH
> NORECOVERY" option without using an archive? Or can I do roll forward by
> restarting the server each time replacing the log file?
> Any help is appreciated.
> -JB
>|||Hi
I am not clear on what you mean by
- backup log file with NT backup !
Is this the backup file created by
- backup log with SQL 'BACKUP log" command ?
When restoring the log you can use the NORECOVERY option when you have a
subsequent log file to restore.
Check out the topic "How to restore to the point of failure" and possibly
"How to restore to a point in time" and "Reducing Recovery Time" in Books
online.
John
"bertiew" wrote:
> I am using SQL serve. We have a probelm after our log backup have been damaged.
> Problem is we have only log files for the daily backups. ie., after the
> entire datbase files were backed up few days ago, it has been a daily
> routine of
> - backup log file with NT backup
> - backup log with SQL 'BACKUP log" command
> The SQL log backup is toast now.
> The dates on the log files are different on the first and last day.
> Replacing the log with the last days log files doesn't seem to work
> spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
> invalid..
> spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
> (database ID 7) could not recover. Contact Technical Support...
> Is there a way we can just restore the daily log file copies from the first
> day and roll forward the database ? Is there a 'RESTORE log.. WITH
> NORECOVERY" option without using an archive? Or can I do roll forward by
> restarting the server each time replacing the log file?
> Any help is appreciated.
> -JB
>|||By NT backup, i mean the file backups created by the Windows backup utility.
Not the one created by the SQL backup command. In short they are copies of
the ldf files.
Can these be restored? With the NO RECOVERY option?
Thanks,
-JB
"John Bell" wrote:
> Hi
> I am not clear on what you mean by
> - backup log file with NT backup !
> Is this the backup file created by
> - backup log with SQL 'BACKUP log" command ?
> When restoring the log you can use the NORECOVERY option when you have a
> subsequent log file to restore.
> Check out the topic "How to restore to the point of failure" and possibly
> "How to restore to a point in time" and "Reducing Recovery Time" in Books
> online.
> John
>
>
> "bertiew" wrote:
> > I am using SQL serve. We have a probelm after our log backup have been damaged.
> >
> > Problem is we have only log files for the daily backups. ie., after the
> > entire datbase files were backed up few days ago, it has been a daily
> > routine of
> > - backup log file with NT backup
> > - backup log with SQL 'BACKUP log" command
> >
> > The SQL log backup is toast now.
> >
> > The dates on the log files are different on the first and last day.
> > Replacing the log with the last days log files doesn't seem to work
> >
> > spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
> > invalid..
> > spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
> > (database ID 7) could not recover. Contact Technical Support...
> >
> > Is there a way we can just restore the daily log file copies from the first
> > day and roll forward the database ? Is there a 'RESTORE log.. WITH
> > NORECOVERY" option without using an archive? Or can I do roll forward by
> > restarting the server each time replacing the log file?
> >
> > Any help is appreciated.
> >
> > -JB
> >|||No,. See my other post. Any type of incremental restore requires you to have performed backups from
SQL Ser4ver and such restore starts with RESTORE DATABASE and then a number of RESTORE LOG. All from
SQL Server backup files, not raw database files.
Seems you have a good opportunity to revise your backup strategy.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"bertiew" <bertiew@.discussions.microsoft.com> wrote in message
news:C74AD621-98A4-4DC3-A0D1-7D4F4188BAF5@.microsoft.com...
> By NT backup, i mean the file backups created by the Windows backup utility.
> Not the one created by the SQL backup command. In short they are copies of
> the ldf files.
> Can these be restored? With the NO RECOVERY option?
> Thanks,
> -JB
> "John Bell" wrote:
>> Hi
>> I am not clear on what you mean by
>> - backup log file with NT backup !
>> Is this the backup file created by
>> - backup log with SQL 'BACKUP log" command ?
>> When restoring the log you can use the NORECOVERY option when you have a
>> subsequent log file to restore.
>> Check out the topic "How to restore to the point of failure" and possibly
>> "How to restore to a point in time" and "Reducing Recovery Time" in Books
>> online.
>> John
>>
>>
>> "bertiew" wrote:
>> > I am using SQL serve. We have a probelm after our log backup have been damaged.
>> >
>> > Problem is we have only log files for the daily backups. ie., after the
>> > entire datbase files were backed up few days ago, it has been a daily
>> > routine of
>> > - backup log file with NT backup
>> > - backup log with SQL 'BACKUP log" command
>> >
>> > The SQL log backup is toast now.
>> >
>> > The dates on the log files are different on the first and last day.
>> > Replacing the log with the last days log files doesn't seem to work
>> >
>> > spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
>> > invalid..
>> > spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
>> > (database ID 7) could not recover. Contact Technical Support...
>> >
>> > Is there a way we can just restore the daily log file copies from the first
>> > day and roll forward the database ? Is there a 'RESTORE log.. WITH
>> > NORECOVERY" option without using an archive? Or can I do roll forward by
>> > restarting the server each time replacing the log file?
>> >
>> > Any help is appreciated.
>> >
>> > -JB
>> >|||Hi
Unless you have stopped SQL Server or detached the database before doing
your NT Backup, then it is unlikely that any mdf or ldf files on your NT
backup are any use. If you have both a data (mdf) and log (ldf) files it will
be possible to restore them info a different location and re-attach them
using sp_attach_db as a different database.
John
"bertiew" wrote:
> By NT backup, i mean the file backups created by the Windows backup utility.
> Not the one created by the SQL backup command. In short they are copies of
> the ldf files.
> Can these be restored? With the NO RECOVERY option?
> Thanks,
> -JB
> "John Bell" wrote:
> > Hi
> >
> > I am not clear on what you mean by
> > - backup log file with NT backup !
> > Is this the backup file created by
> > - backup log with SQL 'BACKUP log" command ?
> >
> > When restoring the log you can use the NORECOVERY option when you have a
> > subsequent log file to restore.
> >
> > Check out the topic "How to restore to the point of failure" and possibly
> > "How to restore to a point in time" and "Reducing Recovery Time" in Books
> > online.
> >
> > John
> >
> >
> >
> >
> >
> > "bertiew" wrote:
> >
> > > I am using SQL serve. We have a probelm after our log backup have been damaged.
> > >
> > > Problem is we have only log files for the daily backups. ie., after the
> > > entire datbase files were backed up few days ago, it has been a daily
> > > routine of
> > > - backup log file with NT backup
> > > - backup log with SQL 'BACKUP log" command
> > >
> > > The SQL log backup is toast now.
> > >
> > > The dates on the log files are different on the first and last day.
> > > Replacing the log with the last days log files doesn't seem to work
> > >
> > > spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
> > > invalid..
> > > spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
> > > (database ID 7) could not recover. Contact Technical Support...
> > >
> > > Is there a way we can just restore the daily log file copies from the first
> > > day and roll forward the database ? Is there a 'RESTORE log.. WITH
> > > NORECOVERY" option without using an archive? Or can I do roll forward by
> > > restarting the server each time replacing the log file?
> > >
> > > Any help is appreciated.
> > >
> > > -JB
> > >|||Here's what I have observed
backups are
(1)data files, log files
(2)logfiles
(3)logfiles
(4)logfiles
(5)logfiles
(6)logfiles
I can make sqlsvr recover
with data files (1) and any of logfiles from (1), (2), or (3)
and the data is recovered in the state it was at the time of backup of the
logfile
But if I use logfiles from (4), I get the LSN error.
I am presuming the log file got reused as the log wrapped around. So can I
make sqlsvr do something like this?
a) Start sqlsvr with data files from (1) and log files from (3) <== This works
b) After recovery is done, shut down sqlsvr
c) Replace log with log files from (4), and recover again <== does'nt work.
Can I work around this?
Thanks,
-JB
"John Bell" wrote:
> Hi
> Unless you have stopped SQL Server or detached the database before doing
> your NT Backup, then it is unlikely that any mdf or ldf files on your NT
> backup are any use. If you have both a data (mdf) and log (ldf) files it will
> be possible to restore them info a different location and re-attach them
> using sp_attach_db as a different database.
> John
> "bertiew" wrote:
> > By NT backup, i mean the file backups created by the Windows backup utility.
> > Not the one created by the SQL backup command. In short they are copies of
> > the ldf files.
> >
> > Can these be restored? With the NO RECOVERY option?
> >
> > Thanks,
> > -JB
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > I am not clear on what you mean by
> > > - backup log file with NT backup !
> > > Is this the backup file created by
> > > - backup log with SQL 'BACKUP log" command ?
> > >
> > > When restoring the log you can use the NORECOVERY option when you have a
> > > subsequent log file to restore.
> > >
> > > Check out the topic "How to restore to the point of failure" and possibly
> > > "How to restore to a point in time" and "Reducing Recovery Time" in Books
> > > online.
> > >
> > > John
> > >
> > >
> > >
> > >
> > >
> > > "bertiew" wrote:
> > >
> > > > I am using SQL serve. We have a probelm after our log backup have been damaged.
> > > >
> > > > Problem is we have only log files for the daily backups. ie., after the
> > > > entire datbase files were backed up few days ago, it has been a daily
> > > > routine of
> > > > - backup log file with NT backup
> > > > - backup log with SQL 'BACKUP log" command
> > > >
> > > > The SQL log backup is toast now.
> > > >
> > > > The dates on the log files are different on the first and last day.
> > > > Replacing the log with the last days log files doesn't seem to work
> > > >
> > > > spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
> > > > invalid..
> > > > spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
> > > > (database ID 7) could not recover. Contact Technical Support...
> > > >
> > > > Is there a way we can just restore the daily log file copies from the first
> > > > day and roll forward the database ? Is there a 'RESTORE log.. WITH
> > > > NORECOVERY" option without using an archive? Or can I do roll forward by
> > > > restarting the server each time replacing the log file?
> > > >
> > > > Any help is appreciated.
> > > >
> > > > -JB
> > > >|||Hi
If these are NT backups then the best you can do is restore the files from
(1) and use sp_attach_db. If (1) is a Full SQL Server Backup and 2-6 are SQL
Log backups then follow the process already described by Tibor and also in
the topic "Reducing Recovery Time" in books online.
John
"bertiew" wrote:
> Here's what I have observed
> backups are
> (1)data files, log files
> (2)logfiles
> (3)logfiles
> (4)logfiles
> (5)logfiles
> (6)logfiles
> I can make sqlsvr recover
> with data files (1) and any of logfiles from (1), (2), or (3)
> and the data is recovered in the state it was at the time of backup of the
> logfile
> But if I use logfiles from (4), I get the LSN error.
> I am presuming the log file got reused as the log wrapped around. So can I
> make sqlsvr do something like this?
> a) Start sqlsvr with data files from (1) and log files from (3) <== This works
> b) After recovery is done, shut down sqlsvr
> c) Replace log with log files from (4), and recover again <== does'nt work.
> Can I work around this?
> Thanks,
> -JB
> "John Bell" wrote:
> > Hi
> >
> > Unless you have stopped SQL Server or detached the database before doing
> > your NT Backup, then it is unlikely that any mdf or ldf files on your NT
> > backup are any use. If you have both a data (mdf) and log (ldf) files it will
> > be possible to restore them info a different location and re-attach them
> > using sp_attach_db as a different database.
> >
> > John
> >
> > "bertiew" wrote:
> >
> > > By NT backup, i mean the file backups created by the Windows backup utility.
> > > Not the one created by the SQL backup command. In short they are copies of
> > > the ldf files.
> > >
> > > Can these be restored? With the NO RECOVERY option?
> > >
> > > Thanks,
> > > -JB
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > I am not clear on what you mean by
> > > > - backup log file with NT backup !
> > > > Is this the backup file created by
> > > > - backup log with SQL 'BACKUP log" command ?
> > > >
> > > > When restoring the log you can use the NORECOVERY option when you have a
> > > > subsequent log file to restore.
> > > >
> > > > Check out the topic "How to restore to the point of failure" and possibly
> > > > "How to restore to a point in time" and "Reducing Recovery Time" in Books
> > > > online.
> > > >
> > > > John
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "bertiew" wrote:
> > > >
> > > > > I am using SQL serve. We have a probelm after our log backup have been damaged.
> > > > >
> > > > > Problem is we have only log files for the daily backups. ie., after the
> > > > > entire datbase files were backed up few days ago, it has been a daily
> > > > > routine of
> > > > > - backup log file with NT backup
> > > > > - backup log with SQL 'BACKUP log" command
> > > > >
> > > > > The SQL log backup is toast now.
> > > > >
> > > > > The dates on the log files are different on the first and last day.
> > > > > Replacing the log with the last days log files doesn't seem to work
> > > > >
> > > > > spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
> > > > > invalid..
> > > > > spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
> > > > > (database ID 7) could not recover. Contact Technical Support...
> > > > >
> > > > > Is there a way we can just restore the daily log file copies from the first
> > > > > day and roll forward the database ? Is there a 'RESTORE log.. WITH
> > > > > NORECOVERY" option without using an archive? Or can I do roll forward by
> > > > > restarting the server each time replacing the log file?
> > > > >
> > > > > Any help is appreciated.
> > > > >
> > > > > -JB
> > > > >|||bertiew wrote:
> Here's what I have observed
> backups are
> (1)data files, log files
> (2)logfiles
> (3)logfiles
> (4)logfiles
> (5)logfiles
> (6)logfiles
> I can make sqlsvr recover
> with data files (1) and any of logfiles from (1), (2), or (3)
> and the data is recovered in the state it was at the time of backup of the
> logfile
> But if I use logfiles from (4), I get the LSN error.
> I am presuming the log file got reused as the log wrapped around. So can I
> make sqlsvr do something like this?
> a) Start sqlsvr with data files from (1) and log files from (3) <== This works
> b) After recovery is done, shut down sqlsvr
> c) Replace log with log files from (4), and recover again <== does'nt work.
> Can I work around this?
> Thanks,
> -JB
> "John Bell" wrote:
>> Hi
>> Unless you have stopped SQL Server or detached the database before doing
>> your NT Backup, then it is unlikely that any mdf or ldf files on your NT
>> backup are any use. If you have both a data (mdf) and log (ldf) files it will
>> be possible to restore them info a different location and re-attach them
>> using sp_attach_db as a different database.
>> John
>> "bertiew" wrote:
>> By NT backup, i mean the file backups created by the Windows backup utility.
>> Not the one created by the SQL backup command. In short they are copies of
>> the ldf files.
>> Can these be restored? With the NO RECOVERY option?
>> Thanks,
>> -JB
>> "John Bell" wrote:
>> Hi
>> I am not clear on what you mean by
>> - backup log file with NT backup !
>> Is this the backup file created by
>> - backup log with SQL 'BACKUP log" command ?
>> When restoring the log you can use the NORECOVERY option when you have a
>> subsequent log file to restore.
>> Check out the topic "How to restore to the point of failure" and possibly
>> "How to restore to a point in time" and "Reducing Recovery Time" in Books
>> online.
>> John
>>
>>
>> "bertiew" wrote:
>> I am using SQL serve. We have a probelm after our log backup have been damaged.
>> Problem is we have only log files for the daily backups. ie., after the
>> entire datbase files were backed up few days ago, it has been a daily
>> routine of
>> - backup log file with NT backup
>> - backup log with SQL 'BACKUP log" command
>> The SQL log backup is toast now.
>> The dates on the log files are different on the first and last day.
>> Replacing the log with the last days log files doesn't seem to work
>> spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
>> invalid..
>> spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
>> (database ID 7) could not recover. Contact Technical Support...
>> Is there a way we can just restore the daily log file copies from the first
>> day and roll forward the database ? Is there a 'RESTORE log.. WITH
>> NORECOVERY" option without using an archive? Or can I do roll forward by
>> restarting the server each time replacing the log file?
>> Any help is appreciated.
>> -JB
Like the other ones, I'm a bit confused about what it is you've done.
An NT Backup will not backup neither a database file nor a logfile
unless you've stopped the SQL server service, or the files has been
detached. Also when you have NT Backups of logfiles, you can not restore
these using SQL server Restore command.
If I follow your above mentioned backup steps, I'd assume that you can
restore the datafile from (1), restore the logfile from (6) and then try
to run sp_attach_db and then hope it works. Here I assume that it's NT
Backup files you've got and that you haven't mingled around with SQL
server backup command in between.
As already mentioned, you should read up on BACKUP/RESTORE in Books On
Line, because is seem like you are missing some basic understanding of
how it works.
Regards
Steen|||Actually you can backup the files while they are in use, either using VSS or
by enabling SQL writer service (SQL 2005).
I did read up about the SQL backup and restore features. I understand that
my question is a bit unorthodox.
I can restore data 1 and log 3. My take is that in log backup 4 ,the log
files get reused. (as SQL server log circles back to the beginning of the
file). If I were to
draw a picture of the backup of the log files, I would presume it were
something like this (at least based on the SQL 2005 documentation of the log
architecture)
Log file backup 1 ===Log file backup 2 =========Log file backup 3 =============Log file backup 4 ++++++========== <-- ++ part of log file reused.
Log file backup 5 ++++++++++++++==
My question is this: Can I make SQL server somehow recover with log file
backup 3 (be done with the part of the log that got reused in log file backup
4) and then make it continue recovery after I switch log files? (the 'O'ther
database company does this - so maybe MS SQL too?)
Thanks,
-JB
"Steen Persson (DK)" wrote:
> bertiew wrote:
> > Here's what I have observed
> >
> > backups are
> > (1)data files, log files
> > (2)logfiles
> > (3)logfiles
> > (4)logfiles
> > (5)logfiles
> > (6)logfiles
> >
> > I can make sqlsvr recover
> > with data files (1) and any of logfiles from (1), (2), or (3)
> > and the data is recovered in the state it was at the time of backup of the
> > logfile
> >
> > But if I use logfiles from (4), I get the LSN error.
> >
> > I am presuming the log file got reused as the log wrapped around. So can I
> > make sqlsvr do something like this?
> > a) Start sqlsvr with data files from (1) and log files from (3) <== This works
> > b) After recovery is done, shut down sqlsvr
> > c) Replace log with log files from (4), and recover again <== does'nt work.
> >
> > Can I work around this?
> >
> > Thanks,
> > -JB
> >
> > "John Bell" wrote:
> >
> >> Hi
> >>
> >> Unless you have stopped SQL Server or detached the database before doing
> >> your NT Backup, then it is unlikely that any mdf or ldf files on your NT
> >> backup are any use. If you have both a data (mdf) and log (ldf) files it will
> >> be possible to restore them info a different location and re-attach them
> >> using sp_attach_db as a different database.
> >>
> >> John
> >>
> >> "bertiew" wrote:
> >>
> >> By NT backup, i mean the file backups created by the Windows backup utility.
> >> Not the one created by the SQL backup command. In short they are copies of
> >> the ldf files.
> >>
> >> Can these be restored? With the NO RECOVERY option?
> >>
> >> Thanks,
> >> -JB
> >>
> >> "John Bell" wrote:
> >>
> >> Hi
> >>
> >> I am not clear on what you mean by
> >> - backup log file with NT backup !
> >> Is this the backup file created by
> >> - backup log with SQL 'BACKUP log" command ?
> >>
> >> When restoring the log you can use the NORECOVERY option when you have a
> >> subsequent log file to restore.
> >>
> >> Check out the topic "How to restore to the point of failure" and possibly
> >> "How to restore to a point in time" and "Reducing Recovery Time" in Books
> >> online.
> >>
> >> John
> >>
> >>
> >>
> >>
> >>
> >> "bertiew" wrote:
> >>
> >> I am using SQL serve. We have a probelm after our log backup have been damaged.
> >>
> >> Problem is we have only log files for the daily backups. ie., after the
> >> entire datbase files were backed up few days ago, it has been a daily
> >> routine of
> >> - backup log file with NT backup
> >> - backup log with SQL 'BACKUP log" command
> >>
> >> The SQL log backup is toast now.
> >>
> >> The dates on the log files are different on the first and last day.
> >> Replacing the log with the last days log files doesn't seem to work
> >>
> >> spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
> >> invalid..
> >> spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
> >> (database ID 7) could not recover. Contact Technical Support...
> >>
> >> Is there a way we can just restore the daily log file copies from the first
> >> day and roll forward the database ? Is there a 'RESTORE log.. WITH
> >> NORECOVERY" option without using an archive? Or can I do roll forward by
> >> restarting the server each time replacing the log file?
> >>
> >> Any help is appreciated.
> >>
> >> -JB
> >>
> Like the other ones, I'm a bit confused about what it is you've done.
> An NT Backup will not backup neither a database file nor a logfile
> unless you've stopped the SQL server service, or the files has been
> detached. Also when you have NT Backups of logfiles, you can not restore
> these using SQL server Restore command.
> If I follow your above mentioned backup steps, I'd assume that you can
> restore the datafile from (1), restore the logfile from (6) and then try
> to run sp_attach_db and then hope it works. Here I assume that it's NT
> Backup files you've got and that you haven't mingled around with SQL
> server backup command in between.
> As already mentioned, you should read up on BACKUP/RESTORE in Books On
> Line, because is seem like you are missing some basic understanding of
> how it works.
> Regards
> Steen
>|||Hi
You did not mention that you were using SQL 2005, SQL Writer or Volume
Shadow Copy Service in your original email, and it is still not clear if you
are actually using them.
To perform point in time recovery you will need to use Full Recovery model,
and the log file will not be overwritten unless it has been backed up i.e.
your situation will not occur. Once backed up SQL Server may re-use the
truncated inactive part of the log.
John
"bertiew" wrote:
> Actually you can backup the files while they are in use, either using VSS or
> by enabling SQL writer service (SQL 2005).
> I did read up about the SQL backup and restore features. I understand that
> my question is a bit unorthodox.
> I can restore data 1 and log 3. My take is that in log backup 4 ,the log
> files get reused. (as SQL server log circles back to the beginning of the
> file). If I were to
> draw a picture of the backup of the log files, I would presume it were
> something like this (at least based on the SQL 2005 documentation of the log
> architecture)
> Log file backup 1 ===> Log file backup 2 =========> Log file backup 3 =============> Log file backup 4 ++++++========== <-- ++ part of log file reused.
> Log file backup 5 ++++++++++++++==> My question is this: Can I make SQL server somehow recover with log file
> backup 3 (be done with the part of the log that got reused in log file backup
> 4) and then make it continue recovery after I switch log files? (the 'O'ther
> database company does this - so maybe MS SQL too?)
> Thanks,
> -JB
> "Steen Persson (DK)" wrote:
> > bertiew wrote:
> > > Here's what I have observed
> > >
> > > backups are
> > > (1)data files, log files
> > > (2)logfiles
> > > (3)logfiles
> > > (4)logfiles
> > > (5)logfiles
> > > (6)logfiles
> > >
> > > I can make sqlsvr recover
> > > with data files (1) and any of logfiles from (1), (2), or (3)
> > > and the data is recovered in the state it was at the time of backup of the
> > > logfile
> > >
> > > But if I use logfiles from (4), I get the LSN error.
> > >
> > > I am presuming the log file got reused as the log wrapped around. So can I
> > > make sqlsvr do something like this?
> > > a) Start sqlsvr with data files from (1) and log files from (3) <== This works
> > > b) After recovery is done, shut down sqlsvr
> > > c) Replace log with log files from (4), and recover again <== does'nt work.
> > >
> > > Can I work around this?
> > >
> > > Thanks,
> > > -JB
> > >
> > > "John Bell" wrote:
> > >
> > >> Hi
> > >>
> > >> Unless you have stopped SQL Server or detached the database before doing
> > >> your NT Backup, then it is unlikely that any mdf or ldf files on your NT
> > >> backup are any use. If you have both a data (mdf) and log (ldf) files it will
> > >> be possible to restore them info a different location and re-attach them
> > >> using sp_attach_db as a different database.
> > >>
> > >> John
> > >>
> > >> "bertiew" wrote:
> > >>
> > >> By NT backup, i mean the file backups created by the Windows backup utility.
> > >> Not the one created by the SQL backup command. In short they are copies of
> > >> the ldf files.
> > >>
> > >> Can these be restored? With the NO RECOVERY option?
> > >>
> > >> Thanks,
> > >> -JB
> > >>
> > >> "John Bell" wrote:
> > >>
> > >> Hi
> > >>
> > >> I am not clear on what you mean by
> > >> - backup log file with NT backup !
> > >> Is this the backup file created by
> > >> - backup log with SQL 'BACKUP log" command ?
> > >>
> > >> When restoring the log you can use the NORECOVERY option when you have a
> > >> subsequent log file to restore.
> > >>
> > >> Check out the topic "How to restore to the point of failure" and possibly
> > >> "How to restore to a point in time" and "Reducing Recovery Time" in Books
> > >> online.
> > >>
> > >> John
> > >>
> > >>
> > >>
> > >>
> > >>
> > >> "bertiew" wrote:
> > >>
> > >> I am using SQL serve. We have a probelm after our log backup have been damaged.
> > >>
> > >> Problem is we have only log files for the daily backups. ie., after the
> > >> entire datbase files were backed up few days ago, it has been a daily
> > >> routine of
> > >> - backup log file with NT backup
> > >> - backup log with SQL 'BACKUP log" command
> > >>
> > >> The SQL log backup is toast now.
> > >>
> > >> The dates on the log files are different on the first and last day.
> > >> Replacing the log with the last days log files doesn't seem to work
> > >>
> > >> spid11 The LSN (5:1223:1) passed to log scan in database 'testdb' is
> > >> invalid..
> > >> spid11 Error: 3414, Severity: 21, State: 1 spid11 Database 'testdb'
> > >> (database ID 7) could not recover. Contact Technical Support...
> > >>
> > >> Is there a way we can just restore the daily log file copies from the first
> > >> day and roll forward the database ? Is there a 'RESTORE log.. WITH
> > >> NORECOVERY" option without using an archive? Or can I do roll forward by
> > >> restarting the server each time replacing the log file?
> > >>
> > >> Any help is appreciated.
> > >>
> > >> -JB
> > >>
> >
> > Like the other ones, I'm a bit confused about what it is you've done.
> > An NT Backup will not backup neither a database file nor a logfile
> > unless you've stopped the SQL server service, or the files has been
> > detached. Also when you have NT Backups of logfiles, you can not restore
> > these using SQL server Restore command.
> >
> > If I follow your above mentioned backup steps, I'd assume that you can
> > restore the datafile from (1), restore the logfile from (6) and then try
> > to run sp_attach_db and then hope it works. Here I assume that it's NT
> > Backup files you've got and that you haven't mingled around with SQL
> > server backup command in between.
> >
> > As already mentioned, you should read up on BACKUP/RESTORE in Books On
> > Line, because is seem like you are missing some basic understanding of
> > how it works.
> >
> > Regards
> > Steen
> >|||bertiew wrote:
> Actually you can backup the files while they are in use, either using VSS or
> by enabling SQL writer service (SQL 2005).
> I did read up about the SQL backup and restore features. I understand that
> my question is a bit unorthodox.
> I can restore data 1 and log 3. My take is that in log backup 4 ,the log
> files get reused. (as SQL server log circles back to the beginning of the
> file). If I were to
> draw a picture of the backup of the log files, I would presume it were
> something like this (at least based on the SQL 2005 documentation of the log
> architecture)
> Log file backup 1 ===> Log file backup 2 =========> Log file backup 3 =============> Log file backup 4 ++++++========== <-- ++ part of log file reused.
> Log file backup 5 ++++++++++++++==> My question is this: Can I make SQL server somehow recover with log file
> backup 3 (be done with the part of the log that got reused in log file backup
> 4) and then make it continue recovery after I switch log files? (the 'O'ther
> database company does this - so maybe MS SQL too?)
> Thanks,
> -JB
>
I still think that you are mixing things up a little bit. An NT Backup
(or similar file backup) is not the same as a BACKUP LOG command in the
way it treats the file. I don't know the SQL writer option in detail,
but to me it looks like it's only enabling you to make a regular file
copy of your logfile. It doesn't truncate the log or anything else like
a regular BACKUP LOG command does. This means that if you use this to
backup your logfile, it will just take a backup of whatever is in your
logfile at that time, and the logfile will still contain "old" data
because it's not being truncated.
I've haven't tried the SQL writer service my self, but I still think
that the best way to set up a backup routine is to do a database backup
e.g. once a day, and then run log backups a number of times during the
day. These backup files can then be backed/copied to somewhere else
since they are now regular files. By doing this, you can always restore
your database backup and then logfiles up to the point in time you need.
It's my feeling that the setup you have now, is a mix of different ways
of backing up your data and that's why it's causing you troubles.
Regards
Steen

Wednesday, March 28, 2012

restoring without log

restoring without log

restoring without log

Restoring Tran. Log files

Restoring to a network paths?

Monday, March 26, 2012

restoring the database has failed

restoring the database has failed

restoring the database has failed

restoring suspect database

restoring suspect database

restoring suspect database

Friday, March 23, 2012

Restoring SQL2000 DB to SQLExpress on different machine

Restoring SQL2000 DB to SQLExpress on different machine

Restoring SQL server from log files

Restoring SQL server from log files

Restoring SQL server from log files

database problem

Blog Archive

About Me