Restore SQL databes to diffrent server using Scripts

I hope someone here can point me in the right direction.
We currently have a "Live" server and a "Backup" server both using SQL
server 2000.
We are trying automating the database restore between these servers if
possible.
It is currently being done by hand.
We have 40 Databases to recover. They are not that big, just time
consuming to restore the data and fix the assigned users which are not
recreated during the restore.
We use the "sp_change_users_login auto_fix, <username>" to recreate the
link between the user and its database.
Is there a way to script this? I'm not very fluent with SQL and queries
so I'm not sure if scripting this or a stored procedure would be easy
to setup.
Any help will be gratefully received!
Bill C
You could consider creating a DTS Package to do this for you. Here are some
articles that will help :-
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
HTH. Ryan
"Williamc_sircon" <williamc@.sircon.co.uk> wrote in message
news:1139826814.497790.140590@.f14g2000cwb.googlegr oups.com...
>I hope someone here can point me in the right direction.
> We currently have a "Live" server and a "Backup" server both using SQL
> server 2000.
> We are trying automating the database restore between these servers if
> possible.
> It is currently being done by hand.
> We have 40 Databases to recover. They are not that big, just time
> consuming to restore the data and fix the assigned users which are not
> recreated during the restore.
> We use the "sp_change_users_login auto_fix, <username>" to recreate the
> link between the user and its database.
> Is there a way to script this? I'm not very fluent with SQL and queries
> so I'm not sure if scripting this or a stored procedure would be easy
> to setup.
>
> Any help will be gratefully received!
> Bill C
>
|||Many thanks!!
These will keep me going for a while by the looks of it!!
Laters
Bill C
sql

Restore SQL databes to diffrent server using Scripts

I hope someone here can point me in the right direction.
We currently have a "Live" server and a "Backup" server both using SQL
server 2000.
We are trying automating the database restore between these servers if
possible.
It is currently being done by hand.
We have 40 Databases to recover. They are not that big, just time
consuming to restore the data and fix the assigned users which are not
recreated during the restore.
We use the "sp_change_users_login auto_fix, <username>" to recreate the
link between the user and its database.
Is there a way to script this? I'm not very fluent with SQL and queries
so I'm not sure if scripting this or a stored procedure would be easy
to setup.
Any help will be gratefully received!
Bill CYou could consider creating a DTS Package to do this for you. Here are some
articles that will help :-
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
HTH. Ryan
"Williamc_sircon" <williamc@.sircon.co.uk> wrote in message
news:1139826814.497790.140590@.f14g2000cwb.googlegroups.com...
>I hope someone here can point me in the right direction.
> We currently have a "Live" server and a "Backup" server both using SQL
> server 2000.
> We are trying automating the database restore between these servers if
> possible.
> It is currently being done by hand.
> We have 40 Databases to recover. They are not that big, just time
> consuming to restore the data and fix the assigned users which are not
> recreated during the restore.
> We use the "sp_change_users_login auto_fix, <username>" to recreate the
> link between the user and its database.
> Is there a way to script this? I'm not very fluent with SQL and queries
> so I'm not sure if scripting this or a stored procedure would be easy
> to setup.
>
> Any help will be gratefully received!
> Bill C
>|||Many thanks!!
These will keep me going for a while by the looks of it!!
Laters
Bill C

Restore readonly filegroup sqlserver 2000

Hi,

