Restore system dbs from SP3 to SP3a?

Can system databases (master, model, msdb) backed up from SQL2k SP3 be restored to an instance of SP3a? Can they be restored from SP3a to SP3? TIA!Not going to happen buddy. try installing a fresh copy of sql server somewhere else upgrade to sp3, restore your system db's there, upgrade to sp3a then move the system db's to the live server. hope this helps.|||Thanks - that's what I thought. However, how can it be determined which SP (3 or 3a) a given instance is running? @.@.version returns 8.00.760 for both of them, & Properties in EM show SP3 for both. I remember there being a way to find the build number, but I don't know how to get it. TIA.|||Check with MS website at the SP3/SP3a download pages. They usually have a section that tells you how to determine which SP you're currently running under.|||Even better: http://www.sqlteam.com/item.asp?ItemID=8318

restore nightmares

I wasn't paying attention and I started a restore on one of my dbs.
I stoped in the middle of it. I tried to reconnect to my db but I was
told that I couldn't. I restored a recent copy but lost all the data I
worked on today. Any chance I can restore it?
Unfortunately not.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"Won Lee" <noemail@.nospam.com> wrote in message
news:er9yAwiWEHA.716@.TK2MSFTNGP11.phx.gbl...
> I wasn't paying attention and I started a restore on one of my dbs.
> I stoped in the middle of it. I tried to reconnect to my db but I was
> told that I couldn't. I restored a recent copy but lost all the data I
> worked on today. Any chance I can restore it?
>
|||Hi,
What type of recovery model you are using? If it is FULL or BULK_Logged you
can recover back the database
provided if you have taken Trasaction log backup.
1. Restore Yesterdays full backup with NORECOVERY
2. Restore the transaction logs one by one till the last with NORECOVERY
3. Restore the last transaction log with RECOVERY option.
This will recover the database till the last trasnaction log backup.
Thanks
Hari
MCDBA
"Won Lee" <noemail@.nospam.com> wrote in message
news:er9yAwiWEHA.716@.TK2MSFTNGP11.phx.gbl...
> I wasn't paying attention and I started a restore on one of my dbs.
> I stoped in the middle of it. I tried to reconnect to my db but I was
> told that I couldn't. I restored a recent copy but lost all the data I
> worked on today. Any chance I can restore it?
>
|||I had changed it to simple because the transaction log was getting to
big during the day before I could shrinkfile it.
Is there a way to see when the last manual backup was made? I'm having
some problems looking for the backup.
Hari wrote:

> Hi,
> What type of recovery model you are using? If it is FULL or BULK_Logged you
> can recover back the database
> provided if you have taken Trasaction log backup.
> 1. Restore Yesterdays full backup with NORECOVERY
> 2. Restore the transaction logs one by one till the last with NORECOVERY
> 3. Restore the last transaction log with RECOVERY option.
> This will recover the database till the last trasnaction log backup.
> --
> Thanks
> Hari
> MCDBA
> "Won Lee" <noemail@.nospam.com> wrote in message
> news:er9yAwiWEHA.716@.TK2MSFTNGP11.phx.gbl...
>
>
|||Hi,
The table BACKUPSET in MSDB contains the information of backup performed.
Use the below query to get the database name and backup date.
Since we are sorting in descenting order of backup_finish_date the latest
backup date will be displayed first.
select database_name,backup_finish_date from msdb..backupset
order by backup_finish_date desc
Thanks
Hari
MCDBA
"Won Lee" <noemail@.nospam.com> wrote in message
news:OLtJAxnWEHA.2940@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> I had changed it to simple because the transaction log was getting to
> big during the day before I could shrinkfile it.
> Is there a way to see when the last manual backup was made? I'm having
> some problems looking for the backup.
>
> Hari wrote:
you
>

restore nightmares

I wasn't paying attention and I started a restore on one of my dbs.
I stoped in the middle of it. I tried to reconnect to my db but I was
told that I couldn't. I restored a recent copy but lost all the data I
worked on today. Any chance I can restore it?Unfortunately not.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"Won Lee" <noemail@.nospam.com> wrote in message
news:er9yAwiWEHA.716@.TK2MSFTNGP11.phx.gbl...
> I wasn't paying attention and I started a restore on one of my dbs.
> I stoped in the middle of it. I tried to reconnect to my db but I was
> told that I couldn't. I restored a recent copy but lost all the data I
> worked on today. Any chance I can restore it?
>|||Hi,
What type of recovery model you are using? If it is FULL or BULK_Logged you
can recover back the database
provided if you have taken Trasaction log backup.
1. Restore Yesterdays full backup with NORECOVERY
2. Restore the transaction logs one by one till the last with NORECOVERY
3. Restore the last transaction log with RECOVERY option.
This will recover the database till the last trasnaction log backup.
Thanks
Hari
MCDBA
"Won Lee" <noemail@.nospam.com> wrote in message
news:er9yAwiWEHA.716@.TK2MSFTNGP11.phx.gbl...
> I wasn't paying attention and I started a restore on one of my dbs.
> I stoped in the middle of it. I tried to reconnect to my db but I was
> told that I couldn't. I restored a recent copy but lost all the data I
> worked on today. Any chance I can restore it?
>|||I had changed it to simple because the transaction log was getting to
big during the day before I could shrinkfile it.
Is there a way to see when the last manual backup was made? I'm having
some problems looking for the backup.
Hari wrote:

> Hi,
> What type of recovery model you are using? If it is FULL or BULK_Logged yo
u
> can recover back the database
> provided if you have taken Trasaction log backup.
> 1. Restore Yesterdays full backup with NORECOVERY
> 2. Restore the transaction logs one by one till the last with NORECOVERY
> 3. Restore the last transaction log with RECOVERY option.
> This will recover the database till the last trasnaction log backup.
> --
> Thanks
> Hari
> MCDBA
> "Won Lee" <noemail@.nospam.com> wrote in message
> news:er9yAwiWEHA.716@.TK2MSFTNGP11.phx.gbl...
>
>
>|||Hi,
The table BACKUPSET in MSDB contains the information of backup performed.
Use the below query to get the database name and backup date.
Since we are sorting in descenting order of backup_finish_date the latest
backup date will be displayed first.
select database_name,backup_finish_date from msdb..backupset
order by backup_finish_date desc
Thanks
Hari
MCDBA
"Won Lee" <noemail@.nospam.com> wrote in message
news:OLtJAxnWEHA.2940@.TK2MSFTNGP09.phx.gbl...
> I had changed it to simple because the transaction log was getting to
> big during the day before I could shrinkfile it.
> Is there a way to see when the last manual backup was made? I'm having
> some problems looking for the backup.
>
> Hari wrote:
>
you[vbcol=seagreen]
>

restore nightmares

I wasn't paying attention and I started a restore on one of my dbs.
I stoped in the middle of it. I tried to reconnect to my db but I was
told that I couldn't. I restored a recent copy but lost all the data I
worked on today. Any chance I can restore it?Unfortunately not.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"Won Lee" <noemail@.nospam.com> wrote in message
news:er9yAwiWEHA.716@.TK2MSFTNGP11.phx.gbl...
> I wasn't paying attention and I started a restore on one of my dbs.
> I stoped in the middle of it. I tried to reconnect to my db but I was
> told that I couldn't. I restored a recent copy but lost all the data I
> worked on today. Any chance I can restore it?
>|||Hi,
What type of recovery model you are using? If it is FULL or BULK_Logged you
can recover back the database
provided if you have taken Trasaction log backup.
1. Restore Yesterdays full backup with NORECOVERY
2. Restore the transaction logs one by one till the last with NORECOVERY
3. Restore the last transaction log with RECOVERY option.
This will recover the database till the last trasnaction log backup.
--
Thanks
Hari
MCDBA
"Won Lee" <noemail@.nospam.com> wrote in message
news:er9yAwiWEHA.716@.TK2MSFTNGP11.phx.gbl...
> I wasn't paying attention and I started a restore on one of my dbs.
> I stoped in the middle of it. I tried to reconnect to my db but I was
> told that I couldn't. I restored a recent copy but lost all the data I
> worked on today. Any chance I can restore it?
>|||I had changed it to simple because the transaction log was getting to
big during the day before I could shrinkfile it.
Is there a way to see when the last manual backup was made? I'm having
some problems looking for the backup.
Hari wrote:
> Hi,
> What type of recovery model you are using? If it is FULL or BULK_Logged you
> can recover back the database
> provided if you have taken Trasaction log backup.
> 1. Restore Yesterdays full backup with NORECOVERY
> 2. Restore the transaction logs one by one till the last with NORECOVERY
> 3. Restore the last transaction log with RECOVERY option.
> This will recover the database till the last trasnaction log backup.
> --
> Thanks
> Hari
> MCDBA
> "Won Lee" <noemail@.nospam.com> wrote in message
> news:er9yAwiWEHA.716@.TK2MSFTNGP11.phx.gbl...
>>I wasn't paying attention and I started a restore on one of my dbs.
>>I stoped in the middle of it. I tried to reconnect to my db but I was
>>told that I couldn't. I restored a recent copy but lost all the data I
>>worked on today. Any chance I can restore it?
>
>|||Hi,
The table BACKUPSET in MSDB contains the information of backup performed.
Use the below query to get the database name and backup date.
Since we are sorting in descenting order of backup_finish_date the latest
backup date will be displayed first.
select database_name,backup_finish_date from msdb..backupset
order by backup_finish_date desc
--
Thanks
Hari
MCDBA
"Won Lee" <noemail@.nospam.com> wrote in message
news:OLtJAxnWEHA.2940@.TK2MSFTNGP09.phx.gbl...
> I had changed it to simple because the transaction log was getting to
> big during the day before I could shrinkfile it.
> Is there a way to see when the last manual backup was made? I'm having
> some problems looking for the backup.
>
> Hari wrote:
> > Hi,
> >
> > What type of recovery model you are using? If it is FULL or BULK_Logged
you
> > can recover back the database
> > provided if you have taken Trasaction log backup.
> >
> > 1. Restore Yesterdays full backup with NORECOVERY
> > 2. Restore the transaction logs one by one till the last with NORECOVERY
> > 3. Restore the last transaction log with RECOVERY option.
> >
> > This will recover the database till the last trasnaction log backup.
> >
> > --
> > Thanks
> > Hari
> > MCDBA
> > "Won Lee" <noemail@.nospam.com> wrote in message
> > news:er9yAwiWEHA.716@.TK2MSFTNGP11.phx.gbl...
> >
> >>I wasn't paying attention and I started a restore on one of my dbs.
> >>I stoped in the middle of it. I tried to reconnect to my db but I was
> >>told that I couldn't. I restored a recent copy but lost all the data I
> >>worked on today. Any chance I can restore it?
> >>
> >
> >
> >
>

