Restoring the master database

As part of testing a cluster-to-cluster migration process, I am
attempting to restore the master database from an old test cluster
into a new one. I've read quite a bit about this online, and it looks
like a relatively easy procedure. These are the steps I'm following:
1. Stop the SQL Server.
2. At the command line, use sqlservr.exe -c -m to start SQL up in
single-user mode.
3. Use Query Analyzer or osql.exe to restore the database.
The problem I'm seeing is, with the MSSQLSERVER service stopped, I
can't get QA or osql.exe to work. I get the following error, no
matter which one I use:
Unable to connect to server:
Server: Msg 17, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist
or access denied.
With the SQL resources in Cluster Administrator offline, but the
MSSQLSERVER service not stopped, however, I can't start SQL from the
command line due to various files already being in use.
Does anyone have any suggestions on how to get around this problem.
It seems a lot of people have done something similar, but I can't
figure out what I'm missing.
Thanks!
Eric
Logon to one of the nodes and bring up 2 command line windows.
In the first command window type 'sqlservr -m'
Once SQL has started in will be running in this command window not allowing
you to do further work so switch over to the other window and run the
following 'osql -E'
You should now have a SQL prompt.
John Vandervliet.
<ecore1@.gmail.com> wrote in message
news:1176232183.047000.210960@.o5g2000hsb.googlegro ups.com...
> As part of testing a cluster-to-cluster migration process, I am
> attempting to restore the master database from an old test cluster
> into a new one. I've read quite a bit about this online, and it looks
> like a relatively easy procedure. These are the steps I'm following:
> 1. Stop the SQL Server.
> 2. At the command line, use sqlservr.exe -c -m to start SQL up in
> single-user mode.
> 3. Use Query Analyzer or osql.exe to restore the database.
> The problem I'm seeing is, with the MSSQLSERVER service stopped, I
> can't get QA or osql.exe to work. I get the following error, no
> matter which one I use:
> Unable to connect to server:
> Server: Msg 17, Level 16, State 1
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist
> or access denied.
> With the SQL resources in Cluster Administrator offline, but the
> MSSQLSERVER service not stopped, however, I can't start SQL from the
> command line due to various files already being in use.
> Does anyone have any suggestions on how to get around this problem.
> It seems a lot of people have done something similar, but I can't
> figure out what I'm missing.
> Thanks!
> Eric
>

Restoring SQL Server Data

hi. I just ran a dts that overwrote my data in my sql server 2000 database. Is it possible to still recover my old data?if you are running in full backup mode and you just ran DML statements, backup the transaction log of the database and restore the whole backup (full + evtl. differential + transaction log) with the option point -in-time recovery, stopping the restore until a specific time. if you don′t have the full backup model or no full backup, then there is no chance.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

I agree with the above answer, but if you have not been taking backups you will not be able to recover the database.

sql

Restoring SQL 7 db's created on NT4-Svr onto a W2K-Svr

Hello,
I am trying to help a small charity whose old NT4 Server
has crashed and has had to be written-off. I have a
Veritas Backup Exec tape with all their SQL Server 7
(SP1) datases on it. The tape was created using the
appropriate Backup Exec SQL Agent. There are no SQL dump
files on it - only the Veritas live database backups.
The new server that the charity has purchased will not
run NT4 Server. It is loaded with W2K Server.
Is it possible to recover their system by performing a
fresh installation of SQL Server 7 onto the new W2K
Server and then restoring all the SQL Server 7 databases
(master , model, msdb + user databases) from the backup
tape into the fresh intallation?
Thanks to anyone who can help.
Paul> Is it possible to recover their system by performing a
> fresh installation of SQL Server 7 onto the new W2K
> Server and then restoring all the SQL Server 7 databases
> (master , model, msdb + user databases) from the backup
> tape into the fresh intallation?
Yes, that should be possible. You probably need the new install having the same service pack for the
system databases. However, I do not know in which way using Veritas might complicate this issue.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Paul" <sheenpaul@.hotmail.com> wrote in message news:044601c39b4d$83daaf80$a301280a@.phx.gbl...
> Hello,
> I am trying to help a small charity whose old NT4 Server
> has crashed and has had to be written-off. I have a
> Veritas Backup Exec tape with all their SQL Server 7
> (SP1) datases on it. The tape was created using the
> appropriate Backup Exec SQL Agent. There are no SQL dump
> files on it - only the Veritas live database backups.
> The new server that the charity has purchased will not
> run NT4 Server. It is loaded with W2K Server.
> Is it possible to recover their system by performing a
> fresh installation of SQL Server 7 onto the new W2K
> Server and then restoring all the SQL Server 7 databases
> (master , model, msdb + user databases) from the backup
> tape into the fresh intallation?
> Thanks to anyone who can help.
> Paul|||Thanks for your encouraging reply, Tibor. Here is an
update in case others get a similar issue.
The restore of the SQL 7 master database from the Veritas
backup tape is proving to be a problem (the restore job
hangs after only 5% with no error messages) but all other
system & user databases restore without a problem. I am
trying to resolve this matter with Veritas support and
will post again later.
>--Original Message--
>> Is it possible to recover their system by performing a
>> fresh installation of SQL Server 7 onto the new W2K
>> Server and then restoring all the SQL Server 7
databases
>> (master , model, msdb + user databases) from the backup
>> tape into the fresh intallation?
>Yes, that should be possible. You probably need the new
install having the same service pack for the
>system databases. However, I do not know in which way
using Veritas might complicate this issue.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Paul" <sheenpaul@.hotmail.com> wrote in message
news:044601c39b4d$83daaf80$a301280a@.phx.gbl...
>> Hello,
>> I am trying to help a small charity whose old NT4
Server
>> has crashed and has had to be written-off. I have a
>> Veritas Backup Exec tape with all their SQL Server 7
>> (SP1) datases on it. The tape was created using the
>> appropriate Backup Exec SQL Agent. There are no SQL
dump
>> files on it - only the Veritas live database backups.
>> The new server that the charity has purchased will not
>> run NT4 Server. It is loaded with W2K Server.
>> Is it possible to recover their system by performing a
>> fresh installation of SQL Server 7 onto the new W2K
>> Server and then restoring all the SQL Server 7
databases
>> (master , model, msdb + user databases) from the backup
>> tape into the fresh intallation?
>> Thanks to anyone who can help.
>> Paul
>
>.
>

Restoring security access using rsconfig?

