Restore DB from mdf and ldf

Hello,
i have an mdf and ldf file, is there any way to restore the DB from these two files? ( sp_detach_db is not used)
Pl discussUse sp_attach_db system stored procedure.

OR

Open Enterprise Manager -> Databases -> Right Mouse Key -> All Tasks -> Attach Database.

If you have Backup file than use Restore Backup from query analyzer.

For more information explore Books OnLine from query analyzer...|||i mentioned in my first post. the sp_detach_db is not used. so i cannot attach the db by a call to sp_attach_db

--|||i mentioned in my first post. the sp_detach_db is not used. so i cannot attach the db by a call to sp_attach_db

Can you describe your problem in detail instead of ambiguous information..?|||i mentioned in my first post. the sp_detach_db is not used. so i cannot attach the db by a call to sp_attach_db
--
why is that so?? Help us...|||Restore specific files or filegroups:

RESTORE DATABASE {database_name | @.database_name_var}
<file_or_filegroup> [,...n]
[FROM <backup_device> [,...n]]
[WITH
[DBO_ONLY]
[[,] FILE = file_number]
[[,] MEDIANAME = {media_name | @.media_name_variable}]
[[,] NORECOVERY]
[[,] {NOUNLOAD | UNLOAD}]
[[,] REPLACE]
[[,] RESTART]
[[,] STATS [= percentage]]

For more info try reading this...

http://doc.ddart.net/mssql/sql70/ra-rz_9.htm|||Restore specific files or filegroups:

RESTORE DATABASE {database_name | @.database_name_var}
<file_or_filegroup> [,...n]
[FROM <backup_device> [,...n]]
[WITH
[DBO_ONLY]
[[,] FILE = file_number]
[[,] MEDIANAME = {media_name | @.media_name_variable}]
[[,] NORECOVERY]
[[,] {NOUNLOAD | UNLOAD}]
[[,] REPLACE]
[[,] RESTART]
[[,] STATS [= percentage]]

For more info try reading this...

http://doc.ddart.net/mssql/sql70/ra-rz_9.htm

yeah that works with backup files. for mdf's and ldfs, you use sp_attach_db, but this guy will not giving us the error he is getting so he is getting ignored.|||Hello ppl,

this is not a real life scenario, i was thinking whether i can copy the mdf and ldf file from a DB server and copy it to another machine, and try to restore the Db, from these two files ( i am not taking a backup or detaching the original DB). my question is, whether i can copy the mdf and ldf files to another system, and restore the DB there.

most of the info in wed says that i have to detach the db and attach it in the destination. i just want to know whether any other mechanism is there to restore the db from copied mdf and ldf files

im using MS SQL 2k|||was thinking whether i can copy the mdf and ldf file from a DB server and copy it to another machine, and try to restore the Db, from these two files ( i am not taking a backup or detaching the original DB). my question is, whether i can copy the mdf and ldf files to another system, and restore the DB there.
you can attach .mdf / .ldf files using EXEC SP_ATTACH_DB

most of the info in wed says that i have to detach the db and attach it in the destination. i just want to know whether any other mechanism is there to restore the db from copied mdf and ldf files.
You need to use SP_DETACH_DB, if database is on-line (i.e. in use). If any user is using database than SQL Server will gives you an error for same.

It's quite not necessary that first you have to use sp_detach_db then sp_attach_db stored procedures (As you have written in your previous post), unless your database is online.

Instead of detach then attach database use backup - restore. Backup - Restore will not hurt database availability.

Remember in SQL every command is made for some special purpose. Refer Book OnLine from query analyzer.

And the most important thing : Be Explanatory, specially when you are asking someones help...|||If you copy the datafiles while the server is running, you will likely get junk, unless the database is very small. Even then, you are not guaranteed anything. If you shut down SQL Server entirely, then you can get a consistent set of files. This is usually called a "cold backup", and is extremely rare in the SQL Server world.|||If you copy the datafiles while the server is running, you will likely get junk, unless the database is very small. Even then, you are not guaranteed anything.
We can not copy database while database is 'ONLINE' on the server, if you try SQL Server will gives you 'database being in use' error for same.

If you shut down SQL Server entirely, then you can get a consistent set of files.
For copying single database we don't need to shutdown entire SQL Server, just put the database offline & copy your database.

-- Make sure no any user is using database, else this will not work.
-- Run this command from query analyzer.

-- this will put the database offline.

use Master
GO
sp_dboption 'pubs', 'offline', 'True'

-- Bring database online.

sp_dboption 'pubs', 'offline', 'False'|||While I have not actually tried it, I think xcopy will give you a file regardless of the file being in use. This tends to lead some administrators to think they have a backup solution, when they really do not. Some backup packages may also be able to back up open files, which also leads to this mistaken impression.

restore database without mdf or ldf file

Hi,

Is it possible to restore a database with only a .bak file??
When I try to restore the system comes up with 2 paths where probably the original .mdf and .ldf files existed, but on my system they don't exist. How can I avoid this problem? (it's not possible to get the ldf and mdf files anymore...)

Grtz
Carloscreate a new database like so...

CREATE DATABASE MyDATABASE

use sp_helpdb to get the filenames for the mdf and the ldf you just created

sp_helpdb MYDATABASE

use RESTORE FILELISTONLY to get the logical filenames of the mdf and ldf in the backup file like

RESTORE FILELISTONLY
FROM DISK = 'C:\Mybackup.bak'

Use RESTORE with MOVE like so

RESTORE DATABASE MyDatabase
FROM DISK = 'C:\Mybackup.bak'
WITH REPLACE,RECOVERY,
MOVE 'Logicalfile_Data' TO 'D:\Microsoft SQL Server\MSSQL\data\physicalfile.mdf' , MOVE 'Logicalfile_Log' TO 'D:\Microsoft SQL Server\MSSQL\data\physicalfile_log.LDF'|||Its quite possible to restore the db even if the underlying db is not preset. U need to provide the correct path where u want the mdf and ldf files to be created.|||Thnx for the quick reply!

Restore database from only log file (.ldf) no backup

I have a log file for a database, the .mdf file was deleted, there are no
backup files for the database. How can I use the log file to restore the
database?
Basically you can't. The log file generally does not include all the
information to recreate an entire database. That is why backups are so
important.
Did you try UNDELETE at the OS level or from Norton?
Andrew J. Kelly SQL MVP
"g2g" <g2g@.discussions.microsoft.com> wrote in message
news:C071BFAB-22FC-482C-A4BD-FF4FEC8452E7@.microsoft.com...
>I have a log file for a database, the .mdf file was deleted, there are no
> backup files for the database. How can I use the log file to restore the
> database?
|||Thanks Andrew, I have a script to recreate the database and the table, it
only had a single table.. I recreated the database and the table but now
can't figure out how to get the transactions in from the saved log file. Yes
tried undelete - its not there.
"Andrew J. Kelly" wrote:

> Basically you can't. The log file generally does not include all the
> information to recreate an entire database. That is why backups are so
> important.
> Did you try UNDELETE at the OS level or from Norton?
> --
> Andrew J. Kelly SQL MVP
>
> "g2g" <g2g@.discussions.microsoft.com> wrote in message
> news:C071BFAB-22FC-482C-A4BD-FF4FEC8452E7@.microsoft.com...
>
>
|||> Thanks Andrew, I have a script to recreate the database and the table, it
> only had a single table.. I recreated the database and the table but now
> can't figure out how to get the transactions in from the saved log file.
Yes
> tried undelete - its not there.
You could try to use Log Explorer - check www.lumigent.com.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||If you still have all the information in the log you may be able to get it
back with some 3rd party tools. One is www.lumigent.com as Dejan mentioned
and another I know of is available at http://www.logpi.com/.
Andrew J. Kelly SQL MVP
"g2g" <g2g@.discussions.microsoft.com> wrote in message
news:1E042AAC-2F02-4BEF-A103-4B7830ED771D@.microsoft.com...[vbcol=seagreen]
> Thanks Andrew, I have a script to recreate the database and the table, it
> only had a single table.. I recreated the database and the table but now
> can't figure out how to get the transactions in from the saved log file.
> Yes
> tried undelete - its not there.
> "Andrew J. Kelly" wrote:

Restore database from only log file (.ldf) no backup

I have a log file for a database, the .mdf file was deleted, there are no
backup files for the database. How can I use the log file to restore the
database?Basically you can't. The log file generally does not include all the
information to recreate an entire database. That is why backups are so
important.
Did you try UNDELETE at the OS level or from Norton?
--
Andrew J. Kelly SQL MVP
"g2g" <g2g@.discussions.microsoft.com> wrote in message
news:C071BFAB-22FC-482C-A4BD-FF4FEC8452E7@.microsoft.com...
>I have a log file for a database, the .mdf file was deleted, there are no
> backup files for the database. How can I use the log file to restore the
> database?|||Thanks Andrew, I have a script to recreate the database and the table, it
only had a single table.. I recreated the database and the table but now
can't figure out how to get the transactions in from the saved log file. Yes
tried undelete - its not there.
"Andrew J. Kelly" wrote:
> Basically you can't. The log file generally does not include all the
> information to recreate an entire database. That is why backups are so
> important.
> Did you try UNDELETE at the OS level or from Norton?
> --
> Andrew J. Kelly SQL MVP
>
> "g2g" <g2g@.discussions.microsoft.com> wrote in message
> news:C071BFAB-22FC-482C-A4BD-FF4FEC8452E7@.microsoft.com...
> >I have a log file for a database, the .mdf file was deleted, there are no
> > backup files for the database. How can I use the log file to restore the
> > database?
>
>|||> Thanks Andrew, I have a script to recreate the database and the table, it
> only had a single table.. I recreated the database and the table but now
> can't figure out how to get the transactions in from the saved log file.
Yes
> tried undelete - its not there.
You could try to use Log Explorer - check www.lumigent.com.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||If you still have all the information in the log you may be able to get it
back with some 3rd party tools. One is www.lumigent.com as Dejan mentioned
and another I know of is available at http://www.logpi.com/.
--
Andrew J. Kelly SQL MVP
"g2g" <g2g@.discussions.microsoft.com> wrote in message
news:1E042AAC-2F02-4BEF-A103-4B7830ED771D@.microsoft.com...
> Thanks Andrew, I have a script to recreate the database and the table, it
> only had a single table.. I recreated the database and the table but now
> can't figure out how to get the transactions in from the saved log file.
> Yes
> tried undelete - its not there.
> "Andrew J. Kelly" wrote:
>> Basically you can't. The log file generally does not include all the
>> information to recreate an entire database. That is why backups are so
>> important.
>> Did you try UNDELETE at the OS level or from Norton?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "g2g" <g2g@.discussions.microsoft.com> wrote in message
>> news:C071BFAB-22FC-482C-A4BD-FF4FEC8452E7@.microsoft.com...
>> >I have a log file for a database, the .mdf file was deleted, there are
>> >no
>> > backup files for the database. How can I use the log file to restore
>> > the
>> > database?
>>

Restore database from only log file (.ldf) no backup

I have a log file for a database, the .mdf file was deleted, there are no
backup files for the database. How can I use the log file to restore the
database?Basically you can't. The log file generally does not include all the
information to recreate an entire database. That is why backups are so
important.
Did you try UNDELETE at the OS level or from Norton?
--
Andrew J. Kelly SQL MVP
"g2g" <g2g@.discussions.microsoft.com> wrote in message
news:C071BFAB-22FC-482C-A4BD-FF4FEC8452E7@.microsoft.com...
>I have a log file for a database, the .mdf file was deleted, there are no
> backup files for the database. How can I use the log file to restore the
> database?|||Thanks Andrew, I have a script to recreate the database and the table, it
only had a single table.. I recreated the database and the table but now
can't figure out how to get the transactions in from the saved log file. Ye
s
tried undelete - its not there.
"Andrew J. Kelly" wrote:

> Basically you can't. The log file generally does not include all the
> information to recreate an entire database. That is why backups are so
> important.
> Did you try UNDELETE at the OS level or from Norton?
> --
> Andrew J. Kelly SQL MVP
>
> "g2g" <g2g@.discussions.microsoft.com> wrote in message
> news:C071BFAB-22FC-482C-A4BD-FF4FEC8452E7@.microsoft.com...
>
>|||> Thanks Andrew, I have a script to recreate the database and the table, it
> only had a single table.. I recreated the database and the table but now
> can't figure out how to get the transactions in from the saved log file.
Yes
> tried undelete - its not there.
You could try to use Log Explorer - check www.lumigent.com.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||If you still have all the information in the log you may be able to get it
back with some 3rd party tools. One is www.lumigent.com as Dejan mentioned
and another I know of is available at http://www.logpi.com/.
Andrew J. Kelly SQL MVP
"g2g" <g2g@.discussions.microsoft.com> wrote in message
news:1E042AAC-2F02-4BEF-A103-4B7830ED771D@.microsoft.com...[vbcol=seagreen]
> Thanks Andrew, I have a script to recreate the database and the table, it
> only had a single table.. I recreated the database and the table but now
> can't figure out how to get the transactions in from the saved log file.
> Yes
> tried undelete - its not there.
> "Andrew J. Kelly" wrote:
>

Restore Database FROM MDF/LDF, then apply Transaction Log From POint of Failure? Is it pOs

If I have a database backup from sunday, and a failure occurs monday... Can the backup .mdf and .ldf files be attached, and the backup log after the point of failure be applied to them?

The problem I am having is it looks like you can only restore from a .bak file, and then apply the log at the point of failure. IT doesn't look like you can restore the .ldf/.mdf files, and then apply the backup log from the point of failure.

Can someone please help? I'm in desparate need of fixing this !

Thanks,
dp

You can restore from backup and apply your log files to recover up to failure unless it is a transactional database you can restore almost everything but you are also supposed to keep a DR(disaster recovery) copy of your database. Try the link below. Hope this helps.

http://www.sql-server-performance.com/log_explorer_spotlight.asp

sql

Restore Database from MDF file without LDF

Iam having SQL Database MDF file how can i restore the database without LDF.
Please anyone reply me and this is very very urgent

quote:

---

Originally posted by Shansinn
Iam having SQL Database MDF file how can i restore the database without LDF.Please anyon
e reply me and this is very very urgent

---

|||You can try sp_attach_single_file_db. But that is only guaranteed if you onl
y had one mdf and one
ldf and actually detached the database first.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Shansinn" <Shansinn.1pa7pq@.mail.codecomments.com> wrote in message
news:Shansinn.1pa7pq@.mail.codecomments.com...
> Iam having SQL Database MDF file how can i restore the database without
> LDF.Please anyone reply me and this is very very urgent
>
> --
> Shansinn
> ---
> Posted via http://www.codecomments.com
> ---
>|||Hi
to restore a database LDF is not required
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Shansinn" wrote:

> Iam having SQL Database MDF file how can i restore the database without
> LDF.Please anyone reply me and this is very very urgent
>
> --
> Shansinn
> ---
> Posted via http://www.codecomments.com
> ---
>|||Add on to Tibor, SP_attach_single_file_db may not work if you have not
detached the database (SP_DETACH_DB).
If this process fails restore from a good database backup.
Thanks
Hari
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uIKoYYGXFHA.2664@.TK2MSFTNGP15.phx.gbl...
> You can try sp_attach_single_file_db. But that is only guaranteed if you
> only had one mdf and one ldf and actually detached the database first.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Shansinn" <Shansinn.1pa7pq@.mail.codecomments.com> wrote in message
> news:Shansinn.1pa7pq@.mail.codecomments.com...
>|||Use stored procedure sp_attach_single_file_db
AMB
"Shansinn" wrote:

> Shansinn wrote:
>
> --
> Shansinn
> ---
> Posted via http://www.codecomments.com
> ---
>

Restore database from mdf and ldf files

Is there a way to restore a database only have the .mdf and .ldf files and
not having a .bak
Anthony
Look up sp_attach_db in Books On-Line.
"Anthony" <anthony@.computerpundits.com> wrote in message
news:#8t4YAWKEHA.1764@.TK2MSFTNGP12.phx.gbl...
> Is there a way to restore a database only have the .mdf and .ldf files and
> not having a .bak
> Anthony
>
|||Just create the Database with the name desired. Stop the database service. Go to the data folder of the Sql Server installed in your program files. Delete the existing .mdf and .ldf files which would be created because of creation of the database. Copy yo
ur (the required) .mdf and .ldf files which you want to restore. Restart the database service, open SQL Server Enterprise Manager, your database will be populated with the tables and stored procs.
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.

restore database from ldf file only

Hello guys,
is it possible to restore an SQL server database from the .ldf file only?
The last operation is a script execution that have drop and create all the tables.
have you some idea?
Thanks to allldf typically contains only logs, which record changes to the database. Any restore must begin by reloading a backup, followed by whatever logs are required/desired.|||thanks for the fast reply.

I have an old backup that is from december, and the log file that is till now.

Do I have some chance?|||Yes, if you have continuous logs.

You can certainly restore the backup from December. If your logs are continuous since then, then you can do a point in time restore. Your best bet is to do it using Enterprise Manager, assuming that it has kept a record of backups and log dumps.|||First check if your database has been in FULL recovery mode. If it is set to SIMPLE than you don't stand a chance.

The simplist way to check this is in EM. Look at the properties of the database, tab "options".|||First check if your database has been in FULL recovery mode. If it is set to SIMPLE than you don't stand a chance.

The simplist way to check this is in EM. Look at the properties of the database, tab "options".

I could wrong but I do not think you can even back up the log in SIMPLE.|||Nope you can't. And even if you could there wouldn't be anything of use to you in there.

But it sounds like they didn't backup logs, there's only talk about a full backup. So if there are no log backups and the database is in FULL recovery mode (that ldf file could be huge!) he could try a restore with a roll forward (I think that's the theory, never done it before).

Who is it that has that nice tag line: "did you hug your backup today?" :D

Lex

Restore Database

Hi,
I've two files : ora8i_Data.MDF, ora8i_Log.LDF, from a database 'ora8i' from
another PC (other SQL server).
How can i restore this database to (my PC) my SQL server ?
Thanks in advance,
Hatziyannis ApostolisCheck out RESTORE DATABASE in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"microsoft" <apostolis@.orbis.gr> wrote in message
news:%23SqcNxa%23FHA.2704@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've two files : ora8i_Data.MDF, ora8i_Log.LDF, from a database 'ora8i'
> from another PC (other SQL server).
> How can i restore this database to (my PC) my SQL server ?
> Thanks in advance,
> Hatziyannis Apostolis
>
>|||microsoft wrote:
> Hi,
> I've two files : ora8i_Data.MDF, ora8i_Log.LDF, from a database 'ora8i' fr
om
> another PC (other SQL server).
> How can i restore this database to (my PC) my SQL server ?
> Thanks in advance,
> Hatziyannis Apostolis
Use sp_attach_db. For example:
EXEC sp_attach_db 'DATABASE_NAME',
'c:\mssql_data\ora8i_Data.MDF',
'c:\mssql_data\ora8i_Log.LDF' ;
Make sure you keep a safe copy of the original.
David Portas
SQL Server MVP
--|||See if the procedure sp_attach_db can help you:
Look in the BOL:
sp_attach_db
Attaches a database to a server.
(...)
HTH, jens Suessmeyer.

Restore Data

Dear all,
I would like to know how can I restore the MSSQL database? I copied the
abc.mdf and abc.ldf files from another computer. I want to know how can I
restore the data in my computer just using these 2 files. Thank you for all
of your help.
Alex
hi Alex,
"alex" <a@.a.com> ha scritto nel messaggio
news:OMP5qnQWEHA.1488@.TK2MSFTNGP09.phx.gbl...
> Dear all,
> I would like to know how can I restore the MSSQL database? I copied
the
> abc.mdf and abc.ldf files from another computer. I want to know how can I
> restore the data in my computer just using these 2 files. Thank you for
all
> of your help.
if you already have the data .Mdf file and log .Ldf file building the
database, you don't have to restore it but just attach it to your MSDE
instance... a database restore can only be performed using a database
backup, which is not a physical copy of the physical files... further info
at
http://msdn.microsoft.com/library/de...ackpc_7cft.asp
in order to perform this task, you have to use the system stored procedure
sp_attach_db, similar to
EXEC sp_attach_db @.dbname = 'your_dbname'
, @.filename1 = '..\full data path of\abc.mdf'
, @.filename2 = '..\full data path of\abc.ldf'
with the query tool interface of your choice... please have a look at
http://msdn.microsoft.com/library/de...ae-az_52oy.asp
for sp_attach_db synopsis and further info about it...
for your convenience, you can have a look at a free prj of mine, available
at the link following my sign., which provide a user interface similar to
Enterprise Manager, that includes this feature too...
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thx Andrea. I have tried the store procedure sp_attach_db; however, when I
executed the procedure, it showed me the following error messages:
Could not attach the database because the character Set, Sort Order, or
Unicode Collation for the database differs from this server.
warning: sort order 52 in database differs from server sort order of
197
warning: Unicode Language locale 1033 in database differs from Server
Unicode language locale of 1028
I would like to ask how can i solve this problem. Thank you for all of your
help.
Alex
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> gl
news:2jt4t4F14gi9gU1@.uni-berlin.de...[vbcol=seagreen]
> hi Alex,
> "alex" <a@.a.com> ha scritto nel messaggio
> news:OMP5qnQWEHA.1488@.TK2MSFTNGP09.phx.gbl...
> the
I
> all
> if you already have the data .Mdf file and log .Ldf file building the
> database, you don't have to restore it but just attach it to your MSDE
> instance... a database restore can only be performed using a database
> backup, which is not a physical copy of the physical files... further info
> at
>
http://msdn.microsoft.com/library/de...us/howtosql/ht
_7_backpc_7cft.asp
> in order to perform this task, you have to use the system stored procedure
> sp_attach_db, similar to
> EXEC sp_attach_db @.dbname = 'your_dbname'
> , @.filename1 = '..\full data path of\abc.mdf'
> , @.filename2 = '..\full data path of\abc.ldf'
> with the query tool interface of your choice... please have a look at
>
http://msdn.microsoft.com/library/de...us/tsqlref/ts_
sp_ae-az_52oy.asp
> for sp_attach_db synopsis and further info about it...
> for your convenience, you can have a look at a free prj of mine, available
> at the link following my sign., which provide a user interface similar to
> Enterprise Manager, that includes this feature too...
> hth
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Alex,
"alex" <a@.a.com> ha scritto nel messaggio
news:OAmiowYWEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Thx Andrea. I have tried the store procedure sp_attach_db; however, when
I
> executed the procedure, it showed me the following error messages:
> Could not attach the database because the character Set, Sort Order,
or
> Unicode Collation for the database differs from this server.
> warning: sort order 52 in database differs from server sort order of
> 197
> warning: Unicode Language locale 1033 in database differs from Server
> Unicode language locale of 1028
> I would like to ask how can i solve this problem. Thank you for all of
your
> help.
are you perhaps running a SQL Server 7.0 instance?
on tha version, the Character Set and Sort Order rules must be the same on
both servers in order to "migrate" a database via attach/restore method..
if this is the case, you shoul'd perform the migration via DTS, if the 2
servers are bound in a lan, or script the database out, both database schema
and data...
database schema can be scripted out via Enterprise Manager, while data can
be pushed to file (and later loaded to SQL Server database) with BCP (Bulk
Copy Program),
http://msdn.microsoft.com/library/de...p_bcp_61et.asp ,
SQL Server MVP Narayana Vyas Kondreddi world famous INSERT INTO stored
procedure ( http://vyaskn.tripod.com/code.htm#inserts ) or have a look at a
free prj of mine at the link following my sign., which provide a user
interface similar to Enterprise Manager that includes this feature too...
DDL schema generation sql scripts and INSERT INTO scripts can be later run
via oSql.exe and/or Query Analyzer..
the other way, is to rebuild your destination SQL Server instance with the
same Character Set and Sort Order rules as the originating server...
SQL Server 2000 and MSDE 2000 allow collations bot at database level and
column level...
http://msdn.microsoft.com/library/de...ation_72pg.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Restore a set of databases from a SQL2000 server to a SQL2005 server

I can restore databases one by one, setting .mdf and .ldf destination paths.

How can I restore all my Databases at the same time?

Thanks

G. Zanghi

You will either have restore or attach them one by one.

HTH, Jens K. Suessmeyer.

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

In SQL2000 i have a script that permits me to restore, at the same time, a group of databases backed up with a maintenance plan.

In SQL2005 this script doesn't run. Do you know a similar one?

Restoring one by one it's an hard work....

|||COuld you send me over the script, perhaps I can try a transistion. My EMail is accessible through my profile.

HTH, Jens K. Suessmeyer.

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

Your Email doesn't run!

check this link: fengyu.china.com/source/sp_CSS_RestoreDir.sql

It's very similar to mine.

|||My Email works, you actually will have to remove the SPAMfuscator to make it work. Which error do you get ? If you are not sure if the statements are right composed you can use the PRINT Command to print our the composed commands for debugging.

HTH, Jens K. Suessmeyer.

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

This is the output of the query:

Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
RESTORING DATABASE dataprova1
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "E:\dataprova1Data\dataprova1.mdf" failed with the operating system error 1008(An attempt was made to reference a token that does not exist.).
Msg 3156, Level 16, State 3, Line 1
File 'dataprova1ForRecovery_Data' cannot be restored to 'E:\dataprova1Data\dataprova1.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5184, Level 16, State 2, Line 1
Cannot use file 'D:\dataprova1Log\dataprova1_log.ldf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used.
Msg 3156, Level 16, State 3, Line 1
File 'dataprova1ForRecovery_Log' cannot be restored to 'D:\dataprova1Log\dataprova1_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.

Thanks!!!

Kind Regards Gianpaolo

Restore a set of databases from a SQL2000 server to a SQL2005 server

I can restore databases one by one, setting .mdf and .ldf destination paths.

How can I restore all my Databases at the same time?

Thanks

G. Zanghi

You will either have restore or attach them one by one.

HTH, Jens K. Suessmeyer.

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

In SQL2000 i have a script that permits me to restore, at the same time, a group of databases backed up with a maintenance plan.

In SQL2005 this script doesn't run. Do you know a similar one?

Restoring one by one it's an hard work....

|||COuld you send me over the script, perhaps I can try a transistion. My EMail is accessible through my profile.

HTH, Jens K. Suessmeyer.

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

Your Email doesn't run!

check this link: fengyu.china.com/source/sp_CSS_RestoreDir.sql

It's very similar to mine.

|||My Email works, you actually will have to remove the SPAMfuscator to make it work. Which error do you get ? If you are not sure if the statements are right composed you can use the PRINT Command to print our the composed commands for debugging.

HTH, Jens K. Suessmeyer.

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

This is the output of the query:

Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
RESTORING DATABASE dataprova1
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "E:\dataprova1Data\dataprova1.mdf" failed with the operating system error 1008(An attempt was made to reference a token that does not exist.).
Msg 3156, Level 16, State 3, Line 1
File 'dataprova1ForRecovery_Data' cannot be restored to 'E:\dataprova1Data\dataprova1.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5184, Level 16, State 2, Line 1
Cannot use file 'D:\dataprova1Log\dataprova1_log.ldf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used.
Msg 3156, Level 16, State 3, Line 1
File 'dataprova1ForRecovery_Log' cannot be restored to 'D:\dataprova1Log\dataprova1_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.

Thanks!!!

Kind Regards Gianpaolo

Restore

Hello:
We had a user/administrator here make a mistake.
What I have is the following:
1. A backup from August 18th
2. An mdf file and an ldf file from Sept 23rd
Facts:
The original database instance from the sql server was deleted before I had
a chance to look at it..
I have tried to restore everything on another server but it is impossible to
get the records the user says are missing since the 18th of August.
The 18th of August backup can be restored without problem. I then tried to
restore the transaction log on to that but it did not help.
I have also brought up the old mdf and ldf file in a new instance, then
backed up the ldf (log) file and tried to resore it with point in time
recovery to both that instance and the instance from the old backup.
Neither effort yielded any new records since the 18th of August.
If anyone can give me information on how to get these records back (the ones
inserted since the 18th) I would appreciate it.
Right now, it does not look very good.
Thanks,
Steve K.Steve:
Not sure what version of SQL Server you are using but it
has been my experience that unless you have all the files
from 8/18/03 forward you will not be able to perform a
restore to 9/29/03. You must have the database backup and
all of the transaction log file backups that are in
between.
I am afraid your success will end with the 8/18/03 backup.
Thomas
>--Original Message--
>Hello:
>We had a user/administrator here make a mistake.
>What I have is the following:
>1. A backup from August 18th
>2. An mdf file and an ldf file from Sept 23rd
>Facts:
>The original database instance from the sql server was
deleted before I had
>a chance to look at it..
>I have tried to restore everything on another server but
it is impossible to
>get the records the user says are missing since the 18th
of August.
>The 18th of August backup can be restored without
problem. I then tried to
>restore the transaction log on to that but it did not
help.
>I have also brought up the old mdf and ldf file in a new
instance, then
>backed up the ldf (log) file and tried to resore it with
point in time
>recovery to both that instance and the instance from the
old backup.
>Neither effort yielded any new records since the 18th of
August.
>If anyone can give me information on how to get these
records back (the ones
>inserted since the 18th) I would appreciate it.
>Right now, it does not look very good.
>Thanks,
>Steve K.
>
>.
>|||Thanks Thomas:
I appreciate your thoughts and insight.
Steve K.
"Thomas Rhodes" <thomas.rhodes@.nghs.com> wrote in message
news:0a9501c386b6$88388080$a101280a@.phx.gbl...
> Steve:
> Not sure what version of SQL Server you are using but it
> has been my experience that unless you have all the files
> from 8/18/03 forward you will not be able to perform a
> restore to 9/29/03. You must have the database backup and
> all of the transaction log file backups that are in
> between.
> I am afraid your success will end with the 8/18/03 backup.
> Thomas
> >--Original Message--
> >Hello:
> >
> >We had a user/administrator here make a mistake.
> >
> >What I have is the following:
> >
> >1. A backup from August 18th
> >2. An mdf file and an ldf file from Sept 23rd
> >
> >Facts:
> >
> >The original database instance from the sql server was
> deleted before I had
> >a chance to look at it..
> >
> >I have tried to restore everything on another server but
> it is impossible to
> >get the records the user says are missing since the 18th
> of August.
> >The 18th of August backup can be restored without
> problem. I then tried to
> >restore the transaction log on to that but it did not
> help.
> >
> >I have also brought up the old mdf and ldf file in a new
> instance, then
> >backed up the ldf (log) file and tried to resore it with
> point in time
> >recovery to both that instance and the instance from the
> old backup.
> >Neither effort yielded any new records since the 18th of
> August.
> >
> >If anyone can give me information on how to get these
> records back (the ones
> >inserted since the 18th) I would appreciate it.
> >
> >Right now, it does not look very good.
> >
> >Thanks,
> >
> >Steve K.
> >
> >
> >.
> >|||To add to Thomas' post:
You cannot apply an ldf file to a database backup. You can, possibly attach the mdf and ldf from
Sept 23, provided that these were the only database files for the database at that time.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Steve Kitley" <steve@.kitley.com> wrote in message news:eramPzqhDHA.3144@.tk2msftngp13.phx.gbl...
> Hello:
> We had a user/administrator here make a mistake.
> What I have is the following:
> 1. A backup from August 18th
> 2. An mdf file and an ldf file from Sept 23rd
> Facts:
> The original database instance from the sql server was deleted before I had
> a chance to look at it..
> I have tried to restore everything on another server but it is impossible to
> get the records the user says are missing since the 18th of August.
> The 18th of August backup can be restored without problem. I then tried to
> restore the transaction log on to that but it did not help.
> I have also brought up the old mdf and ldf file in a new instance, then
> backed up the ldf (log) file and tried to resore it with point in time
> recovery to both that instance and the instance from the old backup.
> Neither effort yielded any new records since the 18th of August.
> If anyone can give me information on how to get these records back (the ones
> inserted since the 18th) I would appreciate it.
> Right now, it does not look very good.
> Thanks,
> Steve K.
>|||Tibor,
Thanks, I did create the same database on another instance. Then I stopped
sql server, deleted the newly created mdf and ldf files, replaced those with
the files from the deleted instance, restarted sql server and the database
opened.
However, while the records from the 18th of August were there, the last
backup; none of the records inserted after that time were there.
From this recreation, I then wrote that transaction log to backup. I then
restored the 18th of August database and that 2nd transaction log backup
(just mentioned before) and I still cannot see any of the records after the
18th of August.
So, it appears either the records simply are not in this mdf and ldf file,
or they are there but bringing these two files back are useless.
An important question in regard to what you said Tibor:
Can I assume that if I can successfully attach the mdf and ldf files from
the 23rd of September, and the records since the 18th of August are not in
there, cannot be found, that they were never in there at all?
Thanks in advance for your help,
Steve K.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:%23wkjU$xhDHA.944@.TK2MSFTNGP11.phx.gbl...
> To add to Thomas' post:
> You cannot apply an ldf file to a database backup. You can, possibly
attach the mdf and ldf from
> Sept 23, provided that these were the only database files for the database
at that time.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Steve Kitley" <steve@.kitley.com> wrote in message
news:eramPzqhDHA.3144@.tk2msftngp13.phx.gbl...
> > Hello:
> >
> > We had a user/administrator here make a mistake.
> >
> > What I have is the following:
> >
> > 1. A backup from August 18th
> > 2. An mdf file and an ldf file from Sept 23rd
> >
> > Facts:
> >
> > The original database instance from the sql server was deleted before I
had
> > a chance to look at it..
> >
> > I have tried to restore everything on another server but it is
impossible to
> > get the records the user says are missing since the 18th of August.
> > The 18th of August backup can be restored without problem. I then tried
to
> > restore the transaction log on to that but it did not help.
> >
> > I have also brought up the old mdf and ldf file in a new instance, then
> > backed up the ldf (log) file and tried to resore it with point in time
> > recovery to both that instance and the instance from the old backup.
> > Neither effort yielded any new records since the 18th of August.
> >
> > If anyone can give me information on how to get these records back (the
ones
> > inserted since the 18th) I would appreciate it.
> >
> > Right now, it does not look very good.
> >
> > Thanks,
> >
> > Steve K.
> >
> >
>|||> Thanks, I did create the same database on another instance. Then I stopped
> sql server, deleted the newly created mdf and ldf files, replaced those with
> the files from the deleted instance, restarted sql server and the database
> opened.
Why go through all this hoopla when you could quite simply try sp_attach_db? :-) From what date
were these files?
> However, while the records from the 18th of August were there, the last
> backup; none of the records inserted after that time were there.
I don't understand above. You keep talking about backup. Do you refer to SQL Server backups taken
using the SQL Server BACKUP command? I think I'll stop here as the rest of the text makes even less
sense to me and info is needed for that as well regarding whether you use SQL Server backup or
something else.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Steve K" <steve@.kitley.com> wrote in message news:OpDjUjyhDHA.580@.tk2msftngp13.phx.gbl...
> Tibor,
> Thanks, I did create the same database on another instance. Then I stopped
> sql server, deleted the newly created mdf and ldf files, replaced those with
> the files from the deleted instance, restarted sql server and the database
> opened.
> However, while the records from the 18th of August were there, the last
> backup; none of the records inserted after that time were there.
> From this recreation, I then wrote that transaction log to backup. I then
> restored the 18th of August database and that 2nd transaction log backup
> (just mentioned before) and I still cannot see any of the records after the
> 18th of August.
> So, it appears either the records simply are not in this mdf and ldf file,
> or they are there but bringing these two files back are useless.
> An important question in regard to what you said Tibor:
> Can I assume that if I can successfully attach the mdf and ldf files from
> the 23rd of September, and the records since the 18th of August are not in
> there, cannot be found, that they were never in there at all?
> Thanks in advance for your help,
> Steve K.
>
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:%23wkjU$xhDHA.944@.TK2MSFTNGP11.phx.gbl...
> > To add to Thomas' post:
> >
> > You cannot apply an ldf file to a database backup. You can, possibly
> attach the mdf and ldf from
> > Sept 23, provided that these were the only database files for the database
> at that time.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> > "Steve Kitley" <steve@.kitley.com> wrote in message
> news:eramPzqhDHA.3144@.tk2msftngp13.phx.gbl...
> > > Hello:
> > >
> > > We had a user/administrator here make a mistake.
> > >
> > > What I have is the following:
> > >
> > > 1. A backup from August 18th
> > > 2. An mdf file and an ldf file from Sept 23rd
> > >
> > > Facts:
> > >
> > > The original database instance from the sql server was deleted before I
> had
> > > a chance to look at it..
> > >
> > > I have tried to restore everything on another server but it is
> impossible to
> > > get the records the user says are missing since the 18th of August.
> > > The 18th of August backup can be restored without problem. I then tried
> to
> > > restore the transaction log on to that but it did not help.
> > >
> > > I have also brought up the old mdf and ldf file in a new instance, then
> > > backed up the ldf (log) file and tried to resore it with point in time
> > > recovery to both that instance and the instance from the old backup.
> > > Neither effort yielded any new records since the 18th of August.
> > >
> > > If anyone can give me information on how to get these records back (the
> ones
> > > inserted since the 18th) I would appreciate it.
> > >
> > > Right now, it does not look very good.
> > >
> > > Thanks,
> > >
> > > Steve K.
> > >
> > >
> >
> >
>|||Sorry Tibor, here is the correct order of events that one of our
administrators has apparently done, and what I am left with:
1. August 18th, backup done.
2. September 23, administrator has trouble with the database, have no idea
what happened and he cannot really explain, possibly no space left on the
disk.
3. Admin does not take a backup. And there are no backups between the Aug
19th and Sept 23rd.
4. Admin copies, on Sept 23rd, the mdf and ldf files to another location
after shutting down the database.
5. Admin then restarts sql server and deletes the database.
What do I have?
1. one good backup from the 18th of August
2. one mdf and ldf file from the 23rd of September.
Thanks, if you can give me an idea of what to do I would appreciate it.
Hope this is more clear.
Best regards,
Steve K.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:unlkgv1hDHA.604@.TK2MSFTNGP10.phx.gbl...
> > Thanks, I did create the same database on another instance. Then I
stopped
> > sql server, deleted the newly created mdf and ldf files, replaced those
with
> > the files from the deleted instance, restarted sql server and the
database
> > opened.
> Why go through all this hoopla when you could quite simply try
sp_attach_db? :-) From what date
> were these files?
> > However, while the records from the 18th of August were there, the last
> > backup; none of the records inserted after that time were there.
> I don't understand above. You keep talking about backup. Do you refer to
SQL Server backups taken
> using the SQL Server BACKUP command? I think I'll stop here as the rest of
the text makes even less
> sense to me and info is needed for that as well regarding whether you use
SQL Server backup or
> something else.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Steve K" <steve@.kitley.com> wrote in message
news:OpDjUjyhDHA.580@.tk2msftngp13.phx.gbl...
> > Tibor,
> >
> > Thanks, I did create the same database on another instance. Then I
stopped
> > sql server, deleted the newly created mdf and ldf files, replaced those
with
> > the files from the deleted instance, restarted sql server and the
database
> > opened.
> >
> > However, while the records from the 18th of August were there, the last
> > backup; none of the records inserted after that time were there.
> >
> > From this recreation, I then wrote that transaction log to backup. I
then
> > restored the 18th of August database and that 2nd transaction log backup
> > (just mentioned before) and I still cannot see any of the records after
the
> > 18th of August.
> >
> > So, it appears either the records simply are not in this mdf and ldf
file,
> > or they are there but bringing these two files back are useless.
> >
> > An important question in regard to what you said Tibor:
> >
> > Can I assume that if I can successfully attach the mdf and ldf files
from
> > the 23rd of September, and the records since the 18th of August are not
in
> > there, cannot be found, that they were never in there at all?
> >
> > Thanks in advance for your help,
> >
> > Steve K.
> >
> >
> >
> > "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > wrote in message news:%23wkjU$xhDHA.944@.TK2MSFTNGP11.phx.gbl...
> > > To add to Thomas' post:
> > >
> > > You cannot apply an ldf file to a database backup. You can, possibly
> > attach the mdf and ldf from
> > > Sept 23, provided that these were the only database files for the
database
> > at that time.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at: http://groups.google.com/groups?oi=djq&as
> > ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Steve Kitley" <steve@.kitley.com> wrote in message
> > news:eramPzqhDHA.3144@.tk2msftngp13.phx.gbl...
> > > > Hello:
> > > >
> > > > We had a user/administrator here make a mistake.
> > > >
> > > > What I have is the following:
> > > >
> > > > 1. A backup from August 18th
> > > > 2. An mdf file and an ldf file from Sept 23rd
> > > >
> > > > Facts:
> > > >
> > > > The original database instance from the sql server was deleted
before I
> > had
> > > > a chance to look at it..
> > > >
> > > > I have tried to restore everything on another server but it is
> > impossible to
> > > > get the records the user says are missing since the 18th of August.
> > > > The 18th of August backup can be restored without problem. I then
tried
> > to
> > > > restore the transaction log on to that but it did not help.
> > > >
> > > > I have also brought up the old mdf and ldf file in a new instance,
then
> > > > backed up the ldf (log) file and tried to resore it with point in
time
> > > > recovery to both that instance and the instance from the old backup.
> > > > Neither effort yielded any new records since the 18th of August.
> > > >
> > > > If anyone can give me information on how to get these records back
(the
> > ones
> > > > inserted since the 18th) I would appreciate it.
> > > >
> > > > Right now, it does not look very good.
> > > >
> > > > Thanks,
> > > >
> > > > Steve K.
> > > >
> > > >
> > >
> > >
> >
> >
>|||one last thing, the backups, actually backup (there is just one from the
18th of August, was taken using SQL Server.
Thanks again,
Steve K.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:unlkgv1hDHA.604@.TK2MSFTNGP10.phx.gbl...
> > Thanks, I did create the same database on another instance. Then I
stopped
> > sql server, deleted the newly created mdf and ldf files, replaced those
with
> > the files from the deleted instance, restarted sql server and the
database
> > opened.
> Why go through all this hoopla when you could quite simply try
sp_attach_db? :-) From what date
> were these files?
> > However, while the records from the 18th of August were there, the last
> > backup; none of the records inserted after that time were there.
> I don't understand above. You keep talking about backup. Do you refer to
SQL Server backups taken
> using the SQL Server BACKUP command? I think I'll stop here as the rest of
the text makes even less
> sense to me and info is needed for that as well regarding whether you use
SQL Server backup or
> something else.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Steve K" <steve@.kitley.com> wrote in message
news:OpDjUjyhDHA.580@.tk2msftngp13.phx.gbl...
> > Tibor,
> >
> > Thanks, I did create the same database on another instance. Then I
stopped
> > sql server, deleted the newly created mdf and ldf files, replaced those
with
> > the files from the deleted instance, restarted sql server and the
database
> > opened.
> >
> > However, while the records from the 18th of August were there, the last
> > backup; none of the records inserted after that time were there.
> >
> > From this recreation, I then wrote that transaction log to backup. I
then
> > restored the 18th of August database and that 2nd transaction log backup
> > (just mentioned before) and I still cannot see any of the records after
the
> > 18th of August.
> >
> > So, it appears either the records simply are not in this mdf and ldf
file,
> > or they are there but bringing these two files back are useless.
> >
> > An important question in regard to what you said Tibor:
> >
> > Can I assume that if I can successfully attach the mdf and ldf files
from
> > the 23rd of September, and the records since the 18th of August are not
in
> > there, cannot be found, that they were never in there at all?
> >
> > Thanks in advance for your help,
> >
> > Steve K.
> >
> >
> >
> > "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > wrote in message news:%23wkjU$xhDHA.944@.TK2MSFTNGP11.phx.gbl...
> > > To add to Thomas' post:
> > >
> > > You cannot apply an ldf file to a database backup. You can, possibly
> > attach the mdf and ldf from
> > > Sept 23, provided that these were the only database files for the
database
> > at that time.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at: http://groups.google.com/groups?oi=djq&as
> > ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Steve Kitley" <steve@.kitley.com> wrote in message
> > news:eramPzqhDHA.3144@.tk2msftngp13.phx.gbl...
> > > > Hello:
> > > >
> > > > We had a user/administrator here make a mistake.
> > > >
> > > > What I have is the following:
> > > >
> > > > 1. A backup from August 18th
> > > > 2. An mdf file and an ldf file from Sept 23rd
> > > >
> > > > Facts:
> > > >
> > > > The original database instance from the sql server was deleted
before I
> > had
> > > > a chance to look at it..
> > > >
> > > > I have tried to restore everything on another server but it is
> > impossible to
> > > > get the records the user says are missing since the 18th of August.
> > > > The 18th of August backup can be restored without problem. I then
tried
> > to
> > > > restore the transaction log on to that but it did not help.
> > > >
> > > > I have also brought up the old mdf and ldf file in a new instance,
then
> > > > backed up the ldf (log) file and tried to resore it with point in
time
> > > > recovery to both that instance and the instance from the old backup.
> > > > Neither effort yielded any new records since the 18th of August.
> > > >
> > > > If anyone can give me information on how to get these records back
(the
> > ones
> > > > inserted since the 18th) I would appreciate it.
> > > >
> > > > Right now, it does not look very good.
> > > >
> > > > Thanks,
> > > >
> > > > Steve K.
> > > >
> > > >
> > >
> > >
> >
> >
>|||OK, so it seems you have a SQL Server backup (.BAK) taken Aug 18 and nothing from then on (except a
couple of copied file from Sept 23).
If you attach the sept 23 files, is all OK?
Or was the problem introduced between Aug 18 and Sept 23?
Or do you want to get stuff backup from *after* Sept 23?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Steve K" <steve@.kitley.com> wrote in message news:u2vxdu6hDHA.1368@.TK2MSFTNGP12.phx.gbl...
> Sorry Tibor, here is the correct order of events that one of our
> administrators has apparently done, and what I am left with:
> 1. August 18th, backup done.
> 2. September 23, administrator has trouble with the database, have no idea
> what happened and he cannot really explain, possibly no space left on the
> disk.
> 3. Admin does not take a backup. And there are no backups between the Aug
> 19th and Sept 23rd.
> 4. Admin copies, on Sept 23rd, the mdf and ldf files to another location
> after shutting down the database.
> 5. Admin then restarts sql server and deletes the database.
> What do I have?
> 1. one good backup from the 18th of August
> 2. one mdf and ldf file from the 23rd of September.
> Thanks, if you can give me an idea of what to do I would appreciate it.
> Hope this is more clear.
> Best regards,
> Steve K.
>
>
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:unlkgv1hDHA.604@.TK2MSFTNGP10.phx.gbl...
> > > Thanks, I did create the same database on another instance. Then I
> stopped
> > > sql server, deleted the newly created mdf and ldf files, replaced those
> with
> > > the files from the deleted instance, restarted sql server and the
> database
> > > opened.
> >
> > Why go through all this hoopla when you could quite simply try
> sp_attach_db? :-) From what date
> > were these files?
> >
> > > However, while the records from the 18th of August were there, the last
> > > backup; none of the records inserted after that time were there.
> >
> > I don't understand above. You keep talking about backup. Do you refer to
> SQL Server backups taken
> > using the SQL Server BACKUP command? I think I'll stop here as the rest of
> the text makes even less
> > sense to me and info is needed for that as well regarding whether you use
> SQL Server backup or
> > something else.
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> > "Steve K" <steve@.kitley.com> wrote in message
> news:OpDjUjyhDHA.580@.tk2msftngp13.phx.gbl...
> > > Tibor,
> > >
> > > Thanks, I did create the same database on another instance. Then I
> stopped
> > > sql server, deleted the newly created mdf and ldf files, replaced those
> with
> > > the files from the deleted instance, restarted sql server and the
> database
> > > opened.
> > >
> > > However, while the records from the 18th of August were there, the last
> > > backup; none of the records inserted after that time were there.
> > >
> > > From this recreation, I then wrote that transaction log to backup. I
> then
> > > restored the 18th of August database and that 2nd transaction log backup
> > > (just mentioned before) and I still cannot see any of the records after
> the
> > > 18th of August.
> > >
> > > So, it appears either the records simply are not in this mdf and ldf
> file,
> > > or they are there but bringing these two files back are useless.
> > >
> > > An important question in regard to what you said Tibor:
> > >
> > > Can I assume that if I can successfully attach the mdf and ldf files
> from
> > > the 23rd of September, and the records since the 18th of August are not
> in
> > > there, cannot be found, that they were never in there at all?
> > >
> > > Thanks in advance for your help,
> > >
> > > Steve K.
> > >
> > >
> > >
> > > "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > > wrote in message news:%23wkjU$xhDHA.944@.TK2MSFTNGP11.phx.gbl...
> > > > To add to Thomas' post:
> > > >
> > > > You cannot apply an ldf file to a database backup. You can, possibly
> > > attach the mdf and ldf from
> > > > Sept 23, provided that these were the only database files for the
> database
> > > at that time.
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > Archive at: http://groups.google.com/groups?oi=djq&as
> > > ugroup=microsoft.public.sqlserver
> > > >
> > > >
> > > > "Steve Kitley" <steve@.kitley.com> wrote in message
> > > news:eramPzqhDHA.3144@.tk2msftngp13.phx.gbl...
> > > > > Hello:
> > > > >
> > > > > We had a user/administrator here make a mistake.
> > > > >
> > > > > What I have is the following:
> > > > >
> > > > > 1. A backup from August 18th
> > > > > 2. An mdf file and an ldf file from Sept 23rd
> > > > >
> > > > > Facts:
> > > > >
> > > > > The original database instance from the sql server was deleted
> before I
> > > had
> > > > > a chance to look at it..
> > > > >
> > > > > I have tried to restore everything on another server but it is
> > > impossible to
> > > > > get the records the user says are missing since the 18th of August.
> > > > > The 18th of August backup can be restored without problem. I then
> tried
> > > to
> > > > > restore the transaction log on to that but it did not help.
> > > > >
> > > > > I have also brought up the old mdf and ldf file in a new instance,
> then
> > > > > backed up the ldf (log) file and tried to resore it with point in
> time
> > > > > recovery to both that instance and the instance from the old backup.
> > > > > Neither effort yielded any new records since the 18th of August.
> > > > >
> > > > > If anyone can give me information on how to get these records back
> (the
> > > ones
> > > > > inserted since the 18th) I would appreciate it.
> > > > >
> > > > > Right now, it does not look very good.
> > > > >
> > > > > Thanks,
> > > > >
> > > > > Steve K.
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>