database problem: script

Showing posts with label script. Show all posts

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'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

Tuesday, March 20, 2012

Restoring model and msdb databases goes to wrong file locations

Hi,
I am working on a script which should be able to restore a full sql server
2000 automatically. When I do a full restore of the system databases the
file locations of the mdf and ldf files don't change but when I restore the
differential backup the ldf file places it self in the default directory of
Microsoft SQL Server.
So my script looks like this:
restore database model from disk = 'c:\backup\model.bkf' with norecovery,
replace
go
restore database model from disk ='c:\backup\modeldiff.bkf' with
recovery,replace
go
The model log file should go into "e:\database\data\" but instead it goes
into "C:\Program Files\Microsoft SQL Server\MSSQL\Data"
why does it work fine when I do like this:
restore database model from disk = 'c:\backup\model.bkf' with recovery,
replace
but not when I want to do a diff restore?
I know I can detach and attach the model database, but then I also have to
stop and start the database with a trace flag - not very pretty...
Any ideas?
ChristofferYou can view the physical files from the backup using
restore filelistonly. If you need to have the files in
another location, use the move option in the restore script
to specify the physical file locations.
-Sue
On Tue, 17 May 2005 16:49:57 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:
>Hi,
>I am working on a script which should be able to restore a full sql server
>2000 automatically. When I do a full restore of the system databases the
>file locations of the mdf and ldf files don't change but when I restore the
>differential backup the ldf file places it self in the default directory of
>Microsoft SQL Server.
>So my script looks like this:
>restore database model from disk = 'c:\backup\model.bkf' with norecovery,
>replace
>go
>restore database model from disk ='c:\backup\modeldiff.bkf' with
>recovery,replace
>go
>The model log file should go into "e:\database\data\" but instead it goes
>into "C:\Program Files\Microsoft SQL Server\MSSQL\Data"
>why does it work fine when I do like this:
>restore database model from disk = 'c:\backup\model.bkf' with recovery,
>replace
>but not when I want to do a diff restore?
>I know I can detach and attach the model database, but then I also have to
>stop and start the database with a trace flag - not very pretty...
>
>Any ideas?
>Christoffer
>|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:47dl81t4qahcv4l8gjbrb6c8oo934vl09p@.4ax.com...
> You can view the physical files from the backup using
> restore filelistonly.
Correct, I can view the paths with restore filelistonly and it shows the
correct file locations.
> If you need to have the files in
> another location, use the move option in the restore script
> to specify the physical file locations.
No, You cannot use the move option with system databases.|||Yes you can use with move. You can follow the example in the
following knowledge base article:
http://support.microsoft.com/?id=304692
-Sue
On Wed, 18 May 2005 08:55:37 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:47dl81t4qahcv4l8gjbrb6c8oo934vl09p@.4ax.com...
>> You can view the physical files from the backup using
>> restore filelistonly.
>Correct, I can view the paths with restore filelistonly and it shows the
>correct file locations.
>> If you need to have the files in
>> another location, use the move option in the restore script
>> to specify the physical file locations.
>No, You cannot use the move option with system databases.
>|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:3s9m811b194jc1qs0v74kugnhqcfg2c4lo@.4ax.com...
> Yes you can use with move. You can follow the example in the
> following knowledge base article:
> http://support.microsoft.com/?id=304692
>
In the article is mentioned the following:
NOTE: These instructions in this article do not apply to SQL Server 2000.
The response from the server is :
tempdb is skipped. You cannot run a query that requires tempdb.|||One of the trace flags settings is different on 2000 if you
are following all of the steps but you can restore with
move. Follow the trace flags in the following article or
just use attach/detach as the article does:
http://support.microsoft.com/?id=224071
-Sue
On Wed, 18 May 2005 15:35:57 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:3s9m811b194jc1qs0v74kugnhqcfg2c4lo@.4ax.com...
>> Yes you can use with move. You can follow the example in the
>> following knowledge base article:
>> http://support.microsoft.com/?id=304692
>In the article is mentioned the following:
>NOTE: These instructions in this article do not apply to SQL Server 2000.
>The response from the server is :
>tempdb is skipped. You cannot run a query that requires tempdb.
>|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:otpn81df0cncebavp40flr7v071i8nn22b@.4ax.com...
> One of the trace flags settings is different on 2000 if you
> are following all of the steps but you can restore with
> move. Follow the trace flags in the following article or
> just use attach/detach as the article does:
> http://support.microsoft.com/?id=224071
I can still not do a restore with a move as described in my first posting.
if I am going to use detach/attach then we are back to my first posting..
which I want to avoid.|||Hi Christoffer,
As Sue said, I am afraid we will have to use detach and attach in this
scenario. I wanted to post a quick note to see if there is anything more I
could help you on this topic.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Restoring model and msdb databases goes to wrong file locations