Hi,
I have accidentally removed myself from the Home folder of my local
installation of reporting services. My security group was using an old
domain group and RS warned me that it was invalid. I deleted the entry
which was Content Manager, thinking that I was in the
BuiltIn/Administrators group and therefore would still have access to
localhost/reports. Unfortunately, I realised that I was not in the
administrators group on my PC (A company policy), so I lost all access
to my reports deployment. However, when I got added to the local
administrators group, I found that I still did not have access to the
Reports website.
My question is: Is there a way to manually add a user as a Content
Manager without using the http://localhost/Reports website? I seem to
recall a possible way using rsconfig, but have not been able to find a
relevant posting. I really do not want to uninstall and reinstall RS.
Thanks!To reply to myself in case other people ever have this problem:
I don't think you can do it with rsconfig.
In the end, I went into the ReportServer database and looked at the
users, roles and policies tables.
I found a user that was in the database as a Content Manager
(builtin/administrators is usually in there by default, but I had
another user there).
I then edited the PolicyUserRole table to change the user_id for a user
that had Content Manager access and a policy of "0" for my own user.
This let me back into the localhost/reports page where I then added my
domain name again. If you don't have a Content Manager already, take
the guid for that role and paste it into your role.
If I didn't have a user in there, I assume I could just add one, but I
am unsure about the generation of the guid.
I don't know what the official way to do this is, but as a hack, it
worked.
MDXQuery wrote:
> Hi,
> I have accidentally removed myself from the Home folder of my local
> installation of reporting services. My security group was using an old
> domain group and RS warned me that it was invalid. I deleted the entry
> which was Content Manager, thinking that I was in the
> BuiltIn/Administrators group and therefore would still have access to
> localhost/reports. Unfortunately, I realised that I was not in the
> administrators group on my PC (A company policy), so I lost all access
> to my reports deployment. However, when I got added to the local
> administrators group, I found that I still did not have access to the
> Reports website.
> My question is: Is there a way to manually add a user as a Content
> Manager without using the http://localhost/Reports website? I seem to
> recall a possible way using rsconfig, but have not been able to find a
> relevant posting. I really do not want to uninstall and reinstall RS.
> Thanks!|||If you can add yourself to the local admininstrator role of the server that
RS resides on, then you will automatically have all the rights you need with
RS.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MDXQuery" <imgroup1@.hotmail.com> wrote in message
news:1125056826.979578.163790@.g43g2000cwa.googlegroups.com...
> To reply to myself in case other people ever have this problem:
> I don't think you can do it with rsconfig.
> In the end, I went into the ReportServer database and looked at the
> users, roles and policies tables.
> I found a user that was in the database as a Content Manager
> (builtin/administrators is usually in there by default, but I had
> another user there).
> I then edited the PolicyUserRole table to change the user_id for a user
> that had Content Manager access and a policy of "0" for my own user.
> This let me back into the localhost/reports page where I then added my
> domain name again. If you don't have a Content Manager already, take
> the guid for that role and paste it into your role.
> If I didn't have a user in there, I assume I could just add one, but I
> am unsure about the generation of the guid.
> I don't know what the official way to do this is, but as a hack, it
> worked.
>
>
> MDXQuery wrote:
>> Hi,
>> I have accidentally removed myself from the Home folder of my local
>> installation of reporting services. My security group was using an old
>> domain group and RS warned me that it was invalid. I deleted the entry
>> which was Content Manager, thinking that I was in the
>> BuiltIn/Administrators group and therefore would still have access to
>> localhost/reports. Unfortunately, I realised that I was not in the
>> administrators group on my PC (A company policy), so I lost all access
>> to my reports deployment. However, when I got added to the local
>> administrators group, I found that I still did not have access to the
>> Reports website.
>> My question is: Is there a way to manually add a user as a Content
>> Manager without using the http://localhost/Reports website? I seem to
>> recall a possible way using rsconfig, but have not been able to find a
>> relevant posting. I really do not want to uninstall and reinstall RS.
>> Thanks!
>

restoring old mdf file

I need to restore a Sql2000 database from an .mdf file from last July. Can
people tell me if the following procedure is correct, or am I missing
something? :
1. restore the .mdf file from backup to temp location (done)
2. backup existing database (in case I need to restore this one)
3. delete existing database (to cleanup metadata in master)
4. shutdown sqlserver
5. replace existing .mdf file with old one
6. startup sqlserver
7. attach database to .mdf -- how do I do this?
Thanks for any assistance with this.
-Frank Brown
Seattle Fire Dept
http://www.inwa.net/~frog/take a look at the sp_attach_db procedure, in BOL.
Alex Ivascu
"frank brown" <someone@.somewhere.net> wrote in message
news:LPTlb.18$Nd3.3944@.news-west.eli.net...
> I need to restore a Sql2000 database from an .mdf file from last July.
Can
> people tell me if the following procedure is correct, or am I missing
> something? :
> 1. restore the .mdf file from backup to temp location (done)
> 2. backup existing database (in case I need to restore this one)
> 3. delete existing database (to cleanup metadata in master)
> 4. shutdown sqlserver
> 5. replace existing .mdf file with old one
> 6. startup sqlserver
> 7. attach database to .mdf -- how do I do this?
> Thanks for any assistance with this.
> -Frank Brown
> Seattle Fire Dept
> http://www.inwa.net/~frog/
>|||Hi ,
This activity do not require a SQL server shutdown. Please use the below
commands to perform:
1. drop database databasename
2. copy the .MDF and .LDF files to c:\mssql\data\ (Itcan be any directory)
3. EXEC sp_attach_db @.dbname = N'DBNAME',
@.filename1 = N'c:\mssql\data\dbname.mdf',
@.filename2 = N'c:\MSSQL\Data\dbname_log.ldf'
Now your old database will be ready to use.
Incase if you need the old and new database in server then u have to use
Move option along with
sp_attach_db command.
Thanks
Hari
MCDBA
"alex ivascu" <alexdivascu@.sbcglobalNO.SPAMnet> wrote in message
news:q6Ulb.5679$sP6.5569@.newssvr27.news.prodigy.com...
> take a look at the sp_attach_db procedure, in BOL.
> Alex Ivascu
>
> "frank brown" <someone@.somewhere.net> wrote in message
> news:LPTlb.18$Nd3.3944@.news-west.eli.net...
> > I need to restore a Sql2000 database from an .mdf file from last July.
> Can
> > people tell me if the following procedure is correct, or am I missing
> > something? :
> >
> > 1. restore the .mdf file from backup to temp location (done)
> > 2. backup existing database (in case I need to restore this one)
> > 3. delete existing database (to cleanup metadata in master)
> > 4. shutdown sqlserver
> > 5. replace existing .mdf file with old one
> > 6. startup sqlserver
> > 7. attach database to .mdf -- how do I do this?
> >
> > Thanks for any assistance with this.
> >
> > -Frank Brown
> > Seattle Fire Dept
> > http://www.inwa.net/~frog/
> >
> >
>|||Huh? If you drop the database, there are no more database file...
Also, sp_attach_db is only guaranteed to work if you first detach the database.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eLPaYodmDHA.1072@.TK2MSFTNGP09.phx.gbl...
> Hi ,
> This activity do not require a SQL server shutdown. Please use the below
> commands to perform:
> 1. drop database databasename
> 2. copy the .MDF and .LDF files to c:\mssql\data\ (Itcan be any directory)
> 3. EXEC sp_attach_db @.dbname = N'DBNAME',
> @.filename1 = N'c:\mssql\data\dbname.mdf',
> @.filename2 = N'c:\MSSQL\Data\dbname_log.ldf'
> Now your old database will be ready to use.
> Incase if you need the old and new database in server then u have to use
> Move option along with
> sp_attach_db command.
> Thanks
> Hari
> MCDBA
>
>
>
>
> "alex ivascu" <alexdivascu@.sbcglobalNO.SPAMnet> wrote in message
> news:q6Ulb.5679$sP6.5569@.newssvr27.news.prodigy.com...
> > take a look at the sp_attach_db procedure, in BOL.
> >
> > Alex Ivascu
> >
> >
> > "frank brown" <someone@.somewhere.net> wrote in message
> > news:LPTlb.18$Nd3.3944@.news-west.eli.net...
> > > I need to restore a Sql2000 database from an .mdf file from last July.
> > Can
> > > people tell me if the following procedure is correct, or am I missing
> > > something? :
> > >
> > > 1. restore the .mdf file from backup to temp location (done)
> > > 2. backup existing database (in case I need to restore this one)
> > > 3. delete existing database (to cleanup metadata in master)
> > > 4. shutdown sqlserver
> > > 5. replace existing .mdf file with old one
> > > 6. startup sqlserver
> > > 7. attach database to .mdf -- how do I do this?
> > >
> > > Thanks for any assistance with this.
> > >
> > > -Frank Brown
> > > Seattle Fire Dept
> > > http://www.inwa.net/~frog/
> > >
> > >
> >
> >
>|||Hi,
Frank's mail says that he need to drop the existing database and load the
old database. That is the reason I mentioned the "drop database" command
initially.
I do agree with you , some times the SP_attachdb wont work incase if we are
not using SP_detachdb.
Frank,
What you can do is perform the below steps to put you in safer
side.
1. Perform a backup of your existing database and keep it in a safe folder.
2. drop database databasename
3. copy the .MDF and .LDF files to c:\mssql\data\ (Itcan be any directory)
4. EXEC sp_attach_db @.dbname = N'DBNAME',
@.filename1 = N'c:\mssql\data\dbname.mdf',
@.filename2 = N'c:\MSSQL\Data\dbname_log.ldf'
Thanks
Hari
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:e0RKXhfmDHA.1244@.TK2MSFTNGP11.phx.gbl...
> Huh? If you drop the database, there are no more database file...
> Also, sp_attach_db is only guaranteed to work if you first detach the
database.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eLPaYodmDHA.1072@.TK2MSFTNGP09.phx.gbl...
> > Hi ,
> >
> > This activity do not require a SQL server shutdown. Please use the below
> > commands to perform:
> >
> > 1. drop database databasename
> > 2. copy the .MDF and .LDF files to c:\mssql\data\ (Itcan be any
directory)
> > 3. EXEC sp_attach_db @.dbname = N'DBNAME',
> > @.filename1 = N'c:\mssql\data\dbname.mdf',
> > @.filename2 = N'c:\MSSQL\Data\dbname_log.ldf'
> >
> > Now your old database will be ready to use.
> >
> > Incase if you need the old and new database in server then u have to use
> > Move option along with
> > sp_attach_db command.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > "alex ivascu" <alexdivascu@.sbcglobalNO.SPAMnet> wrote in message
> > news:q6Ulb.5679$sP6.5569@.newssvr27.news.prodigy.com...
> > > take a look at the sp_attach_db procedure, in BOL.
> > >
> > > Alex Ivascu
> > >
> > >
> > > "frank brown" <someone@.somewhere.net> wrote in message
> > > news:LPTlb.18$Nd3.3944@.news-west.eli.net...
> > > > I need to restore a Sql2000 database from an .mdf file from last
July.
> > > Can
> > > > people tell me if the following procedure is correct, or am I
missing
> > > > something? :
> > > >
> > > > 1. restore the .mdf file from backup to temp location (done)
> > > > 2. backup existing database (in case I need to restore this one)
> > > > 3. delete existing database (to cleanup metadata in master)
> > > > 4. shutdown sqlserver
> > > > 5. replace existing .mdf file with old one
> > > > 6. startup sqlserver
> > > > 7. attach database to .mdf -- how do I do this?
> > > >
> > > > Thanks for any assistance with this.
> > > >
> > > > -Frank Brown
> > > > Seattle Fire Dept
> > > > http://www.inwa.net/~frog/
> > > >
> > > >
> > >
> > >
> >
> >
>

