Restored Backup problem

I had to reinstall my entire Server week. I had everything of importance
backed up.
When I put my Sql Server database .MDF files back, I found that although the
Users within the databases were there, the Logins were not. Where are the
Logins stored? I have my entire previous hard disk contents available. I jus
t
need to know what to restore.Hi
In master database.
t_4g4w.asp" target="_blank">http://msdn.microsoft.com/library/d... />
t_4g4w.asp
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Hoof Hearted" <HoofHearted@.discussions.microsoft.com> wrote in message
news:87C35929-C18C-4C8A-A80C-B36283866EE2@.microsoft.com...
>I had to reinstall my entire Server week. I had everything of importance
> backed up.
> When I put my Sql Server database .MDF files back, I found that although
> the
> Users within the databases were there, the Logins were not. Where are the
> Logins stored? I have my entire previous hard disk contents available. I
> just
> need to know what to restore.|||Hello Mike,
Yes I should have thought of that. It works fine now. Thank you.
Ian

Restore Without msdb.backup Record

How is it possible to restore a DB from .BAK and .TRN
files that aren't listed in the DB restore drop-down lists
in Enterprise Manager?
I've read about the msdb.backupfile and
msdb.backupmediafamily tables and how they are used to
populate the drop-down lists for a DB restore. Is there a
way to tweak those tables to give me access to the files I
need to use in order to do the backup? Is there another
way to restore the database using those files?
Any help or direction is GREATLY appreciated! We are dead
in the water right now...
Thanks in advance!
MicahYou can simply use the RESTORE command in query analyzer. You can view the
contents of the files with the RESTORE FILELISTONLY and RESTORE HEADERONLY
commands. All of these can be found in BooksOnLine.
--
Andrew J. Kelly
SQL Server MVP
"Micah" <anonymous@.discussions.microsoft.com> wrote in message
news:0f9401c3dfb1$41db91a0$a301280a@.phx.gbl...
> How is it possible to restore a DB from .BAK and .TRN
> files that aren't listed in the DB restore drop-down lists
> in Enterprise Manager?
> I've read about the msdb.backupfile and
> msdb.backupmediafamily tables and how they are used to
> populate the drop-down lists for a DB restore. Is there a
> way to tweak those tables to give me access to the files I
> need to use in order to do the backup? Is there another
> way to restore the database using those files?
> Any help or direction is GREATLY appreciated! We are dead
> in the water right now...
> Thanks in advance!
> Micah|||Thank you very much!
It took a little while to get the command just right, but
I was able to do it.
Thanks again,
Micah
>--Original Message--
>You can simply use the RESTORE command in query
analyzer. You can view the
>contents of the files with the RESTORE FILELISTONLY and
RESTORE HEADERONLY
>commands. All of these can be found in BooksOnLine.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Micah" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0f9401c3dfb1$41db91a0$a301280a@.phx.gbl...
>> How is it possible to restore a DB from .BAK and .TRN
>> files that aren't listed in the DB restore drop-down
lists
>> in Enterprise Manager?
>> I've read about the msdb.backupfile and
>> msdb.backupmediafamily tables and how they are used to
>> populate the drop-down lists for a DB restore. Is
there a
>> way to tweak those tables to give me access to the
files I
>> need to use in order to do the backup? Is there another
>> way to restore the database using those files?
>> Any help or direction is GREATLY appreciated! We are
dead
>> in the water right now...
>> Thanks in advance!
>> Micah
>
>.
>|||from EM you can also use the restore from device option and pick the
files from disk.
Micah wrote:
> How is it possible to restore a DB from .BAK and .TRN
> files that aren't listed in the DB restore drop-down lists
> in Enterprise Manager?
> I've read about the msdb.backupfile and
> msdb.backupmediafamily tables and how they are used to
> populate the drop-down lists for a DB restore. Is there a
> way to tweak those tables to give me access to the files I
> need to use in order to do the backup? Is there another
> way to restore the database using those files?
> Any help or direction is GREATLY appreciated! We are dead
> in the water right now...
> Thanks in advance!
> Micah

Restore Transaction log or the .bak

If there is inconsistency on the DB and I want to restore to a last known good state, should I restore the transaction log files which is backed up every hour or the .bak file which is backed up every night. Basically, what is the difference between the t
wo? When should I use the Transaction log files? Thanks.
This depends on when you what you want to do and when you database was last
consistant.
If the last consistant state was just after you last nightly full or
differential backups then you will not need any transaction log backups.
The transaction log allows you to restore up to a particular point in time
between your nightly full and/or differentials backups. If your requirement
is to restore your database to 8:36 AM today, then you will need to use the
last full backup, plus the closest differential (last nights, if there was
one) and then the 1 am, 2 am, 3, am, 4 am, 5 am, 6 am, 7 am, 8 am and 9 am
transaction logs to restore using the STOPAT. The STOPAT command allows you
to stop the restore process at 8:36 am.
What backups do you have and when where they taken? Also when was the last
time you knew for sure the database was consistant.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"mmc" <anonymous@.discussions.microsoft.com> wrote in message
news:AD0940C9-56AF-4851-88B0-CBC2AB05058E@.microsoft.com...
> If there is inconsistency on the DB and I want to restore to a last known
good state, should I restore the transaction log files which is backed up
every hour or the .bak file which is backed up every night. Basically, what
is the difference between the two? When should I use the Transaction log
files? Thanks.
|||You need to read the BOL (Books On-Line) section on Backing up and Restoring
Databases located under Administering SQL Server. It is a bit long and
complex, but then the topic is somewhat complex and deserves a long
explanation.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"mmc" <anonymous@.discussions.microsoft.com> wrote in message
news:AD0940C9-56AF-4851-88B0-CBC2AB05058E@.microsoft.com...
> If there is inconsistency on the DB and I want to restore to a last known
good state, should I restore the transaction log files which is backed up
every hour or the .bak file which is backed up every night. Basically, what
is the difference between the two? When should I use the Transaction log
files? Thanks.

Restore Transaction log or the .bak

If there is inconsistency on the DB and I want to restore to a last known go
od state, should I restore the transaction log files which is backed up ever
y hour or the .bak file which is backed up every night. Basically, what is t
he difference between the t
wo? When should I use the Transaction log files? Thanks.This depends on when you what you want to do and when you database was last
consistant.
If the last consistant state was just after you last nightly full or
differential backups then you will not need any transaction log backups.
The transaction log allows you to restore up to a particular point in time
between your nightly full and/or differentials backups. If your requirement
is to restore your database to 8:36 AM today, then you will need to use the
last full backup, plus the closest differential (last nights, if there was
one) and then the 1 am, 2 am, 3, am, 4 am, 5 am, 6 am, 7 am, 8 am and 9 am
transaction logs to restore using the STOPAT. The STOPAT command allows you
to stop the restore process at 8:36 am.
What backups do you have and when where they taken? Also when was the last
time you knew for sure the database was consistant.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"mmc" <anonymous@.discussions.microsoft.com> wrote in message
news:AD0940C9-56AF-4851-88B0-CBC2AB05058E@.microsoft.com...
> If there is inconsistency on the DB and I want to restore to a last known
good state, should I restore the transaction log files which is backed up
every hour or the .bak file which is backed up every night. Basically, what
is the difference between the two? When should I use the Transaction log
files? Thanks.|||You need to read the BOL (Books On-Line) section on Backing up and Restoring
Databases located under Administering SQL Server. It is a bit long and
complex, but then the topic is somewhat complex and deserves a long
explanation.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"mmc" <anonymous@.discussions.microsoft.com> wrote in message
news:AD0940C9-56AF-4851-88B0-CBC2AB05058E@.microsoft.com...
> If there is inconsistency on the DB and I want to restore to a last known
good state, should I restore the transaction log files which is backed up
every hour or the .bak file which is backed up every night. Basically, what
is the difference between the two? When should I use the Transaction log
files? Thanks.sql

