restore using isql

I have problems when trying to restore a database using the command line
isql program. I use a trusted connection and it says the database is in use
when no one is connected to it. Is there a problem trying to do this?
What is the default database for your trusted connection? If the default
database of your trusted connection is the database you trying to restore it
will v=be problem.
Also make sure no automated jobs periodically connecting the database.
Thanks
Ravi
"Tom Reis" wrote:

> I have problems when trying to restore a database using the command line
> isql program. I use a trusted connection and it says the database is in use
> when no one is connected to it. Is there a problem trying to do this?
>
>
|||The default database is master. Not the one I am trying to restore.
"Ravi" <ravishankart@.hotmail.com> wrote in message
news:203C4DFF-64B5-427E-A841-8AB5F8A51302@.microsoft.com...
> What is the default database for your trusted connection? If the default
> database of your trusted connection is the database you trying to restore
it[vbcol=seagreen]
> will v=be problem.
> Also make sure no automated jobs periodically connecting the database.
> --
> Thanks
> Ravi
>
> "Tom Reis" wrote:
use[vbcol=seagreen]
|||try EXEC sp_who2 and see there are any connections to the database
Thanks
Ravi
"Tom Reis" wrote:

> The default database is master. Not the one I am trying to restore.
> "Ravi" <ravishankart@.hotmail.com> wrote in message
> news:203C4DFF-64B5-427E-A841-8AB5F8A51302@.microsoft.com...
> it
> use
>
>
|||Tom,
From ISQL first go to SQL prompt using:-
ISQL -Usa -Ppassword (ENTER)
Now execute the below commands in sequence:-
ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE Command
GO
ALTER DATABASE <dbname> SET MULTI_USER
Note:
If the SQL Server version is 2000 then use OSQL instead of ISQL.
Thanks
Hari
SQL Server MVP
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:%23Y8VXZbfFHA.3988@.TK2MSFTNGP10.phx.gbl...
>I have problems when trying to restore a database using the command line
> isql program. I use a trusted connection and it says the database is in
> use
> when no one is connected to it. Is there a problem trying to do this?
>
sql

restore using isql

I have problems when trying to restore a database using the command line
isql program. I use a trusted connection and it says the database is in use
when no one is connected to it. Is there a problem trying to do this?What is the default database for your trusted connection? If the default
database of your trusted connection is the database you trying to restore it
will v=be problem.
Also make sure no automated jobs periodically connecting the database.
--
Thanks
Ravi
"Tom Reis" wrote:
> I have problems when trying to restore a database using the command line
> isql program. I use a trusted connection and it says the database is in use
> when no one is connected to it. Is there a problem trying to do this?
>
>|||The default database is master. Not the one I am trying to restore.
"Ravi" <ravishankart@.hotmail.com> wrote in message
news:203C4DFF-64B5-427E-A841-8AB5F8A51302@.microsoft.com...
> What is the default database for your trusted connection? If the default
> database of your trusted connection is the database you trying to restore
it
> will v=be problem.
> Also make sure no automated jobs periodically connecting the database.
> --
> Thanks
> Ravi
>
> "Tom Reis" wrote:
> > I have problems when trying to restore a database using the command line
> > isql program. I use a trusted connection and it says the database is in
use
> > when no one is connected to it. Is there a problem trying to do this?
> >
> >
> >|||try EXEC sp_who2 and see there are any connections to the database
--
Thanks
Ravi
"Tom Reis" wrote:
> The default database is master. Not the one I am trying to restore.
> "Ravi" <ravishankart@.hotmail.com> wrote in message
> news:203C4DFF-64B5-427E-A841-8AB5F8A51302@.microsoft.com...
> > What is the default database for your trusted connection? If the default
> > database of your trusted connection is the database you trying to restore
> it
> > will v=be problem.
> >
> > Also make sure no automated jobs periodically connecting the database.
> >
> > --
> > Thanks
> > Ravi
> >
> >
> > "Tom Reis" wrote:
> >
> > > I have problems when trying to restore a database using the command line
> > > isql program. I use a trusted connection and it says the database is in
> use
> > > when no one is connected to it. Is there a problem trying to do this?
> > >
> > >
> > >
>
>|||Tom,
From ISQL first go to SQL prompt using:-
ISQL -Usa -Ppassword (ENTER)
Now execute the below commands in sequence:-
ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE Command
GO
ALTER DATABASE <dbname> SET MULTI_USER
Note:
If the SQL Server version is 2000 then use OSQL instead of ISQL.
Thanks
Hari
SQL Server MVP
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:%23Y8VXZbfFHA.3988@.TK2MSFTNGP10.phx.gbl...
>I have problems when trying to restore a database using the command line
> isql program. I use a trusted connection and it says the database is in
> use
> when no one is connected to it. Is there a problem trying to do this?
>

restore using isql

I have problems when trying to restore a database using the command line
isql program. I use a trusted connection and it says the database is in use
when no one is connected to it. Is there a problem trying to do this?What is the default database for your trusted connection? If the default
database of your trusted connection is the database you trying to restore it
will v=be problem.
Also make sure no automated jobs periodically connecting the database.
Thanks
Ravi
"Tom Reis" wrote:

> I have problems when trying to restore a database using the command line
> isql program. I use a trusted connection and it says the database is in us
e
> when no one is connected to it. Is there a problem trying to do this?
>
>|||The default database is master. Not the one I am trying to restore.
"Ravi" <ravishankart@.hotmail.com> wrote in message
news:203C4DFF-64B5-427E-A841-8AB5F8A51302@.microsoft.com...
> What is the default database for your trusted connection? If the default
> database of your trusted connection is the database you trying to restore
it[vbcol=seagreen]
> will v=be problem.
> Also make sure no automated jobs periodically connecting the database.
> --
> Thanks
> Ravi
>
> "Tom Reis" wrote:
>
use[vbcol=seagreen]|||try EXEC sp_who2 and see there are any connections to the database
Thanks
Ravi
"Tom Reis" wrote:

> The default database is master. Not the one I am trying to restore.
> "Ravi" <ravishankart@.hotmail.com> wrote in message
> news:203C4DFF-64B5-427E-A841-8AB5F8A51302@.microsoft.com...
> it
> use
>
>|||Tom,
From ISQL first go to SQL prompt using:-
ISQL -Usa -Ppassword (ENTER)
Now execute the below commands in sequence:-
ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE Command
GO
ALTER DATABASE <dbname> SET MULTI_USER
Note:
If the SQL Server version is 2000 then use OSQL instead of ISQL.
Thanks
Hari
SQL Server MVP
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:%23Y8VXZbfFHA.3988@.TK2MSFTNGP10.phx.gbl...
>I have problems when trying to restore a database using the command line
> isql program. I use a trusted connection and it says the database is in
> use
> when no one is connected to it. Is there a problem trying to do this?
>

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 mon
ths
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 do
ing
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 opti
on 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 a
nd
> 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...
>
>|||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:
[vbcol=seagreen]
> 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 op
tion to
> specify a password – so I have to use a script.
>
> "Mike Epprecht (SQL MVP)" wrote:
>

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...
>
>
|||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:
[vbcol=seagreen]
> 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:

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

Restore only data on a database

Hi to all,
I have created a program that weekly restore a database with differential backup.

It works fine the only thing is that when I restore the database the new user are dropped.

There is a way to restore only the data ?

code:

use master
ALTER DATABASE GOROB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE GOROB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\GOROBFULL'
with NORECOVERY

RESTORE DATABASE GOROB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Differential'
WITH FILE =1 ,
RECOVERY

ALTER DATABASE GOROB SET MULTI_USER

Thank you in advance

And data added to the database SINCE the differential backup will be lost when you do a restore.

Database Users are 'data' in the database.

You may wish to add a job step to script out the Users when you make the differential backup, and then add a job step to the restore process to run that script.

|||

Anrie thank you

for your reply, there is a way to look insite the users of the database and grant the access to it?

My problem is that when I do the restore and some database administrator create new user on a database

it cannot access to data .

So I have thinked to grant the access to each user of the db .

The thinks is how this cold be done?

Restore of the SQL Server Program Files

Hello Group,
My Network Admin an myself are trying to come up with a way to quickly
restore our SQL Server in case of partial disk failure. What I am looking to
do is to resore only the Program Files Folder on the Database Server (if that
is possible). We have had good success with using NT Back Up and restoring
the complete server (OS and all).
What he wants me to do next is use a Shadow Copy to restore just the
Programs Folder holding the SQL Server install. This ahs not worked very
well. We can restore the folders and instances and databases but the SQL
Server Service does not start.
Does Microsoft have any instructions on this? The other option is I just
install SQL Server again and dump my BAK files back into the server but they
want a solution that does not include using the SQL Server install CD.
Rich
Hi
There are registry settings that need to come along too.
Files need to be registered also.
If you setup your SQL Server instances using the unattended install scripts
and you save them away, you could possibly install SQL Server from a scratch
faster than looking for a tape.
AFAIK, only full machine restores are supported to restore program files.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:C865BFA9-33DE-4E99-9CA3-42AE7DFF3F84@.microsoft.com...
> Hello Group,
> My Network Admin an myself are trying to come up with a way to quickly
> restore our SQL Server in case of partial disk failure. What I am looking
> to
> do is to resore only the Program Files Folder on the Database Server (if
> that
> is possible). We have had good success with using NT Back Up and
> restoring
> the complete server (OS and all).
> What he wants me to do next is use a Shadow Copy to restore just the
> Programs Folder holding the SQL Server install. This ahs not worked very
> well. We can restore the folders and instances and databases but the SQL
> Server Service does not start.
> Does Microsoft have any instructions on this? The other option is I just
> install SQL Server again and dump my BAK files back into the server but
> they
> want a solution that does not include using the SQL Server install CD.
> Rich
>

Restore of the SQL Server Program Files

Hello Group,
My Network Admin an myself are trying to come up with a way to quickly
restore our SQL Server in case of partial disk failure. What I am looking t
o
do is to resore only the Program Files Folder on the Database Server (if tha
t
is possible). We have had good success with using NT Back Up and restoring
the complete server (OS and all).
What he wants me to do next is use a Shadow Copy to restore just the
Programs Folder holding the SQL Server install. This ahs not worked very
well. We can restore the folders and instances and databases but the SQL
Server Service does not start.
Does Microsoft have any instructions on this? The other option is I just
install SQL Server again and dump my BAK files back into the server but they
want a solution that does not include using the SQL Server install CD.
RichHi
There are registry settings that need to come along too.
Files need to be registered also.
If you setup your SQL Server instances using the unattended install scripts
and you save them away, you could possibly install SQL Server from a scratch
faster than looking for a tape.
AFAIK, only full machine restores are supported to restore program files.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:C865BFA9-33DE-4E99-9CA3-42AE7DFF3F84@.microsoft.com...
> Hello Group,
> My Network Admin an myself are trying to come up with a way to quickly
> restore our SQL Server in case of partial disk failure. What I am looking
> to
> do is to resore only the Program Files Folder on the Database Server (if
> that
> is possible). We have had good success with using NT Back Up and
> restoring
> the complete server (OS and all).
> What he wants me to do next is use a Shadow Copy to restore just the
> Programs Folder holding the SQL Server install. This ahs not worked very
> well. We can restore the folders and instances and databases but the SQL
> Server Service does not start.
> Does Microsoft have any instructions on this? The other option is I just
> install SQL Server again and dump my BAK files back into the server but
> they
> want a solution that does not include using the SQL Server install CD.
> Rich
>