restoring msdb of different server

Hi
Evironment: SQL Server 2000 SP3a
I want to restore master & msdb (which transfers login,
settings, jobs etc.) of old server to the new server. What
are the possible issues with this approach, if any? Is
UPDATE msdb.dbo.sysjobs
SET originating_server = 'NewServerName'
WHERE originating_server = 'OldServerName'
enough in case of msdb?
The old server is Windows 2000 Advanced Server SP4, HP
Proliant DL 380 G3 with 2 x 2.4 GHz Intel Xeon
Hyper-Threading CPUs, 4GB RAM, of which 2GB is for apps,
and HP SmartArray 5300/256 Ultra SCSI 320 RAID controller
with Ultra SCSI 320 15k RPM disks attached. The new server
is Windows 2003 Server Standart SP1, HP ProLiant DL 380 G4
with 2 x 3.6 GHz Intel Xeon Hyper-Threading CPUs, 3.5 GB of
app/OS RAM, the same RAID setup as in the old server.
I haven't changed any SQL Server settings on the old
server, except CPU affinity mask to use just one virtual
CPU on each of the physical CPUs and max. degree of
paralellism to 1.
Is SQL Server Setup aware of more advanced hardware of the
new server to such an extent that it can automatically
produce more optimal SQL server settings (stored in master)
than they were produced for the old server?
I.e. will the auto-generated settings in master database
from old server hurt performance on the new server?
--
Many, many thanks,
OskSee if this helps:
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
AMB
"Osk" wrote:
> Hi
> Evironment: SQL Server 2000 SP3a
> I want to restore master & msdb (which transfers login,
> settings, jobs etc.) of old server to the new server. What
> are the possible issues with this approach, if any? Is
> UPDATE msdb.dbo.sysjobs
> SET originating_server = 'NewServerName'
> WHERE originating_server = 'OldServerName'
> enough in case of msdb?
> The old server is Windows 2000 Advanced Server SP4, HP
> Proliant DL 380 G3 with 2 x 2.4 GHz Intel Xeon
> Hyper-Threading CPUs, 4GB RAM, of which 2GB is for apps,
> and HP SmartArray 5300/256 Ultra SCSI 320 RAID controller
> with Ultra SCSI 320 15k RPM disks attached. The new server
> is Windows 2003 Server Standart SP1, HP ProLiant DL 380 G4
> with 2 x 3.6 GHz Intel Xeon Hyper-Threading CPUs, 3.5 GB of
> app/OS RAM, the same RAID setup as in the old server.
> I haven't changed any SQL Server settings on the old
> server, except CPU affinity mask to use just one virtual
> CPU on each of the physical CPUs and max. degree of
> paralellism to 1.
> Is SQL Server Setup aware of more advanced hardware of the
> new server to such an extent that it can automatically
> produce more optimal SQL server settings (stored in master)
> than they were produced for the old server?
> I.e. will the auto-generated settings in master database
> from old server hurt performance on the new server?
>
> --
> Many, many thanks,
> Osk
>

restoring msdb of different server

Hi
Evironment: SQL Server 2000 SP3a
I want to restore master & msdb (which transfers login,
settings, jobs etc.) of old server to the new server. What
are the possible issues with this approach, if any? Is
UPDATE msdb.dbo.sysjobs
SET originating_server = 'NewServerName'
WHERE originating_server = 'OldServerName'
enough in case of msdb?
The old server is Windows 2000 Advanced Server SP4, HP
Proliant DL 380 G3 with 2 x 2.4 GHz Intel Xeon
Hyper-Threading CPUs, 4GB RAM, of which 2GB is for apps,
and HP SmartArray 5300/256 Ultra SCSI 320 RAID controller
with Ultra SCSI 320 15k RPM disks attached. The new server
is Windows 2003 Server Standart SP1, HP ProLiant DL 380 G4
with 2 x 3.6 GHz Intel Xeon Hyper-Threading CPUs, 3.5 GB of
app/OS RAM, the same RAID setup as in the old server.
I haven't changed any SQL Server settings on the old
server, except CPU affinity mask to use just one virtual
CPU on each of the physical CPUs and max. degree of
paralellism to 1.
Is SQL Server Setup aware of more advanced hardware of the
new server to such an extent that it can automatically
produce more optimal SQL server settings (stored in master)
than they were produced for the old server?
I.e. will the auto-generated settings in master database
from old server hurt performance on the new server?
Many, many thanks,
Osk
See if this helps:
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default...b;en-us;314546
AMB
"Osk" wrote:

> Hi
> Evironment: SQL Server 2000 SP3a
> I want to restore master & msdb (which transfers login,
> settings, jobs etc.) of old server to the new server. What
> are the possible issues with this approach, if any? Is
> UPDATE msdb.dbo.sysjobs
> SET originating_server = 'NewServerName'
> WHERE originating_server = 'OldServerName'
> enough in case of msdb?
> The old server is Windows 2000 Advanced Server SP4, HP
> Proliant DL 380 G3 with 2 x 2.4 GHz Intel Xeon
> Hyper-Threading CPUs, 4GB RAM, of which 2GB is for apps,
> and HP SmartArray 5300/256 Ultra SCSI 320 RAID controller
> with Ultra SCSI 320 15k RPM disks attached. The new server
> is Windows 2003 Server Standart SP1, HP ProLiant DL 380 G4
> with 2 x 3.6 GHz Intel Xeon Hyper-Threading CPUs, 3.5 GB of
> app/OS RAM, the same RAID setup as in the old server.
> I haven't changed any SQL Server settings on the old
> server, except CPU affinity mask to use just one virtual
> CPU on each of the physical CPUs and max. degree of
> paralellism to 1.
> Is SQL Server Setup aware of more advanced hardware of the
> new server to such an extent that it can automatically
> produce more optimal SQL server settings (stored in master)
> than they were produced for the old server?
> I.e. will the auto-generated settings in master database
> from old server hurt performance on the new server?
>
> --
> Many, many thanks,
> Osk
>

restoring msdb of different server