I have a database running on sqlserver 2000. This database (let's call it TestDatabase) has 2 filegroups called 'PRIMARY' and 'SECONDARY', and is bulk-logged. The 'SECONDARY' filegroup is set to readonly since no data gets changed there by the applications, it is read-only data. Sometimes this read-only data needs an update, so I need to update the data in the 'SECONDARY' filegroup by running some long data-operations. These data-operations are executed and checked on a another database (which is a backup of TestDatabase) called TestDatabaseDemo. When everything is correct in TestDatabaseDemo we can copy all objects from the 'SECONDARY'-filegroup of TestDatabaseDemo to the 'SECONDARY'-filegroup of TestDatabase using DTS. This takes a long time and I want to try if I could speed up things by using the following strategy:

Take a full backup of TestDatabase

Restore full backup as TestDatabaseDemo

Run long-running data-operations on TestDatabaseDemo

Take a backup of TestDatabaseDemo

Restore only the 'SECONDARY'-filegroup from TestDatabaseDemo to TestDatabase, and keeping the 'PRIMARY' filegroup from TestDatabase as it was at that moment.

Suppose we arrived at step 5, I am executing the following commands:

Code Snippet

--make full backup of TestDatabase
backup database TestDatabase to DISK='G:\temp\testdatabase.bak' with init
--make full backup of TestDatabaseDemo
backup database TestDatabaseDemo to DISK='G:\temp\testdatabasedemo.bak' with init

backup log TestDatabase to DISK='g:\temp\testdatabase.log'

--restore secondary filegroup from TestDatabasedemo-backup

restore database TestDatabase FILEGROUP='SECONDARY' FROM DISK='G:\temp\testdatabasedemo.bak'
with move 'TestDatabase_Data_Secondary' to 'G:\temp\TestDatabase_Data_secondary.ndf', NORECOVERY

--restore primary filegroup from Testdatabase-backup
restore database TestDatabase FILEGROUP='PRIMARY' FROM DISK='G:\temp\testdatabase.bak'
with move 'TestDatabase_Data' to 'G:\temp\TestDatabase_Data.mdf', NORECOVERY
--restore log and try to get db onlin

restore log TestDatabase FROM DISK='g:\temp\testdatabase.log' with recovery

I get the following error:

The log in this backup set terminates at LSN 6000000021500001, which is too early to apply to the database. A more recent log backup that includes LSN 6000000022400003 can be restored.

When trying to execute "RESTORE DATABASE TestDatabase WITH RECOVERY; " as last statement

I get error:

The database cannot be recovered because the files have been restored to inconsistent points in time.

How i can restore the read-only filegroup correctly?

Hi Stijn,

Simple answer is that you can't do that. The SECONDARY filegroup is now at a different (more recent) point in time than the rest of the TestDatabase - exactly like the final error message says. What you're essentially trying to do is mix-n-match filegroups from different databases.

Thanks

|||

Hi Paul,

Thanks for the answer! I was thinking the mix-n-match would work, because that filegroup is readonly for the database, so applying the transaction log would be good enough for the other (read-write) filegroup, since there couldn't be any inserts/updates/deletes on the secondary filegroup. Which technique is used on very large databases to update a read-only filegroup? Using dts to copy objects from a test-database to the production database? Or is there a more efficient way?

Restore readonly filegroup sqlserver 2000

Hi,

I have a database running on sqlserver 2000. This database (let's call it TestDatabase) has 2 filegroups called 'PRIMARY' and 'SECONDARY', and is bulk-logged. The 'SECONDARY' filegroup is set to readonly since no data gets changed there by the applications, it is read-only data. Sometimes this read-only data needs an update, so I need to update the data in the 'SECONDARY' filegroup by running some long data-operations. These data-operations are executed and checked on a another database (which is a backup of TestDatabase) called TestDatabaseDemo. When everything is correct in TestDatabaseDemo we can copy all objects from the 'SECONDARY'-filegroup of TestDatabaseDemo to the 'SECONDARY'-filegroup of TestDatabase using DTS. This takes a long time and I want to try if I could speed up things by using the following strategy:

Take a full backup of TestDatabase

Restore full backup as TestDatabaseDemo

Run long-running data-operations on TestDatabaseDemo

Take a backup of TestDatabaseDemo

Restore only the 'SECONDARY'-filegroup from TestDatabaseDemo to TestDatabase, and keeping the 'PRIMARY' filegroup from TestDatabase as it was at that moment.

Suppose we arrived at step 5, I am executing the following commands:

Code Snippet

--make full backup of TestDatabase
backup database TestDatabase to DISK='G:\temp\testdatabase.bak' with init
--make full backup of TestDatabaseDemo
backup database TestDatabaseDemo to DISK='G:\temp\testdatabasedemo.bak' with init

backup log TestDatabase to DISK='g:\temp\testdatabase.log'

--restore secondary filegroup from TestDatabasedemo-backup

restore database TestDatabase FILEGROUP='SECONDARY' FROM DISK='G:\temp\testdatabasedemo.bak'
with move 'TestDatabase_Data_Secondary' to 'G:\temp\TestDatabase_Data_secondary.ndf', NORECOVERY

--restore primary filegroup from Testdatabase-backup
restore database TestDatabase FILEGROUP='PRIMARY' FROM DISK='G:\temp\testdatabase.bak'
with move 'TestDatabase_Data' to 'G:\temp\TestDatabase_Data.mdf', NORECOVERY
--restore log and try to get db onlin

restore log TestDatabase FROM DISK='g:\temp\testdatabase.log' with recovery

I get the following error:

The log in this backup set terminates at LSN 6000000021500001, which is too early to apply to the database. A more recent log backup that includes LSN 6000000022400003 can be restored.

When trying to execute "RESTORE DATABASE TestDatabase WITH RECOVERY; " as last statement

I get error:

The database cannot be recovered because the files have been restored to inconsistent points in time.

How i can restore the read-only filegroup correctly?

Hi Stijn,

Simple answer is that you can't do that. The SECONDARY filegroup is now at a different (more recent) point in time than the rest of the TestDatabase - exactly like the final error message says. What you're essentially trying to do is mix-n-match filegroups from different databases.

Thanks

|||

Hi Paul,

Thanks for the answer! I was thinking the mix-n-match would work, because that filegroup is readonly for the database, so applying the transaction log would be good enough for the other (read-write) filegroup, since there couldn't be any inserts/updates/deletes on the secondary filegroup. Which technique is used on very large databases to update a read-only filegroup? Using dts to copy objects from a test-database to the production database? Or is there a more efficient way?

restore question

Hi
I am taking a backup of SQLSERVER on remote machine. I need to restore the
database on a daily basis on the remote machine.
I take backup of transaction log using maintenance plan every 30 mins.
It generates 48 transaciton log per day.
How can write a script to restore the database applying those 48 logs.
I can create linked server to production but what table keeps the backup log
information that I can use to build this.
Thanks
Mangesh
I keep my own table to track which log files I've applied
I store the table in a DB named "Admin"
then I select the logfiles on the file system that have not yet been applied
sorted in datetime order for example
then I apply them one at a time.
hope this makes sense.
I can dig up the scripts if you really need (But it will cost you some
Sushi)
Greg Jackson
PDX, Oregon
|||You can get all the DB backup history from the msdb database.
Specifically, you get the media_set_id values for the backups you wish
to restore from dbo.backupset based on DB name, DB vs LOG backups, dates
& times, etc., etc. And then you can get the physical file location for
those backups from dbo.backupmediafamily (using
dbo.backupset.media_set_id, which corresponds to
dbo.backupmediafamily.media_set_id). Something like:
select bs.backup_start_date, bs.type, bmf.physical_device_name
from msdb.dbo.backupset bs
inner join msdb.dbo.backupmediafamily bmf
on bmf.media_set_id = bs.media_set_id
where bs.database_name = N'MyDB'
order by bs.backup_start_date
Based on that kind of query it should be a piece of cake to automate a
restore procedure given some constraints around the restore (like
DBName, start datetime, finish datetime, etc.) also not forgetting to
restore a "D" backup followed by zero or more "L" backups.
HTH,
Mike.
PS. Do I get Sushi now?
pdxJaxon wrote:
> I keep my own table to track which log files I've applied
> I store the table in a DB named "Admin"
> then I select the logfiles on the file system that have not yet been applied
> sorted in datetime order for example
> then I apply them one at a time.
>
> hope this makes sense.
> I can dig up the scripts if you really need (But it will cost you some
> Sushi)
>
> Greg Jackson
> PDX, Oregon
>
C
|||Hi Mangesh,
If your objective is to have a standby database, have you thought of SQL
Server log-shipping as an option? Its pretty simple to set-up and get it
running.
Thanks
Yogish

restore question

Hi
I am taking a backup of SQLSERVER on remote machine. I need to restore the
database on a daily basis on the remote machine.
I take backup of transaction log using maintenance plan every 30 mins.
It generates 48 transaciton log per day.
How can write a script to restore the database applying those 48 logs.
I can create linked server to production but what table keeps the backup log
information that I can use to build this.
Thanks
MangeshI keep my own table to track which log files I've applied
I store the table in a DB named "Admin"
then I select the logfiles on the file system that have not yet been applied
sorted in datetime order for example
then I apply them one at a time.
hope this makes sense.
I can dig up the scripts if you really need (But it will cost you some
Sushi)
Greg Jackson
PDX, Oregon|||You can get all the DB backup history from the msdb database.
Specifically, you get the media_set_id values for the backups you wish
to restore from dbo.backupset based on DB name, DB vs LOG backups, dates
& times, etc., etc. And then you can get the physical file location for
those backups from dbo.backupmediafamily (using
dbo.backupset.media_set_id, which corresponds to
dbo.backupmediafamily.media_set_id). Something like:
select bs.backup_start_date, bs.type, bmf.physical_device_name
from msdb.dbo.backupset bs
inner join msdb.dbo.backupmediafamily bmf
on bmf.media_set_id = bs.media_set_id
where bs.database_name = N'MyDB'
order by bs.backup_start_date
Based on that kind of query it should be a piece of cake to automate a
restore procedure given some constraints around the restore (like
DBName, start datetime, finish datetime, etc.) also not forgetting to
restore a "D" backup followed by zero or more "L" backups.
HTH,
Mike.
PS. Do I get Sushi now?
pdxJaxon wrote:
> I keep my own table to track which log files I've applied
> I store the table in a DB named "Admin"
> then I select the logfiles on the file system that have not yet been applied
> sorted in datetime order for example
> then I apply them one at a time.
>
> hope this makes sense.
> I can dig up the scripts if you really need (But it will cost you some
> Sushi)
>
> Greg Jackson
> PDX, Oregon
>
--
ÿþC|||Hi Mangesh,
If your objective is to have a standby database, have you thought of SQL
Server log-shipping as an option? Its pretty simple to set-up and get it
running.
--
Thanks
Yogish

restore question

Hi
I am taking a backup of SQLSERVER on remote machine. I need to restore the
database on a daily basis on the remote machine.
I take backup of transaction log using maintenance plan every 30 mins.
It generates 48 transaciton log per day.
How can write a script to restore the database applying those 48 logs.
I can create linked server to production but what table keeps the backup log
information that I can use to build this.
Thanks
MangeshI keep my own table to track which log files I've applied
I store the table in a DB named "Admin"
then I select the logfiles on the file system that have not yet been applied
sorted in datetime order for example
then I apply them one at a time.
hope this makes sense.
I can dig up the scripts if you really need (But it will cost you some
Sushi)
Greg Jackson
PDX, Oregon|||You can get all the DB backup history from the msdb database.
Specifically, you get the media_set_id values for the backups you wish
to restore from dbo.backupset based on DB name, DB vs LOG backups, dates
& times, etc., etc. And then you can get the physical file location for
those backups from dbo.backupmediafamily (using
dbo.backupset.media_set_id, which corresponds to
dbo.backupmediafamily.media_set_id). Something like:
select bs.backup_start_date, bs.type, bmf.physical_device_name
from msdb.dbo.backupset bs
inner join msdb.dbo.backupmediafamily bmf
on bmf.media_set_id = bs.media_set_id
where bs.database_name = N'MyDB'
order by bs.backup_start_date
Based on that kind of query it should be a piece of cake to automate a
restore procedure given some constraints around the restore (like
DBName, start datetime, finish datetime, etc.) also not forgetting to
restore a "D" backup followed by zero or more "L" backups.
HTH,
Mike.
PS. Do I get Sushi now?
pdxJaxon wrote:
> I keep my own table to track which log files I've applied
> I store the table in a DB named "Admin"
> then I select the logfiles on the file system that have not yet been appli
ed
> sorted in datetime order for example
> then I apply them one at a time.
>
> hope this makes sense.
> I can dig up the scripts if you really need (But it will cost you some
> Sushi)
>
> Greg Jackson
> PDX, Oregon
>
C|||Hi Mangesh,
If your objective is to have a standby database, have you thought of SQL
Server log-shipping as an option? Its pretty simple to set-up and get it
running.
Thanks
Yogish

restore multiple databases (Transact SQL?)

Does anyone have a good method for quickly restored multiple SQL
Server 2000 databases'
I'm hoping to execute a Transact SQL script that will restore a bunch
of databases all in one step.
Any ideas/comments?
ThankyouAndrew wrote:
> Does anyone have a good method for quickly restored multiple SQL
> Server 2000 databases'
RESTORE DATABASE [myDB]
FROM DISK = 'd:\Databases\backup\myDb.BAK'
WITH
MOVE 'myDB_Data' TO 'F:\Databases\myDB_Data.mdf',
MOVE 'myDB_Log' TO 'F:\Databases\myDB_Log.ldf',
RECOVERY
rinse, repeat
As you can see this is very scriptable.
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||In Addition to Aaron's response
You can detach a 'source' database. Copy mdf and ldf files and rename them.
Attach these databases with different names.
For more details please refer to BOL
sp_detach_db
sp_attach_db
Note: After remaining the mdf and ldf files you have to issue sp_detach_db
'new name database' in order to attach it successfuly.
"Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
news:u5zATcmDFHA.1836@.tk2msftngp13.phx.gbl...
> Andrew wrote:
>
> RESTORE DATABASE [myDB]
> FROM DISK = 'd:\Databases\backup\myDb.BAK'
> WITH
> MOVE 'myDB_Data' TO 'F:\Databases\myDB_Data.mdf',
> MOVE 'myDB_Log' TO 'F:\Databases\myDB_Log.ldf',
> RECOVERY
>
> rinse, repeat
> As you can see this is very scriptable.
> --
> Aaron Weiker
> http://aaronweiker.com/
> http://www.sqlprogrammer.org/

restore master without sqlserver running?

due to a snafu on the reconfiguring of a server, a raid with the
database files was reformatted. unfortunately, our backup software does
not backup the db files since they are in use at the time of backup.
now, sqlserver won't start because there is no master.mdf file. i do
have a backup of the master db. is there any way i can restore that
without sqlserver running? or will i have to reinstall sqlserver and
then restore master along with all the other db's?This is a multi-part message in MIME format.
--=_NextPart_000_0135_01C3A9CE.AE173720
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
You can use the rebuildm.exe utility to rebuild master and then restore your
backup once that is done.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"chxxx" <chxxx@.dontemailme.com> wrote in message
news:3FB39E43.BBB2A260@.dontemailme.com...
due to a snafu on the reconfiguring of a server, a raid with the
database files was reformatted. unfortunately, our backup software does
not backup the db files since they are in use at the time of backup.
now, sqlserver won't start because there is no master.mdf file. i do
have a backup of the master db. is there any way i can restore that
without sqlserver running? or will i have to reinstall sqlserver and
then restore master along with all the other db's?
--=_NextPart_000_0135_01C3A9CE.AE173720
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You can use the rebuildm.exe utility =to rebuild master and then restore your backup once that is done.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"chxxx" =wrote in message news:3FB39E43.BBB2A260@.do=ntemailme.com...due to a snafu on the reconfiguring of a server, a raid with thedatabase =files was reformatted. unfortunately, our backup software doesnot =backup the db files since they are in use at the time of backup.now, sqlserver =won't start because there is no master.mdf file. i dohave a backup =of the master db. is there any way i can restore thatwithout =sqlserver running? or will i have to reinstall sqlserver andthen restore =master along with all the other db's?

---

--=_NextPart_000_0135_01C3A9CE.AE173720--|||Ch
Look in books on line for how to restore your master
database. If you have a copy of the master.mdf, you may be
able to copy it to the location it should be in (d:mssql
or similar) and start sql server without needing to do
anything else.
If you can't restore master either way. If you still have
your user database you can do a fresh install and then
attach your databases. You will need to add your user
logins to the server before you can use it.
If you can not recover the msdb database you will also
need to recreate any jobs you had.
Hope this helps
John|||chxxx wrote:
> due to a snafu on the reconfiguring of a server, a raid with the
> database files was reformatted. unfortunately, our backup software
> does not backup the db files since they are in use at the time of
> backup. now, sqlserver won't start because there is no master.mdf
> file. i do have a backup of the master db. is there any way i can
> restore that without sqlserver running? or will i have to reinstall
> sqlserver and then restore master along with all the other db's?
when you say you have a backup of the master file is it a sql backup or
a standard file backup. If latter then you should be able to restore
that to its location and restart the server. If you are saying you have
a sql backup then you will need sql started in order to restore. You
can rebuild the master (rebuildm) and once sql is started you can
restore back from your sql backup to return to the original one.
--
Br,
Mark Broadbent
mcdba , mcse+i
=============|||Mark Broadbent wrote:
> chxxx wrote:
> > due to a snafu on the reconfiguring of a server, a raid with the
> > database files was reformatted. unfortunately, our backup software
> > does not backup the db files since they are in use at the time of
> > backup. now, sqlserver won't start because there is no master.mdf
> > file. i do have a backup of the master db. is there any way i can
> > restore that without sqlserver running? or will i have to reinstall
> > sqlserver and then restore master along with all the other db's?
> when you say you have a backup of the master file is it a sql backup or
> a standard file backup. If latter then you should be able to restore
> that to its location and restart the server. If you are saying you have
> a sql backup then you will need sql started in order to restore. You
> can rebuild the master (rebuildm) and once sql is started you can
> restore back from your sql backup to return to the original one.
the only backups i have are the sql backup files (.bak). the .mdf and .ldf
files are gone due to backup software not being able to back those up.
after reading bol, looks like i'll be able to use rebuildm to get sqlserver
running again.
then i'll be able to restore master and all other db's from the .bak files
i have on disk.|||chxxx wrote:
>
> Mark Broadbent wrote:
> > chxxx wrote:
> >
> > > due to a snafu on the reconfiguring of a server, a raid with the
> > > database files was reformatted. unfortunately, our backup
> > > software does not backup the db files since they are in use at
> > > the time of backup. now, sqlserver won't start because there is
> > > no master.mdf file. i do have a backup of the master db. is
> > > there any way i can restore that without sqlserver running? or
> > > will i have to reinstall sqlserver and then restore master along
> > > with all the other db's?
> >
> > when you say you have a backup of the master file is it a sql
> > backup or a standard file backup. If latter then you should be able
> > to restore that to its location and restart the server. If you are
> > saying you have a sql backup then you will need sql started in
> > order to restore. You can rebuild the master (rebuildm) and once
> > sql is started you can restore back from your sql backup to return
> > to the original one.
> the only backups i have are the sql backup files (.bak). the .mdf
> and .ldf files are gone due to backup software not being able to back
> those up.
> after reading bol, looks like i'll be able to use rebuildm to get
> sqlserver running again.
> then i'll be able to restore master and all other db's from the .bak
> files i have on disk.
Correct :?)
--
Br,
Mark Broadbent
mcdba , mcse+i
=============

restore master and not working the enterprise manager**

Hi
I started sqlserver 2000 as single user mode
and then tried to restore master db,and
it prompted "restoring master was completed successfully.and shut down the
sqlserver to complete."
but after this message the window of restoring remained
on main window and everything seemed to be halted
and I had to end task the enterprise manager and restarted
the computer but when I decided to run enterprise manager
again and to open the branches following error appearred:
"cannot open user default database login failed .pleas verify
sql server is running and check your sql server registration
properties by right clicking on it."
how can I repair this problem?
do I have to reinstall it again?
any help would be greatly thanked.Looks like you restored from an old master backup, and hence the default
database assigned to the login with which you registered the server in
Enterprise Manager is different from what's stored in the backup.
I suggest you go to command prompt, and login to SQL Server using the same
login with which you registered the server in Enterprise Manager (also
specify the default database as master). Here's how you would do it:
OSQL -S ServerNameHere -U LoginNameHere -P PasswordHere -d master
If you are using Windows authentication, then run the following command
instead:
OSQL -S ServerNameHere -E -d master
Once logged into SQL Server, use sp_defaultdb procedure to change the
default database of your login to an exisiting database. Once this is done
successfully, quit OSQL, go back to Enterprise Manager, and now you should
be able to connect fine. You can find more info and examples on sp_defaultdb
in SQL Server Books Online.
Note that if you create new databases before the backup (from which you
restored now) was taken, then you will have to restore or attach those
databases again.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsayj8ju4hqligo@.msnews.microsoft.com...
Hi
I started sqlserver 2000 as single user mode
and then tried to restore master db,and
it prompted "restoring master was completed successfully.and shut down the
sqlserver to complete."
but after this message the window of restoring remained
on main window and everything seemed to be halted
and I had to end task the enterprise manager and restarted
the computer but when I decided to run enterprise manager
again and to open the branches following error appearred:
"cannot open user default database login failed .pleas verify
sql server is running and check your sql server registration
properties by right clicking on it."
how can I repair this problem?
do I have to reinstall it again?
any help would be greatly thanked.