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.
Showing posts with label mdf. Show all posts
Showing posts with label mdf. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
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!
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 with Multiple Files
All
I have a database with multiple files ie. an mdf and two ndf. I have
performed a full database backup using BACKUP DATABASE dbname to DISK =
'filename' and I would like to restore the database to another server and
only have one file ie. PRIMARY mdf instead of the three files. Can anyone
advise how this can be achieved.
Thanks
That is not possible. A SQL Server backup is more or less a binary dump if the data pages, so SQL
Server need to put each page in the same file and on the same page address (as other pages might be
pointing to this page). Restore as original, and do the shuffling after the restore. Or do the
shuffling before the backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David" <David@.discussions.microsoft.com> wrote in message
news:89EB659B-C421-4DB2-BB1B-17D30C6CFB8A@.microsoft.com...
> All
> I have a database with multiple files ie. an mdf and two ndf. I have
> performed a full database backup using BACKUP DATABASE dbname to DISK =
> 'filename' and I would like to restore the database to another server and
> only have one file ie. PRIMARY mdf instead of the three files. Can anyone
> advise how this can be achieved.
> Thanks
>
I have a database with multiple files ie. an mdf and two ndf. I have
performed a full database backup using BACKUP DATABASE dbname to DISK =
'filename' and I would like to restore the database to another server and
only have one file ie. PRIMARY mdf instead of the three files. Can anyone
advise how this can be achieved.
Thanks
That is not possible. A SQL Server backup is more or less a binary dump if the data pages, so SQL
Server need to put each page in the same file and on the same page address (as other pages might be
pointing to this page). Restore as original, and do the shuffling after the restore. Or do the
shuffling before the backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David" <David@.discussions.microsoft.com> wrote in message
news:89EB659B-C421-4DB2-BB1B-17D30C6CFB8A@.microsoft.com...
> All
> I have a database with multiple files ie. an mdf and two ndf. I have
> performed a full database backup using BACKUP DATABASE dbname to DISK =
> 'filename' and I would like to restore the database to another server and
> only have one file ie. PRIMARY mdf instead of the three files. Can anyone
> advise how this can be achieved.
> Thanks
>
Restore Database with Multiple Files
All
I have a database with multiple files ie. an mdf and two ndf. I have
performed a full database backup using BACKUP DATABASE dbname to DISK =
'filename' and I would like to restore the database to another server and
only have one file ie. PRIMARY mdf instead of the three files. Can anyone
advise how this can be achieved.
ThanksThat is not possible. A SQL Server backup is more or less a binary dump if t
he data pages, so SQL
Server need to put each page in the same file and on the same page address (
as other pages might be
pointing to this page). Restore as original, and do the shuffling after the
restore. Or do the
shuffling before the backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David" <David@.discussions.microsoft.com> wrote in message
news:89EB659B-C421-4DB2-BB1B-17D30C6CFB8A@.microsoft.com...
> All
> I have a database with multiple files ie. an mdf and two ndf. I have
> performed a full database backup using BACKUP DATABASE dbname to DISK =
> 'filename' and I would like to restore the database to another server and
> only have one file ie. PRIMARY mdf instead of the three files. Can anyone
> advise how this can be achieved.
> Thanks
>
I have a database with multiple files ie. an mdf and two ndf. I have
performed a full database backup using BACKUP DATABASE dbname to DISK =
'filename' and I would like to restore the database to another server and
only have one file ie. PRIMARY mdf instead of the three files. Can anyone
advise how this can be achieved.
ThanksThat is not possible. A SQL Server backup is more or less a binary dump if t
he data pages, so SQL
Server need to put each page in the same file and on the same page address (
as other pages might be
pointing to this page). Restore as original, and do the shuffling after the
restore. Or do the
shuffling before the backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David" <David@.discussions.microsoft.com> wrote in message
news:89EB659B-C421-4DB2-BB1B-17D30C6CFB8A@.microsoft.com...
> All
> I have a database with multiple files ie. an mdf and two ndf. I have
> performed a full database backup using BACKUP DATABASE dbname to DISK =
> 'filename' and I would like to restore the database to another server and
> only have one file ie. PRIMARY mdf instead of the three files. Can anyone
> advise how this can be achieved.
> Thanks
>
Restore Database with Multiple Files
All
I have a database with multiple files ie. an mdf and two ndf. I have
performed a full database backup using BACKUP DATABASE dbname to DISK = 'filename' and I would like to restore the database to another server and
only have one file ie. PRIMARY mdf instead of the three files. Can anyone
advise how this can be achieved.
ThanksThat is not possible. A SQL Server backup is more or less a binary dump if the data pages, so SQL
Server need to put each page in the same file and on the same page address (as other pages might be
pointing to this page). Restore as original, and do the shuffling after the restore. Or do the
shuffling before the backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David" <David@.discussions.microsoft.com> wrote in message
news:89EB659B-C421-4DB2-BB1B-17D30C6CFB8A@.microsoft.com...
> All
> I have a database with multiple files ie. an mdf and two ndf. I have
> performed a full database backup using BACKUP DATABASE dbname to DISK => 'filename' and I would like to restore the database to another server and
> only have one file ie. PRIMARY mdf instead of the three files. Can anyone
> advise how this can be achieved.
> Thanks
>sql
I have a database with multiple files ie. an mdf and two ndf. I have
performed a full database backup using BACKUP DATABASE dbname to DISK = 'filename' and I would like to restore the database to another server and
only have one file ie. PRIMARY mdf instead of the three files. Can anyone
advise how this can be achieved.
ThanksThat is not possible. A SQL Server backup is more or less a binary dump if the data pages, so SQL
Server need to put each page in the same file and on the same page address (as other pages might be
pointing to this page). Restore as original, and do the shuffling after the restore. Or do the
shuffling before the backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David" <David@.discussions.microsoft.com> wrote in message
news:89EB659B-C421-4DB2-BB1B-17D30C6CFB8A@.microsoft.com...
> All
> I have a database with multiple files ie. an mdf and two ndf. I have
> performed a full database backup using BACKUP DATABASE dbname to DISK => 'filename' and I would like to restore the database to another server and
> only have one file ie. PRIMARY mdf instead of the three files. Can anyone
> advise how this can be achieved.
> Thanks
>sql
Friday, March 23, 2012
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:
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?
>>
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:
>
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
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
> ---
>
Please anyone reply me and this is very very urgent
quote:|||You can try sp_attach_single_file_db. But that is only guaranteed if you onl
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
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.
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.
Monday, March 12, 2012
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.
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
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
Wednesday, March 7, 2012
restore a sql db
Hi,
I received a sql xxx.mdf file. I believe it is a copy
after detached from the server.
But I don't know the path. When I tried to attach it back
to my sql server, I received error "Error 5173 Cannot
associate files with different databases."
Any suggestion?
ThanksIF the database is a single mdf file... use
sp_attach_single_file_db databasename, "Fully qualified mdf filename"
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"frankie" <frank.su@.harlandfs.com> wrote in message
news:028201c3debe$7ba77370$a101280a@.phx.gbl...
> Hi,
> I received a sql xxx.mdf file. I believe it is a copy
> after detached from the server.
> But I don't know the path. When I tried to attach it back
> to my sql server, I received error "Error 5173 Cannot
> associate files with different databases."
> Any suggestion?
> Thanks|||this is what I got when I used sp_attached_single db
Server: Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 3(The
system cannot find the path specified.) while attempting
to open or create the physical
file 'E:\NewData\Log\ezteller_log.LDF'.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'ezteller'. CREATE DATABASE is
aborted.
Device activation error. The physical file
name 'D:\Program Files\Microsoft SQL
Server\MSSQL\data\EZTeller_Log.LDF' may be incorrect.
I know the database name, but I don't the file path. Does
it matter?
>--Original Message--
>IF the database is a single mdf file... use
>sp_attach_single_file_db databasename, "Fully qualified
mdf filename"
>
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>
>"frankie" <frank.su@.harlandfs.com> wrote in message
>news:028201c3debe$7ba77370$a101280a@.phx.gbl...
>> Hi,
>> I received a sql xxx.mdf file. I believe it is a copy
>> after detached from the server.
>> But I don't know the path. When I tried to attach it
back
>> to my sql server, I received error "Error 5173 Cannot
>> associate files with different databases."
>> Any suggestion?
>> Thanks
>
>.
>|||No, the file path doesn't matter... You simply use the path you copy the
file to... Let's say you copy the file to D:\data\SQL\ezteller.mdf, then
the command would be
Sp_attach_single_file_db ezteller, 'D:\data\SQL\ezteller.mdf'
The error message is whining about the log file however... Are you sure you
are
1. Using sp_attach_single_file_db and NOT sp_attach_db and
2. The file you are attaching is ACTUALLY an MDF file and not a log file?
and
3. The directory etc actually already exists with the mdf file copied to it?
AND
4. the SQL Server login has read/write permissions on the file?
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"frankie" <frank.su@.harlandfs> wrote in message
news:0a5501c3dec6$e6f9b040$a501280a@.phx.gbl...
> this is what I got when I used sp_attached_single db
> Server: Msg 5123, Level 16, State 1, Line 1
> CREATE FILE encountered operating system error 3(The
> system cannot find the path specified.) while attempting
> to open or create the physical
> file 'E:\NewData\Log\ezteller_log.LDF'.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'ezteller'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file
> name 'D:\Program Files\Microsoft SQL
> Server\MSSQL\data\EZTeller_Log.LDF' may be incorrect.
> I know the database name, but I don't the file path. Does
> it matter?
>
> >--Original Message--
> >IF the database is a single mdf file... use
> >sp_attach_single_file_db databasename, "Fully qualified
> mdf filename"
> >
> >
> >
> >--
> >Wayne Snyder, MCDBA, SQL Server MVP
> >Computer Education Services Corporation (CESC),
> Charlotte, NC
> >www.computeredservices.com
> >(Please respond only to the newsgroups.)
> >
> >I support the Professional Association of SQL Server
> (PASS) and it's
> >community of SQL Server professionals.
> >www.sqlpass.org
> >
> >
> >"frankie" <frank.su@.harlandfs.com> wrote in message
> >news:028201c3debe$7ba77370$a101280a@.phx.gbl...
> >> Hi,
> >> I received a sql xxx.mdf file. I believe it is a copy
> >> after detached from the server.
> >> But I don't know the path. When I tried to attach it
> back
> >> to my sql server, I received error "Error 5173 Cannot
> >> associate files with different databases."
> >>
> >> Any suggestion?
> >>
> >> Thanks
> >
> >
> >.
> >|||I was able to restore the db by create some folders which
I believed the original physical files were at.
Thanks
>--Original Message--
>this is what I got when I used sp_attached_single db
>Server: Msg 5123, Level 16, State 1, Line 1
>CREATE FILE encountered operating system error 3(The
>system cannot find the path specified.) while attempting
>to open or create the physical
>file 'E:\NewData\Log\ezteller_log.LDF'.
>Server: Msg 1813, Level 16, State 1, Line 1
>Could not open new database 'ezteller'. CREATE DATABASE
is
>aborted.
>Device activation error. The physical file
>name 'D:\Program Files\Microsoft SQL
>Server\MSSQL\data\EZTeller_Log.LDF' may be incorrect.
>I know the database name, but I don't the file path. Does
>it matter?
>
>>--Original Message--
>>IF the database is a single mdf file... use
>>sp_attach_single_file_db databasename, "Fully qualified
>mdf filename"
>>
>>--
>>Wayne Snyder, MCDBA, SQL Server MVP
>>Computer Education Services Corporation (CESC),
>Charlotte, NC
>>www.computeredservices.com
>>(Please respond only to the newsgroups.)
>>I support the Professional Association of SQL Server
>(PASS) and it's
>>community of SQL Server professionals.
>>www.sqlpass.org
>>
>>"frankie" <frank.su@.harlandfs.com> wrote in message
>>news:028201c3debe$7ba77370$a101280a@.phx.gbl...
>> Hi,
>> I received a sql xxx.mdf file. I believe it is a copy
>> after detached from the server.
>> But I don't know the path. When I tried to attach it
>back
>> to my sql server, I received error "Error 5173 Cannot
>> associate files with different databases."
>> Any suggestion?
>> Thanks
>>
>>.
>.
>
I received a sql xxx.mdf file. I believe it is a copy
after detached from the server.
But I don't know the path. When I tried to attach it back
to my sql server, I received error "Error 5173 Cannot
associate files with different databases."
Any suggestion?
ThanksIF the database is a single mdf file... use
sp_attach_single_file_db databasename, "Fully qualified mdf filename"
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"frankie" <frank.su@.harlandfs.com> wrote in message
news:028201c3debe$7ba77370$a101280a@.phx.gbl...
> Hi,
> I received a sql xxx.mdf file. I believe it is a copy
> after detached from the server.
> But I don't know the path. When I tried to attach it back
> to my sql server, I received error "Error 5173 Cannot
> associate files with different databases."
> Any suggestion?
> Thanks|||this is what I got when I used sp_attached_single db
Server: Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 3(The
system cannot find the path specified.) while attempting
to open or create the physical
file 'E:\NewData\Log\ezteller_log.LDF'.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'ezteller'. CREATE DATABASE is
aborted.
Device activation error. The physical file
name 'D:\Program Files\Microsoft SQL
Server\MSSQL\data\EZTeller_Log.LDF' may be incorrect.
I know the database name, but I don't the file path. Does
it matter?
>--Original Message--
>IF the database is a single mdf file... use
>sp_attach_single_file_db databasename, "Fully qualified
mdf filename"
>
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>
>"frankie" <frank.su@.harlandfs.com> wrote in message
>news:028201c3debe$7ba77370$a101280a@.phx.gbl...
>> Hi,
>> I received a sql xxx.mdf file. I believe it is a copy
>> after detached from the server.
>> But I don't know the path. When I tried to attach it
back
>> to my sql server, I received error "Error 5173 Cannot
>> associate files with different databases."
>> Any suggestion?
>> Thanks
>
>.
>|||No, the file path doesn't matter... You simply use the path you copy the
file to... Let's say you copy the file to D:\data\SQL\ezteller.mdf, then
the command would be
Sp_attach_single_file_db ezteller, 'D:\data\SQL\ezteller.mdf'
The error message is whining about the log file however... Are you sure you
are
1. Using sp_attach_single_file_db and NOT sp_attach_db and
2. The file you are attaching is ACTUALLY an MDF file and not a log file?
and
3. The directory etc actually already exists with the mdf file copied to it?
AND
4. the SQL Server login has read/write permissions on the file?
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"frankie" <frank.su@.harlandfs> wrote in message
news:0a5501c3dec6$e6f9b040$a501280a@.phx.gbl...
> this is what I got when I used sp_attached_single db
> Server: Msg 5123, Level 16, State 1, Line 1
> CREATE FILE encountered operating system error 3(The
> system cannot find the path specified.) while attempting
> to open or create the physical
> file 'E:\NewData\Log\ezteller_log.LDF'.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'ezteller'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file
> name 'D:\Program Files\Microsoft SQL
> Server\MSSQL\data\EZTeller_Log.LDF' may be incorrect.
> I know the database name, but I don't the file path. Does
> it matter?
>
> >--Original Message--
> >IF the database is a single mdf file... use
> >sp_attach_single_file_db databasename, "Fully qualified
> mdf filename"
> >
> >
> >
> >--
> >Wayne Snyder, MCDBA, SQL Server MVP
> >Computer Education Services Corporation (CESC),
> Charlotte, NC
> >www.computeredservices.com
> >(Please respond only to the newsgroups.)
> >
> >I support the Professional Association of SQL Server
> (PASS) and it's
> >community of SQL Server professionals.
> >www.sqlpass.org
> >
> >
> >"frankie" <frank.su@.harlandfs.com> wrote in message
> >news:028201c3debe$7ba77370$a101280a@.phx.gbl...
> >> Hi,
> >> I received a sql xxx.mdf file. I believe it is a copy
> >> after detached from the server.
> >> But I don't know the path. When I tried to attach it
> back
> >> to my sql server, I received error "Error 5173 Cannot
> >> associate files with different databases."
> >>
> >> Any suggestion?
> >>
> >> Thanks
> >
> >
> >.
> >|||I was able to restore the db by create some folders which
I believed the original physical files were at.
Thanks
>--Original Message--
>this is what I got when I used sp_attached_single db
>Server: Msg 5123, Level 16, State 1, Line 1
>CREATE FILE encountered operating system error 3(The
>system cannot find the path specified.) while attempting
>to open or create the physical
>file 'E:\NewData\Log\ezteller_log.LDF'.
>Server: Msg 1813, Level 16, State 1, Line 1
>Could not open new database 'ezteller'. CREATE DATABASE
is
>aborted.
>Device activation error. The physical file
>name 'D:\Program Files\Microsoft SQL
>Server\MSSQL\data\EZTeller_Log.LDF' may be incorrect.
>I know the database name, but I don't the file path. Does
>it matter?
>
>>--Original Message--
>>IF the database is a single mdf file... use
>>sp_attach_single_file_db databasename, "Fully qualified
>mdf filename"
>>
>>--
>>Wayne Snyder, MCDBA, SQL Server MVP
>>Computer Education Services Corporation (CESC),
>Charlotte, NC
>>www.computeredservices.com
>>(Please respond only to the newsgroups.)
>>I support the Professional Association of SQL Server
>(PASS) and it's
>>community of SQL Server professionals.
>>www.sqlpass.org
>>
>>"frankie" <frank.su@.harlandfs.com> wrote in message
>>news:028201c3debe$7ba77370$a101280a@.phx.gbl...
>> Hi,
>> I received a sql xxx.mdf file. I believe it is a copy
>> after detached from the server.
>> But I don't know the path. When I tried to attach it
>back
>> to my sql server, I received error "Error 5173 Cannot
>> associate files with different databases."
>> Any suggestion?
>> Thanks
>>
>>.
>.
>
restore a sql db
Hi,
I received a sql xxx.mdf file. I believe it is a copy
after detached from the server.
But I don't know the path. When I tried to attach it back
to my sql server, I received error "Error 5173 Cannot
associate files with different databases."
Any suggestion?
ThanksIF the database is a single mdf file... use
sp_attach_single_file_db databasename, "Fully qualified mdf filename"
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"frankie" <frank.su@.harlandfs.com> wrote in message
news:028201c3debe$7ba77370$a101280a@.phx.gbl...
Server: Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 3(The
system cannot find the path specified.) while attempting
to open or create the physical
file 'E:\NewData\Log\ezteller_log.LDF'.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'ezteller'. CREATE DATABASE is
aborted.
Device activation error. The physical file
name 'D:\Program Files\Microsoft SQL
Server\MSSQL\data\EZTeller_Log.LDF' may be incorrect.
I know the database name, but I don't the file path. Does
it matter?
mdf filename"
Charlotte, NC
(PASS) and it's
file to... Let's say you copy the file to D:\data\SQL\ezteller.mdf, then
the command would be
Sp_attach_single_file_db ezteller, 'D:\data\SQL\ezteller.mdf'
The error message is whining about the log file however... Are you sure you
are
1. Using sp_attach_single_file_db and NOT sp_attach_db and
2. The file you are attaching is ACTUALLY an MDF file and not a log file?
and
3. The directory etc actually already exists with the mdf file copied to it?
AND
4. the SQL Server login has read/write permissions on the file?
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"frankie" <frank.su@.harlandfs> wrote in message
news:0a5501c3dec6$e6f9b040$a501280a@.phx.gbl...[QUOTE]
> this is what I got when I used sp_attached_single db
> Server: Msg 5123, Level 16, State 1, Line 1
> CREATE FILE encountered operating system error 3(The
> system cannot find the path specified.) while attempting
> to open or create the physical
> file 'E:\NewData\Log\ezteller_log.LDF'.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'ezteller'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file
> name 'D:\Program Files\Microsoft SQL
> Server\MSSQL\data\EZTeller_Log.LDF' may be incorrect.
> I know the database name, but I don't the file path. Does
> it matter?
>
> mdf filename"
> Charlotte, NC
> (PASS) and it's
> back|||I was able to restore the db by create some folders which
I believed the original physical files were at.
Thanks
is
Server\MSSQL\data\EZTeller_Log.LDF'
I received a sql xxx.mdf file. I believe it is a copy
after detached from the server.
But I don't know the path. When I tried to attach it back
to my sql server, I received error "Error 5173 Cannot
associate files with different databases."
Any suggestion?
ThanksIF the database is a single mdf file... use
sp_attach_single_file_db databasename, "Fully qualified mdf filename"
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"frankie" <frank.su@.harlandfs.com> wrote in message
news:028201c3debe$7ba77370$a101280a@.phx.gbl...
quote:|||this is what I got when I used sp_attached_single db
> Hi,
> I received a sql xxx.mdf file. I believe it is a copy
> after detached from the server.
> But I don't know the path. When I tried to attach it back
> to my sql server, I received error "Error 5173 Cannot
> associate files with different databases."
> Any suggestion?
> Thanks
Server: Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 3(The
system cannot find the path specified.) while attempting
to open or create the physical
file 'E:\NewData\Log\ezteller_log.LDF'.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'ezteller'. CREATE DATABASE is
aborted.
Device activation error. The physical file
name 'D:\Program Files\Microsoft SQL
Server\MSSQL\data\EZTeller_Log.LDF' may be incorrect.
I know the database name, but I don't the file path. Does
it matter?
quote:
>--Original Message--
>IF the database is a single mdf file... use
>sp_attach_single_file_db databasename, "Fully qualified
mdf filename"
quote:
>
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
quote:
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
quote:|||No, the file path doesn't matter... You simply use the path you copy the
>community of SQL Server professionals.
>www.sqlpass.org
>
>"frankie" <frank.su@.harlandfs.com> wrote in message
>news:028201c3debe$7ba77370$a101280a@.phx.gbl...
back[QUOTE]
>
>.
>
file to... Let's say you copy the file to D:\data\SQL\ezteller.mdf, then
the command would be
Sp_attach_single_file_db ezteller, 'D:\data\SQL\ezteller.mdf'
The error message is whining about the log file however... Are you sure you
are
1. Using sp_attach_single_file_db and NOT sp_attach_db and
2. The file you are attaching is ACTUALLY an MDF file and not a log file?
and
3. The directory etc actually already exists with the mdf file copied to it?
AND
4. the SQL Server login has read/write permissions on the file?
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"frankie" <frank.su@.harlandfs> wrote in message
news:0a5501c3dec6$e6f9b040$a501280a@.phx.gbl...[QUOTE]
> this is what I got when I used sp_attached_single db
> Server: Msg 5123, Level 16, State 1, Line 1
> CREATE FILE encountered operating system error 3(The
> system cannot find the path specified.) while attempting
> to open or create the physical
> file 'E:\NewData\Log\ezteller_log.LDF'.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'ezteller'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file
> name 'D:\Program Files\Microsoft SQL
> Server\MSSQL\data\EZTeller_Log.LDF' may be incorrect.
> I know the database name, but I don't the file path. Does
> it matter?
>
> mdf filename"
> Charlotte, NC
> (PASS) and it's
> back|||I was able to restore the db by create some folders which
I believed the original physical files were at.
Thanks
quote:
>--Original Message--
>this is what I got when I used sp_attached_single db
>Server: Msg 5123, Level 16, State 1, Line 1
>CREATE FILE encountered operating system error 3(The
>system cannot find the path specified.) while attempting
>to open or create the physical
>file 'E:\NewData\Log\ezteller_log.LDF'.
>Server: Msg 1813, Level 16, State 1, Line 1
>Could not open new database 'ezteller'. CREATE DATABASE
is
quote:|||Place the file in the path as specified i.e. 'D:\Program Files\Microsoft SQL
>aborted.
>Device activation error. The physical file
>name 'D:\Program Files\Microsoft SQL
>Server\MSSQL\data\EZTeller_Log.LDF' may be incorrect.
>I know the database name, but I don't the file path. Does
>it matter?
>
>mdf filename"
>Charlotte, NC
>(PASS) and it's
>back
>.
>
Server\MSSQL\data\EZTeller_Log.LDF'
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.... 
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....
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 database with 2 datafiles into one datafile - possible?
Hi,
I do have a large Axapta database (114GB) with following file conent:
Datafiles:
d:\...\data\Axapta_data01.mdf - it is 87GB - it is in PRIMARY
Filegroup
f:\....\data\Axapta_data02.mdf - it is 27GB - also in PRIMARY
Filegroup
Logfile:
e:\...\log\Axapta_log02.ldf - 20 GB - none file group since it's
transactionlog file
Unfortunately does this application perform not well with this file
config (known problem within the Axapta enviroment)
So my issue is now following:
How can I move this database to a new server, and create only one
datafile?
I would like this new file config:
d:\....\data\Axapta_data01.mdf - 114GB - Primary filegroup
e:\....\log\axapta_log.ldf
Can anyone explain to me if it is possible, and then... how?
Thanks in advance...
Regards
Torgeir
A restored database is exactly like the original so you can't change the
file configuration during the restore. If your plan is to move the database
using BACKP/RESTORE, you'll need to change the file configuration before the
backup or after the restore. To do this, expand the data01 file to the
desired size and then migrate data from data02 using DBCC
SHRINKFILE...EMPTYFILE. You can then remove the data02 file using ALTER
DATABASE.
Although it is not enforced, the file extension for secondary data files
should be 'ndf' rather than 'mdf'. The 'mdf' extension should be used only
for the primary data file in order to avoid later confusion.
Happy Holidays
Dan Guzman
SQL Server MVP
<dolerud@.start.no> wrote in message
news:1135896641.887437.220980@.g44g2000cwa.googlegr oups.com...
> Hi,
> I do have a large Axapta database (114GB) with following file conent:
> Datafiles:
> d:\...\data\Axapta_data01.mdf - it is 87GB - it is in PRIMARY
> Filegroup
> f:\....\data\Axapta_data02.mdf - it is 27GB - also in PRIMARY
> Filegroup
> Logfile:
> e:\...\log\Axapta_log02.ldf - 20 GB - none file group since it's
> transactionlog file
> Unfortunately does this application perform not well with this file
> config (known problem within the Axapta enviroment)
> So my issue is now following:
> How can I move this database to a new server, and create only one
> datafile?
> I would like this new file config:
> d:\....\data\Axapta_data01.mdf - 114GB - Primary filegroup
> e:\....\log\axapta_log.ldf
> Can anyone explain to me if it is possible, and then... how?
> Thanks in advance...
> Regards
> Torgeir
>
|||Hi, and thank you for your quick response!
I assume I also can do this operation after I have moved the database
to a new server.
The existing server do not have enought diskspace.
Regards
Torgeir
|||Yes, you can consolidate the files after moving to the new server.
Happy Holidays
Dan Guzman
SQL Server MVP
<dolerud@.start.no> wrote in message
news:1135898358.019743.33740@.f14g2000cwb.googlegro ups.com...
> Hi, and thank you for your quick response!
> I assume I also can do this operation after I have moved the database
> to a new server.
> The existing server do not have enought diskspace.
> Regards
> Torgeir
>
I do have a large Axapta database (114GB) with following file conent:
Datafiles:
d:\...\data\Axapta_data01.mdf - it is 87GB - it is in PRIMARY
Filegroup
f:\....\data\Axapta_data02.mdf - it is 27GB - also in PRIMARY
Filegroup
Logfile:
e:\...\log\Axapta_log02.ldf - 20 GB - none file group since it's
transactionlog file
Unfortunately does this application perform not well with this file
config (known problem within the Axapta enviroment)
So my issue is now following:
How can I move this database to a new server, and create only one
datafile?
I would like this new file config:
d:\....\data\Axapta_data01.mdf - 114GB - Primary filegroup
e:\....\log\axapta_log.ldf
Can anyone explain to me if it is possible, and then... how?
Thanks in advance...
Regards
Torgeir
A restored database is exactly like the original so you can't change the
file configuration during the restore. If your plan is to move the database
using BACKP/RESTORE, you'll need to change the file configuration before the
backup or after the restore. To do this, expand the data01 file to the
desired size and then migrate data from data02 using DBCC
SHRINKFILE...EMPTYFILE. You can then remove the data02 file using ALTER
DATABASE.
Although it is not enforced, the file extension for secondary data files
should be 'ndf' rather than 'mdf'. The 'mdf' extension should be used only
for the primary data file in order to avoid later confusion.
Happy Holidays
Dan Guzman
SQL Server MVP
<dolerud@.start.no> wrote in message
news:1135896641.887437.220980@.g44g2000cwa.googlegr oups.com...
> Hi,
> I do have a large Axapta database (114GB) with following file conent:
> Datafiles:
> d:\...\data\Axapta_data01.mdf - it is 87GB - it is in PRIMARY
> Filegroup
> f:\....\data\Axapta_data02.mdf - it is 27GB - also in PRIMARY
> Filegroup
> Logfile:
> e:\...\log\Axapta_log02.ldf - 20 GB - none file group since it's
> transactionlog file
> Unfortunately does this application perform not well with this file
> config (known problem within the Axapta enviroment)
> So my issue is now following:
> How can I move this database to a new server, and create only one
> datafile?
> I would like this new file config:
> d:\....\data\Axapta_data01.mdf - 114GB - Primary filegroup
> e:\....\log\axapta_log.ldf
> Can anyone explain to me if it is possible, and then... how?
> Thanks in advance...
> Regards
> Torgeir
>
|||Hi, and thank you for your quick response!
I assume I also can do this operation after I have moved the database
to a new server.
The existing server do not have enought diskspace.
Regards
Torgeir
|||Yes, you can consolidate the files after moving to the new server.
Happy Holidays
Dan Guzman
SQL Server MVP
<dolerud@.start.no> wrote in message
news:1135898358.019743.33740@.f14g2000cwb.googlegro ups.com...
> Hi, and thank you for your quick response!
> I assume I also can do this operation after I have moved the database
> to a new server.
> The existing server do not have enought diskspace.
> Regards
> Torgeir
>
Saturday, February 25, 2012
Restore a database with 2 datafiles into one datafile - possible?
Hi,
I do have a large Axapta database (114GB) with following file conent:
Datafiles:
d:\...\data\Axapta_data01.mdf - it is 87GB - it is in PRIMARY
Filegroup
f:\....\data\Axapta_data02.mdf - it is 27GB - also in PRIMARY
Filegroup
Logfile:
e:\...\log\Axapta_log02.ldf - 20 GB - none file group since it's
transactionlog file
Unfortunately does this application perform not well with this file
config (known problem within the Axapta enviroment)
So my issue is now following:
How can I move this database to a new server, and create only one
datafile?
I would like this new file config:
d:\....\data\Axapta_data01.mdf - 114GB - Primary filegroup
e:\....\log\axapta_log.ldf
Can anyone explain to me if it is possible, and then... how?
Thanks in advance...
Regards
TorgeirA restored database is exactly like the original so you can't change the
file configuration during the restore. If your plan is to move the database
using BACKP/RESTORE, you'll need to change the file configuration before the
backup or after the restore. To do this, expand the data01 file to the
desired size and then migrate data from data02 using DBCC
SHRINKFILE...EMPTYFILE. You can then remove the data02 file using ALTER
DATABASE.
Although it is not enforced, the file extension for secondary data files
should be 'ndf' rather than 'mdf'. The 'mdf' extension should be used only
for the primary data file in order to avoid later confusion.
--
Happy Holidays
Dan Guzman
SQL Server MVP
<dolerud@.start.no> wrote in message
news:1135896641.887437.220980@.g44g2000cwa.googlegroups.com...
> Hi,
> I do have a large Axapta database (114GB) with following file conent:
> Datafiles:
> d:\...\data\Axapta_data01.mdf - it is 87GB - it is in PRIMARY
> Filegroup
> f:\....\data\Axapta_data02.mdf - it is 27GB - also in PRIMARY
> Filegroup
> Logfile:
> e:\...\log\Axapta_log02.ldf - 20 GB - none file group since it's
> transactionlog file
> Unfortunately does this application perform not well with this file
> config (known problem within the Axapta enviroment)
> So my issue is now following:
> How can I move this database to a new server, and create only one
> datafile?
> I would like this new file config:
> d:\....\data\Axapta_data01.mdf - 114GB - Primary filegroup
> e:\....\log\axapta_log.ldf
> Can anyone explain to me if it is possible, and then... how?
> Thanks in advance...
> Regards
> Torgeir
>|||Hi, and thank you for your quick response!
I assume I also can do this operation after I have moved the database
to a new server.
The existing server do not have enought diskspace.
Regards
Torgeir|||Yes, you can consolidate the files after moving to the new server.
--
Happy Holidays
Dan Guzman
SQL Server MVP
<dolerud@.start.no> wrote in message
news:1135898358.019743.33740@.f14g2000cwb.googlegroups.com...
> Hi, and thank you for your quick response!
> I assume I also can do this operation after I have moved the database
> to a new server.
> The existing server do not have enought diskspace.
> Regards
> Torgeir
>
I do have a large Axapta database (114GB) with following file conent:
Datafiles:
d:\...\data\Axapta_data01.mdf - it is 87GB - it is in PRIMARY
Filegroup
f:\....\data\Axapta_data02.mdf - it is 27GB - also in PRIMARY
Filegroup
Logfile:
e:\...\log\Axapta_log02.ldf - 20 GB - none file group since it's
transactionlog file
Unfortunately does this application perform not well with this file
config (known problem within the Axapta enviroment)
So my issue is now following:
How can I move this database to a new server, and create only one
datafile?
I would like this new file config:
d:\....\data\Axapta_data01.mdf - 114GB - Primary filegroup
e:\....\log\axapta_log.ldf
Can anyone explain to me if it is possible, and then... how?
Thanks in advance...
Regards
TorgeirA restored database is exactly like the original so you can't change the
file configuration during the restore. If your plan is to move the database
using BACKP/RESTORE, you'll need to change the file configuration before the
backup or after the restore. To do this, expand the data01 file to the
desired size and then migrate data from data02 using DBCC
SHRINKFILE...EMPTYFILE. You can then remove the data02 file using ALTER
DATABASE.
Although it is not enforced, the file extension for secondary data files
should be 'ndf' rather than 'mdf'. The 'mdf' extension should be used only
for the primary data file in order to avoid later confusion.
--
Happy Holidays
Dan Guzman
SQL Server MVP
<dolerud@.start.no> wrote in message
news:1135896641.887437.220980@.g44g2000cwa.googlegroups.com...
> Hi,
> I do have a large Axapta database (114GB) with following file conent:
> Datafiles:
> d:\...\data\Axapta_data01.mdf - it is 87GB - it is in PRIMARY
> Filegroup
> f:\....\data\Axapta_data02.mdf - it is 27GB - also in PRIMARY
> Filegroup
> Logfile:
> e:\...\log\Axapta_log02.ldf - 20 GB - none file group since it's
> transactionlog file
> Unfortunately does this application perform not well with this file
> config (known problem within the Axapta enviroment)
> So my issue is now following:
> How can I move this database to a new server, and create only one
> datafile?
> I would like this new file config:
> d:\....\data\Axapta_data01.mdf - 114GB - Primary filegroup
> e:\....\log\axapta_log.ldf
> Can anyone explain to me if it is possible, and then... how?
> Thanks in advance...
> Regards
> Torgeir
>|||Hi, and thank you for your quick response!
I assume I also can do this operation after I have moved the database
to a new server.
The existing server do not have enought diskspace.
Regards
Torgeir|||Yes, you can consolidate the files after moving to the new server.
--
Happy Holidays
Dan Guzman
SQL Server MVP
<dolerud@.start.no> wrote in message
news:1135898358.019743.33740@.f14g2000cwb.googlegroups.com...
> Hi, and thank you for your quick response!
> I assume I also can do this operation after I have moved the database
> to a new server.
> The existing server do not have enought diskspace.
> Regards
> Torgeir
>
Restore a database with 2 datafiles into one datafile - possible?
Hi,
I do have a large Axapta database (114GB) with following file conent:
Datafiles:
d:\...\data\Axapta_data01.mdf - it is 87GB - it is in PRIMARY
Filegroup
f:\....\data\Axapta_data02.mdf - it is 27GB - also in PRIMARY
Filegroup
Logfile:
e:\...\log\Axapta_log02.ldf - 20 GB - none file group since it's
transactionlog file
Unfortunately does this application perform not well with this file
config (known problem within the Axapta enviroment)
So my issue is now following:
How can I move this database to a new server, and create only one
datafile?
I would like this new file config:
d:\....\data\Axapta_data01.mdf - 114GB - Primary filegroup
e:\....\log\axapta_log.ldf
Can anyone explain to me if it is possible, and then... how?
Thanks in advance...
Regards
TorgeirA restored database is exactly like the original so you can't change the
file configuration during the restore. If your plan is to move the database
using BACKP/RESTORE, you'll need to change the file configuration before the
backup or after the restore. To do this, expand the data01 file to the
desired size and then migrate data from data02 using DBCC
SHRINKFILE...EMPTYFILE. You can then remove the data02 file using ALTER
DATABASE.
Although it is not enforced, the file extension for secondary data files
should be 'ndf' rather than 'mdf'. The 'mdf' extension should be used only
for the primary data file in order to avoid later confusion.
Happy Holidays
Dan Guzman
SQL Server MVP
<dolerud@.start.no> wrote in message
news:1135896641.887437.220980@.g44g2000cwa.googlegroups.com...
> Hi,
> I do have a large Axapta database (114GB) with following file conent:
> Datafiles:
> d:\...\data\Axapta_data01.mdf - it is 87GB - it is in PRIMARY
> Filegroup
> f:\....\data\Axapta_data02.mdf - it is 27GB - also in PRIMARY
> Filegroup
> Logfile:
> e:\...\log\Axapta_log02.ldf - 20 GB - none file group since it's
> transactionlog file
> Unfortunately does this application perform not well with this file
> config (known problem within the Axapta enviroment)
> So my issue is now following:
> How can I move this database to a new server, and create only one
> datafile?
> I would like this new file config:
> d:\....\data\Axapta_data01.mdf - 114GB - Primary filegroup
> e:\....\log\axapta_log.ldf
> Can anyone explain to me if it is possible, and then... how?
> Thanks in advance...
> Regards
> Torgeir
>|||Hi, and thank you for your quick response!
I assume I also can do this operation after I have moved the database
to a new server.
The existing server do not have enought diskspace.
Regards
Torgeir|||Yes, you can consolidate the files after moving to the new server.
Happy Holidays
Dan Guzman
SQL Server MVP
<dolerud@.start.no> wrote in message
news:1135898358.019743.33740@.f14g2000cwb.googlegroups.com...
> Hi, and thank you for your quick response!
> I assume I also can do this operation after I have moved the database
> to a new server.
> The existing server do not have enought diskspace.
> Regards
> Torgeir
>
I do have a large Axapta database (114GB) with following file conent:
Datafiles:
d:\...\data\Axapta_data01.mdf - it is 87GB - it is in PRIMARY
Filegroup
f:\....\data\Axapta_data02.mdf - it is 27GB - also in PRIMARY
Filegroup
Logfile:
e:\...\log\Axapta_log02.ldf - 20 GB - none file group since it's
transactionlog file
Unfortunately does this application perform not well with this file
config (known problem within the Axapta enviroment)
So my issue is now following:
How can I move this database to a new server, and create only one
datafile?
I would like this new file config:
d:\....\data\Axapta_data01.mdf - 114GB - Primary filegroup
e:\....\log\axapta_log.ldf
Can anyone explain to me if it is possible, and then... how?
Thanks in advance...
Regards
TorgeirA restored database is exactly like the original so you can't change the
file configuration during the restore. If your plan is to move the database
using BACKP/RESTORE, you'll need to change the file configuration before the
backup or after the restore. To do this, expand the data01 file to the
desired size and then migrate data from data02 using DBCC
SHRINKFILE...EMPTYFILE. You can then remove the data02 file using ALTER
DATABASE.
Although it is not enforced, the file extension for secondary data files
should be 'ndf' rather than 'mdf'. The 'mdf' extension should be used only
for the primary data file in order to avoid later confusion.
Happy Holidays
Dan Guzman
SQL Server MVP
<dolerud@.start.no> wrote in message
news:1135896641.887437.220980@.g44g2000cwa.googlegroups.com...
> Hi,
> I do have a large Axapta database (114GB) with following file conent:
> Datafiles:
> d:\...\data\Axapta_data01.mdf - it is 87GB - it is in PRIMARY
> Filegroup
> f:\....\data\Axapta_data02.mdf - it is 27GB - also in PRIMARY
> Filegroup
> Logfile:
> e:\...\log\Axapta_log02.ldf - 20 GB - none file group since it's
> transactionlog file
> Unfortunately does this application perform not well with this file
> config (known problem within the Axapta enviroment)
> So my issue is now following:
> How can I move this database to a new server, and create only one
> datafile?
> I would like this new file config:
> d:\....\data\Axapta_data01.mdf - 114GB - Primary filegroup
> e:\....\log\axapta_log.ldf
> Can anyone explain to me if it is possible, and then... how?
> Thanks in advance...
> Regards
> Torgeir
>|||Hi, and thank you for your quick response!
I assume I also can do this operation after I have moved the database
to a new server.
The existing server do not have enought diskspace.
Regards
Torgeir|||Yes, you can consolidate the files after moving to the new server.
Happy Holidays
Dan Guzman
SQL Server MVP
<dolerud@.start.no> wrote in message
news:1135898358.019743.33740@.f14g2000cwb.googlegroups.com...
> Hi, and thank you for your quick response!
> I assume I also can do this operation after I have moved the database
> to a new server.
> The existing server do not have enought diskspace.
> Regards
> Torgeir
>
Subscribe to:
Posts (Atom)