Hi
Evironment: SQL Server 2000 SP3a
I want to restore master & msdb (which transfers login,
settings, jobs etc.) of old server to the new server. What
are the possible issues with this approach, if any? Is
UPDATE msdb.dbo.sysjobs
SET originating_server = 'NewServerName'
WHERE originating_server = 'OldServerName'
enough in case of msdb?
The old server is Windows 2000 Advanced Server SP4, HP
Proliant DL 380 G3 with 2 x 2.4 GHz Intel Xeon
Hyper-Threading CPUs, 4GB RAM, of which 2GB is for apps,
and HP SmartArray 5300/256 Ultra SCSI 320 RAID controller
with Ultra SCSI 320 15k RPM disks attached. The new server
is Windows 2003 Server Standart SP1, HP ProLiant DL 380 G4
with 2 x 3.6 GHz Intel Xeon Hyper-Threading CPUs, 3.5 GB of
app/OS RAM, the same RAID setup as in the old server.
I haven't changed any SQL Server settings on the old
server, except CPU affinity mask to use just one virtual
CPU on each of the physical CPUs and max. degree of
paralellism to 1.
Is SQL Server Setup aware of more advanced hardware of the
new server to such an extent that it can automatically
produce more optimal SQL server settings (stored in master)
than they were produced for the old server?
I.e. will the auto-generated settings in master database
from old server hurt performance on the new server?
Many, many thanks,
OskSee if this helps:
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/defaul...kb;en-us;314546
AMB
"Osk" wrote:

> Hi
> Evironment: SQL Server 2000 SP3a
> I want to restore master & msdb (which transfers login,
> settings, jobs etc.) of old server to the new server. What
> are the possible issues with this approach, if any? Is
> UPDATE msdb.dbo.sysjobs
> SET originating_server = 'NewServerName'
> WHERE originating_server = 'OldServerName'
> enough in case of msdb?
> The old server is Windows 2000 Advanced Server SP4, HP
> Proliant DL 380 G3 with 2 x 2.4 GHz Intel Xeon
> Hyper-Threading CPUs, 4GB RAM, of which 2GB is for apps,
> and HP SmartArray 5300/256 Ultra SCSI 320 RAID controller
> with Ultra SCSI 320 15k RPM disks attached. The new server
> is Windows 2003 Server Standart SP1, HP ProLiant DL 380 G4
> with 2 x 3.6 GHz Intel Xeon Hyper-Threading CPUs, 3.5 GB of
> app/OS RAM, the same RAID setup as in the old server.
> I haven't changed any SQL Server settings on the old
> server, except CPU affinity mask to use just one virtual
> CPU on each of the physical CPUs and max. degree of
> paralellism to 1.
> Is SQL Server Setup aware of more advanced hardware of the
> new server to such an extent that it can automatically
> produce more optimal SQL server settings (stored in master)
> than they were produced for the old server?
> I.e. will the auto-generated settings in master database
> from old server hurt performance on the new server?
>
> --
> Many, many thanks,
> Osk
>

Restoring master db from old install of different SQL version

All,
I had to downgrade from Enterprise to Standard on a server. So I took
backups of all DB's, uninstalled enterprise and installed standard with
SP3a. Then I restored all user DB's.
I then wanted to use the master from enterprise and install into standard so
I would keep users and passwords etc. We have apps that use users like
SalesAppUser with a password hardly anyone knows. So I just wanted a nice
plain replace new master with old master.
Thanks to Robert Davies who helped me get to the command line prompt for
opening up the server in single user mode. I then restored master from the
previous backup and server no longer functions.
I just want to confirm that is what you would expect from one install to
another. Reading back it now looks like I was trying to put the square piece
through the round hole !!
Any suggestions for how I might achieve my goal?
Thanks
MPMHi
If logins/passwords are the only things you need to keep then you may want
to look at
http://support.microsoft.com/kb/246133/
Good overall references are:
http://support.microsoft.com/kb/224071/EN-US/
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546
John
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:A30FB667-82C7-4156-A70C-E24497E3E96D@.microsoft.com...
> All,
> I had to downgrade from Enterprise to Standard on a server. So I took
> backups of all DB's, uninstalled enterprise and installed standard with
> SP3a. Then I restored all user DB's.
> I then wanted to use the master from enterprise and install into standard
> so
> I would keep users and passwords etc. We have apps that use users like
> SalesAppUser with a password hardly anyone knows. So I just wanted a nice
> plain replace new master with old master.
> Thanks to Robert Davies who helped me get to the command line prompt for
> opening up the server in single user mode. I then restored master from
> the
> previous backup and server no longer functions.
> I just want to confirm that is what you would expect from one install to
> another. Reading back it now looks like I was trying to put the square
> piece
> through the round hole !!
> Any suggestions for how I might achieve my goal?
> Thanks
> MPM

Restoring master db from old install of different SQL version

All,
I had to downgrade from Enterprise to Standard on a server. So I took
backups of all DB's, uninstalled enterprise and installed standard with
SP3a. Then I restored all user DB's.
I then wanted to use the master from enterprise and install into standard so
I would keep users and passwords etc. We have apps that use users like
SalesAppUser with a password hardly anyone knows. So I just wanted a nice
plain replace new master with old master.
Thanks to Robert Davies who helped me get to the command line prompt for
opening up the server in single user mode. I then restored master from the
previous backup and server no longer functions.
I just want to confirm that is what you would expect from one install to
another. Reading back it now looks like I was trying to put the square piece
through the round hole !!
Any suggestions for how I might achieve my goal?
Thanks
MPM
Hi
If logins/passwords are the only things you need to keep then you may want
to look at
http://support.microsoft.com/kb/246133/
Good overall references are:
http://support.microsoft.com/kb/224071/EN-US/
http://support.microsoft.com/default...;en-us;Q314546
John
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:A30FB667-82C7-4156-A70C-E24497E3E96D@.microsoft.com...
> All,
> I had to downgrade from Enterprise to Standard on a server. So I took
> backups of all DB's, uninstalled enterprise and installed standard with
> SP3a. Then I restored all user DB's.
> I then wanted to use the master from enterprise and install into standard
> so
> I would keep users and passwords etc. We have apps that use users like
> SalesAppUser with a password hardly anyone knows. So I just wanted a nice
> plain replace new master with old master.
> Thanks to Robert Davies who helped me get to the command line prompt for
> opening up the server in single user mode. I then restored master from
> the
> previous backup and server no longer functions.
> I just want to confirm that is what you would expect from one install to
> another. Reading back it now looks like I was trying to put the square
> piece
> through the round hole !!
> Any suggestions for how I might achieve my goal?
> Thanks
> MPM

Restoring master db from old install of different SQL version

All,
I had to downgrade from Enterprise to Standard on a server. So I took
backups of all DB's, uninstalled enterprise and installed standard with
SP3a. Then I restored all user DB's.
I then wanted to use the master from enterprise and install into standard so
I would keep users and passwords etc. We have apps that use users like
SalesAppUser with a password hardly anyone knows. So I just wanted a nice
plain replace new master with old master.
Thanks to Robert Davies who helped me get to the command line prompt for
opening up the server in single user mode. I then restored master from the
previous backup and server no longer functions.
I just want to confirm that is what you would expect from one install to
another. Reading back it now looks like I was trying to put the square piec
e
through the round hole !!
Any suggestions for how I might achieve my goal?
Thanks
MPMHi
If logins/passwords are the only things you need to keep then you may want
to look at
http://support.microsoft.com/kb/246133/
Good overall references are:
http://support.microsoft.com/kb/224071/EN-US/
http://support.microsoft.com/defaul...b;en-us;Q314546
John
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:A30FB667-82C7-4156-A70C-E24497E3E96D@.microsoft.com...
> All,
> I had to downgrade from Enterprise to Standard on a server. So I took
> backups of all DB's, uninstalled enterprise and installed standard with
> SP3a. Then I restored all user DB's.
> I then wanted to use the master from enterprise and install into standard
> so
> I would keep users and passwords etc. We have apps that use users like
> SalesAppUser with a password hardly anyone knows. So I just wanted a nice
> plain replace new master with old master.
> Thanks to Robert Davies who helped me get to the command line prompt for
> opening up the server in single user mode. I then restored master from
> the
> previous backup and server no longer functions.
> I just want to confirm that is what you would expect from one install to
> another. Reading back it now looks like I was trying to put the square
> piece
> through the round hole !!
> Any suggestions for how I might achieve my goal?
> Thanks
> MPM