Restore of the SQL Server Program Files

Hello Group,
My Network Admin an myself are trying to come up with a way to quickly
restore our SQL Server in case of partial disk failure. What I am looking to
do is to resore only the Program Files Folder on the Database Server (if that
is possible). We have had good success with using NT Back Up and restoring
the complete server (OS and all).
What he wants me to do next is use a Shadow Copy to restore just the
Programs Folder holding the SQL Server install. This ahs not worked very
well. We can restore the folders and instances and databases but the SQL
Server Service does not start.
Does Microsoft have any instructions on this? The other option is I just
install SQL Server again and dump my BAK files back into the server but they
want a solution that does not include using the SQL Server install CD.
RichHi
There are registry settings that need to come along too.
Files need to be registered also.
If you setup your SQL Server instances using the unattended install scripts
and you save them away, you could possibly install SQL Server from a scratch
faster than looking for a tape.
AFAIK, only full machine restores are supported to restore program files.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:C865BFA9-33DE-4E99-9CA3-42AE7DFF3F84@.microsoft.com...
> Hello Group,
> My Network Admin an myself are trying to come up with a way to quickly
> restore our SQL Server in case of partial disk failure. What I am looking
> to
> do is to resore only the Program Files Folder on the Database Server (if
> that
> is possible). We have had good success with using NT Back Up and
> restoring
> the complete server (OS and all).
> What he wants me to do next is use a Shadow Copy to restore just the
> Programs Folder holding the SQL Server install. This ahs not worked very
> well. We can restore the folders and instances and databases but the SQL
> Server Service does not start.
> Does Microsoft have any instructions on this? The other option is I just
> install SQL Server again and dump my BAK files back into the server but
> they
> want a solution that does not include using the SQL Server install CD.
> Rich
>

Restore new server different database name not working