Hi,
I am working on a script which should be able to restore a full sql server
2000 automatically. When I do a full restore of the system databases the
file locations of the mdf and ldf files don't change but when I restore the
differential backup the ldf file places it self in the default directory of
Microsoft SQL Server.
So my script looks like this:
restore database model from disk = 'c:\backup\model.bkf' with norecovery,
replace
go
restore database model from disk ='c:\backup\modeldiff.bkf' with
recovery,replace
go
The model log file should go into "e:\database\data\" but instead it goes
into "C:\Program Files\Microsoft SQL Server\MSSQL\Data"
why does it work fine when I do like this:
restore database model from disk = 'c:\backup\model.bkf' with recovery,
replace
but not when I want to do a diff restore?
I know I can detach and attach the model database, but then I also have to
stop and start the database with a trace flag - not very pretty...
Any ideas?
Christoffer
You can view the physical files from the backup using
restore filelistonly. If you need to have the files in
another location, use the move option in the restore script
to specify the physical file locations.
-Sue
On Tue, 17 May 2005 16:49:57 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:

>Hi,
>I am working on a script which should be able to restore a full sql server
>2000 automatically. When I do a full restore of the system databases the
>file locations of the mdf and ldf files don't change but when I restore the
>differential backup the ldf file places it self in the default directory of
>Microsoft SQL Server.
>So my script looks like this:
>restore database model from disk = 'c:\backup\model.bkf' with norecovery,
>replace
>go
>restore database model from disk ='c:\backup\modeldiff.bkf' with
>recovery,replace
>go
>The model log file should go into "e:\database\data\" but instead it goes
>into "C:\Program Files\Microsoft SQL Server\MSSQL\Data"
>why does it work fine when I do like this:
>restore database model from disk = 'c:\backup\model.bkf' with recovery,
>replace
>but not when I want to do a diff restore?
>I know I can detach and attach the model database, but then I also have to
>stop and start the database with a trace flag - not very pretty...
>
>Any ideas?
>Christoffer
>
|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:47dl81t4qahcv4l8gjbrb6c8oo934vl09p@.4ax.com...
> You can view the physical files from the backup using
> restore filelistonly.
Correct, I can view the paths with restore filelistonly and it shows the
correct file locations.

> If you need to have the files in
> another location, use the move option in the restore script
> to specify the physical file locations.
No, You cannot use the move option with system databases.
|||Yes you can use with move. You can follow the example in the
following knowledge base article:
http://support.microsoft.com/?id=304692
-Sue
On Wed, 18 May 2005 08:55:37 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:

>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:47dl81t4qahcv4l8gjbrb6c8oo934vl09p@.4ax.com.. .
>Correct, I can view the paths with restore filelistonly and it shows the
>correct file locations.
>No, You cannot use the move option with system databases.
>
|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:3s9m811b194jc1qs0v74kugnhqcfg2c4lo@.4ax.com...
> Yes you can use with move. You can follow the example in the
> following knowledge base article:
> http://support.microsoft.com/?id=304692
>
In the article is mentioned the following:
NOTE: These instructions in this article do not apply to SQL Server 2000.
The response from the server is :
tempdb is skipped. You cannot run a query that requires tempdb.
|||One of the trace flags settings is different on 2000 if you
are following all of the steps but you can restore with
move. Follow the trace flags in the following article or
just use attach/detach as the article does:
http://support.microsoft.com/?id=224071
-Sue
On Wed, 18 May 2005 15:35:57 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:

>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:3s9m811b194jc1qs0v74kugnhqcfg2c4lo@.4ax.com.. .
>In the article is mentioned the following:
>NOTE: These instructions in this article do not apply to SQL Server 2000.
>The response from the server is :
>tempdb is skipped. You cannot run a query that requires tempdb.
>
|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:otpn81df0cncebavp40flr7v071i8nn22b@.4ax.com...
> One of the trace flags settings is different on 2000 if you
> are following all of the steps but you can restore with
> move. Follow the trace flags in the following article or
> just use attach/detach as the article does:
> http://support.microsoft.com/?id=224071
I can still not do a restore with a move as described in my first posting.
if I am going to use detach/attach then we are back to my first posting..
which I want to avoid.
|||Hi Christoffer,
As Sue said, I am afraid we will have to use detach and attach in this
scenario. I wanted to post a quick note to see if there is anything more I
could help you on this topic.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Restoring model and msdb databases goes to wrong file locations

Hi,
I am working on a script which should be able to restore a full sql server
2000 automatically. When I do a full restore of the system databases the
file locations of the mdf and ldf files don't change but when I restore the
differential backup the ldf file places it self in the default directory of
Microsoft SQL Server.
So my script looks like this:
restore database model from disk = 'c:\backup\model.bkf' with norecovery,
replace
go
restore database model from disk ='c:\backup\modeldiff.bkf' with
recovery,replace
go
The model log file should go into "e:\database\data\" but instead it goes
into "C:\Program Files\Microsoft SQL Server\MSSQL\Data"
why does it work fine when I do like this:
restore database model from disk = 'c:\backup\model.bkf' with recovery,
replace
but not when I want to do a diff restore?
I know I can detach and attach the model database, but then I also have to
stop and start the database with a trace flag - not very pretty...
Any ideas?
ChristofferYou can view the physical files from the backup using
restore filelistonly. If you need to have the files in
another location, use the move option in the restore script
to specify the physical file locations.
-Sue
On Tue, 17 May 2005 16:49:57 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:

>Hi,
>I am working on a script which should be able to restore a full sql server
>2000 automatically. When I do a full restore of the system databases the
>file locations of the mdf and ldf files don't change but when I restore the
>differential backup the ldf file places it self in the default directory of
>Microsoft SQL Server.
>So my script looks like this:
>restore database model from disk = 'c:\backup\model.bkf' with norecovery,
>replace
>go
>restore database model from disk ='c:\backup\modeldiff.bkf' with
>recovery,replace
>go
>The model log file should go into "e:\database\data\" but instead it goes
>into "C:\Program Files\Microsoft SQL Server\MSSQL\Data"
>why does it work fine when I do like this:
>restore database model from disk = 'c:\backup\model.bkf' with recovery,
>replace
>but not when I want to do a diff restore?
>I know I can detach and attach the model database, but then I also have to
>stop and start the database with a trace flag - not very pretty...
>
>Any ideas?
>Christoffer
>|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:47dl81t4qahcv4l8gjbrb6c8oo934vl09p@.
4ax.com...
> You can view the physical files from the backup using
> restore filelistonly.
Correct, I can view the paths with restore filelistonly and it shows the
correct file locations.

> If you need to have the files in
> another location, use the move option in the restore script
> to specify the physical file locations.
No, You cannot use the move option with system databases.|||Yes you can use with move. You can follow the example in the
following knowledge base article:
http://support.microsoft.com/?id=304692
-Sue
On Wed, 18 May 2005 08:55:37 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:

>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:47dl81t4qahcv4l8gjbrb6c8oo934vl09p@.
4ax.com...
>Correct, I can view the paths with restore filelistonly and it shows the
>correct file locations.
>
>No, You cannot use the move option with system databases.
>|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:3s9m811b194jc1qs0v74kugnhqcfg2c4lo@.
4ax.com...
> Yes you can use with move. You can follow the example in the
> following knowledge base article:
> http://support.microsoft.com/?id=304692
>
In the article is mentioned the following:
NOTE: These instructions in this article do not apply to SQL Server 2000.
The response from the server is :
tempdb is skipped. You cannot run a query that requires tempdb.|||One of the trace flags settings is different on 2000 if you
are following all of the steps but you can restore with
move. Follow the trace flags in the following article or
just use attach/detach as the article does:
http://support.microsoft.com/?id=224071
-Sue
On Wed, 18 May 2005 15:35:57 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:

>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:3s9m811b194jc1qs0v74kugnhqcfg2c4lo@.
4ax.com...
>In the article is mentioned the following:
>NOTE: These instructions in this article do not apply to SQL Server 2000.
>The response from the server is :
>tempdb is skipped. You cannot run a query that requires tempdb.
>|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:otpn81df0cncebavp40flr7v071i8nn22b@.
4ax.com...
> One of the trace flags settings is different on 2000 if you
> are following all of the steps but you can restore with
> move. Follow the trace flags in the following article or
> just use attach/detach as the article does:
> http://support.microsoft.com/?id=224071
I can still not do a restore with a move as described in my first posting.
if I am going to use detach/attach then we are back to my first posting..
which I want to avoid.|||Hi Christoffer,
As Sue said, I am afraid we will have to use detach and attach in this
scenario. I wanted to post a quick note to see if there is anything more I
could help you on this topic.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Friday, March 9, 2012

Restoring from non-truncated transaction log

We have a database which unfortunately someone has restored from a backup
which was 2+ months old after he ran a script against the database that had
negative effects. So, now there is a gap in the data from Nov23 â' Jan 31.
What I am wondering is would it be possible to restore the missing data in
the database with the transaction log? We were not backing up the
transaction log; so, it was not truncated. We have detached the database and
the transaction log and reattached them on another server.
For some reason every time I explain this in person, people miss something.
So, here are the basic facts:
MS SQL server 2000 database restored from backup
2 months of missing data
in tact(not truncated) transaction log
a script was run against the database before it was restored from backup on
Jan 31
database structure is identical.
Is this savable data, or should we just give up. I am not a DBA. He quit
about 1.5 months ago; so please be specific about what to do as I am not yet
extreamly familiar with SQL server 2000. By the way we have been working on
this since the 31st, and all we know for sure is that SQL doesn't like it
when you do something like this. Thanks in advance for any guidance either
way.> We have a database which unfortunately someone has restored from a backup
> which was 2+ months old after he ran a script against the database that had
> negative effects.
That restore overwrites everything inside the database. Including the entries in the transaction
log. This of a restore like applying a VM Ware image on a machine.
> What I am wondering is would it be possible to restore the missing data in
> the database with the transaction log?
Are you saying that you saved something, somehow (backup, detach) before that restore was performed?
If not, I fail to see anyway to get anything back. Unelss you have some type of backup which is more
recent that the 2 month old backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Support" <Support@.discussions.microsoft.com> wrote in message
news:A8AE3D87-6975-444D-BD77-23407F529DEE@.microsoft.com...
> We have a database which unfortunately someone has restored from a backup
> which was 2+ months old after he ran a script against the database that had
> negative effects. So, now there is a gap in the data from Nov23 - Jan 31.
> What I am wondering is would it be possible to restore the missing data in
> the database with the transaction log? We were not backing up the
> transaction log; so, it was not truncated. We have detached the database and
> the transaction log and reattached them on another server.
> For some reason every time I explain this in person, people miss something.
> So, here are the basic facts:
> MS SQL server 2000 database restored from backup
> 2 months of missing data
> in tact(not truncated) transaction log
> a script was run against the database before it was restored from backup on
> Jan 31
> database structure is identical.
> Is this savable data, or should we just give up. I am not a DBA. He quit
> about 1.5 months ago; so please be specific about what to do as I am not yet
> extreamly familiar with SQL server 2000. By the way we have been working on
> this since the 31st, and all we know for sure is that SQL doesn't like it
> when you do something like this. Thanks in advance for any guidance either
> way.|||> That restore overwrites everything inside the database. Including the
entries in the transaction
> log. This of a restore like applying a VM Ware image on a machine.
Like I said I am not very experienced with SQL. Please forgive my ignorance
in this matter. We were not backing up the Transaction log, just the
database. When the restore was done it wasn't restoring the transaction log.
Does it remove the entries from the transaction log when you restore the
database form a backup even if it doesn't restore the transaction log? I
would think that would be the opposite of what the transaction log was
supposed to do.
"Tibor Karaszi" wrote:
> > We have a database which unfortunately someone has restored from a backup
> > which was 2+ months old after he ran a script against the database that had
> > negative effects.
> That restore overwrites everything inside the database. Including the entries in the transaction
> log. This of a restore like applying a VM Ware image on a machine.
>
> > What I am wondering is would it be possible to restore the missing data in
> > the database with the transaction log?
> Are you saying that you saved something, somehow (backup, detach) before that restore was performed?
> If not, I fail to see anyway to get anything back. Unelss you have some type of backup which is more
> recent that the 2 month old backup.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Support" <Support@.discussions.microsoft.com> wrote in message
> news:A8AE3D87-6975-444D-BD77-23407F529DEE@.microsoft.com...
> > We have a database which unfortunately someone has restored from a backup
> > which was 2+ months old after he ran a script against the database that had
> > negative effects. So, now there is a gap in the data from Nov23 - Jan 31.
> > What I am wondering is would it be possible to restore the missing data in
> > the database with the transaction log? We were not backing up the
> > transaction log; so, it was not truncated. We have detached the database and
> > the transaction log and reattached them on another server.
> >
> > For some reason every time I explain this in person, people miss something.
> > So, here are the basic facts:
> >
> > MS SQL server 2000 database restored from backup
> > 2 months of missing data
> > in tact(not truncated) transaction log
> > a script was run against the database before it was restored from backup on
> > Jan 31
> > database structure is identical.
> >
> > Is this savable data, or should we just give up. I am not a DBA. He quit
> > about 1.5 months ago; so please be specific about what to do as I am not yet
> > extreamly familiar with SQL server 2000. By the way we have been working on
> > this since the 31st, and all we know for sure is that SQL doesn't like it
> > when you do something like this. Thanks in advance for any guidance either
> > way.
>
>|||Hi,
In my opinion, if
1- the DB was in Full recovery mode
and
2- you have have the complete T-log since Nov
then it will work.
But I would be surprised if you meet both requirement
Chris
"Support" <Support@.discussions.microsoft.com> a écrit dans le message de
news:A8AE3D87-6975-444D-BD77-23407F529DEE@.microsoft.com...
> We have a database which unfortunately someone has restored from a backup
> which was 2+ months old after he ran a script against the database that
had
> negative effects. So, now there is a gap in the data from Nov23 - Jan 31.
> What I am wondering is would it be possible to restore the missing data in
> the database with the transaction log? We were not backing up the
> transaction log; so, it was not truncated. We have detached the database
and
> the transaction log and reattached them on another server.
> For some reason every time I explain this in person, people miss
something.
> So, here are the basic facts:
> MS SQL server 2000 database restored from backup
> 2 months of missing data
> in tact(not truncated) transaction log
> a script was run against the database before it was restored from backup
on
> Jan 31
> database structure is identical.
> Is this savable data, or should we just give up. I am not a DBA. He quit
> about 1.5 months ago; so please be specific about what to do as I am not
yet
> extreamly familiar with SQL server 2000. By the way we have been working
on
> this since the 31st, and all we know for sure is that SQL doesn't like it
> when you do something like this. Thanks in advance for any guidance
either
> way.|||>> 1- the DB was in Full recovery mode
Sorry about this. Like I said, I am not very experienced with SQL. I had
NOT been backing up the transaction log, so I would assume the transaction
log would still have all of that data in it. Does it remove data from the
transaction log when you restore a database but not the transaction log? As
far as the "Full recovery mode" goes, do you mean backup the entire database
every time. We do backup the entire database as opposed to just the changes,
but not the transaction logs. Is this what you are referring to, or is it a
setting on the restore?
"Chris" wrote:
> Hi,
> In my opinion, if
> 1- the DB was in Full recovery mode
> and
> 2- you have have the complete T-log since Nov
> then it will work.
> But I would be surprised if you meet both requirement
> Chris
>
> "Support" <Support@.discussions.microsoft.com> a Ã©crit dans le message de
> news:A8AE3D87-6975-444D-BD77-23407F529DEE@.microsoft.com...
> > We have a database which unfortunately someone has restored from a backup
> > which was 2+ months old after he ran a script against the database that
> had
> > negative effects. So, now there is a gap in the data from Nov23 - Jan 31.
> > What I am wondering is would it be possible to restore the missing data in
> > the database with the transaction log? We were not backing up the
> > transaction log; so, it was not truncated. We have detached the database
> and
> > the transaction log and reattached them on another server.
> >
> > For some reason every time I explain this in person, people miss
> something.
> > So, here are the basic facts:
> >
> > MS SQL server 2000 database restored from backup
> > 2 months of missing data
> > in tact(not truncated) transaction log
> > a script was run against the database before it was restored from backup
> on
> > Jan 31
> > database structure is identical.
> >
> > Is this savable data, or should we just give up. I am not a DBA. He quit
> > about 1.5 months ago; so please be specific about what to do as I am not
> yet
> > extreamly familiar with SQL server 2000. By the way we have been working
> on
> > this since the 31st, and all we know for sure is that SQL doesn't like it
> > when you do something like this. Thanks in advance for any guidance
> either
> > way.
>
>|||Hi - I suggest you read the Books Online topics on recovery models
('Selecting a Recovery Model' is a good start) as that will help you
understand the issues and alternatives you have depending on what recovery
model you're using.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"2 Struggling Admins" <2StrugglingAdmins@.discussions.microsoft.com> wrote in
message news:BABCE850-09CC-4766-A370-71360F7C9C8C@.microsoft.com...
> >> 1- the DB was in Full recovery mode
> Sorry about this. Like I said, I am not very experienced with SQL. I had
> NOT been backing up the transaction log, so I would assume the transaction
> log would still have all of that data in it. Does it remove data from the
> transaction log when you restore a database but not the transaction log?
As
> far as the "Full recovery mode" goes, do you mean backup the entire
database
> every time. We do backup the entire database as opposed to just the
changes,
> but not the transaction logs. Is this what you are referring to, or is it
a
> setting on the restore?
> "Chris" wrote:
> > Hi,
> >
> > In my opinion, if
> > 1- the DB was in Full recovery mode
> > and
> > 2- you have have the complete T-log since Nov
> >
> > then it will work.
> >
> > But I would be surprised if you meet both requirement
> > Chris
> >
> >
> > "Support" <Support@.discussions.microsoft.com> a écrit dans le message de
> > news:A8AE3D87-6975-444D-BD77-23407F529DEE@.microsoft.com...
> > > We have a database which unfortunately someone has restored from a
backup
> > > which was 2+ months old after he ran a script against the database
that
> > had
> > > negative effects. So, now there is a gap in the data from Nov23 - Jan
31.
> > > What I am wondering is would it be possible to restore the missing
data in
> > > the database with the transaction log? We were not backing up the
> > > transaction log; so, it was not truncated. We have detached the
database
> > and
> > > the transaction log and reattached them on another server.
> > >
> > > For some reason every time I explain this in person, people miss
> > something.
> > > So, here are the basic facts:
> > >
> > > MS SQL server 2000 database restored from backup
> > > 2 months of missing data
> > > in tact(not truncated) transaction log
> > > a script was run against the database before it was restored from
backup
> > on
> > > Jan 31
> > > database structure is identical.
> > >
> > > Is this savable data, or should we just give up. I am not a DBA. He
quit
> > > about 1.5 months ago; so please be specific about what to do as I am
not
> > yet
> > > extreamly familiar with SQL server 2000. By the way we have been
working
> > on
> > > this since the 31st, and all we know for sure is that SQL doesn't like
it
> > > when you do something like this. Thanks in advance for any guidance
> > either
> > > way.
> >
> >
> >|||The transaction log is there to support transactional consistency (rollbacks and recovery) as well
as supporting "incremental" backup (backup of the transaction log).
A database backup contains both data pages as well as the necessary log records (from the
transaction log file(s)). This is so that SQL Server when you restore can apply the data pages and
perform recovery (roll -forward and -back). When your restore a database backup, the contents of the
ldf file of the current database will be overwritten. I agree with Paul R regarding reading up on
the suggested section on Books Online. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"2 Struggling Admins" <2StrugglingAdmins@.discussions.microsoft.com> wrote in message
news:12970127-DB33-4D7B-ADE7-7136BD17E4F8@.microsoft.com...
>> That restore overwrites everything inside the database. Including the
> entries in the transaction
>> log. This of a restore like applying a VM Ware image on a machine.
> Like I said I am not very experienced with SQL. Please forgive my ignorance
> in this matter. We were not backing up the Transaction log, just the
> database. When the restore was done it wasn't restoring the transaction log.
> Does it remove the entries from the transaction log when you restore the
> database form a backup even if it doesn't restore the transaction log? I
> would think that would be the opposite of what the transaction log was
> supposed to do.
>
> "Tibor Karaszi" wrote:
>> > We have a database which unfortunately someone has restored from a backup
>> > which was 2+ months old after he ran a script against the database that had
>> > negative effects.
>> That restore overwrites everything inside the database. Including the entries in the transaction
>> log. This of a restore like applying a VM Ware image on a machine.
>>
>> > What I am wondering is would it be possible to restore the missing data in
>> > the database with the transaction log?
>> Are you saying that you saved something, somehow (backup, detach) before that restore was
>> performed?
>> If not, I fail to see anyway to get anything back. Unelss you have some type of backup which is
>> more
>> recent that the 2 month old backup.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Support" <Support@.discussions.microsoft.com> wrote in message
>> news:A8AE3D87-6975-444D-BD77-23407F529DEE@.microsoft.com...
>> > We have a database which unfortunately someone has restored from a backup
>> > which was 2+ months old after he ran a script against the database that had
>> > negative effects. So, now there is a gap in the data from Nov23 - Jan 31.
>> > What I am wondering is would it be possible to restore the missing data in
>> > the database with the transaction log? We were not backing up the
>> > transaction log; so, it was not truncated. We have detached the database and
>> > the transaction log and reattached them on another server.
>> >
>> > For some reason every time I explain this in person, people miss something.
>> > So, here are the basic facts:
>> >
>> > MS SQL server 2000 database restored from backup
>> > 2 months of missing data
>> > in tact(not truncated) transaction log
>> > a script was run against the database before it was restored from backup on
>> > Jan 31
>> > database structure is identical.
>> >
>> > Is this savable data, or should we just give up. I am not a DBA. He quit
>> > about 1.5 months ago; so please be specific about what to do as I am not yet
>> > extreamly familiar with SQL server 2000. By the way we have been working on
>> > this since the 31st, and all we know for sure is that SQL doesn't like it
>> > when you do something like this. Thanks in advance for any guidance either
>> > way.
>>