Restore Transaction log or the .bak

If there is inconsistency on the DB and I want to restore to a last known good state, should I restore the transaction log files which is backed up every hour or the .bak file which is backed up every night. Basically, what is the difference between the two? When should I use the Transaction log files? Thanks.This depends on when you what you want to do and when you database was last
consistant.
If the last consistant state was just after you last nightly full or
differential backups then you will not need any transaction log backups.
The transaction log allows you to restore up to a particular point in time
between your nightly full and/or differentials backups. If your requirement
is to restore your database to 8:36 AM today, then you will need to use the
last full backup, plus the closest differential (last nights, if there was
one) and then the 1 am, 2 am, 3, am, 4 am, 5 am, 6 am, 7 am, 8 am and 9 am
transaction logs to restore using the STOPAT. The STOPAT command allows you
to stop the restore process at 8:36 am.
What backups do you have and when where they taken? Also when was the last
time you knew for sure the database was consistant.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"mmc" <anonymous@.discussions.microsoft.com> wrote in message
news:AD0940C9-56AF-4851-88B0-CBC2AB05058E@.microsoft.com...
> If there is inconsistency on the DB and I want to restore to a last known
good state, should I restore the transaction log files which is backed up
every hour or the .bak file which is backed up every night. Basically, what
is the difference between the two? When should I use the Transaction log
files? Thanks.|||You need to read the BOL (Books On-Line) section on Backing up and Restoring
Databases located under Administering SQL Server. It is a bit long and
complex, but then the topic is somewhat complex and deserves a long
explanation.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"mmc" <anonymous@.discussions.microsoft.com> wrote in message
news:AD0940C9-56AF-4851-88B0-CBC2AB05058E@.microsoft.com...
> If there is inconsistency on the DB and I want to restore to a last known
good state, should I restore the transaction log files which is backed up
every hour or the .bak file which is backed up every night. Basically, what
is the difference between the two? When should I use the Transaction log
files? Thanks.

restore transaction log

dear all,

I want to restore transaction log to other server using transact-SQL.

can we restore serveral transaction log files at the same time ?

anybody have experience on this one.

thanks

Use the "WITH NO RECOVERY" option after each restore to allow subsequent restores. The "WITH STANDBY" option allows the database to be inspected (read only) after eah restore. Use the "WITH RECOVERY" option on the final restore to make the database read/write.

You need to make sure each log is restored in the order in which it was created. Further, you need to ensure the transaction log backup matches the full backup i.e. : you can't skip log backups, and if you truncated the log at any point, then the sequence is broken and you would need to start again with another full backup.

|||

hi rod,

I have tries your suggestion but I still have some problem.

I restore my full backup to other server using no recovery option and then I restore the transaction log. which on the last transcation log I use recovery option.

that part its work perfectly.

the problem is when I want to restore another transcation log there is an error that said :

Msg 3117, Level 16, State 4, Line 1

The log or differential backup cannot be restored because no files are ready to rollforward.

Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.

we do daily restore transaction log to other server.

regards,

-dedys

|||

Ok, now I understand what you are trying to do.

Basically you can't do what you trying. Once you've restored a log "with recovery" you cannot restore another log at a later point. You would need to take another full backup.

What you should probably do is setup transaction log shipping but select the option to leave the database in a read only state.This allows you to read the database between log restores, however, if you are using the database when the log restore is scheduled, then either the resore will fail, or you can select the option to terminate current connections before the restore.

Does this make sense ? Let me know if you need further help on setting up log shipping.

Hope this helps.

|||

hi rod,

yes actually I want to set log shipping.

but I can't set it cause my production server is using sql 2000 and my staging server is using sql 2005.

so I plan to do it manually using job.

if you said that we can't add more transaction log after using with recovery.

so what is the best option for my case.

is there a third party software to do this ?

thanks

-dedys

Restore to Multiple Files?

I am in the process of configuring a new laptop. It has two hard drives and
I want to restore several of my large databases to files on both hard
drives, even though the original dbs on my old laptop are single-file.
Anyone know a secret that will allow you to restore a single-file database
to spread it's data over two files during the restore'
--
TheSQLGuru
President
Indicium Resources, Inc.Hi
I'm affraid you cannot do that. As SQL Server needs to restore/attach the
same number of files as you created db.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:%23mNKpyH9HHA.4180@.TK2MSFTNGP05.phx.gbl...
>I am in the process of configuring a new laptop. It has two hard drives
>and I want to restore several of my large databases to files on both hard
>drives, even though the original dbs on my old laptop are single-file.
>Anyone know a secret that will allow you to restore a single-file database
>to spread it's data over two files during the restore'
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
>
>|||I didn't think it could be done either, but I have found quite a number of
things that fall into that category on this forum so I thought I would ask
anyway!! :-)
--
TheSQLGuru
President
Indicium Resources, Inc.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uqQ5U8H9HHA.1484@.TK2MSFTNGP06.phx.gbl...
> Hi
> I'm affraid you cannot do that. As SQL Server needs to restore/attach the
> same number of files as you created db.
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:%23mNKpyH9HHA.4180@.TK2MSFTNGP05.phx.gbl...
>>I am in the process of configuring a new laptop. It has two hard drives
>>and I want to restore several of my large databases to files on both hard
>>drives, even though the original dbs on my old laptop are single-file.
>>Anyone know a secret that will allow you to restore a single-file database
>>to spread it's data over two files during the restore'
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>>
>|||You are probably better off placing the log files on one drive and the data
on the other.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:%23mNKpyH9HHA.4180@.TK2MSFTNGP05.phx.gbl...
>I am in the process of configuring a new laptop. It has two hard drives
>and I want to restore several of my large databases to files on both hard
>drives, even though the original dbs on my old laptop are single-file.
>Anyone know a secret that will allow you to restore a single-file database
>to spread it's data over two files during the restore'
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
>
>|||That will be done for my 'standard' databases. I have a few very large ones
I use for analyzing trace files and also one large test db for a client and
for those the read-throughput is key to optimal perforance thus the desire
to spread the data across both drives.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uTTCReJ9HHA.5456@.TK2MSFTNGP05.phx.gbl...
> You are probably better off placing the log files on one drive and the
> data on the other.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:%23mNKpyH9HHA.4180@.TK2MSFTNGP05.phx.gbl...
>>I am in the process of configuring a new laptop. It has two hard drives
>>and I want to restore several of my large databases to files on both hard
>>drives, even though the original dbs on my old laptop are single-file.
>>Anyone know a secret that will allow you to restore a single-file database
>>to spread it's data over two files during the restore'
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>>
>sql