I have a database on my new server:
tempFTS
From Properties:
FileName: tempFTS_Data Location: C:\Program Files\Microsoft Sql
Server\MSSQL\Data\tempFTS_DATA.MDF
Log: tempFTS_Log Location: C:\Program Files\Microsoft
Sql Server\MSSQL\Data\tempFTS_LOG.LDF
Backup from old Server:
FTS_db_200510180200.BAK
Right-Click tempFTS database and choose Restore Database
Select From Device
Select "Select Devices..."
Select Add
Double click on FTS_db_200510180200.BAK
Select OK
Go to Options and Displayed:
FTS_log d:\Microsoft SQL Server\MSSQL\data\FTS.ldf
FTS_dat d:\Microsoft SQL Server\MSSQL\data\FTS.mdf
This is where it originally was.
I now want to change the name to tempFTS
So I change the Logical fileNames to (AS shown above from the properties of
tempFTS:
tempFTS_Log
tempFTS_Data
I change the Physical names to where I want the new files to be located
C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_DATA.MDF
C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_LOG.LDF
Force Restore (just in case)
I now get the error:
Logical file 'tempFTS_Log', not part of the database "tempFTS".
I then tried to put the Logical names back to FTS_log and FTS_dat to see
what would happen there:
Now it tries to overwrite the FTS_Data.mdf, which is the old database that I
am restoring, but I want to put into TempFTS first to look at a couple of
tables.
What is the point of changing the Physical file names, if it is not going to
use them?
What did I do wrong?
Thanks,
Tom
Ok,
I got it to work.
This time it worked correctly. I am not sure why it wasn't working before
but I had to leave the Logical File Names the same as it found on the device
and change the Physical name to the folder and name that were in the new
Database (tempFTS).
It is just confusing in the way the window is laid out.
Where it says
Restore Database as:
Logical File Name --> really should say something like "Logical File Name
From Device". That was what was confusing. I thought I tried that before
and it didn't work.
So the left column (Logical File Name) is from the Device and the Move to
Physical File is where your new Database is pointing at (I think).
Tom
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OAkV7%23D1FHA.3820@.TK2MSFTNGP10.phx.gbl...
>I have a database on my new server:
> tempFTS
> From Properties:
> FileName: tempFTS_Data Location: C:\Program Files\Microsoft Sql
> Server\MSSQL\Data\tempFTS_DATA.MDF
> Log: tempFTS_Log Location: C:\Program Files\Microsoft
> Sql Server\MSSQL\Data\tempFTS_LOG.LDF
> Backup from old Server:
> FTS_db_200510180200.BAK
> Right-Click tempFTS database and choose Restore Database
> Select From Device
> Select "Select Devices..."
> Select Add
> Double click on FTS_db_200510180200.BAK
> Select OK
> Go to Options and Displayed:
> FTS_log d:\Microsoft SQL Server\MSSQL\data\FTS.ldf
> FTS_dat d:\Microsoft SQL Server\MSSQL\data\FTS.mdf
> This is where it originally was.
> I now want to change the name to tempFTS
> So I change the Logical fileNames to (AS shown above from the properties
> of tempFTS:
> tempFTS_Log
> tempFTS_Data
> I change the Physical names to where I want the new files to be located
> C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_DATA.MDF
> C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_LOG.LDF
> Force Restore (just in case)
> I now get the error:
> Logical file 'tempFTS_Log', not part of the database "tempFTS".
> I then tried to put the Logical names back to FTS_log and FTS_dat to see
> what would happen there:
> Now it tries to overwrite the FTS_Data.mdf, which is the old database that
> I am restoring, but I want to put into TempFTS first to look at a couple
> of tables.
> What is the point of changing the Physical file names, if it is not going
> to use them?
> What did I do wrong?
> Thanks,
> Tom
>

Restore new server different database name not working

I have a database on my new server:
tempFTS
From Properties:
FileName: tempFTS_Data Location: C:\Program Files\Microsoft Sql
Server\MSSQL\Data\tempFTS_DATA.MDF
Log: tempFTS_Log Location: C:\Program Files\Microsoft
Sql Server\MSSQL\Data\tempFTS_LOG.LDF
Backup from old Server:
FTS_db_200510180200.BAK
Right-Click tempFTS database and choose Restore Database
Select From Device
Select "Select Devices..."
Select Add
Double click on FTS_db_200510180200.BAK
Select OK
Go to Options and Displayed:
FTS_log d:\Microsoft SQL Server\MSSQL\data\FTS.ldf
FTS_dat d:\Microsoft SQL Server\MSSQL\data\FTS.mdf
This is where it originally was.
I now want to change the name to tempFTS
So I change the Logical fileNames to (AS shown above from the properties of
tempFTS:
tempFTS_Log
tempFTS_Data
I change the Physical names to where I want the new files to be located
C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_DATA.MDF
C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_LOG.LDF
Force Restore (just in case)
I now get the error:
Logical file 'tempFTS_Log', not part of the database "tempFTS".
I then tried to put the Logical names back to FTS_log and FTS_dat to see
what would happen there:
Now it tries to overwrite the FTS_Data.mdf, which is the old database that I
am restoring, but I want to put into TempFTS first to look at a couple of
tables.
What is the point of changing the Physical file names, if it is not going to
use them?
What did I do wrong?
Thanks,
TomOk,
I got it to work.
This time it worked correctly. I am not sure why it wasn't working before
but I had to leave the Logical File Names the same as it found on the device
and change the Physical name to the folder and name that were in the new
Database (tempFTS).
It is just confusing in the way the window is laid out.
Where it says
Restore Database as:
Logical File Name --> really should say something like "Logical File Name
From Device". That was what was confusing. I thought I tried that before
and it didn't work.
So the left column (Logical File Name) is from the Device and the Move to
Physical File is where your new Database is pointing at (I think).
Tom
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OAkV7%23D1FHA.3820@.TK2MSFTNGP10.phx.gbl...
>I have a database on my new server:
> tempFTS
> From Properties:
> FileName: tempFTS_Data Location: C:\Program Files\Microsoft Sql
> Server\MSSQL\Data\tempFTS_DATA.MDF
> Log: tempFTS_Log Location: C:\Program Files\Microsoft
> Sql Server\MSSQL\Data\tempFTS_LOG.LDF
> Backup from old Server:
> FTS_db_200510180200.BAK
> Right-Click tempFTS database and choose Restore Database
> Select From Device
> Select "Select Devices..."
> Select Add
> Double click on FTS_db_200510180200.BAK
> Select OK
> Go to Options and Displayed:
> FTS_log d:\Microsoft SQL Server\MSSQL\data\FTS.ldf
> FTS_dat d:\Microsoft SQL Server\MSSQL\data\FTS.mdf
> This is where it originally was.
> I now want to change the name to tempFTS
> So I change the Logical fileNames to (AS shown above from the properties
> of tempFTS:
> tempFTS_Log
> tempFTS_Data
> I change the Physical names to where I want the new files to be located
> C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_DATA.MDF
> C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_LOG.LDF
> Force Restore (just in case)
> I now get the error:
> Logical file 'tempFTS_Log', not part of the database "tempFTS".
> I then tried to put the Logical names back to FTS_log and FTS_dat to see
> what would happen there:
> Now it tries to overwrite the FTS_Data.mdf, which is the old database that
> I am restoring, but I want to put into TempFTS first to look at a couple
> of tables.
> What is the point of changing the Physical file names, if it is not going
> to use them?
> What did I do wrong?
> Thanks,
> Tom
>

Restore new server different database name not working

I have a database on my new server:
tempFTS
From Properties:
FileName: tempFTS_Data Location: C:\Program Files\Microsoft Sql
Server\MSSQL\Data\tempFTS_DATA.MDF
Log: tempFTS_Log Location: C:\Program Files\Microsoft
Sql Server\MSSQL\Data\tempFTS_LOG.LDF
Backup from old Server:
FTS_db_200510180200.BAK
Right-Click tempFTS database and choose Restore Database
Select From Device
Select "Select Devices..."
Select Add
Double click on FTS_db_200510180200.BAK
Select OK
Go to Options and Displayed:
FTS_log d:\Microsoft SQL Server\MSSQL\data\FTS.ldf
FTS_dat d:\Microsoft SQL Server\MSSQL\data\FTS.mdf
This is where it originally was.
I now want to change the name to tempFTS
So I change the Logical fileNames to (AS shown above from the properties of
tempFTS:
tempFTS_Log
tempFTS_Data
I change the Physical names to where I want the new files to be located
C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_DATA.MDF
C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_LOG.LDF
Force Restore (just in case)
I now get the error:
Logical file 'tempFTS_Log', not part of the database "tempFTS".
I then tried to put the Logical names back to FTS_log and FTS_dat to see
what would happen there:
Now it tries to overwrite the FTS_Data.mdf, which is the old database that I
am restoring, but I want to put into TempFTS first to look at a couple of
tables.
What is the point of changing the Physical file names, if it is not going to
use them?
What did I do wrong?
Thanks,
TomOk,
I got it to work.
This time it worked correctly. I am not sure why it wasn't working before
but I had to leave the Logical File Names the same as it found on the device
and change the Physical name to the folder and name that were in the new
Database (tempFTS).
It is just confusing in the way the window is laid out.
Where it says
Restore Database as:
Logical File Name --> really should say something like "Logical File Name
From Device". That was what was confusing. I thought I tried that before
and it didn't work.
So the left column (Logical File Name) is from the Device and the Move to
Physical File is where your new Database is pointing at (I think).
Tom
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OAkV7%23D1FHA.3820@.TK2MSFTNGP10.phx.gbl...
>I have a database on my new server:
> tempFTS
> From Properties:
> FileName: tempFTS_Data Location: C:\Program Files\Microsoft Sql
> Server\MSSQL\Data\tempFTS_DATA.MDF
> Log: tempFTS_Log Location: C:\Program Files\Microsoft
> Sql Server\MSSQL\Data\tempFTS_LOG.LDF
> Backup from old Server:
> FTS_db_200510180200.BAK
> Right-Click tempFTS database and choose Restore Database
> Select From Device
> Select "Select Devices..."
> Select Add
> Double click on FTS_db_200510180200.BAK
> Select OK
> Go to Options and Displayed:
> FTS_log d:\Microsoft SQL Server\MSSQL\data\FTS.ldf
> FTS_dat d:\Microsoft SQL Server\MSSQL\data\FTS.mdf
> This is where it originally was.
> I now want to change the name to tempFTS
> So I change the Logical fileNames to (AS shown above from the properties
> of tempFTS:
> tempFTS_Log
> tempFTS_Data
> I change the Physical names to where I want the new files to be located
> C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_DATA.MDF
> C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_LOG.LDF
> Force Restore (just in case)
> I now get the error:
> Logical file 'tempFTS_Log', not part of the database "tempFTS".
> I then tried to put the Logical names back to FTS_log and FTS_dat to see
> what would happen there:
> Now it tries to overwrite the FTS_Data.mdf, which is the old database that
> I am restoring, but I want to put into TempFTS first to look at a couple
> of tables.
> What is the point of changing the Physical file names, if it is not going
> to use them?
> What did I do wrong?
> Thanks,
> Tom
>

Restore master database with SQL 2005 made an error with me

- First I made a full backup of master database

- Then I stopped SQL Server Service

- in cmd: cd C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Binn

then sqlservr.exe -m

- I entered that single user mode

> Problem here:

- I tried to connect to sql by sqlcmd -E >

HResult 0xE9, Level 16, State 1
Shared Memory Provider: No process is on the other end of the pipe.

Sqlcmd: Error: Microsoft SQL Native Client : Communication link failure.

> I tried to connect to management studio:

TITLE: Connect to Server

Cannot connect to BISHOYSERVER.

ADDITIONAL INFORMATION:

Login failed for user 'OFFICIALPROJECT\Administrator'. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)

You can not utilize Management Studio when SQL Server is in single user mode. SSMS launches multiple connections which is not allowed. When you launch sqlcmd, add the -S parameter to specify an instance name.|||

The SQL server will not allow a login for anything once in single user mode. I have been trying for a week to move the System databases to two RAID 1+0 disk enclosures (one for MDF and one for the Transaction logs) with no success.

The information given in documentation is inaccurate as the solution does not work.

How does one run the query 'ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME = data, FILENAME = 'E:\SQLData\mssqlsystemresource.mdf'); go' if you cannot attach to the single user mode database?

Who is not telling us what?

|||If you can't connect, then that means someone or something else is grabbing the only connection when you put it in single user mode. You need to find every connection that is normally there and make sure that every application which would connect is shut down and can not get to the SQL Server and that no one else is connecting either. Worst case scenario if you can't get everything to quit connecting and tying up your connection is that you walk into the data center, login directly to the console, walk around behind the machine, yank the network cable out of the back, and then do your work. At that point, it is completely impossible for anyone else to connect to the machine.|||I was having this same problem. This is a brand new server that's no one is even connected to, so when i saw Michael's post, I realized that there were a few other SQL related services that were running that could be connecting to the database server.

My guess is that the reporting service is what was connecting, but just to be sure, I stopped all services and then followed Microsoft's guide to moving the master and mssqlsystemresource databases. This seemed to fix the problem.

Hope this helps!
|||

Stop the SQL server reporting services

and login using syntax

sqlcmd -E -S %SQLName% -Q"ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME = 'log', FILENAME = 'f:\sql05data\mssqlsystemresource.ldf')"

Cheers !!!

Nithin

Restore master database with SQL 2005 made an error with me

- First I made a full backup of master database

- Then I stopped SQL Server Service

- in cmd: cd C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Binn

then sqlservr.exe -m

- I entered that single user mode

> Problem here:

- I tried to connect to sql by sqlcmd -E >

HResult 0xE9, Level 16, State 1
Shared Memory Provider: No process is on the other end of the pipe.

Sqlcmd: Error: Microsoft SQL Native Client : Communication link failure.

> I tried to connect to management studio:

TITLE: Connect to Server

Cannot connect to BISHOYSERVER.

ADDITIONAL INFORMATION:

Login failed for user 'OFFICIALPROJECT\Administrator'. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)

You can not utilize Management Studio when SQL Server is in single user mode. SSMS launches multiple connections which is not allowed. When you launch sqlcmd, add the -S parameter to specify an instance name.|||

The SQL server will not allow a login for anything once in single user mode. I have been trying for a week to move the System databases to two RAID 1+0 disk enclosures (one for MDF and one for the Transaction logs) with no success.

The information given in documentation is inaccurate as the solution does not work.

How does one run the query 'ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME = data, FILENAME = 'E:\SQLData\mssqlsystemresource.mdf'); go' if you cannot attach to the single user mode database?

Who is not telling us what?

|||If you can't connect, then that means someone or something else is grabbing the only connection when you put it in single user mode. You need to find every connection that is normally there and make sure that every application which would connect is shut down and can not get to the SQL Server and that no one else is connecting either. Worst case scenario if you can't get everything to quit connecting and tying up your connection is that you walk into the data center, login directly to the console, walk around behind the machine, yank the network cable out of the back, and then do your work. At that point, it is completely impossible for anyone else to connect to the machine.|||I was having this same problem. This is a brand new server that's no one is even connected to, so when i saw Michael's post, I realized that there were a few other SQL related services that were running that could be connecting to the database server.

My guess is that the reporting service is what was connecting, but just to be sure, I stopped all services and then followed Microsoft's guide to moving the master and mssqlsystemresource databases. This seemed to fix the problem.

Hope this helps!
|||

Stop the SQL server reporting services

and login using syntax

sqlcmd -E -S %SQLName% -Q"ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME = 'log', FILENAME = 'f:\sql05data\mssqlsystemresource.ldf')"

Cheers !!!

Nithin

Restore master database with SQL 2005 made an error with me

- First I made a full backup of master database

- Then I stopped SQL Server Service

- in cmd: cd C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Binn

then sqlservr.exe -m

- I entered that single user mode

> Problem here:

- I tried to connect to sql by sqlcmd -E >

HResult 0xE9, Level 16, State 1
Shared Memory Provider: No process is on the other end of the pipe.

Sqlcmd: Error: Microsoft SQL Native Client : Communication link failure.

> I tried to connect to management studio:

TITLE: Connect to Server

Cannot connect to BISHOYSERVER.

ADDITIONAL INFORMATION:

Login failed for user 'OFFICIALPROJECT\Administrator'. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)

You can not utilize Management Studio when SQL Server is in single user mode. SSMS launches multiple connections which is not allowed. When you launch sqlcmd, add the -S parameter to specify an instance name.|||

The SQL server will not allow a login for anything once in single user mode. I have been trying for a week to move the System databases to two RAID 1+0 disk enclosures (one for MDF and one for the Transaction logs) with no success.

The information given in documentation is inaccurate as the solution does not work.

How does one run the query 'ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME = data, FILENAME = 'E:\SQLData\mssqlsystemresource.mdf'); go' if you cannot attach to the single user mode database?

Who is not telling us what?

|||If you can't connect, then that means someone or something else is grabbing the only connection when you put it in single user mode. You need to find every connection that is normally there and make sure that every application which would connect is shut down and can not get to the SQL Server and that no one else is connecting either. Worst case scenario if you can't get everything to quit connecting and tying up your connection is that you walk into the data center, login directly to the console, walk around behind the machine, yank the network cable out of the back, and then do your work. At that point, it is completely impossible for anyone else to connect to the machine.|||I was having this same problem. This is a brand new server that's no one is even connected to, so when i saw Michael's post, I realized that there were a few other SQL related services that were running that could be connecting to the database server.

My guess is that the reporting service is what was connecting, but just to be sure, I stopped all services and then followed Microsoft's guide to moving the master and mssqlsystemresource databases. This seemed to fix the problem.

Hope this helps!|||

Stop the SQL server reporting services

and login using syntax

sqlcmd -E -S %SQLName% -Q"ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME = 'log', FILENAME = 'f:\sql05data\mssqlsystemresource.ldf')"

Cheers !!!

Nithin

Restore master database with SQL 2005 made an error with me

- First I made a full backup of master database
- Then I stopped SQL Server Service
- in cmd: cd C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Binn
then sqlservr.exe -m
- I entered that single user mode
--> Problem here:
- I tried to connect to sql by sqlcmd -E -->
HResult 0xE9, Level 16, State 1
Shared Memory Provider: No process is on the other end of the pipe.
Sqlcmd: Error: Microsoft SQL Native Client : Communication link failure.
--> I tried to connect to management studio:
TITLE: Connect to Server
Cannot connect to BISHOYSERVER.
ADDITIONAL INFORMATION:
Login failed for user 'OFFICIALPROJECT\Administrator'. Reason: Server is in
single user mode. Only one administrator can connect at this time. (Microsoft
SQL Server, Error: 18461)
Thank you
Bishoy
No one else is connected.
Agent service, as you should know, is stopped immediately when you stop
MSSqlServer service.
Also this SQL server instance is on my machine and I am sure no application
uses it.
Thanks
Bishoy
"Tibor Karaszi" wrote:

> Somebody else is connected. That might be Agent service, some user or some other service or local
> app. Hunt down this, then connect using SQLCMD.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Bishoy" <Bishoy@.discussions.microsoft.com> wrote in message
> news:5D9C15D7-1B68-444E-957B-83932E76AC9E@.microsoft.com...
>

Restore master database with SQL 2005 made an error with me

- First I made a full backup of master database
- Then I stopped SQL Server Service
- in cmd: cd C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Binn
then sqlservr.exe -m
- I entered that single user mode
--> Problem here:
- I tried to connect to sql by sqlcmd -E -->
HResult 0xE9, Level 16, State 1
Shared Memory Provider: No process is on the other end of the pipe.
Sqlcmd: Error: Microsoft SQL Native Client : Communication link failure.
--> I tried to connect to management studio:
TITLE: Connect to Server
--
Cannot connect to BISHOYSERVER.
ADDITIONAL INFORMATION:
Login failed for user 'OFFICIALPROJECT\Administrator'. Reason: Server is in
single user mode. Only one administrator can connect at this time. (Microsof
t
SQL Server, Error: 18461)
Thank you
BishoySomebody else is connected. That might be Agent service, some user or some o
ther service or local
app. Hunt down this, then connect using SQLCMD.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bishoy" <Bishoy@.discussions.microsoft.com> wrote in message
news:5D9C15D7-1B68-444E-957B-83932E76AC9E@.microsoft.com...
>- First I made a full backup of master database
> - Then I stopped SQL Server Service
> - in cmd: cd C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Binn
> then sqlservr.exe -m
> - I entered that single user mode
> --> Problem here:
> - I tried to connect to sql by sqlcmd -E -->
> HResult 0xE9, Level 16, State 1
> Shared Memory Provider: No process is on the other end of the pipe.
> Sqlcmd: Error: Microsoft SQL Native Client : Communication link failure.
> --> I tried to connect to management studio:
> TITLE: Connect to Server
> --
> Cannot connect to BISHOYSERVER.
> --
> ADDITIONAL INFORMATION:
> Login failed for user 'OFFICIALPROJECT\Administrator'. Reason: Server is i
n
> single user mode. Only one administrator can connect at this time. (Micros
oft
> SQL Server, Error: 18461)
> Thank you
> Bishoy|||No one else is connected.
Agent service, as you should know, is stopped immediately when you stop
MSSqlServer service.
Also this SQL server instance is on my machine and I am sure no application
uses it.
Thanks
Bishoy
"Tibor Karaszi" wrote:

> Somebody else is connected. That might be Agent service, some user or some
other service or local
> app. Hunt down this, then connect using SQLCMD.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Bishoy" <Bishoy@.discussions.microsoft.com> wrote in message
> news:5D9C15D7-1B68-444E-957B-83932E76AC9E@.microsoft.com...
>|||Is it a named instance?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bishoy" <Bishoy@.discussions.microsoft.com> wrote in message
news:7C480D1F-D426-41CA-A850-B5679ABB9785@.microsoft.com...[vbcol=seagreen]
> No one else is connected.
> Agent service, as you should know, is stopped immediately when you stop
> MSSqlServer service.
> Also this SQL server instance is on my machine and I am sure no applicatio
n
> uses it.
> Thanks
> Bishoy
> --
> "Tibor Karaszi" wrote:
>

Restore master database with SQL 2005 made an error with me

- First I made a full backup of master database
- Then I stopped SQL Server Service
- in cmd: cd C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Binn
then sqlservr.exe -m
- I entered that single user mode
--> Problem here:
- I tried to connect to sql by sqlcmd -E -->
HResult 0xE9, Level 16, State 1
Shared Memory Provider: No process is on the other end of the pipe.
Sqlcmd: Error: Microsoft SQL Native Client : Communication link failure.
--> I tried to connect to management studio:
TITLE: Connect to Server
--
Cannot connect to BISHOYSERVER.
--
ADDITIONAL INFORMATION:
Login failed for user 'OFFICIALPROJECT\Administrator'. Reason: Server is in
single user mode. Only one administrator can connect at this time. (Microsoft
SQL Server, Error: 18461)
Thank you
BishoySomebody else is connected. That might be Agent service, some user or some other service or local
app. Hunt down this, then connect using SQLCMD.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bishoy" <Bishoy@.discussions.microsoft.com> wrote in message
news:5D9C15D7-1B68-444E-957B-83932E76AC9E@.microsoft.com...
>- First I made a full backup of master database
> - Then I stopped SQL Server Service
> - in cmd: cd C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Binn
> then sqlservr.exe -m
> - I entered that single user mode
> --> Problem here:
> - I tried to connect to sql by sqlcmd -E -->
> HResult 0xE9, Level 16, State 1
> Shared Memory Provider: No process is on the other end of the pipe.
> Sqlcmd: Error: Microsoft SQL Native Client : Communication link failure.
> --> I tried to connect to management studio:
> TITLE: Connect to Server
> --
> Cannot connect to BISHOYSERVER.
> --
> ADDITIONAL INFORMATION:
> Login failed for user 'OFFICIALPROJECT\Administrator'. Reason: Server is in
> single user mode. Only one administrator can connect at this time. (Microsoft
> SQL Server, Error: 18461)
> Thank you
> Bishoy|||No one else is connected.
Agent service, as you should know, is stopped immediately when you stop
MSSqlServer service.
Also this SQL server instance is on my machine and I am sure no application
uses it.
Thanks
Bishoy
--
"Tibor Karaszi" wrote:
> Somebody else is connected. That might be Agent service, some user or some other service or local
> app. Hunt down this, then connect using SQLCMD.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Bishoy" <Bishoy@.discussions.microsoft.com> wrote in message
> news:5D9C15D7-1B68-444E-957B-83932E76AC9E@.microsoft.com...
> >- First I made a full backup of master database
> >
> > - Then I stopped SQL Server Service
> >
> > - in cmd: cd C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Binn
> >
> > then sqlservr.exe -m
> >
> > - I entered that single user mode
> >
> > --> Problem here:
> >
> > - I tried to connect to sql by sqlcmd -E -->
> >
> > HResult 0xE9, Level 16, State 1
> > Shared Memory Provider: No process is on the other end of the pipe.
> >
> > Sqlcmd: Error: Microsoft SQL Native Client : Communication link failure.
> >
> > --> I tried to connect to management studio:
> >
> > TITLE: Connect to Server
> > --
> >
> > Cannot connect to BISHOYSERVER.
> >
> > --
> > ADDITIONAL INFORMATION:
> >
> > Login failed for user 'OFFICIALPROJECT\Administrator'. Reason: Server is in
> > single user mode. Only one administrator can connect at this time. (Microsoft
> > SQL Server, Error: 18461)
> >
> > Thank you
> > Bishoy
>|||Is it a named instance?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bishoy" <Bishoy@.discussions.microsoft.com> wrote in message
news:7C480D1F-D426-41CA-A850-B5679ABB9785@.microsoft.com...
> No one else is connected.
> Agent service, as you should know, is stopped immediately when you stop
> MSSqlServer service.
> Also this SQL server instance is on my machine and I am sure no application
> uses it.
> Thanks
> Bishoy
> --
> "Tibor Karaszi" wrote:
>> Somebody else is connected. That might be Agent service, some user or some other service or local
>> app. Hunt down this, then connect using SQLCMD.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Bishoy" <Bishoy@.discussions.microsoft.com> wrote in message
>> news:5D9C15D7-1B68-444E-957B-83932E76AC9E@.microsoft.com...
>> >- First I made a full backup of master database
>> >
>> > - Then I stopped SQL Server Service
>> >
>> > - in cmd: cd C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Binn
>> >
>> > then sqlservr.exe -m
>> >
>> > - I entered that single user mode
>> >
>> > --> Problem here:
>> >
>> > - I tried to connect to sql by sqlcmd -E -->
>> >
>> > HResult 0xE9, Level 16, State 1
>> > Shared Memory Provider: No process is on the other end of the pipe.
>> >
>> > Sqlcmd: Error: Microsoft SQL Native Client : Communication link failure.
>> >
>> > --> I tried to connect to management studio:
>> >
>> > TITLE: Connect to Server
>> > --
>> >
>> > Cannot connect to BISHOYSERVER.
>> >
>> > --
>> > ADDITIONAL INFORMATION:
>> >
>> > Login failed for user 'OFFICIALPROJECT\Administrator'. Reason: Server is in
>> > single user mode. Only one administrator can connect at this time. (Microsoft
>> > SQL Server, Error: 18461)
>> >
>> > Thank you
>> > Bishoy
>>

restore master database using sqlservr.exe at command line

When I restore master database by executing sqlservr.exe
at command line I encountered an error message:
Cannot find the file 'C:\Program' (or one of its
components). Make sure the path and filename are correct
and that all required libraries are available.
I have checked sqlservr.exe file it is there.
Thank you for help in advance.
Jeff LeeJeff
It sounds like you might have just specified the location of the executable
incorrectly. Can you actual move to the right directory first, and then run
sqlservr.exe right from there? Or show us the entire command line you are
using.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<anonymous@.discussions.microsoft.com> wrote in message
news:039901c3d3c7$9c67d930$a101280a@.phx.gbl...
> When I restore master database by executing sqlservr.exe
> at command line I encountered an error message:
> Cannot find the file 'C:\Program' (or one of its
> components). Make sure the path and filename are correct
> and that all required libraries are available.
> I have checked sqlservr.exe file it is there.
> Thank you for help in advance.
> Jeff Lee|||Kalen,
Thank you for your direction. Following is the command I
used in command line
C:\Program Files\Microsoft SQL
Server\MSSQL\Binn\sqlservr.exe -c -m
It appears that sqlservr.exe reacts (quickly shows command
window and disappears serveral seconds later) when just
executing it without parameters -c -m.
>--Original Message--
>Jeff
>It sounds like you might have just specified the location
of the executable
>incorrectly. Can you actual move to the right directory
first, and then run
>sqlservr.exe right from there? Or show us the entire
command line you are
>using.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:039901c3d3c7$9c67d930$a101280a@.phx.gbl...
>> When I restore master database by executing sqlservr.exe
>> at command line I encountered an error message:
>> Cannot find the file 'C:\Program' (or one of its
>> components). Make sure the path and filename are correct
>> and that all required libraries are available.
>> I have checked sqlservr.exe file it is there.
>> Thank you for help in advance.
>> Jeff Lee
>
>.
>|||If you use that as the command, the parser will choke at the first space and
give you the error about Program not being recognized. You could put the
whole command in quotes, or as I suggested, first navigate to the binn
directory, and run sqlservr.exe from there.
If I put the command below in quotes, with or without the parameters, it
starts the SQL Server in that command window. From another command window,
or from QA, I can connect normally. Note that if you close the command
window where you started SQL Server it will stop the SQL Server.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<anonymous@.discussions.microsoft.com> wrote in message
news:03ff01c3d3cc$da6e0ba0$a101280a@.phx.gbl...
> Kalen,
> Thank you for your direction. Following is the command I
> used in command line
> C:\Program Files\Microsoft SQL
> Server\MSSQL\Binn\sqlservr.exe -c -m
> It appears that sqlservr.exe reacts (quickly shows command
> window and disappears serveral seconds later) when just
> executing it without parameters -c -m.
>
> >--Original Message--
> >Jeff
> >
> >It sounds like you might have just specified the location
> of the executable
> >incorrectly. Can you actual move to the right directory
> first, and then run
> >sqlservr.exe right from there? Or show us the entire
> command line you are
> >using.
> >
> >--
> >HTH
> >--
> >Kalen Delaney
> >SQL Server MVP
> >www.SolidQualityLearning.com
> >
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:039901c3d3c7$9c67d930$a101280a@.phx.gbl...
> >> When I restore master database by executing sqlservr.exe
> >> at command line I encountered an error message:
> >>
> >> Cannot find the file 'C:\Program' (or one of its
> >> components). Make sure the path and filename are correct
> >> and that all required libraries are available.
> >>
> >> I have checked sqlservr.exe file it is there.
> >>
> >> Thank you for help in advance.
> >>
> >> Jeff Lee
> >
> >
> >.
> >