Restoring from non-truncated transaction log

We have a database which unfortunately someone has restored from a backup
which was 2+ months old after he ran a script against the database that had
negative effects. So, now there is a gap in the data from Nov23 – Jan 31.
What I am wondering is would it be possible to restore the missing data in
the database with the transaction log? We were not backing up the
transaction log; so, it was not truncated. We have detached the database and
the transaction log and reattached them on another server.
For some reason every time I explain this in person, people miss something.
So, here are the basic facts:
MS SQL server 2000 database restored from backup
2 months of missing data
in tact(not truncated) transaction log
a script was run against the database before it was restored from backup on
Jan 31
database structure is identical.
Is this savable data, or should we just give up. I am not a DBA. He quit
about 1.5 months ago; so please be specific about what to do as I am not yet
extreamly familiar with SQL server 2000. By the way we have been working on
this since the 31st, and all we know for sure is that SQL doesn't like it
when you do something like this. Thanks in advance for any guidance either
way.
> We have a database which unfortunately someone has restored from a backup
> which was 2+ months old after he ran a script against the database that had
> negative effects.
That restore overwrites everything inside the database. Including the entries in the transaction
log. This of a restore like applying a VM Ware image on a machine.

> What I am wondering is would it be possible to restore the missing data in
> the database with the transaction log?
Are you saying that you saved something, somehow (backup, detach) before that restore was performed?
If not, I fail to see anyway to get anything back. Unelss you have some type of backup which is more
recent that the 2 month old backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Support" <Support@.discussions.microsoft.com> wrote in message
news:A8AE3D87-6975-444D-BD77-23407F529DEE@.microsoft.com...
> We have a database which unfortunately someone has restored from a backup
> which was 2+ months old after he ran a script against the database that had
> negative effects. So, now there is a gap in the data from Nov23 - Jan 31.
> What I am wondering is would it be possible to restore the missing data in
> the database with the transaction log? We were not backing up the
> transaction log; so, it was not truncated. We have detached the database and
> the transaction log and reattached them on another server.
> For some reason every time I explain this in person, people miss something.
> So, here are the basic facts:
> MS SQL server 2000 database restored from backup
> 2 months of missing data
> in tact(not truncated) transaction log
> a script was run against the database before it was restored from backup on
> Jan 31
> database structure is identical.
> Is this savable data, or should we just give up. I am not a DBA. He quit
> about 1.5 months ago; so please be specific about what to do as I am not yet
> extreamly familiar with SQL server 2000. By the way we have been working on
> this since the 31st, and all we know for sure is that SQL doesn't like it
> when you do something like this. Thanks in advance for any guidance either
> way.
|||> That restore overwrites everything inside the database. Including the
entries in the transaction
> log. This of a restore like applying a VM Ware image on a machine.
Like I said I am not very experienced with SQL. Please forgive my ignorance
in this matter. We were not backing up the Transaction log, just the
database. When the restore was done it wasn't restoring the transaction log.
Does it remove the entries from the transaction log when you restore the
database form a backup even if it doesn't restore the transaction log? I
would think that would be the opposite of what the transaction log was
supposed to do.
"Tibor Karaszi" wrote:

> That restore overwrites everything inside the database. Including the entries in the transaction
> log. This of a restore like applying a VM Ware image on a machine.
>
> Are you saying that you saved something, somehow (backup, detach) before that restore was performed?
> If not, I fail to see anyway to get anything back. Unelss you have some type of backup which is more
> recent that the 2 month old backup.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Support" <Support@.discussions.microsoft.com> wrote in message
> news:A8AE3D87-6975-444D-BD77-23407F529DEE@.microsoft.com...
>
>
|||Hi,
In my opinion, if
1- the DB was in Full recovery mode
and
2- you have have the complete T-log since Nov
then it will work.
But I would be surprised if you meet both requirement
Chris
"Support" <Support@.discussions.microsoft.com> a crit dans le message de
news:A8AE3D87-6975-444D-BD77-23407F529DEE@.microsoft.com...
> We have a database which unfortunately someone has restored from a backup
> which was 2+ months old after he ran a script against the database that
had
> negative effects. So, now there is a gap in the data from Nov23 - Jan 31.
> What I am wondering is would it be possible to restore the missing data in
> the database with the transaction log? We were not backing up the
> transaction log; so, it was not truncated. We have detached the database
and
> the transaction log and reattached them on another server.
> For some reason every time I explain this in person, people miss
something.
> So, here are the basic facts:
> MS SQL server 2000 database restored from backup
> 2 months of missing data
> in tact(not truncated) transaction log
> a script was run against the database before it was restored from backup
on
> Jan 31
> database structure is identical.
> Is this savable data, or should we just give up. I am not a DBA. He quit
> about 1.5 months ago; so please be specific about what to do as I am not
yet
> extreamly familiar with SQL server 2000. By the way we have been working
on
> this since the 31st, and all we know for sure is that SQL doesn't like it
> when you do something like this. Thanks in advance for any guidance
either
> way.
|||>> 1- the DB was in Full recovery mode
Sorry about this. Like I said, I am not very experienced with SQL. I had
NOT been backing up the transaction log, so I would assume the transaction
log would still have all of that data in it. Does it remove data from the
transaction log when you restore a database but not the transaction log? As
far as the "Full recovery mode" goes, do you mean backup the entire database
every time. We do backup the entire database as opposed to just the changes,
but not the transaction logs. Is this what you are referring to, or is it a
setting on the restore?
"Chris" wrote:

> Hi,
> In my opinion, if
> 1- the DB was in Full recovery mode
> and
> 2- you have have the complete T-log since Nov
> then it will work.
> But I would be surprised if you meet both requirement
> Chris
>
> "Support" <Support@.discussions.microsoft.com> a écrit dans le message de
> news:A8AE3D87-6975-444D-BD77-23407F529DEE@.microsoft.com...
> had
> and
> something.
> on
> yet
> on
> either
>
>
|||Hi - I suggest you read the Books Online topics on recovery models
('Selecting a Recovery Model' is a good start) as that will help you
understand the issues and alternatives you have depending on what recovery
model you're using.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"2 Struggling Admins" <2StrugglingAdmins@.discussions.microsoft.com> wrote in
message news:BABCE850-09CC-4766-A370-71360F7C9C8C@.microsoft.com...
> Sorry about this. Like I said, I am not very experienced with SQL. I had
> NOT been backing up the transaction log, so I would assume the transaction
> log would still have all of that data in it. Does it remove data from the
> transaction log when you restore a database but not the transaction log?
As
> far as the "Full recovery mode" goes, do you mean backup the entire
database
> every time. We do backup the entire database as opposed to just the
changes,
> but not the transaction logs. Is this what you are referring to, or is it
a[vbcol=seagreen]
> setting on the restore?
> "Chris" wrote:
backup[vbcol=seagreen]
that[vbcol=seagreen]
31.[vbcol=seagreen]
data in[vbcol=seagreen]
database[vbcol=seagreen]
backup[vbcol=seagreen]
quit[vbcol=seagreen]
not[vbcol=seagreen]
working[vbcol=seagreen]
it[vbcol=seagreen]
|||The transaction log is there to support transactional consistency (rollbacks and recovery) as well
as supporting "incremental" backup (backup of the transaction log).
A database backup contains both data pages as well as the necessary log records (from the
transaction log file(s)). This is so that SQL Server when you restore can apply the data pages and
perform recovery (roll -forward and -back). When your restore a database backup, the contents of the
ldf file of the current database will be overwritten. I agree with Paul R regarding reading up on
the suggested section on Books Online. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"2 Struggling Admins" <2StrugglingAdmins@.discussions.microsoft.com> wrote in message
news:12970127-DB33-4D7B-ADE7-7136BD17E4F8@.microsoft.com...[vbcol=seagreen]
> entries in the transaction
> Like I said I am not very experienced with SQL. Please forgive my ignorance
> in this matter. We were not backing up the Transaction log, just the
> database. When the restore was done it wasn't restoring the transaction log.
> Does it remove the entries from the transaction log when you restore the
> database form a backup even if it doesn't restore the transaction log? I
> would think that would be the opposite of what the transaction log was
> supposed to do.
>
> "Tibor Karaszi" wrote:

Restoring from non-truncated transaction log

We have a database which unfortunately someone has restored from a backup
which was 2+ months old after he ran a script against the database that had
negative effects. So, now there is a gap in the data from Nov23 â' Jan 31.
What I am wondering is would it be possible to restore the missing data in
the database with the transaction log? We were not backing up the
transaction log; so, it was not truncated. We have detached the database and
the transaction log and reattached them on another server.
For some reason every time I explain this in person, people miss something.
So, here are the basic facts:
MS SQL server 2000 database restored from backup
2 months of missing data
in tact(not truncated) transaction log
a script was run against the database before it was restored from backup on
Jan 31
database structure is identical.
Is this savable data, or should we just give up. I am not a DBA. He quit
about 1.5 months ago; so please be specific about what to do as I am not yet
extreamly familiar with SQL server 2000. By the way we have been working on
this since the 31st, and all we know for sure is that SQL doesn't like it
when you do something like this. Thanks in advance for any guidance either
way.> We have a database which unfortunately someone has restored from a backup
> which was 2+ months old after he ran a script against the database that had
> negative effects.
That restore overwrites everything inside the database. Including the entries in the transaction
log. This of a restore like applying a VM Ware image on a machine.
> What I am wondering is would it be possible to restore the missing data in
> the database with the transaction log?
Are you saying that you saved something, somehow (backup, detach) before that restore was performed?
If not, I fail to see anyway to get anything back. Unelss you have some type of backup which is more
recent that the 2 month old backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Support" <Support@.discussions.microsoft.com> wrote in message
news:A8AE3D87-6975-444D-BD77-23407F529DEE@.microsoft.com...
> We have a database which unfortunately someone has restored from a backup
> which was 2+ months old after he ran a script against the database that had
> negative effects. So, now there is a gap in the data from Nov23 - Jan 31.
> What I am wondering is would it be possible to restore the missing data in
> the database with the transaction log? We were not backing up the
> transaction log; so, it was not truncated. We have detached the database and
> the transaction log and reattached them on another server.
> For some reason every time I explain this in person, people miss something.
> So, here are the basic facts:
> MS SQL server 2000 database restored from backup
> 2 months of missing data
> in tact(not truncated) transaction log
> a script was run against the database before it was restored from backup on
> Jan 31
> database structure is identical.
> Is this savable data, or should we just give up. I am not a DBA. He quit
> about 1.5 months ago; so please be specific about what to do as I am not yet
> extreamly familiar with SQL server 2000. By the way we have been working on
> this since the 31st, and all we know for sure is that SQL doesn't like it
> when you do something like this. Thanks in advance for any guidance either
> way.|||> That restore overwrites everything inside the database. Including the
entries in the transaction
> log. This of a restore like applying a VM Ware image on a machine.
Like I said I am not very experienced with SQL. Please forgive my ignorance
in this matter. We were not backing up the Transaction log, just the
database. When the restore was done it wasn't restoring the transaction log.
Does it remove the entries from the transaction log when you restore the
database form a backup even if it doesn't restore the transaction log? I
would think that would be the opposite of what the transaction log was
supposed to do.
"Tibor Karaszi" wrote:
> > We have a database which unfortunately someone has restored from a backup
> > which was 2+ months old after he ran a script against the database that had
> > negative effects.
> That restore overwrites everything inside the database. Including the entries in the transaction
> log. This of a restore like applying a VM Ware image on a machine.
>
> > What I am wondering is would it be possible to restore the missing data in
> > the database with the transaction log?
> Are you saying that you saved something, somehow (backup, detach) before that restore was performed?
> If not, I fail to see anyway to get anything back. Unelss you have some type of backup which is more
> recent that the 2 month old backup.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Support" <Support@.discussions.microsoft.com> wrote in message
> news:A8AE3D87-6975-444D-BD77-23407F529DEE@.microsoft.com...
> > We have a database which unfortunately someone has restored from a backup
> > which was 2+ months old after he ran a script against the database that had
> > negative effects. So, now there is a gap in the data from Nov23 - Jan 31.
> > What I am wondering is would it be possible to restore the missing data in
> > the database with the transaction log? We were not backing up the
> > transaction log; so, it was not truncated. We have detached the database and
> > the transaction log and reattached them on another server.
> >
> > For some reason every time I explain this in person, people miss something.
> > So, here are the basic facts:
> >
> > MS SQL server 2000 database restored from backup
> > 2 months of missing data
> > in tact(not truncated) transaction log
> > a script was run against the database before it was restored from backup on
> > Jan 31
> > database structure is identical.
> >
> > Is this savable data, or should we just give up. I am not a DBA. He quit
> > about 1.5 months ago; so please be specific about what to do as I am not yet
> > extreamly familiar with SQL server 2000. By the way we have been working on
> > this since the 31st, and all we know for sure is that SQL doesn't like it
> > when you do something like this. Thanks in advance for any guidance either
> > way.
>
>|||Hi,
In my opinion, if
1- the DB was in Full recovery mode
and
2- you have have the complete T-log since Nov
then it will work.
But I would be surprised if you meet both requirement
Chris
"Support" <Support@.discussions.microsoft.com> a écrit dans le message de
news:A8AE3D87-6975-444D-BD77-23407F529DEE@.microsoft.com...
> We have a database which unfortunately someone has restored from a backup
> which was 2+ months old after he ran a script against the database that
had
> negative effects. So, now there is a gap in the data from Nov23 - Jan 31.
> What I am wondering is would it be possible to restore the missing data in
> the database with the transaction log? We were not backing up the
> transaction log; so, it was not truncated. We have detached the database
and
> the transaction log and reattached them on another server.
> For some reason every time I explain this in person, people miss
something.
> So, here are the basic facts:
> MS SQL server 2000 database restored from backup
> 2 months of missing data
> in tact(not truncated) transaction log
> a script was run against the database before it was restored from backup
on
> Jan 31
> database structure is identical.
> Is this savable data, or should we just give up. I am not a DBA. He quit
> about 1.5 months ago; so please be specific about what to do as I am not
yet
> extreamly familiar with SQL server 2000. By the way we have been working
on
> this since the 31st, and all we know for sure is that SQL doesn't like it
> when you do something like this. Thanks in advance for any guidance
either
> way.|||>> 1- the DB was in Full recovery mode
Sorry about this. Like I said, I am not very experienced with SQL. I had
NOT been backing up the transaction log, so I would assume the transaction
log would still have all of that data in it. Does it remove data from the
transaction log when you restore a database but not the transaction log? As
far as the "Full recovery mode" goes, do you mean backup the entire database
every time. We do backup the entire database as opposed to just the changes,
but not the transaction logs. Is this what you are referring to, or is it a
setting on the restore?
"Chris" wrote:
> Hi,
> In my opinion, if
> 1- the DB was in Full recovery mode
> and
> 2- you have have the complete T-log since Nov
> then it will work.
> But I would be surprised if you meet both requirement
> Chris
>
> "Support" <Support@.discussions.microsoft.com> a écrit dans le message de
> news:A8AE3D87-6975-444D-BD77-23407F529DEE@.microsoft.com...
> > We have a database which unfortunately someone has restored from a backup
> > which was 2+ months old after he ran a script against the database that
> had
> > negative effects. So, now there is a gap in the data from Nov23 - Jan 31.
> > What I am wondering is would it be possible to restore the missing data in
> > the database with the transaction log? We were not backing up the
> > transaction log; so, it was not truncated. We have detached the database
> and
> > the transaction log and reattached them on another server.
> >
> > For some reason every time I explain this in person, people miss
> something.
> > So, here are the basic facts:
> >
> > MS SQL server 2000 database restored from backup
> > 2 months of missing data
> > in tact(not truncated) transaction log
> > a script was run against the database before it was restored from backup
> on
> > Jan 31
> > database structure is identical.
> >
> > Is this savable data, or should we just give up. I am not a DBA. He quit
> > about 1.5 months ago; so please be specific about what to do as I am not
> yet
> > extreamly familiar with SQL server 2000. By the way we have been working
> on
> > this since the 31st, and all we know for sure is that SQL doesn't like it
> > when you do something like this. Thanks in advance for any guidance
> either
> > way.
>
>|||Hi - I suggest you read the Books Online topics on recovery models
('Selecting a Recovery Model' is a good start) as that will help you
understand the issues and alternatives you have depending on what recovery
model you're using.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"2 Struggling Admins" <2StrugglingAdmins@.discussions.microsoft.com> wrote in
message news:BABCE850-09CC-4766-A370-71360F7C9C8C@.microsoft.com...
> >> 1- the DB was in Full recovery mode
> Sorry about this. Like I said, I am not very experienced with SQL. I had
> NOT been backing up the transaction log, so I would assume the transaction
> log would still have all of that data in it. Does it remove data from the
> transaction log when you restore a database but not the transaction log?
As
> far as the "Full recovery mode" goes, do you mean backup the entire
database
> every time. We do backup the entire database as opposed to just the
changes,
> but not the transaction logs. Is this what you are referring to, or is it
a
> setting on the restore?
> "Chris" wrote:
> > Hi,
> >
> > In my opinion, if
> > 1- the DB was in Full recovery mode
> > and
> > 2- you have have the complete T-log since Nov
> >
> > then it will work.
> >
> > But I would be surprised if you meet both requirement
> > Chris
> >
> >
> > "Support" <Support@.discussions.microsoft.com> a écrit dans le message de
> > news:A8AE3D87-6975-444D-BD77-23407F529DEE@.microsoft.com...
> > > We have a database which unfortunately someone has restored from a
backup
> > > which was 2+ months old after he ran a script against the database
that
> > had
> > > negative effects. So, now there is a gap in the data from Nov23 - Jan
31.
> > > What I am wondering is would it be possible to restore the missing
data in
> > > the database with the transaction log? We were not backing up the
> > > transaction log; so, it was not truncated. We have detached the
database
> > and
> > > the transaction log and reattached them on another server.
> > >
> > > For some reason every time I explain this in person, people miss
> > something.
> > > So, here are the basic facts:
> > >
> > > MS SQL server 2000 database restored from backup
> > > 2 months of missing data
> > > in tact(not truncated) transaction log
> > > a script was run against the database before it was restored from
backup
> > on
> > > Jan 31
> > > database structure is identical.
> > >
> > > Is this savable data, or should we just give up. I am not a DBA. He
quit
> > > about 1.5 months ago; so please be specific about what to do as I am
not
> > yet
> > > extreamly familiar with SQL server 2000. By the way we have been
working
> > on
> > > this since the 31st, and all we know for sure is that SQL doesn't like
it
> > > when you do something like this. Thanks in advance for any guidance
> > either
> > > way.
> >
> >
> >|||The transaction log is there to support transactional consistency (rollbacks and recovery) as well
as supporting "incremental" backup (backup of the transaction log).
A database backup contains both data pages as well as the necessary log records (from the
transaction log file(s)). This is so that SQL Server when you restore can apply the data pages and
perform recovery (roll -forward and -back). When your restore a database backup, the contents of the
ldf file of the current database will be overwritten. I agree with Paul R regarding reading up on
the suggested section on Books Online. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"2 Struggling Admins" <2StrugglingAdmins@.discussions.microsoft.com> wrote in message
news:12970127-DB33-4D7B-ADE7-7136BD17E4F8@.microsoft.com...
>> That restore overwrites everything inside the database. Including the
> entries in the transaction
>> log. This of a restore like applying a VM Ware image on a machine.
> Like I said I am not very experienced with SQL. Please forgive my ignorance
> in this matter. We were not backing up the Transaction log, just the
> database. When the restore was done it wasn't restoring the transaction log.
> Does it remove the entries from the transaction log when you restore the
> database form a backup even if it doesn't restore the transaction log? I
> would think that would be the opposite of what the transaction log was
> supposed to do.
>
> "Tibor Karaszi" wrote:
>> > We have a database which unfortunately someone has restored from a backup
>> > which was 2+ months old after he ran a script against the database that had
>> > negative effects.
>> That restore overwrites everything inside the database. Including the entries in the transaction
>> log. This of a restore like applying a VM Ware image on a machine.
>>
>> > What I am wondering is would it be possible to restore the missing data in
>> > the database with the transaction log?
>> Are you saying that you saved something, somehow (backup, detach) before that restore was
>> performed?
>> If not, I fail to see anyway to get anything back. Unelss you have some type of backup which is
>> more
>> recent that the 2 month old backup.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Support" <Support@.discussions.microsoft.com> wrote in message
>> news:A8AE3D87-6975-444D-BD77-23407F529DEE@.microsoft.com...
>> > We have a database which unfortunately someone has restored from a backup
>> > which was 2+ months old after he ran a script against the database that had
>> > negative effects. So, now there is a gap in the data from Nov23 - Jan 31.
>> > What I am wondering is would it be possible to restore the missing data in
>> > the database with the transaction log? We were not backing up the
>> > transaction log; so, it was not truncated. We have detached the database and
>> > the transaction log and reattached them on another server.
>> >
>> > For some reason every time I explain this in person, people miss something.
>> > So, here are the basic facts:
>> >
>> > MS SQL server 2000 database restored from backup
>> > 2 months of missing data
>> > in tact(not truncated) transaction log
>> > a script was run against the database before it was restored from backup on
>> > Jan 31
>> > database structure is identical.
>> >
>> > Is this savable data, or should we just give up. I am not a DBA. He quit
>> > about 1.5 months ago; so please be specific about what to do as I am not yet
>> > extreamly familiar with SQL server 2000. By the way we have been working on
>> > this since the 31st, and all we know for sure is that SQL doesn't like it
>> > when you do something like this. Thanks in advance for any guidance either
>> > way.
>>