Restore to different file size

My production database files total about 100GB, of which about 35GB is used.
My test machine has about 60GB available, so has plenty of room to
accommodate the entire database. When I try to restore a full backup though,
it screams at me that there is not enough disk space.
I created a new database with 40GB of data files, and that should be enough
to hold all the data. How can I keep the restore process from trying to
create exactly the same file configuration as the original database?
You can't.
The SQL restore process requires the creation of exactly the same file sizes
on the target system as existed on the originating system. I suggest
purchasing an external HDD, restorign to that, shrinking, then moving the
data files.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Daniel Rimmelzwaan (MVP - Dynamics NAV)" <daniel@.nadaspam.risplus.com>
wrote in message news:Ow$oUoH0HHA.1168@.TK2MSFTNGP02.phx.gbl...
> My production database files total about 100GB, of which about 35GB is
> used. My test machine has about 60GB available, so has plenty of room to
> accommodate the entire database. When I try to restore a full backup
> though, it screams at me that there is not enough disk space.
> I created a new database with 40GB of data files, and that should be
> enough to hold all the data. How can I keep the restore process from
> trying to create exactly the same file configuration as the original
> database?
|||I was afraid of that. Thanks Geoff.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OcDFQuH0HHA.1208@.TK2MSFTNGP03.phx.gbl...
> You can't.
> The SQL restore process requires the creation of exactly the same file
> sizes on the target system as existed on the originating system. I
> suggest purchasing an external HDD, restorign to that, shrinking, then
> moving the data files.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Daniel Rimmelzwaan (MVP - Dynamics NAV)" <daniel@.nadaspam.risplus.com>
> wrote in message news:Ow$oUoH0HHA.1168@.TK2MSFTNGP02.phx.gbl...
>

Restore to different file size

My production database files total about 100GB, of which about 35GB is used.
My test machine has about 60GB available, so has plenty of room to
accommodate the entire database. When I try to restore a full backup though,
it screams at me that there is not enough disk space.
I created a new database with 40GB of data files, and that should be enough
to hold all the data. How can I keep the restore process from trying to
create exactly the same file configuration as the original database?You can't.
The SQL restore process requires the creation of exactly the same file sizes
on the target system as existed on the originating system. I suggest
purchasing an external HDD, restorign to that, shrinking, then moving the
data files.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Daniel Rimmelzwaan (MVP - Dynamics NAV)" <daniel@.nadaspam.risplus.com>
wrote in message news:Ow$oUoH0HHA.1168@.TK2MSFTNGP02.phx.gbl...
> My production database files total about 100GB, of which about 35GB is
> used. My test machine has about 60GB available, so has plenty of room to
> accommodate the entire database. When I try to restore a full backup
> though, it screams at me that there is not enough disk space.
> I created a new database with 40GB of data files, and that should be
> enough to hold all the data. How can I keep the restore process from
> trying to create exactly the same file configuration as the original
> database?|||I was afraid of that. Thanks Geoff.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OcDFQuH0HHA.1208@.TK2MSFTNGP03.phx.gbl...
> You can't.
> The SQL restore process requires the creation of exactly the same file
> sizes on the target system as existed on the originating system. I
> suggest purchasing an external HDD, restorign to that, shrinking, then
> moving the data files.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Daniel Rimmelzwaan (MVP - Dynamics NAV)" <daniel@.nadaspam.risplus.com>
> wrote in message news:Ow$oUoH0HHA.1168@.TK2MSFTNGP02.phx.gbl...
>> My production database files total about 100GB, of which about 35GB is
>> used. My test machine has about 60GB available, so has plenty of room to
>> accommodate the entire database. When I try to restore a full backup
>> though, it screams at me that there is not enough disk space.
>> I created a new database with 40GB of data files, and that should be
>> enough to hold all the data. How can I keep the restore process from
>> trying to create exactly the same file configuration as the original
>> database?
>

Restore to different file size

My production database files total about 100GB, of which about 35GB is used.
My test machine has about 60GB available, so has plenty of room to
accommodate the entire database. When I try to restore a full backup though,
it screams at me that there is not enough disk space.
I created a new database with 40GB of data files, and that should be enough
to hold all the data. How can I keep the restore process from trying to
create exactly the same file configuration as the original database?You can't.
The SQL restore process requires the creation of exactly the same file sizes
on the target system as existed on the originating system. I suggest
purchasing an external HDD, restorign to that, shrinking, then moving the
data files.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Daniel Rimmelzwaan (MVP - Dynamics NAV)" <daniel@.nadaspam.risplus.com>
wrote in message news:Ow$oUoH0HHA.1168@.TK2MSFTNGP02.phx.gbl...
> My production database files total about 100GB, of which about 35GB is
> used. My test machine has about 60GB available, so has plenty of room to
> accommodate the entire database. When I try to restore a full backup
> though, it screams at me that there is not enough disk space.
> I created a new database with 40GB of data files, and that should be
> enough to hold all the data. How can I keep the restore process from
> trying to create exactly the same file configuration as the original
> database?|||I was afraid of that. Thanks Geoff.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OcDFQuH0HHA.1208@.TK2MSFTNGP03.phx.gbl...
> You can't.
> The SQL restore process requires the creation of exactly the same file
> sizes on the target system as existed on the originating system. I
> suggest purchasing an external HDD, restorign to that, shrinking, then
> moving the data files.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Daniel Rimmelzwaan (MVP - Dynamics NAV)" <daniel@.nadaspam.risplus.com>
> wrote in message news:Ow$oUoH0HHA.1168@.TK2MSFTNGP02.phx.gbl...
>

restore the replication system

Database A and B are replicated to each other. Both db reside on the same box.
The box has been rebuild due to a system crash. All data base files have
been preserved.
NT SA restored all user databases by attaching the datafiles except
master and msdb. Now, the replication system is out of sync.
Can I restore the master and msdb using attach db to restore the
entire replication system?
Any other better or correct way to restore the entire system when there is a
system crash?
Appreciate any help.
Wen
Wen,
provided you also restore the distribution database this may be possible. It
partly depends on 2 factors:
firstly, is the server name the same as the original one? If not then the
recovery strategy won't work and you'll need to start again - recreate the
publications and initialize.
If the servername is identical, then it may be possible - have a look in BOL
for "replication, backup and restore operations".
The second question is what type of replication are you using? In most cases
it is ok but I'd mention that if the distribution database's backup has been
allowed to get ahead of the publisher's backup and you are using
transactional replication there are likely to be problems.
Anyway, the first port of call is the BOL reference above, and please post
back if you have any issues which arise from that.
Regards,
Paul Ibison
|||Paul.
1. Yes, the servername is the same as the original one. and the
Distribution db
has been restored.
2. We have transactional and merge replication.
I will attache back the master and msdb and see if it works.
Thanks for the help.
Wen
"Paul Ibison" wrote:

> Wen,
> provided you also restore the distribution database this may be possible. It
> partly depends on 2 factors:
> firstly, is the server name the same as the original one? If not then the
> recovery strategy won't work and you'll need to start again - recreate the
> publications and initialize.
> If the servername is identical, then it may be possible - have a look in BOL
> for "replication, backup and restore operations".
> The second question is what type of replication are you using? In most cases
> it is ok but I'd mention that if the distribution database's backup has been
> allowed to get ahead of the publisher's backup and you are using
> transactional replication there are likely to be problems.
> Anyway, the first port of call is the BOL reference above, and please post
> back if you have any issues which arise from that.
> Regards,
> Paul Ibison
>
>
|||Wen,
there should be no problem with the merge database restoration, and you
might want to synchronize with the most upto date subscriber after the
restore.
As for the transactional, there could be issues. Did the publisher have the
sync with backup option set to true? If not, the distribution backup could
be ahead of the publisher restore. After the restore you will get an error
from the Log Reader Agent because it will detect that the Distributor is
ahead of the Publisher. The recommendation is to run sp_replrestart in the
publication database with no parameters. and ensure that that the
distribution agent, which could now deliver duplicate rows to Subscribers,
can continue despite these failures. Choose the -SkipError Distribution
Agent profile, or you can manually add the -SkipError parameter to the
runtime parameters of the Distribution Agents and supply the errors you want
the Distribution Agents to ignore.
You'll need to use linked servers to fix the inconsistencies, or you could
of course resort to reinitializing.
HTH,
Paul Ibison
|||Paul, the sync with backup option is fairly useless unless you are shipping
the transaction logs to a standby server.
The sequence to get this to work is
1) use the sync with backup option on your publication database and possibly
your distribution database
2) restore the msdb, publication database, and distribution database backups
on the standby server with the keep_replication switch.
3) then ship the publication and distribution databases tlogs and without
the keep_replication switch.
4) when the publisher goes offline you rename the standby server with the
name of the original publisher, and then do a sp_replrestart,
and -skiperrors switch.
In the case of this user you can't really use any of these options as they
have not been shipping the database. With a new master database, the user
probably does not have any of the subscribers listed as remote servers and
restoring or attaching any databases will be highly problematic.
The user is best to drop all replication and create publications and
subscriptions from scratch as their data is likely out of sync.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uY4Vt9RfEHA.3916@.TK2MSFTNGP11.phx.gbl...
> Wen,
> there should be no problem with the merge database restoration, and you
> might want to synchronize with the most upto date subscriber after the
> restore.
> As for the transactional, there could be issues. Did the publisher have
the
> sync with backup option set to true? If not, the distribution backup could
> be ahead of the publisher restore. After the restore you will get an error
> from the Log Reader Agent because it will detect that the Distributor is
> ahead of the Publisher. The recommendation is to run sp_replrestart in the
> publication database with no parameters. and ensure that that the
> distribution agent, which could now deliver duplicate rows to Subscribers,
> can continue despite these failures. Choose the -SkipError Distribution
> Agent profile, or you can manually add the -SkipError parameter to the
> runtime parameters of the Distribution Agents and supply the errors you
want
> the Distribution Agents to ignore.
> You'll need to use linked servers to fix the inconsistencies, or you could
> of course resort to reinitializing.
> HTH,
> Paul Ibison
>
|||Hilary,
from a practical point of view I accept your point - log-shipping, the 'sync
with backup' option and keep_replication go well together to minimize the
latency involved if you ship every few minutes.
However in BOL
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL% 20Server\80\Tools\Books\re
plsql.chm::/replbackup_3js7.htm) the recommendation is different to your
implementation:
- they advise 'keep_replication' for the last log.
- and sp_replrestart for semi-synchronous mode only - ie when the 'sync with
backup' option is false.
Are these just alternative methods?
Regards,
Paul Ibison
|||BOL lies.
Try to restore a tlog with the keep_replication switch.
I have reported this doc bug to Microsoft.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23PFoxqifEHA.3520@.TK2MSFTNGP10.phx.gbl...
> Hilary,
> from a practical point of view I accept your point - log-shipping, the
'sync
> with backup' option and keep_replication go well together to minimize the
> latency involved if you ship every few minutes.
> However in BOL
>
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL% 20Server\80\Tools\Books\re
> plsql.chm::/replbackup_3js7.htm) the recommendation is different to your
> implementation:
> - they advise 'keep_replication' for the last log.
> - and sp_replrestart for semi-synchronous mode only - ie when the 'sync
with
> backup' option is false.
> Are these just alternative methods?
> Regards,
> Paul Ibison
>
|||Hilary,
thanks for pointing this out, and for posting up a bonafide working
sequence.
As far as I remember, when I tested this I followed a different methodology
where I used "WITH KEEP_REPLICATION, RECOVERY" on the last transaction log
and this seemed to work. Unfortunately I didn't make a proper note of it and
I really need 3 boxes to repeat the test. Later this week it might be
possible to set it up and I'll post back then.
Regards,
Paul Ibison

Restore SQL 2000 Tables from Transaction Log Files

I inadvertently deleted related records from one table when I deleted anothe
r table. I am not sure how often the server admin backups the data or how h
e is doing it. I thought that I read somewhere that one could do that from
the log files. Can someone
please help me with this?
Thanks,Hi,
Steps to recover your table in the database, (Hope you are using FULL
Recovery Model)
1. Perform a transaction log backup in your current database
Backup log dbname to disk='drivename\txlog_final.bak'
2. Restore the FULL database backup into a new database
Restore database Newdbname from disk='physicaldrive\filename.bak' with
move 'logical_data_filename' to 'new_data_physicalfile.mdf',
move 'logical_log_filename' to 'new_log_physicalfile.ldf',
with NORECOVERY
3. Restore the subsequent TX log files in sequential order till the last
transaction log file in which log backup is taken
Restore log newdbname from disk='physicaldrive\txlogfile1.bak' with
norecovery
COntinue the step 3 for all transaction log files , but do not restore
the last transaction log file.
4. For the Last Transaction log file , use RECOVERY option
Restore log newdbname from disk='physicaldrive\txlog_final.bak' with
RECOVERY,STOPAT = 'Feb 26, 2004 10:00 AM'
This will revover the new database till 'Feb 26, 2004 10:00 AM'
Thanks
Hari
MCDBA
"Stephanie" <anonymous@.discussions.microsoft.com> wrote in message
news:77CEFC43-E1B9-454D-97BA-A4B32825D8E8@.microsoft.com...
> I inadvertently deleted related records from one table when I deleted
another table. I am not sure how often the server admin backups the data or
how he is doing it. I thought that I read somewhere that one could do that
from the log files. Can someone please help me with this?
> Thanks,|||Lumigent offers a tool called LogReader that is useful in finding what
happened.
Russell Fields
"Stephanie" <anonymous@.discussions.microsoft.com> wrote in message
news:77CEFC43-E1B9-454D-97BA-A4B32825D8E8@.microsoft.com...
> I inadvertently deleted related records from one table when I deleted
another table. I am not sure how often the server admin backups the data or
how he is doing it. I thought that I read somewhere that one could do that
from the log files. Can someone please help me with this?
> Thanks,

Restore SQL 2000 Tables from Transaction Log Files

I inadvertently deleted related records from one table when I deleted another table. I am not sure how often the server admin backups the data or how he is doing it. I thought that I read somewhere that one could do that from the log files. Can someone please help me with this
Thanks,Hi,
Steps to recover your table in the database, (Hope you are using FULL
Recovery Model)
1. Perform a transaction log backup in your current database
Backup log dbname to disk='drivename\txlog_final.bak'
2. Restore the FULL database backup into a new database
Restore database Newdbname from disk='physicaldrive\filename.bak' with
move 'logical_data_filename' to 'new_data_physicalfile.mdf',
move 'logical_log_filename' to 'new_log_physicalfile.ldf',
with NORECOVERY
3. Restore the subsequent TX log files in sequential order till the last
transaction log file in which log backup is taken
Restore log newdbname from disk='physicaldrive\txlogfile1.bak' with
norecovery
COntinue the step 3 for all transaction log files , but do not restore
the last transaction log file.
4. For the Last Transaction log file , use RECOVERY option
Restore log newdbname from disk='physicaldrive\txlog_final.bak' with
RECOVERY,STOPAT = 'Feb 26, 2004 10:00 AM'
This will revover the new database till 'Feb 26, 2004 10:00 AM'
Thanks
Hari
MCDBA
"Stephanie" <anonymous@.discussions.microsoft.com> wrote in message
news:77CEFC43-E1B9-454D-97BA-A4B32825D8E8@.microsoft.com...
> I inadvertently deleted related records from one table when I deleted
another table. I am not sure how often the server admin backups the data or
how he is doing it. I thought that I read somewhere that one could do that
from the log files. Can someone please help me with this?
> Thanks,|||Lumigent offers a tool called LogReader that is useful in finding what
happened.
Russell Fields
"Stephanie" <anonymous@.discussions.microsoft.com> wrote in message
news:77CEFC43-E1B9-454D-97BA-A4B32825D8E8@.microsoft.com...
> I inadvertently deleted related records from one table when I deleted
another table. I am not sure how often the server admin backups the data or
how he is doing it. I thought that I read somewhere that one could do that
from the log files. Can someone please help me with this?
> Thanks,

Restore Production database to Development

I have a production database with a backup job that creates files with the
naming convention dbname_db_200503291800.bak. I want to schedule a restore
job that will retire yesterdays backup. How can I write my restore statement
so that it will specify the backup file with yesterdays date.

ThanksTerri (terri@.cybernets.com) writes:
> I have a production database with a backup job that creates files with
> the naming convention dbname_db_200503291800.bak. I want to schedule a
> restore job that will retire yesterdays backup. How can I write my
> restore statement so that it will specify the backup file with
> yesterdays date.

DECLARE @.filename sysname
SELECT @.filename = 'dbname_db' +
convert(char(8), dateadd(DAY, -1, getdate()), 112) +
'.bak'
RESTORE DATABASE db FROM disk=@.filename

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Restore problem... "The media family on device '%ls' is incorrectly formed"

A client keeps sending me .BAK files. A couple have worked, but I
have gotten this error on many:
Error 3241 Severity 16 State 1
The media family on device '%ls' is incorrectly formed. SQL Server
cannot process this media family.
If I view the contents of the .BAK (in Enterprise Manager), it says it
is INCOMPLETE.
This indicates that maybe the backup was bad. However, since they have
sent me so many bad ones, my question is why are these backups turning
out bad?
Some details:
-The client is using SQL Server 2000 SP4, so am I
-I am running Win2K Server, they are running Win 2003 server.
-I looked at KB 297104 which deals with this error, but that is for
pre-SP4.
-When I run a restore verifyonly, I get the same error
Since I am off-site and can't go to the client site, any ideas on what
they may be doing wrong or what we can do for me to get a legitimate
copy of this database on my server? I am going crazy with these files
that won't load and don't know what to tell my client.have them run a verify before sending them to you. This should guarantee a
valid backup.
--
RelevantNoise.com - dedicated to mining blogs for business intelligence.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mike C" <michaeljc70@.hotmail.com> wrote in message
news:1187736521.741462.231380@.l22g2000prc.googlegroups.com...
>A client keeps sending me .BAK files. A couple have worked, but I
> have gotten this error on many:
> Error 3241 Severity 16 State 1
> The media family on device '%ls' is incorrectly formed. SQL Server
> cannot process this media family.
> If I view the contents of the .BAK (in Enterprise Manager), it says it
> is INCOMPLETE.
> This indicates that maybe the backup was bad. However, since they have
> sent me so many bad ones, my question is why are these backups turning
> out bad?
> Some details:
> -The client is using SQL Server 2000 SP4, so am I
> -I am running Win2K Server, they are running Win 2003 server.
> -I looked at KB 297104 which deals with this error, but that is for
> pre-SP4.
> -When I run a restore verifyonly, I get the same error
> Since I am off-site and can't go to the client site, any ideas on what
> they may be doing wrong or what we can do for me to get a legitimate
> copy of this database on my server? I am going crazy with these files
> that won't load and don't know what to tell my client.
>|||Hello,
First of all tell your client to do a restore with verify only or header only
to test, on their side, the correctness of the backup. If it's possible, tell
the client to do a full restore of the backup.
Second, if the first worked out well, send back the media to the client and
tell them to try the above process.
It is possible that between different copies of the backup file it becomes
corrupt. If the transfer of the backup file it is done over wan this is
something sure. A second possibility it is this file is part of a multivolume
backup. Sql Server has the option to backup a database to more than one file
to speed up the backup process.
Hope this helps you solve the problem.
I recommend you reading this too
http://www.dbforums.com/showthread.php?t=682406.
Cheers.
Mike C wrote:
>A client keeps sending me .BAK files. A couple have worked, but I
>have gotten this error on many:
>Error 3241 Severity 16 State 1
>The media family on device '%ls' is incorrectly formed. SQL Server
>cannot process this media family.
>If I view the contents of the .BAK (in Enterprise Manager), it says it
>is INCOMPLETE.
>This indicates that maybe the backup was bad. However, since they have
>sent me so many bad ones, my question is why are these backups turning
>out bad?
>Some details:
>-The client is using SQL Server 2000 SP4, so am I
>-I am running Win2K Server, they are running Win 2003 server.
>-I looked at KB 297104 which deals with this error, but that is for
>pre-SP4.
>-When I run a restore verifyonly, I get the same error
>Since I am off-site and can't go to the client site, any ideas on what
>they may be doing wrong or what we can do for me to get a legitimate
>copy of this database on my server? I am going crazy with these files
>that won't load and don't know what to tell my client.|||On Aug 22, 5:08 am, "flooriin" <u36842@.uwe> wrote:
> Hello,
> First of all tell your client to do arestorewith verify only or header only
> to test, on their side, the correctness of the backup. If it's possible, tell
> the client to do a fullrestoreof the backup.
> Second, if the first worked out well, send back themediato the client and
> tell them to try the above process.
> It is possible that between different copies of the backup file it becomes
> corrupt. If the transfer of the backup file it is done over wan this is
> something sure. A second possibility it is this file is part of a multivolume
> backup. Sql Server has the option to backup a database to more than one file
> to speed up the backup process.
> Hope this helps you solve theproblem.
> I recommend you reading this toohttp://www.dbforums.com/showthread.php?t=682406.
> Cheers.
> Mike C wrote:
> >A client keeps sending me .BAK files. A couple have worked, but I
> >have gotten this error on many:
> >Error 3241 Severity 16 State 1
> >Themediafamily on device '%ls' is incorrectly formed. SQL Server
> >cannot process thismediafamily.
> >If I view the contents of the .BAK (in Enterprise Manager), it says it
> >is INCOMPLETE.
> >This indicates that maybe the backup was bad. However, since they have
> >sent me so many bad ones, my question is why are these backups turning
> >out bad?
> >Some details:
> >-The client is using SQL Server 2000 SP4, so am I
> >-I am running Win2K Server, they are running Win 2003 server.
> >-I looked at KB 297104 which deals with this error, but that is for
> >pre-SP4.
> >-When I run arestoreverifyonly, I get the same error
> >Since I am off-site and can't go to the client site, any ideas on what
> >they may be doing wrong or what we can do for me to get a legitimate
> >copy of this database on my server? I am going crazy with these files
> >that won't load and don't know what to tell my client.
The client said they did a verify when they did the backup. I will
have them check to see if RESTORE VERIFYONLY works. Assuming the file
is good, I can only think of 2 unlikely issues:
-The file is becoming corrupt when they zip it up. Since it is bigger
than a DVD, that is the only way to get it to me.
-Running W2K Server vs. W2003 Server is making some dofference.|||On Aug 22, 5:08 am, "flooriin" <u36842@.uwe> wrote:
> Hello,
> First of all tell your client to do a restore with verify only or header only
> to test, on their side, the correctness of the backup. If it's possible, tell
> the client to do a full restore of the backup.
> Second, if the first worked out well, send back the media to the client and
> tell them to try the above process.
> It is possible that between different copies of the backup file it becomes
> corrupt. If the transfer of the backup file it is done over wan this is
> something sure. A second possibility it is this file is part of a multivolume
> backup. Sql Server has the option to backup a database to more than one file
> to speed up the backup process.
> Hope this helps you solve the problem.
> I recommend you reading this toohttp://www.dbforums.com/showthread.php?t=682406.
> Cheers.
> Mike C wrote:
> >A client keeps sending me .BAK files. A couple have worked, but I
> >have gotten this error on many:
> >Error 3241 Severity 16 State 1
> >The media family on device '%ls' is incorrectly formed. SQL Server
> >cannot process this media family.
> >If I view the contents of the .BAK (in Enterprise Manager), it says it
> >is INCOMPLETE.
> >This indicates that maybe the backup was bad. However, since they have
> >sent me so many bad ones, my question is why are these backups turning
> >out bad?
> >Some details:
> >-The client is using SQL Server 2000 SP4, so am I
> >-I am running Win2K Server, they are running Win 2003 server.
> >-I looked at KB 297104 which deals with this error, but that is for
> >pre-SP4.
> >-When I run a restore verifyonly, I get the same error
> >Since I am off-site and can't go to the client site, any ideas on what
> >they may be doing wrong or what we can do for me to get a legitimate
> >copy of this database on my server? I am going crazy with these files
> >that won't load and don't know what to tell my client.
The restore verifyonly or backuponly works at the client site. I
verified versions. They are using Sql Server 2000 SP3 (plus a
cumulative patch) while I am running SP4. I can try and go back to
SP3, but that seems unlikely culprit (more likely if their version
were higher than mine). Any other ideas?|||On Aug 22, 5:08 am, "flooriin" <u36842@.uwe> wrote:
> Hello,
> First of all tell your client to do a restore with verify only or header only
> to test, on their side, the correctness of the backup. If it's possible, tell
> the client to do a full restore of the backup.
> Second, if the first worked out well, send back the media to the client and
> tell them to try the above process.
> It is possible that between different copies of the backup file it becomes
> corrupt. If the transfer of the backup file it is done over wan this is
> something sure. A second possibility it is this file is part of a multivolume
> backup. Sql Server has the option to backup a database to more than one file
> to speed up the backup process.
> Hope this helps you solve the problem.
> I recommend you reading this toohttp://www.dbforums.com/showthread.php?t=682406.
> Cheers.
> Mike C wrote:
> >A client keeps sending me .BAK files. A couple have worked, but I
> >have gotten this error on many:
> >Error 3241 Severity 16 State 1
> >The media family on device '%ls' is incorrectly formed. SQL Server
> >cannot process this media family.
> >If I view the contents of the .BAK (in Enterprise Manager), it says it
> >is INCOMPLETE.
> >This indicates that maybe the backup was bad. However, since they have
> >sent me so many bad ones, my question is why are these backups turning
> >out bad?
> >Some details:
> >-The client is using SQL Server 2000 SP4, so am I
> >-I am running Win2K Server, they are running Win 2003 server.
> >-I looked at KB 297104 which deals with this error, but that is for
> >pre-SP4.
> >-When I run a restore verifyonly, I get the same error
> >Since I am off-site and can't go to the client site, any ideas on what
> >they may be doing wrong or what we can do for me to get a legitimate
> >copy of this database on my server? I am going crazy with these files
> >that won't load and don't know what to tell my client.
Do you think me being on w2K and them on W2003 server makes a
difference?|||I do not believe the problem arise because of different Windows versions. I
think it's more about database and the process to backup and deliver the
database. You have said that are cases when the backup was restored
successfully. What is different from your current situation? I am more
concern about different Sql Server version. I know about a bug in SP3 when a
differential database backup could miss to save some extents ... but you are
talking about full backup.
Read this too
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=24&messageid=167366&p=2
Maybe it will help you. Read the last post and try out the utility told
there.
Cheers.
Mike C wrote:
>> Hello,
>[quoted text clipped - 40 lines]
>> >copy of this database on my server? I am going crazy with these files
>> >that won't load and don't know what to tell my client.
>Do you think me being on w2K and them on W2003 server makes a
>difference?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1|||On Aug 24, 6:25 am, "flooriin via SQLMonster.com" <u36842@.uwe> wrote:
> I do not believe the problem arise because of different Windows versions. I
> think it's more about database and the process to backup and deliver the
> database. You have said that are cases when the backup was restored
> successfully. What is different from your current situation? I am more
> concern about different Sql Server version. I know about a bug in SP3 when a
> differential database backup could miss to save some extents ... but you are
> talking about full backup.
> Read this toohttp://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=24&mes...
> Maybe it will help you. Read the last post and try out the utility told
> there.
> Cheers.
> Mike C wrote:
> >> Hello,
> >[quoted text clipped - 40 lines]
> >> >copy of this database on my server? I am going crazy with these files
> >> >that won't load and don't know what to tell my client.
> >Do you think me being on w2K and them on W2003 server makes a
> >difference?
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1
I installed SQL Server 2000 MSDE on an XP box and I was able to
restore the backup. That means the file is fine. I am just going to
work with that since I don't need any of the enterprise features.
This is a short project and it isn't worth spending 100 hrs trying to
figure out what the problem is.

Restore of the SQL Server Program Files

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

Restore of the SQL Server Program Files

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

Restore of the SQL Server Program Files

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

Restore of DB

We had a DB A. The person reinstalled SQL, and A now isn't recognized. They never did a backup. The MDF and LDF Files still exist. Is there a way to recreate the DB, and have it look at the old LDF and MDF files, or is there a way to recreate the DB, and copy the information from the old Files over
I think I saw a similar question to this before, but I don't know if it will apply to me. As it referenced to DBs that exist, and mine is about one that used to exist, but somehow went away
DaniCheck out sp_attach_db.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dani" <anonymous@.discussions.microsoft.com> wrote in message
news:297C504B-F507-45CE-82EA-BE2ADC5EF5AF@.microsoft.com...
> We had a DB A. The person reinstalled SQL, and A now isn't recognized.
They never did a backup. The MDF and LDF Files still exist. Is there a way
to recreate the DB, and have it look at the old LDF and MDF files, or is
there a way to recreate the DB, and copy the information from the old Files
over?
> I think I saw a similar question to this before, but I don't know if it
will apply to me. As it referenced to DBs that exist, and mine is about one
that used to exist, but somehow went away.
> Dani|||Hi Tibor,
I think the .ldf and .mdf files would be on a different
drive ( for example on drive D )when SQL Server was up and
running, and as usual the guy would have formatted Drive
C coz of some issues and Installed the SQL Server again.
Does sp_attach_db suceed, as the database is not detached?
Regards
Thirumal
>--Original Message--
>Check out sp_attach_db.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Dani" <anonymous@.discussions.microsoft.com> wrote in
message
>news:297C504B-F507-45CE-82EA-BE2ADC5EF5AF@.microsoft.com...
>> We had a DB A. The person reinstalled SQL, and A now
isn't recognized.
>They never did a backup. The MDF and LDF Files still
exist. Is there a way
>to recreate the DB, and have it look at the old LDF and
MDF files, or is
>there a way to recreate the DB, and copy the information
from the old Files
>over?
>> I think I saw a similar question to this before, but I
don't know if it
>will apply to me. As it referenced to DBs that exist,
and mine is about one
>that used to exist, but somehow went away.
>> Dani
>
>.
>|||> I think the .ldf and .mdf files would be on a different
> drive ( for example on drive D )when SQL Server was up and
> running, and as usual the guy would have formatted Drive
> C coz of some issues and Installed the SQL Server again.
> Does sp_attach_db suceed, as the database is not detached?
It should work, as long as you've got the mdf file you can use the
sp_attach_db, just specify the new file location.
Steve|||I thought sp_detach_db took some data out of master and attached it to the
mdb. I didn't think attach would work without detach.
Christian Smith
"Steve Thompson" <SteveThompson@.nomail.please> wrote in message
news:uc%23X2Qx9DHA.1816@.TK2MSFTNGP12.phx.gbl...
> > I think the .ldf and .mdf files would be on a different
> > drive ( for example on drive D )when SQL Server was up and
> > running, and as usual the guy would have formatted Drive
> > C coz of some issues and Installed the SQL Server again.
> > Does sp_attach_db suceed, as the database is not detached?
> It should work, as long as you've got the mdf file you can use the
> sp_attach_db, just specify the new file location.
> Steve
>|||Hi All in my experience especially in this case I have definitely used
sp_attach_single_file_db and passing only the mdf file location, the ldf
file will be auto created and you should be fine. committed transacions that
were not physically written to disk(in controller cache) will be lost
obviously depends on yor cache settings
--
Olu Adedeji
"Christian Smith" <csmith@.digex.com> wrote in message
news:OTMIEwx9DHA.4020@.TK2MSFTNGP09.phx.gbl...
> I thought sp_detach_db took some data out of master and attached it to the
> mdb. I didn't think attach would work without detach.
> Christian Smith
> "Steve Thompson" <SteveThompson@.nomail.please> wrote in message
> news:uc%23X2Qx9DHA.1816@.TK2MSFTNGP12.phx.gbl...
> > > I think the .ldf and .mdf files would be on a different
> > > drive ( for example on drive D )when SQL Server was up and
> > > running, and as usual the guy would have formatted Drive
> > > C coz of some issues and Installed the SQL Server again.
> > > Does sp_attach_db suceed, as the database is not detached?
> >
> > It should work, as long as you've got the mdf file you can use the
> > sp_attach_db, just specify the new file location.
> >
> > Steve
> >
> >
>|||Hi,
This is interesting as I use to think that only cleanly
detached databases can be attached.
Thanks for all your views
Regards
Thirumal
>--Original Message--
>Hi All in my experience especially in this case I have
definitely used
>sp_attach_single_file_db and passing only the mdf file
location, the ldf
>file will be auto created and you should be fine.
committed transacions that
>were not physically written to disk(in controller cache)
will be lost
>obviously depends on yor cache settings
>--
>Olu Adedeji
>"Christian Smith" <csmith@.digex.com> wrote in message
>news:OTMIEwx9DHA.4020@.TK2MSFTNGP09.phx.gbl...
>> I thought sp_detach_db took some data out of master and
attached it to the
>> mdb. I didn't think attach would work without detach.
>> Christian Smith
>> "Steve Thompson" <SteveThompson@.nomail.please> wrote in
message
>> news:uc%23X2Qx9DHA.1816@.TK2MSFTNGP12.phx.gbl...
>> > > I think the .ldf and .mdf files would be on a
different
>> > > drive ( for example on drive D )when SQL Server was
up and
>> > > running, and as usual the guy would have formatted
Drive
>> > > C coz of some issues and Installed the SQL Server
again.
>> > > Does sp_attach_db suceed, as the database is not
detached?
>> >
>> > It should work, as long as you've got the mdf file
you can use the
>> > sp_attach_db, just specify the new file location.
>> >
>> > Steve
>> >
>> >
>>
>
>.
>|||Sometimes it work to attach a database that wasn't detach, sometimes it
doesn't. We have tons of messages here from users who assumes that attach
would work without a prior detach. Books Online states that attach work *if*
you detached first, so consider the rest a lucky bonus. And for re-creating
the log file, it will only work if the db had only one db and one log file
and it didn't have any recovery work to do (in most cases, it was indeed
properly detached first).
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Thirumal" <anonymous@.discussions.microsoft.com> wrote in message
news:1339901c3f75f$0b630b60$a001280a@.phx.gbl...
> Hi,
> This is interesting as I use to think that only cleanly
> detached databases can be attached.
> Thanks for all your views
> Regards
> Thirumal
> >--Original Message--
> >Hi All in my experience especially in this case I have
> definitely used
> >sp_attach_single_file_db and passing only the mdf file
> location, the ldf
> >file will be auto created and you should be fine.
> committed transacions that
> >were not physically written to disk(in controller cache)
> will be lost
> >obviously depends on yor cache settings
> >
> >--
> >Olu Adedeji
> >"Christian Smith" <csmith@.digex.com> wrote in message
> >news:OTMIEwx9DHA.4020@.TK2MSFTNGP09.phx.gbl...
> >> I thought sp_detach_db took some data out of master and
> attached it to the
> >> mdb. I didn't think attach would work without detach.
> >>
> >> Christian Smith
> >>
> >> "Steve Thompson" <SteveThompson@.nomail.please> wrote in
> message
> >> news:uc%23X2Qx9DHA.1816@.TK2MSFTNGP12.phx.gbl...
> >> > > I think the .ldf and .mdf files would be on a
> different
> >> > > drive ( for example on drive D )when SQL Server was
> up and
> >> > > running, and as usual the guy would have formatted
> Drive
> >> > > C coz of some issues and Installed the SQL Server
> again.
> >> > > Does sp_attach_db suceed, as the database is not
> detached?
> >> >
> >> > It should work, as long as you've got the mdf file
> you can use the
> >> > sp_attach_db, just specify the new file location.
> >> >
> >> > Steve
> >> >
> >> >
> >>
> >>
> >
> >
> >.
> >

Restore of DB

We had a DB A. The person reinstalled SQL, and A now isn't recognized. The
y never did a backup. The MDF and LDF Files still exist. Is there a way to
recreate the DB, and have it look at the old LDF and MDF files, or is there
a way to recreate the DB,
and copy the information from the old Files over?
I think I saw a similar question to this before, but I don't know if it will
apply to me. As it referenced to DBs that exist, and mine is about one tha
t used to exist, but somehow went away.
DaniCheck out sp_attach_db.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Dani" <anonymous@.discussions.microsoft.com> wrote in message
news:297C504B-F507-45CE-82EA-BE2ADC5EF5AF@.microsoft.com...
> We had a DB A. The person reinstalled SQL, and A now isn't recognized.
They never did a backup. The MDF and LDF Files still exist. Is there a way
to recreate the DB, and have it look at the old LDF and MDF files, or is
there a way to recreate the DB, and copy the information from the old Files
over?
> I think I saw a similar question to this before, but I don't know if it
will apply to me. As it referenced to DBs that exist, and mine is about one
that used to exist, but somehow went away.
> Dani|||Hi Tibor,
I think the .ldf and .mdf files would be on a different
drive ( for example on drive D )when SQL Server was up and
running, and as usual the guy would have formatted Drive
C coz of some issues and Installed the SQL Server again.
Does sp_attach_db suceed, as the database is not detached?
Regards
Thirumal
>--Original Message--
>Check out sp_attach_db.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Dani" <anonymous@.discussions.microsoft.com> wrote in
message
>news:297C504B-F507-45CE-82EA-BE2ADC5EF5AF@.microsoft.com...
isn't recognized.
>They never did a backup. The MDF and LDF Files still
exist. Is there a way
>to recreate the DB, and have it look at the old LDF and
MDF files, or is
>there a way to recreate the DB, and copy the information
from the old Files
>over?
don't know if it
>will apply to me. As it referenced to DBs that exist,
and mine is about one
>that used to exist, but somehow went away.
>
>.
>|||> I think the .ldf and .mdf files would be on a different
> drive ( for example on drive D )when SQL Server was up and
> running, and as usual the guy would have formatted Drive
> C coz of some issues and Installed the SQL Server again.
> Does sp_attach_db suceed, as the database is not detached?
It should work, as long as you've got the mdf file you can use the
sp_attach_db, just specify the new file location.
Steve|||I thought sp_detach_db took some data out of master and attached it to the
mdb. I didn't think attach would work without detach.
Christian Smith
"Steve Thompson" <SteveThompson@.nomail.please> wrote in message
news:uc%23X2Qx9DHA.1816@.TK2MSFTNGP12.phx.gbl...
> It should work, as long as you've got the mdf file you can use the
> sp_attach_db, just specify the new file location.
> Steve
>|||Hi All in my experience especially in this case I have definitely used
sp_attach_single_file_db and passing only the mdf file location, the ldf
file will be auto created and you should be fine. committed transacions that
were not physically written to disk(in controller cache) will be lost
obviously depends on yor cache settings
Olu Adedeji
"Christian Smith" <csmith@.digex.com> wrote in message
news:OTMIEwx9DHA.4020@.TK2MSFTNGP09.phx.gbl...
> I thought sp_detach_db took some data out of master and attached it to the
> mdb. I didn't think attach would work without detach.
> Christian Smith
> "Steve Thompson" <SteveThompson@.nomail.please> wrote in message
> news:uc%23X2Qx9DHA.1816@.TK2MSFTNGP12.phx.gbl...
>|||Hi,
This is interesting as I use to think that only cleanly
detached databases can be attached.
Thanks for all your views
Regards
Thirumal
>--Original Message--
>Hi All in my experience especially in this case I have
definitely used
>sp_attach_single_file_db and passing only the mdf file
location, the ldf
>file will be auto created and you should be fine.
committed transacions that
>were not physically written to disk(in controller cache)
will be lost
>obviously depends on yor cache settings
>--
>Olu Adedeji
>"Christian Smith" <csmith@.digex.com> wrote in message
>news:OTMIEwx9DHA.4020@.TK2MSFTNGP09.phx.gbl...
attached it to the
message
different
up and
Drive
again.
detached?
you can use the
>
>.
>|||Sometimes it work to attach a database that wasn't detach, sometimes it
doesn't. We have tons of messages here from users who assumes that attach
would work without a prior detach. Books Online states that attach work *if*
you detached first, so consider the rest a lucky bonus. And for re-creating
the log file, it will only work if the db had only one db and one log file
and it didn't have any recovery work to do (in most cases, it was indeed
properly detached first).
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Thirumal" <anonymous@.discussions.microsoft.com> wrote in message
news:1339901c3f75f$0b630b60$a001280a@.phx
.gbl...
> Hi,
> This is interesting as I use to think that only cleanly
> detached databases can be attached.
> Thanks for all your views
> Regards
> Thirumal
> definitely used
> location, the ldf
> committed transacions that
> will be lost
> attached it to the
> message
> different
> up and
> Drive
> again.
> detached?
> you can use the