I am trying to create sql code that restores a backup of a master database to a new database on the same server. It seems to run correctly as no errors are produced. However, the most recent updates to the master database are not present in the new databases. All databases are using the Full recovery model. What is really strange is that if I do (what I think is) the same function in Enterprise Administrator, the restore works fine! For both methods I used the same backup file!
Any and all help is sincerely appreciated.
The master databases from which the backups are made start with MODTRNMaster
The databases which are created from the restores start with M1_ and M2_. (We call them training room databases.)
My script for backing up the master databases:
-- Backup the master training database
backup database MODTRNMaster
to disk = 'f:\bkup\MODTRNMaster.bak'
backup database MODTRNMaster_IMG
to disk = 'f:\bkup\MODTRNMaster_IMG.bak'
backup database MODTRNMaster_MNC
to disk = 'f:\bkup\MODTRNMaster_MNC.bak'
backup database MODTRNMaster_VM
to disk = 'f:\bkup\MODTRNMaster_VM.bak'
go
This is the restore script for restoring the first training room databases. Im hoping that there is just something simple that Im overlooking in these restore statements! J
-- Restore the backup of the master training database into the
-- training room #1 database.
use master
go
drop database M1_MSLH
go
restore database M1_MSLH
from disk = 'f:\bkup\MODTRNMaster.bak'
with move 'DEV5_Data' to 'f:\mssql\data\M1_MLSH.mdf',
move 'MM' to 'f:\mssql\data\M1_MLSH_1.mdf',
move 'AMB' to 'f:\mssql\data\M1_MLSH_2.mdf',
move 'DM' to 'f:\mssql\data\M1_MLSH_3.mdf',
move 'IMM' to 'f:\mssql\data\M1_MLSH_4.mdf',
move 'ED' to 'f:\mssql\data\M1_MLSH_5.mdf',
move 'DEV5_Log' to 'f:\mssql\log\M1_MLSH_log.ldf',
recovery
go
Thanks in advance
:eek:
:eek:that surely sounds like some magic. I do not know off the the top of my head what the problem could be but you should only have one mdf file and the rest of your data files should be ndfs.|||I am trying to create sql code that restores a backup of a master database to a new database on the same server. It seems to run correctly as no errors are produced. However, the most recent updates to the master database are not present in the new databases. All databases are using the Full recovery model. What is really strange is that if I do (what I think is) the same function in Enterprise Administrator, the restore works fine! For both methods I used the same backup file!
Are you sure that the master database is in full recovery mode? I did not think that was possible and/or mattered.
Also, even if it is in full recovery, your restore script indicated that you had restored only the last full backup (.BAK) file and had not restored any transaction logs (.TRN). Without restoring the transaction logs, you will only restore the database to the point in time when the last full backup completed; any transacations performed after that will not get restored.
Finally, I have to ask: why are you restoring the master database? I'm not sure that doing so gets you anything (except maybe practice).
Regards,
hmscott|||sorry. I should have been a little clearer. :o It is not actually THE master database that I'm backing up. We are having some training sessions for users of a new system. The trainer makes his updates in a database named MODTRNMaster (the master database for our purposes). At night I am restoring into 2 individual training databases M1_MLSH and M2_MLSH that the users are accessing.
No transaction log backups are being done. These databases are only being accessed during the day. At night there is no activity in those databases. At night I backup MODTRNMaster and then immediately (attempt to) restore the backup file as M1_MLSH and then again as M2_MLSH.|||You need to add with init to your backup commands, or with file = # to the restore commands. By default, SQL Server will append your backup to the exisiting file (you may have noticed them getting bigger), and restore from the first backup found. Do this and get back to us with the results:
restore headeronly from disk = 'f:\bkup\MODTRNMaster.bak'|||Thanks So much for your suggestion! Adding the "init" parameter to the backup database statement was exactly what was needed!! :D
Showing posts with label code. Show all posts
Showing posts with label code. Show all posts
Monday, March 26, 2012
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?
> >> >
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
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...
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:
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:
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...
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...
quote:|||Yes I had read apbout that however two things. First to start my
> 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?
>
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]
>
Subscribe to:
Posts (Atom)