restore master db

HI can I restore master and msdb dbs from sql 2000 to sql 2005 sp2?
ThankasYou can't do that. Either let SETUP upgrade the whole instance, or you are yourself responsible of
transferring whatever you need from the system databases.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"mecn" <mecn2002@.yahoo.com> wrote in message news:eL2ee$XFIHA.2004@.TK2MSFTNGP06.phx.gbl...
> HI can I restore master and msdb dbs from sql 2000 to sql 2005 sp2?
> Thankas
>|||got it thanks
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:DF06CD17-F322-4C5F-A920-17B25FFBD759@.microsoft.com...
> You can't do that. Either let SETUP upgrade the whole instance, or you are
> yourself responsible of transferring whatever you need from the system
> databases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:eL2ee$XFIHA.2004@.TK2MSFTNGP06.phx.gbl...
>> HI can I restore master and msdb dbs from sql 2000 to sql 2005 sp2?
>> Thankas
>

restore master

Hi
I want to restore master db(first) in another server
which has sql server 2000 before restoring user dbs,
to locate login and ...
but following error happended,what's wrong?
"resore database must be used in single user mode
when restoring master db"
and I found there's only one login name called "sa"
as system administrator,
but when I expand the user branch in master database
I found "sa" and "guest" there,
and when I tried to drop guest following error happened too:
"cannot drop the guest user from master or tempdb"
1 - does not restoring master related to guest?
2 - anyway,how can I drop guest?
any help would be greatly thanked.
thanks,
but can I just script my login definition and
relation between them and he users and run it in destination
instead of restoring master database?
On Sat, 10 Jul 2004 05:44:31 -0700, Thirumal <treddym@.yahoo.nospam.com>
wrote:
[vbcol=seagreen]
> Hi,
> 1]To restore master database u need to start SQL service
> in single user mode. navigate to appropriate SQL Server
> directory and issue the below at command prompt.
> sqlservr.exe -c -m
> 2]It is not recommended to drop guest login from master
> and tempdb.
> When users login into SQL Server, by default they have
> access to 'master' database. SQL Server internally uses
> guest for access , who has less permissions, so need not
> worry. If u drop 'guest' (assuming that it is allowed) you
> must be a 'sa' to access the master all the time.
>
> With Regards
> Thirumal
> www.thirumal.com
> too:
|||and how can I understand that I
"start SQL service in single user mode"?
On Sat, 10 Jul 2004 05:44:31 -0700, Thirumal <treddym@.yahoo.nospam.com>
wrote:
[vbcol=seagreen]
> Hi,
> 1]To restore master database u need to start SQL service
> in single user mode. navigate to appropriate SQL Server
> directory and issue the below at command prompt.
> sqlservr.exe -c -m
> 2]It is not recommended to drop guest login from master
> and tempdb.
> When users login into SQL Server, by default they have
> access to 'master' database. SQL Server internally uses
> guest for access , who has less permissions, so need not
> worry. If u drop 'guest' (assuming that it is allowed) you
> must be a 'sa' to access the master all the time.
>
> With Regards
> Thirumal
> www.thirumal.com
> too:
|||Hi,
Login can be copied from source server to destination server. But Master
database stores the other details like:-
1. Configuration parameters
2. Server Names ,..
So after creating the logins you have to manually sync. that as well.
See the below link to transfer logins from one server to another server with
the same password.
http://www.databasejournal.com/featu...le.php/2228611
Thanks
Hari
MCDBA
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsayeffn6hqligo@.msnews.microsoft.com...
> thanks,
> but can I just script my login definition and
> relation between them and he users and run it in destination
> instead of restoring master database?
>
> On Sat, 10 Jul 2004 05:44:31 -0700, Thirumal <treddym@.yahoo.nospam.com>
> wrote:
>
|||Hi,
Execute the below command from Query Analyzer:-
select serverproperty('IsSingleUser')
If the value returned is "1" then the server is in single user mode.
If the value returned is "0" then the server is in multi user mode.
Thanks
Hari
MCDBA
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsayehds6hqligo@.msnews.microsoft.com...
> and how can I understand that I
> "start SQL service in single user mode"?
> On Sat, 10 Jul 2004 05:44:31 -0700, Thirumal <treddym@.yahoo.nospam.com>
> wrote:
>