Restoring from non-truncated transaction log

We have a database which unfortunately someone has restored from a backup
which was 2+ months old after he ran a script against the database that had
negative effects. So, now there is a gap in the data from Nov23 – Jan 31.
What I am wondering is would it be possible to restore the missing data in
the database with the transaction log? We were not backing up the
transaction log; so, it was not truncated. We have detached the database an
d
the transaction log and reattached them on another server.
For some reason every time I explain this in person, people miss something.
So, here are the basic facts:
MS SQL server 2000 database restored from backup
2 months of missing data
in tact(not truncated) transaction log
a script was run against the database before it was restored from backup on
Jan 31
database structure is identical.
Is this savable data, or should we just give up. I am not a DBA. He quit
about 1.5 months ago; so please be specific about what to do as I am not yet
extreamly familiar with SQL server 2000. By the way we have been working on
this since the 31st, and all we know for sure is that SQL doesn't like it
when you do something like this. Thanks in advance for any guidance either
way.> We have a database which unfortunately someone has restored from a backup
> which was 2+ months old after he ran a script against the database that ha
d
> negative effects.
That restore overwrites everything inside the database. Including the entrie
s in the transaction
log. This of a restore like applying a VM Ware image on a machine.

> What I am wondering is would it be possible to restore the missing data in
> the database with the transaction log?
Are you saying that you saved something, somehow (backup, detach) before tha
t restore was performed?
If not, I fail to see anyway to get anything back. Unelss you have some type
of backup which is more
recent that the 2 month old backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Support" <Support@.discussions.microsoft.com> wrote in message
news:A8AE3D87-6975-444D-BD77-23407F529DEE@.microsoft.com...
> We have a database which unfortunately someone has restored from a backup
> which was 2+ months old after he ran a script against the database that ha
d
> negative effects. So, now there is a gap in the data from Nov23 - Jan 31.
> What I am wondering is would it be possible to restore the missing data in
> the database with the transaction log? We were not backing up the
> transaction log; so, it was not truncated. We have detached the database
and
> the transaction log and reattached them on another server.
> For some reason every time I explain this in person, people miss something
.
> So, here are the basic facts:
> MS SQL server 2000 database restored from backup
> 2 months of missing data
> in tact(not truncated) transaction log
> a script was run against the database before it was restored from backup o
n
> Jan 31
> database structure is identical.
> Is this savable data, or should we just give up. I am not a DBA. He quit
> about 1.5 months ago; so please be specific about what to do as I am not y
et
> extreamly familiar with SQL server 2000. By the way we have been working
on
> this since the 31st, and all we know for sure is that SQL doesn't like it
> when you do something like this. Thanks in advance for any guidance eithe
r
> way.|||> That restore overwrites everything inside the database. Including the
entries in the transaction
> log. This of a restore like applying a VM Ware image on a machine.
Like I said I am not very experienced with SQL. Please forgive my ignorance
in this matter. We were not backing up the Transaction log, just the
database. When the restore was done it wasn't restoring the transaction log
.
Does it remove the entries from the transaction log when you restore the
database form a backup even if it doesn't restore the transaction log? I
would think that would be the opposite of what the transaction log was
supposed to do.
"Tibor Karaszi" wrote:

> That restore overwrites everything inside the database. Including the entr
ies in the transaction
> log. This of a restore like applying a VM Ware image on a machine.
>
> Are you saying that you saved something, somehow (backup, detach) before t
hat restore was performed?
> If not, I fail to see anyway to get anything back. Unelss you have some ty
pe of backup which is more
> recent that the 2 month old backup.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Support" <Support@.discussions.microsoft.com> wrote in message
> news:A8AE3D87-6975-444D-BD77-23407F529DEE@.microsoft.com...
>
>|||Hi,
In my opinion, if
1- the DB was in Full recovery mode
and
2- you have have the complete T-log since Nov
then it will work.
But I would be surprised if you meet both requirement
Chris
"Support" <Support@.discussions.microsoft.com> a crit dans le message de
news:A8AE3D87-6975-444D-BD77-23407F529DEE@.microsoft.com...
> We have a database which unfortunately someone has restored from a backup
> which was 2+ months old after he ran a script against the database that
had
> negative effects. So, now there is a gap in the data from Nov23 - Jan 31.
> What I am wondering is would it be possible to restore the missing data in
> the database with the transaction log? We were not backing up the
> transaction log; so, it was not truncated. We have detached the database
and
> the transaction log and reattached them on another server.
> For some reason every time I explain this in person, people miss
something.
> So, here are the basic facts:
> MS SQL server 2000 database restored from backup
> 2 months of missing data
> in tact(not truncated) transaction log
> a script was run against the database before it was restored from backup
on
> Jan 31
> database structure is identical.
> Is this savable data, or should we just give up. I am not a DBA. He quit
> about 1.5 months ago; so please be specific about what to do as I am not
yet
> extreamly familiar with SQL server 2000. By the way we have been working
on
> this since the 31st, and all we know for sure is that SQL doesn't like it
> when you do something like this. Thanks in advance for any guidance
either
> way.|||>> 1- the DB was in Full recovery mode
Sorry about this. Like I said, I am not very experienced with SQL. I had
NOT been backing up the transaction log, so I would assume the transaction
log would still have all of that data in it. Does it remove data from the
transaction log when you restore a database but not the transaction log? As
far as the "Full recovery mode" goes, do you mean backup the entire database
every time. We do backup the entire database as opposed to just the changes
,
but not the transaction logs. Is this what you are referring to, or is it a
setting on the restore?
"Chris" wrote:

> Hi,
> In my opinion, if
> 1- the DB was in Full recovery mode
> and
> 2- you have have the complete T-log since Nov
> then it will work.
> But I would be surprised if you meet both requirement
> Chris
>
> "Support" <Support@.discussions.microsoft.com> a écrit dans le message de
> news:A8AE3D87-6975-444D-BD77-23407F529DEE@.microsoft.com...
> had
> and
> something.
> on
> yet
> on
> either
>
>|||Hi - I suggest you read the Books Online topics on recovery models
('Selecting a Recovery Model' is a good start) as that will help you
understand the issues and alternatives you have depending on what recovery
model you're using.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"2 Struggling Admins" <2StrugglingAdmins@.discussions.microsoft.com> wrote in
message news:BABCE850-09CC-4766-A370-71360F7C9C8C@.microsoft.com...
> Sorry about this. Like I said, I am not very experienced with SQL. I had
> NOT been backing up the transaction log, so I would assume the transaction
> log would still have all of that data in it. Does it remove data from the
> transaction log when you restore a database but not the transaction log?
As
> far as the "Full recovery mode" goes, do you mean backup the entire
database
> every time. We do backup the entire database as opposed to just the
changes,
> but not the transaction logs. Is this what you are referring to, or is it
a[vbcol=seagreen]
> setting on the restore?
> "Chris" wrote:
>
backup[vbcol=seagreen]
that[vbcol=seagreen]
31.[vbcol=seagreen]
data in[vbcol=seagreen]
database[vbcol=seagreen]
backup[vbcol=seagreen]
quit[vbcol=seagreen]
not[vbcol=seagreen]
working[vbcol=seagreen]
it[vbcol=seagreen]|||The transaction log is there to support transactional consistency (rollbacks
and recovery) as well
as supporting "incremental" backup (backup of the transaction log).
A database backup contains both data pages as well as the necessary log reco
rds (from the
transaction log file(s)). This is so that SQL Server when you restore can ap
ply the data pages and
perform recovery (roll -forward and -back). When your restore a database bac
kup, the contents of the
ldf file of the current database will be overwritten. I agree with Paul R re
garding reading up on
the suggested section on Books Online. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"2 Struggling Admins" <2StrugglingAdmins@.discussions.microsoft.com> wrote in
message
news:12970127-DB33-4D7B-ADE7-7136BD17E4F8@.microsoft.com...[vbcol=seagreen]
> entries in the transaction
> Like I said I am not very experienced with SQL. Please forgive my ignoran
ce
> in this matter. We were not backing up the Transaction log, just the
> database. When the restore was done it wasn't restoring the transaction l
og.
> Does it remove the entries from the transaction log when you restore the
> database form a backup even if it doesn't restore the transaction log? I
> would think that would be the opposite of what the transaction log was
> supposed to do.
>
> "Tibor Karaszi" wrote:
>

Restoring DB

Hi all,

I have a very old MSSQL data backup file.
I do not even remember that it was created in SQL7 or 2000.
Anyway, I'm trying to recreate DB from this backup.
Is it possible to create DB from backup file?

I tried restore from that file and SQL server complaining.
"The file on device '***' is not valid Microsoft tape format backup set."
Thanks
-JayKev (nospam@.msu.edu) writes:
> I have a very old MSSQL data backup file.
> I do not even remember that it was created in SQL7 or 2000.
> Anyway, I'm trying to recreate DB from this backup.
> Is it possible to create DB from backup file?
> I tried restore from that file and SQL server complaining.
> "The file on device '***' is not valid Microsoft tape format backup set."

Maybe it is so old that it is from SQL 6.5? In that case, you need SQL 6.5
to read it.

If you are using SQL 2000, you should be able to restore the database,
no matter if the backup was taken on SQL7 or SQL2000.

Does the error message actually say ***, or is that something you put
in?

The actual command, and the actual error message could give a clue if
you are happening to do something wrong.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Yes, It might be 6.5.
'***' was name of my file.

Thank you.

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns944872A36966Yazorman@.127.0.0.1...
> Kev (nospam@.msu.edu) writes:
> > I have a very old MSSQL data backup file.
> > I do not even remember that it was created in SQL7 or 2000.
> > Anyway, I'm trying to recreate DB from this backup.
> > Is it possible to create DB from backup file?
> > I tried restore from that file and SQL server complaining.
> > "The file on device '***' is not valid Microsoft tape format backup
set."
> Maybe it is so old that it is from SQL 6.5? In that case, you need SQL 6.5
> to read it.
> If you are using SQL 2000, you should be able to restore the database,
> no matter if the backup was taken on SQL7 or SQL2000.
> Does the error message actually say ***, or is that something you put
> in?
> The actual command, and the actual error message could give a clue if
> you are happening to do something wrong.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp