restore master database: following

Showing posts with label following. Show all posts

Wednesday, March 28, 2012

Restore using Microsoft SQL Server Management Studio?

Can someone help me with restoring a database using sql server
management studio? Every time I attempt to restore a database, I get
the following message:
Restore failed for Server 'localhost\SQLExpress'.
Additional Information:
System.Data.SqlClient.SqlError: Exclusive access could not be
obtained because the database is in use.
I have no problem restarting the db server locally, but in a
production environment where the db is shared, I cannot restart it.
How can I restore a db successfully using the management studio?
Perhaps MS should add a checkbox to the restore dialog that kills all
processes before the restore is issued...
Anyway, thanks for any help
JimHi
It seems that someone is using the database and therefore stopping you
restoring over it. Look at the current activity on the server and check to
see who is connected. You can either kill the process of get the to logoff
properly.
John
"Jim Geurts" wrote:
> Can someone help me with restoring a database using sql server
> management studio? Every time I attempt to restore a database, I get
> the following message:
> Restore failed for Server 'localhost\SQLExpress'.
> Additional Information:
> System.Data.SqlClient.SqlError: Exclusive access could not be
> obtained because the database is in use.
>
> I have no problem restarting the db server locally, but in a
> production environment where the db is shared, I cannot restart it.
> How can I restore a db successfully using the management studio?
> Perhaps MS should add a checkbox to the restore dialog that kills all
> processes before the restore is issued...
> Anyway, thanks for any help
> Jim
>|||On Wed, 31 Oct 2007 07:41:02 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:
>Hi
>It seems that someone is using the database and therefore stopping you
>restoring over it. Look at the current activity on the server and check to
>see who is connected. You can either kill the process of get the to logoff
>properly.
>John
And don't forget to allow for it being yourself! 8-)
Roy Harvey
Beacon Falls, CT|||I would first set the database to single-user mode or restricted user during
restore if you are encountering such errors
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:A29E8900-FD44-4493-84DD-CDCBB0D16F6D@.microsoft.com...
> Hi
> It seems that someone is using the database and therefore stopping you
> restoring over it. Look at the current activity on the server and check to
> see who is connected. You can either kill the process of get the to logoff
> properly.
> John
> "Jim Geurts" wrote:
>> Can someone help me with restoring a database using sql server
>> management studio? Every time I attempt to restore a database, I get
>> the following message:
>> Restore failed for Server 'localhost\SQLExpress'.
>> Additional Information:
>> System.Data.SqlClient.SqlError: Exclusive access could not be
>> obtained because the database is in use.
>>
>> I have no problem restarting the db server locally, but in a
>> production environment where the db is shared, I cannot restart it.
>> How can I restore a db successfully using the management studio?
>> Perhaps MS should add a checkbox to the restore dialog that kills all
>> processes before the restore is issued...
>> Anyway, thanks for any help
>> Jim
>>|||On Oct 31, 10:04 am, "bass_player [SBS-MVP]" <bass_pla...@.mvps.org>
wrote:
> I would first set the database to single-user mode or restricted user during
> restore if you are encountering such errors
> "John Bell" <jbellnewspo...@.hotmail.com> wrote in message
> news:A29E8900-FD44-4493-84DD-CDCBB0D16F6D@.microsoft.com...
> > Hi
> > It seems that someone is using the database and therefore stopping you
> > restoring over it. Look at the current activity on the server and check to
> > see who is connected. You can either kill the process of get the to logoff
> > properly.
> > John
> > "Jim Geurts" wrote:
> >> Can someone help me with restoring a database using sql server
> >> management studio? Every time I attempt to restore a database, I get
> >> the following message:
> >> Restore failed for Server 'localhost\SQLExpress'.
> >> Additional Information:
> >> System.Data.SqlClient.SqlError: Exclusive access could not be
> >> obtained because the database is in use.
> >> I have no problem restarting the db server locally, but in a
> >> production environment where the db is shared, I cannot restart it.
> >> How can I restore a db successfully using the management studio?
> >> Perhaps MS should add a checkbox to the restore dialog that kills all
> >> processes before the restore is issued...
> >> Anyway, thanks for any help
> >> Jim
I tried setting the production db to single user mode in the past, but
then it wouldn't let me connect to restore the db. Honestly, there
has to be a simple way of doing this, no? This seems like a fairly
common task. I'm amazed that it's so difficult to have it work with a
click of a button, using the management studio. Is there a block of
sql that will accomplish everything that is needed (set to single user
mode, restore db, restore back to multi-user mode, etc)?|||On Oct 31, 9:55 am, Jim Geurts <jgeu...@.gmail.com> wrote:
> Can someone help me with restoring a database using sql server
> management studio? Every time I attempt to restore a database, I get
> the following message:
> Restore failed for Server 'localhost\SQLExpress'.
> Additional Information:
> System.Data.SqlClient.SqlError: Exclusive access could not be
> obtained because the database is in use.
> I have no problem restarting the db server locally, but in a
> production environment where the db is shared, I cannot restart it.
> How can I restore a db successfully using the management studio?
> Perhaps MS should add a checkbox to the restore dialog that kills all
> processes before the restore is issued...
> Anyway, thanks for any help
> Jim
Here is another one of the many ways:
Detach the Database. OR
Kill all conections in the Database and Run the Restore T-SQL(Use With
Replace) or thourgh Studio
How critical is this DB ( How many users are connected at one time ),
if not many users, sut them off(Kill) the then restoe the DB|||Hi
You should not be connected to the database to restore, if the database is
the default for your login it may be an issue connecting once you have a
database in single user mode and the "connection" is used. SQL2000 used to
have a disconnect button on the detach dialog which could be used to remove
connections but this is not available on SQL2005.
John
"Jim Geurts" wrote:
> On Oct 31, 10:04 am, "bass_player [SBS-MVP]" <bass_pla...@.mvps.org>
> wrote:
> > I would first set the database to single-user mode or restricted user during
> > restore if you are encountering such errors
> >
> > "John Bell" <jbellnewspo...@.hotmail.com> wrote in message
> >
> > news:A29E8900-FD44-4493-84DD-CDCBB0D16F6D@.microsoft.com...
> >
> > > Hi
> >
> > > It seems that someone is using the database and therefore stopping you
> > > restoring over it. Look at the current activity on the server and check to
> > > see who is connected. You can either kill the process of get the to logoff
> > > properly.
> >
> > > John
> >
> > > "Jim Geurts" wrote:
> >
> > >> Can someone help me with restoring a database using sql server
> > >> management studio? Every time I attempt to restore a database, I get
> > >> the following message:
> >
> > >> Restore failed for Server 'localhost\SQLExpress'.
> >
> > >> Additional Information:
> > >> System.Data.SqlClient.SqlError: Exclusive access could not be
> > >> obtained because the database is in use.
> >
> > >> I have no problem restarting the db server locally, but in a
> > >> production environment where the db is shared, I cannot restart it.
> > >> How can I restore a db successfully using the management studio?
> > >> Perhaps MS should add a checkbox to the restore dialog that kills all
> > >> processes before the restore is issued...
> >
> > >> Anyway, thanks for any help
> >
> > >> Jim
> I tried setting the production db to single user mode in the past, but
> then it wouldn't let me connect to restore the db. Honestly, there
> has to be a simple way of doing this, no? This seems like a fairly
> common task. I'm amazed that it's so difficult to have it work with a
> click of a button, using the management studio. Is there a block of
> sql that will accomplish everything that is needed (set to single user
> mode, restore db, restore back to multi-user mode, etc)?
>|||You can use the toolbar button to script the restore that you are attempting
to do in SSMS. Then add to the top of that script the following command:
ALTER DATABASE [YourDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
After the backup place it back in Multi-user mode.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jim Geurts" <jgeurts@.gmail.com> wrote in message
news:1193844965.038551.326520@.e9g2000prf.googlegroups.com...
> On Oct 31, 10:04 am, "bass_player [SBS-MVP]" <bass_pla...@.mvps.org>
> wrote:
>> I would first set the database to single-user mode or restricted user
>> during
>> restore if you are encountering such errors
>> "John Bell" <jbellnewspo...@.hotmail.com> wrote in message
>> news:A29E8900-FD44-4493-84DD-CDCBB0D16F6D@.microsoft.com...
>> > Hi
>> > It seems that someone is using the database and therefore stopping you
>> > restoring over it. Look at the current activity on the server and check
>> > to
>> > see who is connected. You can either kill the process of get the to
>> > logoff
>> > properly.
>> > John
>> > "Jim Geurts" wrote:
>> >> Can someone help me with restoring a database using sql server
>> >> management studio? Every time I attempt to restore a database, I get
>> >> the following message:
>> >> Restore failed for Server 'localhost\SQLExpress'.
>> >> Additional Information:
>> >> System.Data.SqlClient.SqlError: Exclusive access could not be
>> >> obtained because the database is in use.
>> >> I have no problem restarting the db server locally, but in a
>> >> production environment where the db is shared, I cannot restart it.
>> >> How can I restore a db successfully using the management studio?
>> >> Perhaps MS should add a checkbox to the restore dialog that kills all
>> >> processes before the restore is issued...
>> >> Anyway, thanks for any help
>> >> Jim
> I tried setting the production db to single user mode in the past, but
> then it wouldn't let me connect to restore the db. Honestly, there
> has to be a simple way of doing this, no? This seems like a fairly
> common task. I'm amazed that it's so difficult to have it work with a
> click of a button, using the management studio. Is there a block of
> sql that will accomplish everything that is needed (set to single user
> mode, restore db, restore back to multi-user mode, etc)?
>|||On Oct 31, 12:40 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> You can use the toolbar button to script the restore that you are attempting
> to do in SSMS. Then add to the top of that script the following command:
> ALTER DATABASE [YourDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> After the backup place it back in Multi-user mode.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
> "Jim Geurts" <jgeu...@.gmail.com> wrote in message
> news:1193844965.038551.326520@.e9g2000prf.googlegroups.com...
> > On Oct 31, 10:04 am, "bass_player [SBS-MVP]" <bass_pla...@.mvps.org>
> > wrote:
> >> I would first set the database to single-user mode or restricted user
> >> during
> >> restore if you are encountering such errors
> >> "John Bell" <jbellnewspo...@.hotmail.com> wrote in message
> >>news:A29E8900-FD44-4493-84DD-CDCBB0D16F6D@.microsoft.com...
> >> > Hi
> >> > It seems that someone is using the database and therefore stopping you
> >> > restoring over it. Look at the current activity on the server and check
> >> > to
> >> > see who is connected. You can either kill the process of get the to
> >> > logoff
> >> > properly.
> >> > John
> >> > "Jim Geurts" wrote:
> >> >> Can someone help me with restoring a database using sql server
> >> >> management studio? Every time I attempt to restore a database, I get
> >> >> the following message:
> >> >> Restore failed for Server 'localhost\SQLExpress'.
> >> >> Additional Information:
> >> >> System.Data.SqlClient.SqlError: Exclusive access could not be
> >> >> obtained because the database is in use.
> >> >> I have no problem restarting the db server locally, but in a
> >> >> production environment where the db is shared, I cannot restart it.
> >> >> How can I restore a db successfully using the management studio?
> >> >> Perhaps MS should add a checkbox to the restore dialog that kills all
> >> >> processes before the restore is issued...
> >> >> Anyway, thanks for any help
> >> >> Jim
> > I tried setting the production db to single user mode in the past, but
> > then it wouldn't let me connect to restore the db. Honestly, there
> > has to be a simple way of doing this, no? This seems like a fairly
> > common task. I'm amazed that it's so difficult to have it work with a
> > click of a button, using the management studio. Is there a block of
> > sql that will accomplish everything that is needed (set to single user
> > mode, restore db, restore back to multi-user mode, etc)?
Cool, thanks Andrew. I'll give that a shot.
@.Maninder: The database server is very critical. It's at a hosting
provider and is shared with many other users. Thus, my login defaults
to my database and as John mentioned, that's probably why it always
says someone is connected. As far as my specific db, there are
usually only two or three applications that access it.

Restore TSQL Scripts

Hi
I would like to creat a job in MS SQL Server 2000 to do the following:
1. Drop Test_DB
2. Restore Current_DB (latest backup in my_dir) as Test_DB
Can anyone help me on this? I think it is a simple as
--start code
DROP DATABASE Test_DB
GO
RESTORE DATABASE Test_DB
FROM Current_DB ?
GO
--end code
It is the second line that bothers me. I want to restore the most
recent backup of Current_DB. How can I get and specify this info?
Also, when I use EM to restore it gives me the option to rename the
files, can I specify this too?
thanks
dabenTry something like:
RESTORE DATABASE Test_DB
FROM DISK='C:\My_Dir\Current_DB.bak'
WITH
MOVE 'Current_DB' TO 'E:\DataFiles\Test_DB.mdf',
MOVE 'Current_DB_Log' TO 'C:\LogFiles\Test_DB_Log.ldf',
REPLACE
GO
You find the names log the logical files for the above command with
RESTORE FILELISTONLY:
RESTORE FILELISTONLY
FROM DISK='C:\MyDir\Current_DB.bak'
GO
See RESTORE in the Books Online for more information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"daben" <dabenpb@.yahoo.com> wrote in message
news:1adbeff3.0309111224.1fe1b9ac@.posting.google.com...
> Hi
> I would like to creat a job in MS SQL Server 2000 to do the following:
> 1. Drop Test_DB
> 2. Restore Current_DB (latest backup in my_dir) as Test_DB
> Can anyone help me on this? I think it is a simple as
> --start code
> DROP DATABASE Test_DB
> GO
> RESTORE DATABASE Test_DB
> FROM Current_DB ?
> GO
> --end code
> It is the second line that bothers me. I want to restore the most
> recent backup of Current_DB. How can I get and specify this info?
> Also, when I use EM to restore it gives me the option to rename the
> files, can I specify this too?
> thanks
> daben

Monday, March 26, 2012

Restore Tranaction log

I am trying to restore a Transaction Log in SQL Server 2000 in Full recovery status, However I was received the following error Message:
----------------------------------------
Error message: Exclusive access could not be obtained because the database is in use.
----------------------------------------
Also I changed the UserAccess database from MULTI_USER to SINGLE_USER, but still do not work and shows the same error message.
Please let me know, if you have any solution for this problem.

Thanks,

Try the link below to test drive Lumigent. Hope this helps.
http://www.lumigent.com/

Restore to read-only mode

Hi all,

EM has the following restore option:

"Leave database read-only and able to restore additional transaction logs."

Using this, I can restore to a named transaction, then run a SELECT statement that outputs the current state of my data to a file while the db is still in load mode.

What is the T-SQL version of this EM property, if it exists. I'm using:

restore log MyDatabase from MyDiskDevice with file=1, norecovery,
stopatmark = 'mymark'

I can't find a read-only attribute in BOL though to use with the RESTORE statement. Can I do this in script?

Thanks,

BObALTER DATABASE <dbname> SET READ_ONLY WITH ROLLBACK IMMEDIATE|||Thanks Brett. I'll give it a try.

Bob

Wednesday, March 21, 2012

Restore single user mode

I have the following code to restore a database (it was not written by
me)
Dim ServerName As String
Dim DBName As String
Dim BackupToRestore As String
ServerName = "PENTIUM4\VIPER"
DBName = "master"
BackupToRestore = "ViperData\Bmaster"
Dim oServer As SQLDMO.SQLServer
Dim oRestore As SQLDMO.Restore
On Error GoTo Handler
'simple err checking
If ServerName = "" Or DBName = "" Or BackupToRestore = "" Then
MsgBox "You MUST provide server name, database name, and the name of
the bak file you want to restore", vbInformation + vbOKOnly, "Error"
Exit Sub
End If
'open connection to server
Set oServer = New SQLDMO.SQLServer
With oServer
.LoginSecure = True
.Connect ServerName
End With
'also need a restore object
Set oRestore = New SQLDMO.Restore
'use the 'with' construct to minimize property lookups
With oRestore
'this is where your backup will be restored to
.Database = DBName
'same as EM or TSQL, you can restore database, file, or log, here
we're going to
'use database
.Action = SQLDMORestore_Database
'this is the "force restore over existing database" option
.ReplaceDatabase = True
'this does a restore from a file instead of a device - note that we're
still
'restoring a database, NOT a file group
.Files = BackupToRestore
'do it
.SQLRestore oServer
End With
'standard clean up
Set oRestore = Nothing
oServer.DisConnect
Set oServer = Nothing
Exit Sub
Handler:
If MsgBox(Err.Description & ". Would you like to continue?",
vbInformation + vbYesNo) = vbYes Then
Resume Next
End If
I keep getting the error message that the sql server must be in single
use mode
The serve comes with a program I purchased and is controlled by
Microsoft service manager 8.00.760
How do i run it in single user mode?It sounds like you are trying to restore the database "Master" To =restore master the entire SQl server must be in single user mode. This =means starting SQl server from a command prompt or adjusting the startup =parameters for running it a a service. From a command line is simplest - =sqlservr -m.
Full detail;s of starting in single user mode are in BOL.
Mike John
<NoNothing@.aol.com> wrote in message =news:3kt3101tic672lvlp4fj78tepkqg4pviuf@.4ax.com...
> I have the following code to restore a database (it was not written by
> me)
> > Dim ServerName As String
> Dim DBName As String
> Dim BackupToRestore As String
> ServerName =3D "PENTIUM4\VIPER"
> DBName =3D "master"
> BackupToRestore =3D "ViperData\Bmaster"
> > Dim oServer As SQLDMO.SQLServer
> Dim oRestore As SQLDMO.Restore
> On Error GoTo Handler
> > 'simple err checking
> If ServerName =3D "" Or DBName =3D "" Or BackupToRestore =3D "" Then
> MsgBox "You MUST provide server name, database name, and the name of
> the bak file you want to restore", vbInformation + vbOKOnly, "Error"
> Exit Sub
> End If
> > 'open connection to server
> Set oServer =3D New SQLDMO.SQLServer
> With oServer
> .LoginSecure =3D True
> .Connect ServerName
> End With
> > 'also need a restore object
> Set oRestore =3D New SQLDMO.Restore
> > 'use the 'with' construct to minimize property lookups
> With oRestore
> 'this is where your backup will be restored to
> .Database =3D DBName
> 'same as EM or TSQL, you can restore database, file, or log, here
> we're going to
> 'use database
> .Action =3D SQLDMORestore_Database
> 'this is the "force restore over existing database" option
> .ReplaceDatabase =3D True
> 'this does a restore from a file instead of a device - note that we're
> still
> 'restoring a database, NOT a file group
> .Files =3D BackupToRestore
> 'do it
> .SQLRestore oServer
> End With
> > 'standard clean up
> Set oRestore =3D Nothing
> oServer.DisConnect
> Set oServer =3D Nothing
> > Exit Sub
> > Handler:
> If MsgBox(Err.Description & ". Would you like to continue?",
> vbInformation + vbYesNo) =3D vbYes Then
> Resume Next
> End If
> > > > I keep getting the error message that the sql server must be in single
> use mode
> > The serve comes with a program I purchased and is controlled by
> Microsoft service manager 8.00.760
> > How do i run it in single user mode?
>|||Yes I had read apbout that however two things. First to start my
server I need to type sqlservr.exe NOT sqlmangr.exe and when I type
sqlservr.exe -m I still get the error message
Am I using a different version of MS sqlmanager?
On Sat, 24 Jan 2004 08:23:04 -0000, "Mike John"
<Mike.John@.knowledgepool.spamtrap.com> wrote:
>It sounds like you are trying to restore the database "Master" To restore master the entire SQl server must be in single user mode. This means starting SQl server from a command prompt or adjusting the startup parameters for running it a a service. From a command line is simplest - sqlservr -m.
>Full detail;s of starting in single user mode are in BOL.
>Mike John
><NoNothing@.aol.com> wrote in message news:3kt3101tic672lvlp4fj78tepkqg4pviuf@.4ax.com...
>> I have the following code to restore a database (it was not written by
>> me)
>> Dim ServerName As String
>> Dim DBName As String
>> Dim BackupToRestore As String
>> ServerName = "PENTIUM4\VIPER"
>> DBName = "master"
>> BackupToRestore = "ViperData\Bmaster"
>> Dim oServer As SQLDMO.SQLServer
>> Dim oRestore As SQLDMO.Restore
>> On Error GoTo Handler
>> 'simple err checking
>> If ServerName = "" Or DBName = "" Or BackupToRestore = "" Then
>> MsgBox "You MUST provide server name, database name, and the name of
>> the bak file you want to restore", vbInformation + vbOKOnly, "Error"
>> Exit Sub
>> End If
>> 'open connection to server
>> Set oServer = New SQLDMO.SQLServer
>> With oServer
>> .LoginSecure = True
>> .Connect ServerName
>> End With
>> 'also need a restore object
>> Set oRestore = New SQLDMO.Restore
>> 'use the 'with' construct to minimize property lookups
>> With oRestore
>> 'this is where your backup will be restored to
>> .Database = DBName
>> 'same as EM or TSQL, you can restore database, file, or log, here
>> we're going to
>> 'use database
>> .Action = SQLDMORestore_Database
>> 'this is the "force restore over existing database" option
>> .ReplaceDatabase = True
>> 'this does a restore from a file instead of a device - note that we're
>> still
>> 'restoring a database, NOT a file group
>> .Files = BackupToRestore
>> 'do it
>> .SQLRestore oServer
>> End With
>> 'standard clean up
>> Set oRestore = Nothing
>> oServer.DisConnect
>> Set oServer = Nothing
>> Exit Sub
>> Handler:
>> If MsgBox(Err.Description & ". Would you like to continue?",
>> vbInformation + vbYesNo) = vbYes Then
>> Resume Next
>> End If
>>
>> I keep getting the error message that the sql server must be in single
>> use mode
>> The serve comes with a program I purchased and is controlled by
>> Microsoft service manager 8.00.760
>> How do i run it in single user mode?|||Soory for my ignorance what is BOL?
On Sat, 24 Jan 2004 08:23:04 -0000, "Mike John"
<Mike.John@.knowledgepool.spamtrap.com> wrote:
>It sounds like you are trying to restore the database "Master" To restore master the entire SQl server must be in single user mode. This means starting SQl server from a command prompt or adjusting the startup parameters for running it a a service. From a command line is simplest - sqlservr -m.
>Full detail;s of starting in single user mode are in BOL.
>Mike John
><NoNothing@.aol.com> wrote in message news:3kt3101tic672lvlp4fj78tepkqg4pviuf@.4ax.com...
>> I have the following code to restore a database (it was not written by
>> me)
>> Dim ServerName As String
>> Dim DBName As String
>> Dim BackupToRestore As String
>> ServerName = "PENTIUM4\VIPER"
>> DBName = "master"
>> BackupToRestore = "ViperData\Bmaster"
>> Dim oServer As SQLDMO.SQLServer
>> Dim oRestore As SQLDMO.Restore
>> On Error GoTo Handler
>> 'simple err checking
>> If ServerName = "" Or DBName = "" Or BackupToRestore = "" Then
>> MsgBox "You MUST provide server name, database name, and the name of
>> the bak file you want to restore", vbInformation + vbOKOnly, "Error"
>> Exit Sub
>> End If
>> 'open connection to server
>> Set oServer = New SQLDMO.SQLServer
>> With oServer
>> .LoginSecure = True
>> .Connect ServerName
>> End With
>> 'also need a restore object
>> Set oRestore = New SQLDMO.Restore
>> 'use the 'with' construct to minimize property lookups
>> With oRestore
>> 'this is where your backup will be restored to
>> .Database = DBName
>> 'same as EM or TSQL, you can restore database, file, or log, here
>> we're going to
>> 'use database
>> .Action = SQLDMORestore_Database
>> 'this is the "force restore over existing database" option
>> .ReplaceDatabase = True
>> 'this does a restore from a file instead of a device - note that we're
>> still
>> 'restoring a database, NOT a file group
>> .Files = BackupToRestore
>> 'do it
>> .SQLRestore oServer
>> End With
>> 'standard clean up
>> Set oRestore = Nothing
>> oServer.DisConnect
>> Set oServer = Nothing
>> Exit Sub
>> Handler:
>> If MsgBox(Err.Description & ". Would you like to continue?",
>> vbInformation + vbYesNo) = vbYes Then
>> Resume Next
>> End If
>>
>> I keep getting the error message that the sql server must be in single
>> use mode
>> The serve comes with a program I purchased and is controlled by
>> Microsoft service manager 8.00.760
>> How do i run it in single user mode?|||BOL is Books online. Comes with SQlmserver and is the full product =manual. Should read by everyone!
SQLSERVR.EXE is the command you want, sqlmangr is the little app that =sits in the system tray tellimng you its running.
Look up restoring the master database in Books Online and you will find =all the steps, but I would question WHY your VB code is trying to =restore master if you were not aware of it. Do you really want to =restore master?
Mike John
<NoNothing@.aol.com> wrote in message =news:0lg710lh55q4pc37jqotvp0khl0c8ob838@.4ax.com...
> Soory for my ignorance what is BOL?
> > On Sat, 24 Jan 2004 08:23:04 -0000, "Mike John"
> <Mike.John@.knowledgepool.spamtrap.com> wrote:
> > >It sounds like you are trying to restore the database "Master" To =restore master the entire SQl server must be in single user mode. This =means starting SQl server from a command prompt or adjusting the startup =parameters for running it a a service. From a command line is simplest - =sqlservr -m.
> >
> >Full detail;s of starting in single user mode are in BOL.
> >
> >Mike John
> >
> ><NoNothing@.aol.com> wrote in message =news:3kt3101tic672lvlp4fj78tepkqg4pviuf@.4ax.com...
> >> I have the following code to restore a database (it was not written =by
> >> me)
> >> > >> Dim ServerName As String
> >> Dim DBName As String
> >> Dim BackupToRestore As String
> >> ServerName =3D "PENTIUM4\VIPER"
> >> DBName =3D "master"
> >> BackupToRestore =3D "ViperData\Bmaster"
> >> > >> Dim oServer As SQLDMO.SQLServer
> >> Dim oRestore As SQLDMO.Restore
> >> On Error GoTo Handler
> >> > >> 'simple err checking
> >> If ServerName =3D "" Or DBName =3D "" Or BackupToRestore =3D "" =Then
> >> MsgBox "You MUST provide server name, database name, and the name =of
> >> the bak file you want to restore", vbInformation + vbOKOnly, ="Error"
> >> Exit Sub
> >> End If
> >> > >> 'open connection to server
> >> Set oServer =3D New SQLDMO.SQLServer
> >> With oServer
> >> .LoginSecure =3D True
> >> .Connect ServerName
> >> End With
> >> > >> 'also need a restore object
> >> Set oRestore =3D New SQLDMO.Restore
> >> > >> 'use the 'with' construct to minimize property lookups
> >> With oRestore
> >> 'this is where your backup will be restored to
> >> .Database =3D DBName
> >> 'same as EM or TSQL, you can restore database, file, or log, here
> >> we're going to
> >> 'use database
> >> .Action =3D SQLDMORestore_Database
> >> 'this is the "force restore over existing database" option
> >> .ReplaceDatabase =3D True
> >> 'this does a restore from a file instead of a device - note that =we're
> >> still
> >> 'restoring a database, NOT a file group
> >> .Files =3D BackupToRestore
> >> 'do it
> >> .SQLRestore oServer
> >> End With
> >> > >> 'standard clean up
> >> Set oRestore =3D Nothing
> >> oServer.DisConnect
> >> Set oServer =3D Nothing
> >> > >> Exit Sub
> >> > >> Handler:
> >> If MsgBox(Err.Description & ". Would you like to continue?",
> >> vbInformation + vbYesNo) =3D vbYes Then
> >> Resume Next
> >> End If
> >> > >> > >> > >> I keep getting the error message that the sql server must be in =single
> >> use mode
> >> > >> The serve comes with a program I purchased and is controlled by
> >> Microsoft service manager 8.00.760
> >> > >> How do i run it in single user mode?
> >> >

Restore single user mode

quote:

> I have the following code to restore a database (it was not written by
> me)
>=20
> Dim ServerName As String
> Dim DBName As String
> Dim BackupToRestore As String
> ServerName =3D "PENTIUM4\VIPER"
> DBName =3D "master"
> BackupToRestore =3D "ViperData\Bmaster"
>=20
> Dim oServer As SQLDMO.SQLServer
> Dim oRestore As SQLDMO.Restore
> On Error GoTo Handler
> =20
> 'simple err checking
> If ServerName =3D "" Or DBName =3D "" Or BackupToRestore =3D "" Then
> MsgBox "You MUST provide server name, database name, and the name of
> the bak file you want to restore", vbInformation + vbOKOnly, "Error"
> Exit Sub
> End If
> =20
> 'open connection to server
> Set oServer =3D New SQLDMO.SQLServer
> With oServer
> .LoginSecure =3D True
> .Connect ServerName
> End With
> =20
> 'also need a restore object
> Set oRestore =3D New SQLDMO.Restore
> =20
> 'use the 'with' construct to minimize property lookups
> With oRestore
> 'this is where your backup will be restored to
> .Database =3D DBName
> 'same as EM or TSQL, you can restore database, file, or log, here
> we're going to
> 'use database
> .Action =3D SQLDMORestore_Database
> 'this is the "force restore over existing database" option
> .ReplaceDatabase =3D True
> 'this does a restore from a file instead of a device - note that we're
> still
> 'restoring a database, NOT a file group
> .Files =3D BackupToRestore
> 'do it
> .SQLRestore oServer
> End With
> =20
> 'standard clean up
> Set oRestore =3D Nothing
> oServer.DisConnect
> Set oServer =3D Nothing
> =20
> Exit Sub
> =20
> Handler:
> If MsgBox(Err.Description & ". Would you like to continue?",
> vbInformation + vbYesNo) =3D vbYes Then
> Resume Next
> End If
>=20
>=20
>=20
> I keep getting the error message that the sql server must be in single
> use mode
>=20
> The serve comes with a program I purchased and is controlled by
> Microsoft service manager 8.00.760
>=20
> How do i run it in single user mode?
>

|||Yes I had read apbout that however two things. First to start my
server I need to type sqlservr.exe NOT sqlmangr.exe and when I type
sqlservr.exe -m I still get the error message
Am I using a different version of MS sqlmanager?
On Sat, 24 Jan 2004 08:23:04 -0000, "Mike John"
<Mike.John@.knowledgepool.spamtrap.com> wrote:

quote:

>It sounds like you are trying to restore the database "Master" To restore master the entire
SQl server must be in single user mode. This means starting SQl server from a command promp
t or adjusting the startup parameters for running it a a service. From

a command line is simplest - sqlservr -m.[QUOTE]
>Full detail;s of starting in single user mode are in BOL.
>Mike John
><NoNothing@.aol.com> wrote in message news:3kt3101tic672lvlp4fj78tepkqg4pviu
f@.4ax.com...|||Soory for my ignorance what is BOL?
On Sat, 24 Jan 2004 08:23:04 -0000, "Mike John"
<Mike.John@.knowledgepool.spamtrap.com> wrote:

quote:

>It sounds like you are trying to restore the database "Master" To restore master the entire
SQl server must be in single user mode. This means starting SQl server from a command promp
t or adjusting the startup parameters for running it a a service. From

a command line is simplest - sqlservr -m.[QUOTE]
>Full detail;s of starting in single user mode are in BOL.
>Mike John
><NoNothing@.aol.com> wrote in message news:3kt3101tic672lvlp4fj78tepkqg4pviu
f@.4ax.com...|||BOL is Books online. Comes with SQlmserver and is the full product =
manual. Should read by everyone!
SQLSERVR.EXE is the command you want, sqlmangr is the little app that =
sits in the system tray tellimng you its running.
Look up restoring the master database in Books Online and you will find =
all the steps, but I would question WHY your VB code is trying to =
restore master if you were not aware of it. Do you really want to =
restore master?
Mike John
<NoNothing@.aol.com> wrote in message =
news:0lg710lh55q4pc37jqotvp0khl0c8ob838@.
4ax.com...

quote:

> Soory for my ignorance what is BOL?
>=20
> On Sat, 24 Jan 2004 08:23:04 -0000, "Mike John"
> <Mike.John@.knowledgepool.spamtrap.com> wrote:
>=20
restore master the entire SQl server must be in single user mode. This =
means starting SQl server from a command prompt or adjusting the startup =
parameters for running it a a service. From a command line is simplest - =
sqlservr -m.[QUOTE]
news:3kt3101tic672lvlp4fj78tepkqg4pviuf@.
4ax.com...[QUOTE]
by[QUOTE]
Then[QUOTE]
of[QUOTE]
"Error"[QUOTE]
we're[QUOTE]
single[QUOTE]
>

Tuesday, March 20, 2012

Restore script restoring to old version of the database

A month ago I set up a small program to backup my databases â' it ran the
following script against the database:
BACKUP DATABASE MyDatabase TO DISK = 'F:\SQLBackups\MyDatabase.bak' WITH
NOINIT, PASSWORD='xxx', RETAINDAYS=90
This was run every day and saves the backup to a removable USB drive which
is taken off-site at night. The backed up file grew every time the script
was run â' so I assumed it was working fine and left it to it.
Then yesterday I had a main drive failure and spent the day rebuilding my
machine. Then I went to restore my databases with the following script (in
query analyzer) (note the MOVE was needed as I am storing the data files in a
different location now):
RESTORE DATABASE MyDatabase FROM DISK = 'F:\SQLBackups\MyDatabase.bak'
WITH MOVE 'MyDatabase' TO 'D:\SQLData\MyDatabase.mdf', MOVE 'MyDatabase_Log'
TO 'D:\SQLData\Mydatabase_Log.ldf', Password='xxx'
But to my horror it has only restored the database to the point it was in a
month ago when I first started these backups â' I appear to have lost a months
worth of data.
The .Bak file has a modified datetime equal to the last time the script was
run (the night before last) â' so the backups were happening. What am I doing
wrong here? Note that the recovery model was set to FULL.
Please help.Hi
Looks like you appended each new backup into the same file. With your
restore script, you restored the 1st one.
Look at RESTORE HEADERONLY in BOL. This will show you all the backup sets
that the file contains. Then use that information to do a RESTORE DATABASE.
If you do the restore though enterprise manager, it will the backups too and
then you can select which one you want to restore.
--
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/
"RodneyL" <RodneyL@.discussions.microsoft.com> wrote in message
news:C55ED1C4-3295-45A7-B6CA-BAF902B56B1B@.microsoft.com...
>A month ago I set up a small program to backup my databases - it ran the
> following script against the database:
> BACKUP DATABASE MyDatabase TO DISK = 'F:\SQLBackups\MyDatabase.bak' WITH
> NOINIT, PASSWORD='xxx', RETAINDAYS=90
> This was run every day and saves the backup to a removable USB drive which
> is taken off-site at night. The backed up file grew every time the script
> was run - so I assumed it was working fine and left it to it.
> Then yesterday I had a main drive failure and spent the day rebuilding my
> machine. Then I went to restore my databases with the following script
> (in
> query analyzer) (note the MOVE was needed as I am storing the data files
> in a
> different location now):
> RESTORE DATABASE MyDatabase FROM DISK = 'F:\SQLBackups\MyDatabase.bak'
> WITH MOVE 'MyDatabase' TO 'D:\SQLData\MyDatabase.mdf', MOVE
> 'MyDatabase_Log'
> TO 'D:\SQLData\Mydatabase_Log.ldf', Password='xxx'
> But to my horror it has only restored the database to the point it was in
> a
> month ago when I first started these backups - I appear to have lost a
> months
> worth of data.
> The .Bak file has a modified datetime equal to the last time the script
> was
> run (the night before last) - so the backups were happening. What am I
> doing
> wrong here? Note that the recovery model was set to FULL.
> Please help.|||Thanks Mike. I get 13 rows using RESTORE HEADERONLY. The BackupName and
BackupDescription fields are all Null. So how do I specify that I want to
restore the last one?
Also â' I canâ't use Enterprise Manager because it doesnâ't have the option to
specify a password â' so I have to use a script.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Looks like you appended each new backup into the same file. With your
> restore script, you restored the 1st one.
> Look at RESTORE HEADERONLY in BOL. This will show you all the backup sets
> that the file contains. Then use that information to do a RESTORE DATABASE.
> If you do the restore though enterprise manager, it will the backups too and
> then you can select which one you want to restore.
>
> --
> --
> 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/
> "RodneyL" <RodneyL@.discussions.microsoft.com> wrote in message
> news:C55ED1C4-3295-45A7-B6CA-BAF902B56B1B@.microsoft.com...
> >A month ago I set up a small program to backup my databases - it ran the
> > following script against the database:
> >
> > BACKUP DATABASE MyDatabase TO DISK = 'F:\SQLBackups\MyDatabase.bak' WITH
> > NOINIT, PASSWORD='xxx', RETAINDAYS=90
> >
> > This was run every day and saves the backup to a removable USB drive which
> > is taken off-site at night. The backed up file grew every time the script
> > was run - so I assumed it was working fine and left it to it.
> >
> > Then yesterday I had a main drive failure and spent the day rebuilding my
> > machine. Then I went to restore my databases with the following script
> > (in
> > query analyzer) (note the MOVE was needed as I am storing the data files
> > in a
> > different location now):
> >
> > RESTORE DATABASE MyDatabase FROM DISK = 'F:\SQLBackups\MyDatabase.bak'
> > WITH MOVE 'MyDatabase' TO 'D:\SQLData\MyDatabase.mdf', MOVE
> > 'MyDatabase_Log'
> > TO 'D:\SQLData\Mydatabase_Log.ldf', Password='xxx'
> >
> > But to my horror it has only restored the database to the point it was in
> > a
> > month ago when I first started these backups - I appear to have lost a
> > months
> > worth of data.
> >
> > The .Bak file has a modified datetime equal to the last time the script
> > was
> > run (the night before last) - so the backups were happening. What am I
> > doing
> > wrong here? Note that the recovery model was set to FULL.
> >
> > Please help.
>
>|||Ahhhh - Problem solved. I needed to add FILE=13 to the WITH parameters:
RESTORE DATABASE MyDatabase
FROM DISK = 'F:\SQLBackups\MyDatabase.bak'
WITH FILE=13,
MOVE 'MyDatabase' TO 'D:\SQLData\MyDatabase.mdf',
MOVE 'MyDatabase_Log' TO 'D:\SQLData\Mydatabase_Log.ldf',
Password='xxx'
Many thanks Mike for pointing me in the right direction!
"RodneyL" wrote:
> Thanks Mike. I get 13 rows using RESTORE HEADERONLY. The BackupName and
> BackupDescription fields are all Null. So how do I specify that I want to
> restore the last one?
> Also â' I canâ't use Enterprise Manager because it doesnâ't have the option to
> specify a password â' so I have to use a script.
>
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > Looks like you appended each new backup into the same file. With your
> > restore script, you restored the 1st one.
> >
> > Look at RESTORE HEADERONLY in BOL. This will show you all the backup sets
> > that the file contains. Then use that information to do a RESTORE DATABASE.
> >
> > If you do the restore though enterprise manager, it will the backups too and
> > then you can select which one you want to restore.
> >
> >
> >
> > --
> > --
> > 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/
> >
> > "RodneyL" <RodneyL@.discussions.microsoft.com> wrote in message
> > news:C55ED1C4-3295-45A7-B6CA-BAF902B56B1B@.microsoft.com...
> > >A month ago I set up a small program to backup my databases - it ran the
> > > following script against the database:
> > >
> > > BACKUP DATABASE MyDatabase TO DISK = 'F:\SQLBackups\MyDatabase.bak' WITH
> > > NOINIT, PASSWORD='xxx', RETAINDAYS=90
> > >
> > > This was run every day and saves the backup to a removable USB drive which
> > > is taken off-site at night. The backed up file grew every time the script
> > > was run - so I assumed it was working fine and left it to it.
> > >
> > > Then yesterday I had a main drive failure and spent the day rebuilding my
> > > machine. Then I went to restore my databases with the following script
> > > (in
> > > query analyzer) (note the MOVE was needed as I am storing the data files
> > > in a
> > > different location now):
> > >
> > > RESTORE DATABASE MyDatabase FROM DISK = 'F:\SQLBackups\MyDatabase.bak'
> > > WITH MOVE 'MyDatabase' TO 'D:\SQLData\MyDatabase.mdf', MOVE
> > > 'MyDatabase_Log'
> > > TO 'D:\SQLData\Mydatabase_Log.ldf', Password='xxx'
> > >
> > > But to my horror it has only restored the database to the point it was in
> > > a
> > > month ago when I first started these backups - I appear to have lost a
> > > months
> > > worth of data.
> > >
> > > The .Bak file has a modified datetime equal to the last time the script
> > > was
> > > run (the night before last) - so the backups were happening. What am I
> > > doing
> > > wrong here? Note that the recovery model was set to FULL.
> > >
> > > Please help.
> >
> >
> >

Monday, March 12, 2012

Restore production database into development database problem - Ur

I restored the production database backup(170GB) onto a development system.
I first tried to do the following command:
restore filelistonly from disk='f:\EEMSuite_db_200411111600.BAK';
Then stopped it after 30 minutes realized that I need to use the MOVE
command because the log and data file have different location on the
development system.
I then initiated the following:
restore database MYDB from disk='f:\EEMSuite_db_200411111600.BAK'
with move 'EEMSuite_model_Data' to
'E:\Program Files\Microsoft SQL Server\MSSQL\Data\EEMSuite.mdf',
move 'EEMSuite_model_Log' to
'E:\Program Files\Microsoft SQL Server\MSSQL\Data\EEMSuite_log.LDF',
replace
It ran for two days and finally came back with the following After two days
of restore process:
Server: Msg 5173, Level 16, State 1, Line 1
Cannot associate files with different databases.
Log file 'E:\Program Files\Microsoft SQL Server\MSSQL\data\EEMSuite_log.LDF'
does not match the primary file. It may be from a different database or the
log may have been rebuilt previously.
Processed 22272096 pages for database 'MYDB', file 'EEMSuite_model_Data' on
file 1.
Processed 18042 pages for database 'MYDB', file 'EEMSuite_model_Log' on file
1.
RESTORE DATABASE successfully processed 22290138 pages in 176133.332 seconds
(1.036 MB/sec).
I am concerned about the frist error message but was wondering if that came
from first failed attempt. By looking at the following two messages it looks
like the restore was a successful one?
My user can not access the database and the newly restored user database
does not show up in the Object Browser. What am missing? Do I need a
"recovery" command somewhere? Please help!!
--LingAfter the restore, I also can not see the Permissions under properties for
MYDB. And, I while I tried to open the properties for other database such as
master, it flashes and goes away. Is this a permission problem after
restore? Thanks in advance.
Ling
"Ling" wrote:
> I restored the production database backup(170GB) onto a development system.
> I first tried to do the following command:
> restore filelistonly from disk='f:\EEMSuite_db_200411111600.BAK';
> Then stopped it after 30 minutes realized that I need to use the MOVE
> command because the log and data file have different location on the
> development system.
> I then initiated the following:
> restore database MYDB from disk='f:\EEMSuite_db_200411111600.BAK'
> with move 'EEMSuite_model_Data' to
> 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\EEMSuite.mdf',
> move 'EEMSuite_model_Log' to
> 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\EEMSuite_log.LDF',
> replace
> It ran for two days and finally came back with the following After two days
> of restore process:
> Server: Msg 5173, Level 16, State 1, Line 1
> Cannot associate files with different databases.
> Log file 'E:\Program Files\Microsoft SQL Server\MSSQL\data\EEMSuite_log.LDF'
> does not match the primary file. It may be from a different database or the
> log may have been rebuilt previously.
> Processed 22272096 pages for database 'MYDB', file 'EEMSuite_model_Data' on
> file 1.
> Processed 18042 pages for database 'MYDB', file 'EEMSuite_model_Log' on file
> 1.
> RESTORE DATABASE successfully processed 22290138 pages in 176133.332 seconds
> (1.036 MB/sec).
> I am concerned about the frist error message but was wondering if that came
> from first failed attempt. By looking at the following two messages it looks
> like the restore was a successful one?
> My user can not access the database and the newly restored user database
> does not show up in the Object Browser. What am missing? Do I need a
> "recovery" command somewhere? Please help!!
> --Ling|||Log in as a system administrator and try to access the database.. It is
common to have user/login mismatches when restoring a database to a
different server..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ling" <Ling@.discussions.microsoft.com> wrote in message
news:5176807C-4D79-4DD2-B074-C9D010E51FB0@.microsoft.com...
> After the restore, I also can not see the Permissions under properties for
> MYDB. And, I while I tried to open the properties for other database such
as
> master, it flashes and goes away. Is this a permission problem after
> restore? Thanks in advance.
> Ling
> "Ling" wrote:
> > I restored the production database backup(170GB) onto a development
system.
> > I first tried to do the following command:
> >
> > restore filelistonly from disk='f:\EEMSuite_db_200411111600.BAK';
> >
> > Then stopped it after 30 minutes realized that I need to use the MOVE
> > command because the log and data file have different location on the
> > development system.
> >
> > I then initiated the following:
> >
> > restore database MYDB from disk='f:\EEMSuite_db_200411111600.BAK'
> > with move 'EEMSuite_model_Data' to
> > 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\EEMSuite.mdf',
> > move 'EEMSuite_model_Log' to
> > 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\EEMSuite_log.LDF',
> > replace
> >
> > It ran for two days and finally came back with the following After two
days
> > of restore process:
> >
> > Server: Msg 5173, Level 16, State 1, Line 1
> > Cannot associate files with different databases.
> > Log file 'E:\Program Files\Microsoft SQL
Server\MSSQL\data\EEMSuite_log.LDF'
> > does not match the primary file. It may be from a different database or
the
> > log may have been rebuilt previously.
> > Processed 22272096 pages for database 'MYDB', file 'EEMSuite_model_Data'
on
> > file 1.
> > Processed 18042 pages for database 'MYDB', file 'EEMSuite_model_Log' on
file
> > 1.
> > RESTORE DATABASE successfully processed 22290138 pages in 176133.332
seconds
> > (1.036 MB/sec).
> >
> > I am concerned about the frist error message but was wondering if that
came
> > from first failed attempt. By looking at the following two messages it
looks
> > like the restore was a successful one?
> >
> > My user can not access the database and the newly restored user database
> > does not show up in the Object Browser. What am missing? Do I need a
> > "recovery" command somewhere? Please help!!
> >
> > --Ling|||I rebooted the server and now I can query the tables within the database.
But, while my user trying to connect, they can not! Please advise how to fix
this problem. Thank you!!
Ling
"Ling" wrote:
> I restored the production database backup(170GB) onto a development system.
> I first tried to do the following command:
> restore filelistonly from disk='f:\EEMSuite_db_200411111600.BAK';
> Then stopped it after 30 minutes realized that I need to use the MOVE
> command because the log and data file have different location on the
> development system.
> I then initiated the following:
> restore database MYDB from disk='f:\EEMSuite_db_200411111600.BAK'
> with move 'EEMSuite_model_Data' to
> 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\EEMSuite.mdf',
> move 'EEMSuite_model_Log' to
> 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\EEMSuite_log.LDF',
> replace
> It ran for two days and finally came back with the following After two days
> of restore process:
> Server: Msg 5173, Level 16, State 1, Line 1
> Cannot associate files with different databases.
> Log file 'E:\Program Files\Microsoft SQL Server\MSSQL\data\EEMSuite_log.LDF'
> does not match the primary file. It may be from a different database or the
> log may have been rebuilt previously.
> Processed 22272096 pages for database 'MYDB', file 'EEMSuite_model_Data' on
> file 1.
> Processed 18042 pages for database 'MYDB', file 'EEMSuite_model_Log' on file
> 1.
> RESTORE DATABASE successfully processed 22290138 pages in 176133.332 seconds
> (1.036 MB/sec).
> I am concerned about the frist error message but was wondering if that came
> from first failed attempt. By looking at the following two messages it looks
> like the restore was a successful one?
> My user can not access the database and the newly restored user database
> does not show up in the Object Browser. What am missing? Do I need a
> "recovery" command somewhere? Please help!!
> --Ling|||First verify if the logins exist in the new server, then run
sp_change_users_login. See BOL for more details.
Gary
"Ling" <Ling@.discussions.microsoft.com> wrote in message
news:5FD532FE-7547-4018-9177-6B2A5D9A693F@.microsoft.com...
> I rebooted the server and now I can query the tables within the database.
> But, while my user trying to connect, they can not! Please advise how to
fix
> this problem. Thank you!!
>
> Ling
> "Ling" wrote:
> > I restored the production database backup(170GB) onto a development
system.
> > I first tried to do the following command:
> >
> > restore filelistonly from disk='f:\EEMSuite_db_200411111600.BAK';
> >
> > Then stopped it after 30 minutes realized that I need to use the MOVE
> > command because the log and data file have different location on the
> > development system.
> >
> > I then initiated the following:
> >
> > restore database MYDB from disk='f:\EEMSuite_db_200411111600.BAK'
> > with move 'EEMSuite_model_Data' to
> > 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\EEMSuite.mdf',
> > move 'EEMSuite_model_Log' to
> > 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\EEMSuite_log.LDF',
> > replace
> >
> > It ran for two days and finally came back with the following After two
days
> > of restore process:
> >
> > Server: Msg 5173, Level 16, State 1, Line 1
> > Cannot associate files with different databases.
> > Log file 'E:\Program Files\Microsoft SQL
Server\MSSQL\data\EEMSuite_log.LDF'
> > does not match the primary file. It may be from a different database or
the
> > log may have been rebuilt previously.
> > Processed 22272096 pages for database 'MYDB', file 'EEMSuite_model_Data'
on
> > file 1.
> > Processed 18042 pages for database 'MYDB', file 'EEMSuite_model_Log' on
file
> > 1.
> > RESTORE DATABASE successfully processed 22290138 pages in 176133.332
seconds
> > (1.036 MB/sec).
> >
> > I am concerned about the frist error message but was wondering if that
came
> > from first failed attempt. By looking at the following two messages it
looks
> > like the restore was a successful one?
> >
> > My user can not access the database and the newly restored user database
> > does not show up in the Object Browser. What am missing? Do I need a
> > "recovery" command somewhere? Please help!!
> >
> > --Ling

Restore production database into development database problem - Ur

Restore Problems

From time to time I receive the following error while
restoring:
"The log in this backup set terminates at LSNxxx, which
is too early to apply to the database. A more recent log
backup that includes LSNxxx can be restored."
I am not backing up any logs. This is my backup command:
BACKUP DATABASE [Order] TO DISK = N'C:\Program
Files\Microsoft SQL Server\MSSQL\BACKUP\Orderback' WITH
INIT , NOUNLOAD , NAME = N'Order backup', NOSKIP ,
STATS = 10, NOFORMAT
This is my restore:
restore database [order]
filegroup = 'primary', filegroup = 'ORDER1', filegroup
= 'ORDER2'
from disk='c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Orderback'
with file=1,norecovery,partial,replace,
move 'order_data' to 'c:\program files\microsoft sql
server\mssql\data\order_data.mdf',
move 'order_data1' to 'c:\program files\microsoft sql
server\mssql\data\order_data1.ndf',
move 'order_data2' to 'c:\program files\microsoft sql
server\mssql\data\order_data2.ndf',
move 'order_log' to 'c:\program files\microsoft sql
server\mssql\data\order_log.ldf'
go
restore log [order]
from disk='c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Orderback'
with file=1,recovery
goHi Paul.
I don't think you need that final restore log command - the full database
restore will fully restore the database and log entries in that file so I'm
wondering what you're expecting to get from that restore log command?
Regards,
Greg Linwood
SQL Server MVP
"Paul Offerle" <pofferle@.fmcconnell.com> wrote in message
news:664a01c4016a$6821c020$a001280a@.phx.gbl...
> From time to time I receive the following error while
> restoring:
> "The log in this backup set terminates at LSNxxx, which
> is too early to apply to the database. A more recent log
> backup that includes LSNxxx can be restored."
> I am not backing up any logs. This is my backup command:
> BACKUP DATABASE [Order] TO DISK = N'C:\Program
> Files\Microsoft SQL Server\MSSQL\BACKUP\Orderback' WITH
> INIT , NOUNLOAD , NAME = N'Order backup', NOSKIP ,
> STATS = 10, NOFORMAT
> This is my restore:
> restore database [order]
> filegroup = 'primary', filegroup = 'ORDER1', filegroup
> = 'ORDER2'
> from disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Orderback'
> with file=1,norecovery,partial,replace,
> move 'order_data' to 'c:\program files\microsoft sql
> server\mssql\data\order_data.mdf',
> move 'order_data1' to 'c:\program files\microsoft sql
> server\mssql\data\order_data1.ndf',
> move 'order_data2' to 'c:\program files\microsoft sql
> server\mssql\data\order_data2.ndf',
> move 'order_log' to 'c:\program files\microsoft sql
> server\mssql\data\order_log.ldf'
> go
> restore log [order]
> from disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Orderback'
> with file=1,recovery
> go|||When I omit the restore log, I am unable to access the
database. Enterprise Manager displays "(loading)" next to
the database name.|||Paul
Although Greg said the restore database will "will fully restore the
database and log entries in that file", he meant it COULD do the full
restore, but only if you specify the keyword RECOVERY. The default is not to
run recovery, with the assumption that you will first be applying more logs.
Recovery needs to be run with the last thing you're restoring.
Use the recovery option on your RESTORE DATABASE command and you won't need
to restore any logs.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<anonymous@.discussions.microsoft.com> wrote in message
news:6cca01c401fd$90a802a0$a301280a@.phx.gbl...
> When I omit the restore log, I am unable to access the
> database. Enterprise Manager displays "(loading)" next to
> the database name.|||Try dropping the database before doing the restore.
I have found that doing filegroup restores using the REPLACE option creates
these type of errors.
Good Luck.
**********************************
"Paul Offerle" <pofferle@.fmcconnell.com> wrote in message
news:664a01c4016a$6821c020$a001280a@.phx.gbl...
> From time to time I receive the following error while
> restoring:
> "The log in this backup set terminates at LSNxxx, which
> is too early to apply to the database. A more recent log
> backup that includes LSNxxx can be restored."
> I am not backing up any logs. This is my backup command:
> BACKUP DATABASE [Order] TO DISK = N'C:\Program
> Files\Microsoft SQL Server\MSSQL\BACKUP\Orderback' WITH
> INIT , NOUNLOAD , NAME = N'Order backup', NOSKIP ,
> STATS = 10, NOFORMAT
> This is my restore:
> restore database [order]
> filegroup = 'primary', filegroup = 'ORDER1', filegroup
> = 'ORDER2'
> from disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Orderback'
> with file=1,norecovery,partial,replace,
> move 'order_data' to 'c:\program files\microsoft sql
> server\mssql\data\order_data.mdf',
> move 'order_data1' to 'c:\program files\microsoft sql
> server\mssql\data\order_data1.ndf',
> move 'order_data2' to 'c:\program files\microsoft sql
> server\mssql\data\order_data2.ndf',
> move 'order_log' to 'c:\program files\microsoft sql
> server\mssql\data\order_log.ldf'
> go
> restore log [order]
> from disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Orderback'
> with file=1,recovery
> go|||ooops - I missed that the restore db didn't have recovery option..
Regards,
Greg Linwood
SQL Server MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%238XCQEgAEHA.1420@.TK2MSFTNGP11.phx.gbl...
> Paul
> Although Greg said the restore database will "will fully restore the
> database and log entries in that file", he meant it COULD do the full
> restore, but only if you specify the keyword RECOVERY. The default is not
to
> run recovery, with the assumption that you will first be applying more
logs.
> Recovery needs to be run with the last thing you're restoring.
> Use the recovery option on your RESTORE DATABASE command and you won't
need
> to restore any logs.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:6cca01c401fd$90a802a0$a301280a@.phx.gbl...
>|||Thank you very much.

Restore Problems

From time to time I receive the following error while
restoring:
"The log in this backup set terminates at LSNxxx, which
is too early to apply to the database. A more recent log
backup that includes LSNxxx can be restored."
I am not backing up any logs. This is my backup command:
BACKUP DATABASE [Order] TO DISK = N'C:\Program
Files\Microsoft SQL Server\MSSQL\BACKUP\Orderback' WITH
INIT , NOUNLOAD , NAME = N'Order backup', NOSKIP ,
STATS = 10, NOFORMAT
This is my restore:
restore database [order]
filegroup = 'primary', filegroup = 'ORDER1', filegroup
= 'ORDER2'
from disk='c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Orderback'
with file=1,norecovery,partial,replace,
move 'order_data' to 'c:\program files\microsoft sql
server\mssql\data\order_data.mdf',
move 'order_data1' to 'c:\program files\microsoft sql
server\mssql\data\order_data1.ndf',
move 'order_data2' to 'c:\program files\microsoft sql
server\mssql\data\order_data2.ndf',
move 'order_log' to 'c:\program files\microsoft sql
server\mssql\data\order_log.ldf'
go
restore log [order]
from disk='c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Orderback'
with file=1,recovery
goHi Paul.
I don't think you need that final restore log command - the full database
restore will fully restore the database and log entries in that file so I'm
wondering what you're expecting to get from that restore log command?
Regards,
Greg Linwood
SQL Server MVP
"Paul Offerle" <pofferle@.fmcconnell.com> wrote in message
news:664a01c4016a$6821c020$a001280a@.phx.gbl...
> From time to time I receive the following error while
> restoring:
> "The log in this backup set terminates at LSNxxx, which
> is too early to apply to the database. A more recent log
> backup that includes LSNxxx can be restored."
> I am not backing up any logs. This is my backup command:
> BACKUP DATABASE [Order] TO DISK = N'C:\Program
> Files\Microsoft SQL Server\MSSQL\BACKUP\Orderback' WITH
> INIT , NOUNLOAD , NAME = N'Order backup', NOSKIP ,
> STATS = 10, NOFORMAT
> This is my restore:
> restore database [order]
> filegroup = 'primary', filegroup = 'ORDER1', filegroup
> = 'ORDER2'
> from disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Orderback'
> with file=1,norecovery,partial,replace,
> move 'order_data' to 'c:\program files\microsoft sql
> server\mssql\data\order_data.mdf',
> move 'order_data1' to 'c:\program files\microsoft sql
> server\mssql\data\order_data1.ndf',
> move 'order_data2' to 'c:\program files\microsoft sql
> server\mssql\data\order_data2.ndf',
> move 'order_log' to 'c:\program files\microsoft sql
> server\mssql\data\order_log.ldf'
> go
> restore log [order]
> from disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Orderback'
> with file=1,recovery
> go|||When I omit the restore log, I am unable to access the
database. Enterprise Manager displays "(loading)" next to
the database name.|||Paul
Although Greg said the restore database will "will fully restore the
database and log entries in that file", he meant it COULD do the full
restore, but only if you specify the keyword RECOVERY. The default is not to
run recovery, with the assumption that you will first be applying more logs.
Recovery needs to be run with the last thing you're restoring.
Use the recovery option on your RESTORE DATABASE command and you won't need
to restore any logs.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<anonymous@.discussions.microsoft.com> wrote in message
news:6cca01c401fd$90a802a0$a301280a@.phx.gbl...
> When I omit the restore log, I am unable to access the
> database. Enterprise Manager displays "(loading)" next to
> the database name.|||Try dropping the database before doing the restore.
I have found that doing filegroup restores using the REPLACE option creates
these type of errors.
Good Luck.
**********************************
"Paul Offerle" <pofferle@.fmcconnell.com> wrote in message
news:664a01c4016a$6821c020$a001280a@.phx.gbl...
> From time to time I receive the following error while
> restoring:
> "The log in this backup set terminates at LSNxxx, which
> is too early to apply to the database. A more recent log
> backup that includes LSNxxx can be restored."
> I am not backing up any logs. This is my backup command:
> BACKUP DATABASE [Order] TO DISK = N'C:\Program
> Files\Microsoft SQL Server\MSSQL\BACKUP\Orderback' WITH
> INIT , NOUNLOAD , NAME = N'Order backup', NOSKIP ,
> STATS = 10, NOFORMAT
> This is my restore:
> restore database [order]
> filegroup = 'primary', filegroup = 'ORDER1', filegroup
> = 'ORDER2'
> from disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Orderback'
> with file=1,norecovery,partial,replace,
> move 'order_data' to 'c:\program files\microsoft sql
> server\mssql\data\order_data.mdf',
> move 'order_data1' to 'c:\program files\microsoft sql
> server\mssql\data\order_data1.ndf',
> move 'order_data2' to 'c:\program files\microsoft sql
> server\mssql\data\order_data2.ndf',
> move 'order_log' to 'c:\program files\microsoft sql
> server\mssql\data\order_log.ldf'
> go
> restore log [order]
> from disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Orderback'
> with file=1,recovery
> go|||ooops - I missed that the restore db didn't have recovery option..
Regards,
Greg Linwood
SQL Server MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%238XCQEgAEHA.1420@.TK2MSFTNGP11.phx.gbl...
> Paul
> Although Greg said the restore database will "will fully restore the
> database and log entries in that file", he meant it COULD do the full
> restore, but only if you specify the keyword RECOVERY. The default is not
to
> run recovery, with the assumption that you will first be applying more
logs.
> Recovery needs to be run with the last thing you're restoring.
> Use the recovery option on your RESTORE DATABASE command and you won't
need
> to restore any logs.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:6cca01c401fd$90a802a0$a301280a@.phx.gbl...
> > When I omit the restore log, I am unable to access the
> > database. Enterprise Manager displays "(loading)" next to
> > the database name.
>

Restore Problem - Error 5243

Dear All,

SQL : SQL Express 2005

I having a problem when trying to restore a database. there were around 10 databases, but one is giving the following error and restore fails, will be great if someone helps..

I also tried taking the backup to another site having SQL 2005 Standard edition, the same error comes.

TITLE: Microsoft SQL Server Management Studio

Restore failed for Server '.\SQL2K5'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

An inconsistency was detected during an internal operation. Please contact technical support. Reference number 8. (Microsoft SQL Server, Error: 5243)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=5243&LinkId=20476

BUTTONS:

I also installed Service Pack, no luck..HELP!!!

HI,

Refer http://support.microsoft.com/kb/916086 which has workaround and hot fix for the problem.

Hemantgiri S. Goswami

|||

Hi,

Thanks for the reply. I went through that already and hence I applied the service pack 1. NO LUCK!!!

Good you pointed the KB link, I am just little confused.It says about "Creating and drop temporary tables...." and what I am doing is Restoring the DB from a backup..Does it mean while restoring temporary tables are created ?

Time is running out, HELP!!

No reply on this from Microsoft Support team!!! where are u guys?

Regards,

Wednesday, March 28, 2012

Restore using Microsoft SQL Server Management Studio?

Restore TSQL Scripts

Monday, March 26, 2012

Restore Tranaction log

Restore to read-only mode

Wednesday, March 21, 2012

Restore single user mode

Restore single user mode

Tuesday, March 20, 2012

Restore script restoring to old version of the database

Monday, March 12, 2012

Restore production database into development database problem - Ur

Restore production database into development database problem - Ur

Restore production database into development database problem - Ur

Restore Problems

Restore Problems

Restore Problem - Error 5243

restore master database

Blog Archive

About Me