Restoring from non-truncated transaction log

Tuesday, February 21, 2012

Restoring Database using Scripts

I have and MSDE environment and I use "OSQL" to administer the database. I've written a backup script and restore script. My problem/question as to do with the restore. Both scripts run just fine.
However, I want my restore to use the lastest and greatest backup set within the backup device. I never really know what the lastest backup set is most of the time. But when ever my restore process runs I want it to use the lastest backup within the back
up device. How do I specify in my script to use the lastest backup set when a restore is done?
Thanks
Hi,
If you are going to restore in the same server see the MSDB..BACKUPSET table
for position. See the below query.
select database_name,backup_finish_date,position from msdb..backupset where
database_name='msdb'
If you are doing it in a different machne use the below command.
restore headeronly from disk='c:\msdb.bak'
You can restore the latest backup based on position
Thanks
Hari
MCDBA
"Larry Bird" <LarryBird@.discussions.microsoft.com> wrote in message
news:C6B6DEC9-1CA4-4624-A6F9-2A7004037BC1@.microsoft.com...
> I have and MSDE environment and I use "OSQL" to administer the database.
I've written a backup script and restore script. My problem/question as to
do with the restore. Both scripts run just fine.
> However, I want my restore to use the lastest and greatest backup set
within the backup device. I never really know what the lastest backup set
is most of the time. But when ever my restore process runs I want it to use
the lastest backup within the backup device. How do I specify in my script
to use the lastest backup set when a restore is done?
> Thanks
|||hi Larry,
"Larry Bird" <LarryBird@.discussions.microsoft.com> ha scritto nel messaggio
news:C6B6DEC9-1CA4-4624-A6F9-2A7004037BC1@.microsoft.com...
> I have and MSDE environment and I use "OSQL" to administer the database.
>I've written a backup script and restore script. My problem/question as to
do with
>the restore. Both scripts run just fine.
> However, I want my restore to use the lastest and greatest backup set
within the
> backup device. I never really know what the lastest backup set is most of
the time.
>But when ever my restore process runs I want it to use the lastest backup
within the
>backup device. How do I specify in my script to use the lastest backup set
when a
>restore is done?
Im'm late, and Hari already pointed out what needed, but, anyway =;-D
I woul'd not automatically scritp this... restore shoul'd be better a manual
operation as it's not a dayly house keeping activity...
anyway... you can start from getting the max file position...
SET NOCOUNT ON
DECLARE @.db VARCHAR(128)
DECLARE @.file VARCHAR(128)
DECLARE @.cmd VARCHAR(1024)
SELECT @.file = 'D:\ADOvb6\VBH_FILE\vbhotel.bak' , @.db = 'vbhotel'
SET @.cmd = 'RESTORE HEADERONLY FROM DISK = '''+ @.file + ''''
CREATE TABLE #restore (
BackupName VARCHAR(128) ,
BackupDescription VARCHAR(128) ,
BackupType INT ,
ExpirationDate DATETIME ,
Compressed INT ,
Position INT ,
DeviceType INT ,
UserName VARCHAR(128) ,
ServerName VARCHAR(128) ,
DatabaseName VARCHAR(128) ,
DatabaseVersion INT ,
DatabaseCreationDate DATETIME ,
BackupSize INT ,
FirstLsn VARCHAR(128) ,
LastLsn VARCHAR(128) ,
CheckPointLsn VARCHAR(128) ,
DifferentialBaseLsn VARCHAR(128) ,
BackupStartDate DATETIME ,
BackupFinishDate DATETIME ,
SortOrder SMALLINT ,
CodePage SMALLINT ,
UnicodeLocaleId INT ,
UnicodeComparisonStyle INT ,
CompatibilityLevel TINYINT ,
SoftwareVendorId INT ,
SoftwareVersionMajor INT ,
SoftwareVersionMinor INT ,
SoftwareVersionBuild INT ,
MachineName NVARCHAR(128) ,
Flags INT ,
BindingId uniqueidentifier ,
RecoveryForkId uniqueidentifier ,
Collation NVARCHAR(128)
)
INSERT INTO #restore EXEC(@.cmd)
SELECT MAX(r.Position) AS [MaxFile]
FROM #restore r
WHERE r.BackupType = 1
AND r.DatabaseName = @.db
AND r.MachineName = @.@.SERVERNAME
GO
DROP TABLE #restore
--<--
MaxFile
1
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Restoring database to a different name and location

Can someone please tell me what's wrong with my script below? I am trying to restore a database backup to a different database name and location.

RESTORE DATABASE test
FROM 'C:\mybackup.bak'
WITH MOVE 'h_data' TO 'C:\Program Files\SQL2000\MSSQL\Data\Test_data.mdf'

Thanks

SHKWhat error are you getting?

I do the following

ALTER DATABASE TaxReconDB_Prod SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE TaxReconDB_Prod
FROM DISK = 'D:\Tax\BackUp\TaxReconDB.dmp'
WITH MOVE 'TaxReconDB_Data' TO 'D:\Database\NJROS1D151DEV\MSSQL$NJROS1D151DEV\Dat a\TaxReconDB_Prod_Data.MDF'
, MOVE 'TaxReconDB_Log' TO 'D:\Database\NJROS1D151DEV\MSSQL$NJROS1D151DEV\Dat a\TaxReconDB_Prod_Log.LDF'
, REPLACE|||The error said that I have a syntax error on line 2.

I tried

FROM DISK = 'C:\Mybackup.bak"

and I got an error "Cannot open backup device", which makes sense because the backup is not from a disk.

then I tried

FROM FILE = 'C:\MyBackup.bak"

and I got a syntax error.

Help. I am not familiar with restoring database at all.

Thanks

SHK|||The following is correct:

FROM DISK = 'C:\Mybackup.bak"

As you are getting "Cannot open backup device", looks like you have mispelled the file name or missed a blank or something...check again|||Check the ending double quote:::
FROM DISK = 'C:\Mybackup.bak"|||I double checked and the spelling is correct.

It is a single quote at the end of the FROM DISK.

I am out of ideas.

SHK|||Ok, do this

RESTORE FILELISTONLY FROM DISK = 'C:\...'

btw where is the back up file physically at?

What happend when you run

master..xp_cmdshell 'DIR C:\*.bak'

What do you see?|||I got it to work.

I copied the backup from the network to my C drive and wanted to restore the backup to my MSDE 2000. I was trying to run the script from the network when I got all those errors. I was playing around and figured that maybe I could try to run the script locally and there it ran.
I guess the script failed because the backup was on my C drive and I tried to run it from the network server.

Thanks for all the suggestions and helps.

SHK

Monday, March 26, 2012

restoring the database has failed

Tuesday, March 20, 2012

Restoring model and msdb databases goes to wrong file locations

Restoring model and msdb databases goes to wrong file locations

Restoring model and msdb databases goes to wrong file locations

Friday, March 9, 2012

Restoring from non-truncated transaction log

Restoring from non-truncated transaction log

Restoring from non-truncated transaction log

Tuesday, February 21, 2012

Restoring Database using Scripts

Restoring database to a different name and location

database problem

Blog Archive

About Me