Asp.net / sql help: March 2012

Thursday, March 29, 2012

Attach norecovery database

Is there a way to attach a database to norecovery mode? We have a 2nd site
where the target database is in norecovery mode for continuous log files.
If we detach the database while it is in norecovery mode, could we attach
the database back in norecovery mode. I tried it and it turned into
recovery mode with few transactions rollback while detach. Thanks!
When you detach a db it rolls back any open trans so the answer is no. You
can attach it but it it will already have been put into recovery.
Andrew J. Kelly SQL MVP
"Kevin" <Kevin@.noemail.com> wrote in message
news:OWajj3qmEHA.1288@.TK2MSFTNGP10.phx.gbl...
> Is there a way to attach a database to norecovery mode? We have a 2nd
site
> where the target database is in norecovery mode for continuous log files.
> If we detach the database while it is in norecovery mode, could we attach
> the database back in norecovery mode. I tried it and it turned into
> recovery mode with few transactions rollback while detach. Thanks!
>
>

Attach norecovery database

Is there a way to attach a database to norecovery mode? We have a 2nd site
where the target database is in norecovery mode for continuous log files.
If we detach the database while it is in norecovery mode, could we attach
the database back in norecovery mode. I tried it and it turned into
recovery mode with few transactions rollback while detach. Thanks!When you detach a db it rolls back any open trans so the answer is no. You
can attach it but it it will already have been put into recovery.
--
Andrew J. Kelly SQL MVP
"Kevin" <Kevin@.noemail.com> wrote in message
news:OWajj3qmEHA.1288@.TK2MSFTNGP10.phx.gbl...
> Is there a way to attach a database to norecovery mode? We have a 2nd
site
> where the target database is in norecovery mode for continuous log files.
> If we detach the database while it is in norecovery mode, could we attach
> the database back in norecovery mode. I tried it and it turned into
> recovery mode with few transactions rollback while detach. Thanks!
>
>sql

Attach network database Sql Server Express

I am trying to attach a network database to my sql server express

After some reading I "Enabled" tcp/ip, named pipes, and shared memory in sql server confiuration manager.

But when I go to "Attach Database" in Sql Express managemnent studio. It does not show the network drives much less allow me to attach anything on a network drive.

What am I missing here?

If I install sql server on the network machine will my local Sql express recognize it?

Database files on network shares are not supported.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||You can also see http://support.microsoft.com/kb/304261.

Attach MySql database via internet

Hie !

Please, how to manage (attach,connect to) MySql database located on host server http://www.avanti.si from my home PC with Microsoft SQL Server Management Studio Express? This "http://www.avanti.si:8080\MySqlDatabaseName,3306" fail !

Thenks!

hi,

you should ask you provider about connection info..

assuming the "server" is named and available as "www.avanti.si" and 3306 is the port you have to use to connect to, you should provide that info in the SQL Server MAnagement Studio Express connection dialog as

www.avanti.si,3306
in the server property, and provide your SQL Server standard login and pwd.. in the "options" tab you can, but is not mandatory as the <default> value will be resolved as tcp/ip, set the network protocol as tcp/ip and specify the "connect to database" property to "MySqlDatabase", but this one should be resolved as well without your intervention becouse you should be usually only grated db access to your db, MySqlDatabase...

you have to verify the www.avanti.si is the actual SQL Server instance name as well, and I doubt about it..

regards

|||

Actually, you can't connect directly to MySQL database, Management Studio is designed for use with SQL Server. You can create a linked server to any database that offers an OLEDB provider, which I believe MySQL has. The linked server will allow you to query your data from MySQL, but not manage the database.

Regards,

Mike

|||

ooppss...

did not notice/intended MySqlDatabase as a "MySql.. database"

thank you Mike

attach multiply databases

This summary is not available. Please click here to view the post.

attach multiply databases

This summary is not available. Please click here to view the post.

attach IDENTITY property to an existing column

Hi All,
Can any body tell me that how we can attach IDENTITY property to an existing int columnyou can not.

you have to...

1. rename the existing table
2. drop the foriegn keys referencing it.
3. drop the constraints on the renamed table.
4. define a new table with the identity property with the orginal table name
--the next 2 can go in any order based on what you decide is best
5. then reapply all constraints on the table, usually doing the primary key first.
6. insert data from the renamed table to the new table. you may want to do a INSERT IDENTITY MyTable ON to insert your existing keys. Just do not forget to turn it off when you are done.
7. drop the renamed table (optional)

you may want to wrap all or part of this in an explicit transaction with appropriate error handlers and print messages and you may not want to drop renamed table until you have committed the transaction without error and your row count after the insert matches the rowcount in the new table.

attach files to new box

sql2k sp3
Is there a way to attach the files of a db to a newly
created box? No backups as these are QA db's not
production.
TIA, ChrisR
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:c6fe01c48a03$56389420$a401280a@.phx.gbl...
> sql2k sp3
> Is there a way to attach the files of a db to a newly
> created box? No backups as these are QA db's not
> production.
Assuming that these files are now in use by SQL Server there are several
methods. You can detach the database, copy the *.mdf & *.ldf file to the new
server and reattach the files. Or, use the BACKUP command, then RESTORE on
your target server.
The following reference covers your options fairly well:
http://support.microsoft.com/default...b;en-us;314546
Steve

attach files to new box

sql2k sp3
Is there a way to attach the files of a db to a newly
created box? No backups as these are QA db's not
production.
TIA, ChrisR"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:c6fe01c48a03$56389420$a401280a@.phx.gbl...
> sql2k sp3
> Is there a way to attach the files of a db to a newly
> created box? No backups as these are QA db's not
> production.
Assuming that these files are now in use by SQL Server there are several
methods. You can detach the database, copy the *.mdf & *.ldf file to the new
server and reattach the files. Or, use the BACKUP command, then RESTORE on
your target server.
The following reference covers your options fairly well:
http://support.microsoft.com/defaul...kb;en-us;314546
Steve

attach files to new box

sql2k sp3
Is there a way to attach the files of a db to a newly
created box? No backups as these are QA db's not
production.
TIA, ChrisR"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:c6fe01c48a03$56389420$a401280a@.phx.gbl...
> sql2k sp3
> Is there a way to attach the files of a db to a newly
> created box? No backups as these are QA db's not
> production.
Assuming that these files are now in use by SQL Server there are several
methods. You can detach the database, copy the *.mdf & *.ldf file to the new
server and reattach the files. Or, use the BACKUP command, then RESTORE on
your target server.
The following reference covers your options fairly well:
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
Steve

Attach failed

I moved the files to free-up some space and now when I try to attach the
files I am getting this error. How do you troubleshoot this and what is the
caused of it?
Thank you.
Server: Msg 5184, Level 16, State 2, Line 1
Cannot use file 'L:\mssql\data\InterDTA_Data' for clustered server.
Only formatted files on which the cluster resource of the server has a
dependency can be used.
Your drive L: is not part of the clustered SQL group. Either place the file
on a clustered drive on which SQL Server is a dependent, or make the L:
drive a dependency.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"J Pacquiao" <JPacquiao@.discussions.microsoft.com> wrote in message
news:E1DE2C43-8554-402E-9699-27865351614D@.microsoft.com...
I moved the files to free-up some space and now when I try to attach the
files I am getting this error. How do you troubleshoot this and what is the
caused of it?
Thank you.
Server: Msg 5184, Level 16, State 2, Line 1
Cannot use file 'L:\mssql\data\InterDTA_Data' for clustered server.
Only formatted files on which the cluster resource of the server has a
dependency can be used.
sql

Attach failed

I moved the files to free-up some space and now when I try to attach the
files I am getting this error. How do you troubleshoot this and what is the
caused of it?
Thank you.
Server: Msg 5184, Level 16, State 2, Line 1
Cannot use file 'L:\mssql\data\InterDTA_Data' for clustered server.
Only formatted files on which the cluster resource of the server has a
dependency can be used.Your drive L: is not part of the clustered SQL group. Either place the file
on a clustered drive on which SQL Server is a dependent, or make the L:
drive a dependency.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"J Pacquiao" <JPacquiao@.discussions.microsoft.com> wrote in message
news:E1DE2C43-8554-402E-9699-27865351614D@.microsoft.com...
I moved the files to free-up some space and now when I try to attach the
files I am getting this error. How do you troubleshoot this and what is the
caused of it?
Thank you.
Server: Msg 5184, Level 16, State 2, Line 1
Cannot use file 'L:\mssql\data\InterDTA_Data' for clustered server.
Only formatted files on which the cluster resource of the server has a
dependency can be used.

Attach failed

I moved the files to free-up some space and now when I try to attach the
files I am getting this error. How do you troubleshoot this and what is the
caused of it?
Thank you.
Server: Msg 5184, Level 16, State 2, Line 1
Cannot use file 'L:\mssql\data\InterDTA_Data' for clustered server.
Only formatted files on which the cluster resource of the server has a
dependency can be used.Your drive L: is not part of the clustered SQL group. Either place the file
on a clustered drive on which SQL Server is a dependent, or make the L:
drive a dependency.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"J Pacquiao" <JPacquiao@.discussions.microsoft.com> wrote in message
news:E1DE2C43-8554-402E-9699-27865351614D@.microsoft.com...
I moved the files to free-up some space and now when I try to attach the
files I am getting this error. How do you troubleshoot this and what is the
caused of it?
Thank you.
Server: Msg 5184, Level 16, State 2, Line 1
Cannot use file 'L:\mssql\data\InterDTA_Data' for clustered server.
Only formatted files on which the cluster resource of the server has a
dependency can be used.

attach error in sql server express management studio

tried to attach a database in sse-ms, and for some reason the database name is changing to G:\someFolder\some\Folder\db.mdf, even tho i dont have a drive g:,

during "attach as" screens inside sse-ms, , ie attach>add> then select the database the mdf loction is what it is suppose to be ie C:\inetpub\wwwroot\app_data\chp11\wroxunited.mdf but the database name in the same screen shot is G:\someOtherFolderAndPath\wroxunited.mdf and the attach as is also the G:\someOtherFolderAndPath\wroxunited.mdf.

pretty confused, could use a clue, thanks

my data entry error above, the mdf locattion in the sse-ms attach area/screen is C:\inetpub\wwwroot\chp11\app_data\wroxunited.mdf, the rest of the intial post is ok tho,

anyone know if the log file (ldf) is causing this problem, chp11 came in a download form the wrox book site btw.

|||hmm found a work around, ie just deleted that database from within sse-ms, had a backup of the mdf so wasnt to worried, anyway after deleting the mdf that had the G:\WrongPath\db.mdf, i was able to attach the desired database and the mdf location, the database name and the attach as are now all correct, still kinda interested in what caused that but i am

now, thanks people, have a lot to learn about sql and permission tho,

Attach Error

I get the below error when trying to attach a database ONLY on my SQL 2000
computers. SQL 2005 attaches the file just fine. How can I run the
DBCC-CHECKTABLE command on the database if I can't attach it on my sql 2000
pc's? The database is 2000 format.
ERROR:
error 602: could not find row in sysindexes for database id 15, object ID 1,
index ID 1. Run DBCC-CHECKTABLE on sysindexes.
Pls post the error you get?
also read about sp_attach_single_file_db
scott wrote:
>I get the below error when trying to attach a database ONLY on my SQL 2000
>computers. SQL 2005 attaches the file just fine. How can I run the
>DBCC-CHECKTABLE command on the database if I can't attach it on my sql 2000
>pc's? The database is 2000 format.
>ERROR:
>error 602: could not find row in sysindexes for database id 15, object ID 1,
>index ID 1. Run DBCC-CHECKTABLE on sysindexes.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200603/1
|||The error is:
error 602: could not find row in sysindexes for database id 15, object ID 1,
index ID 1. Run DBCC-CHECKTABLE on sysindexes.
"ranjitjain via droptable.com" <u16198@.uwe> wrote in message
news:5cee810f493ea@.uwe...
> Pls post the error you get?
> also read about sp_attach_single_file_db
> scott wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200603/1

Attach error

I plan to move my database file to another folder, I detach it first (successfull), but I can't move (Cut&Paste) my database to another folder (Access is denied), And then I go back object explorer but error when I try to Attach

"Failed to retrieve data for this request (Microsoft.SQLServer.SmoEnum)...... CREATE FILE encountered operating system error 5 (Access is denied)"

please tell me how to attach my database, I really need the data..

thx..

Hi,

you will have to logon with a user who has access to the file first. If you don′t have any, you can logon on SSMS with a SQL Server login (if enabled) and attach the database. The SQL Server Account will be used then for attaching the database whic apparantly had the access to the database.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.desql

Attach Error

Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.

If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box.

I get this error when I try to attcah a Database file sitting in "My Documents" folder.

Any help will be appreciated

--Thanks

Have you verified that the file actually exists, and that there is no misspelling in the path, and all else that the message tells you to verify?

If all that is ok, maybe try to enclose the path in double quotes. (in case it's the spacing that gives you trouble.
"C:\My Documents\myfile.dat"

/Kenneth

Attach Error

I get the below error when trying to attach a database ONLY on my SQL 2000
computers. SQL 2005 attaches the file just fine. How can I run the
DBCC-CHECKTABLE command on the database if I can't attach it on my sql 2000
pc's? The database is 2000 format.
ERROR:
error 602: could not find row in sysindexes for database id 15, object ID 1,
index ID 1. Run DBCC-CHECKTABLE on sysindexes.Pls post the error you get?
also read about sp_attach_single_file_db
scott wrote:
>I get the below error when trying to attach a database ONLY on my SQL 2000
>computers. SQL 2005 attaches the file just fine. How can I run the
>DBCC-CHECKTABLE command on the database if I can't attach it on my sql 2000
>pc's? The database is 2000 format.
>ERROR:
>error 602: could not find row in sysindexes for database id 15, object ID 1,
>index ID 1. Run DBCC-CHECKTABLE on sysindexes.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1|||The error is:
error 602: could not find row in sysindexes for database id 15, object ID 1,
index ID 1. Run DBCC-CHECKTABLE on sysindexes.
"ranjitjain via SQLMonster.com" <u16198@.uwe> wrote in message
news:5cee810f493ea@.uwe...
> Pls post the error you get?
> also read about sp_attach_single_file_db
> scott wrote:
>>I get the below error when trying to attach a database ONLY on my SQL 2000
>>computers. SQL 2005 attaches the file just fine. How can I run the
>>DBCC-CHECKTABLE command on the database if I can't attach it on my sql
>>2000
>>pc's? The database is 2000 format.
>>ERROR:
>>error 602: could not find row in sysindexes for database id 15, object ID
>>1,
>>index ID 1. Run DBCC-CHECKTABLE on sysindexes.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1

Attach Error

I get the below error when trying to attach a database ONLY on my SQL 2000
computers. SQL 2005 attaches the file just fine. How can I run the
DBCC-CHECKTABLE command on the database if I can't attach it on my sql 2000
pc's? The database is 2000 format.
ERROR:
error 602: could not find row in sysindexes for database id 15, object ID 1,
index ID 1. Run DBCC-CHECKTABLE on sysindexes.Pls post the error you get?
also read about sp_attach_single_file_db
scott wrote:
>I get the below error when trying to attach a database ONLY on my SQL 2000
>computers. SQL 2005 attaches the file just fine. How can I run the
>DBCC-CHECKTABLE command on the database if I can't attach it on my sql 2000
>pc's? The database is 2000 format.
>ERROR:
>error 602: could not find row in sysindexes for database id 15, object ID 1
,
>index ID 1. Run DBCC-CHECKTABLE on sysindexes.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1|||The error is:
error 602: could not find row in sysindexes for database id 15, object ID 1,
index ID 1. Run DBCC-CHECKTABLE on sysindexes.
"ranjitjain via droptable.com" <u16198@.uwe> wrote in message
news:5cee810f493ea@.uwe...
> Pls post the error you get?
> also read about sp_attach_single_file_db
> scott wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200603/1

Attach e Detach database

Maybe this a stupid question, but is there any differences beetwen detach
and attach a database from Enterprise Manager (All Tasks-Detach/Attach) or
with the system stored procedures ?
e.g.
if I look at KB 224071
http://support.microsoft.com/support/kb/articles/q224/0/71.asp (Moving SQL
Server Databases to a New Location with Detach/Attach) the articole said to
detach the database as follows :
use master
go
sp_detach_db 'mydb'
go
and to re-attach the database as follows :
use master
go
sp_attach_db 'mydb','...','...'
go
I think it's more simple to detach and attach the database from Enterprise
Manager than to write the stored procedure ...
Thanks,
Michele L.EA justs acts as a nice GUI. In the backgroud it actually
runs the sp_detach_db, sp_attach_db.
So your answer is - no not really ;)
J
>--Original Message--
>Maybe this a stupid question, but is there any
differences beetwen detach
>and attach a database from Enterprise Manager (All Tasks-
Detach/Attach) or
>with the system stored procedures ?
>e.g.
>if I look at KB 224071
>http://support.microsoft.com/support/kb/articles/q224/0/71
.asp (Moving SQL
>Server Databases to a New Location with Detach/Attach)
the articole said to
>detach the database as follows :
>use master
>go
>sp_detach_db 'mydb'
>go
>and to re-attach the database as follows :
>use master
>go
>sp_attach_db 'mydb','...','...'
>go
>I think it's more simple to detach and attach the
database from Enterprise
>Manager than to write the stored procedure ...
>Thanks,
>Michele L.
>
>
>
>
>.
>

Attach Detach System Databases

Hi. We are migrating databases from a server with
Standard edition (2000) to Enterprise Edition (2000).
Trying to find out if there is any difference in system
databases between these versions. For example, can we
move the master database from the Standard instance to
the Enterprise instance without any hangups. Thanks.
You can move the system databases from one Edition of SQL Server to another
Edition.
Rand
This posting is provided "as is" with no warranties and confers no rights.
sql

Attach Detach System Databases

Hi. We are migrating databases from a server with
Standard edition (2000) to Enterprise Edition (2000).
Trying to find out if there is any difference in system
databases between these versions. For example, can we
move the master database from the Standard instance to
the Enterprise instance without any hangups. Thanks.You can move the system databases from one Edition of SQL Server to another
Edition.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Attach Detach System Databases

Hi. We are migrating databases from a server with
Standard edition (2000) to Enterprise Edition (2000).
Trying to find out if there is any difference in system
databases between these versions. For example, can we
move the master database from the Standard instance to
the Enterprise instance without any hangups. Thanks.You can move the system databases from one Edition of SQL Server to another
Edition.
Rand
This posting is provided "as is" with no warranties and confers no rights.

attach detach db

Is it possible detaching one db coping under/ different names /master and lof
files and reattaching db again to cause login problems later. if yes why?Not sure I understand the question but the answer is most likely in one of
these:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
--
Andrew J. Kelly SQL MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
> Is it possible detaching one db coping under/ different names /master and
> lof
> files and reattaching db again to cause login problems later. if yes why?
>|||Hi,
If you are attaching the database with different name in same server, you
will never get any Login issue. If you are attaching into
a different server then you have syncronize the Logins and users using the
system procedure SP_CHANGE_USERS_LOGIN (see Books online)
Thanks
Hari
SQL Server MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
> Is it possible detaching one db coping under/ different names /master and
> lof
> files and reattaching db again to cause login problems later. if yes why?
>|||Hi Hary,
thanks for your reply. Let me try to explain the situation I am in. I had to
make copy for training purposes of a production db. I detached prod db and
copied mdf and ldf in the same data directory but with names test.mdf and
test.ldf. Then I attached our prod db and new test db. During attaching I had
to choose dbowner (domain admin). It seemed to me that i saved time using
this teqniche. But later users reported that they couldnt log in prod and
test db via the application (its in citrix). After short phone talk with
vendors they explained that detaching and attaching caused dbo alias
deleting. They have app login that use dbo alias. I ran sp_addalias 'app,
'dbo' and everithing looks fine with prod db. But its impossible to use test
db. Users that are in sys admin group dont have problem but ordinary users
that connect to applications db via app user cannot login at all. I wonder
whats the problem? all ordinary users are stored in users table in prod db
and have encrypted passwords. app user password is encrypted and stored in
different db.
i will be dismissed : )
"Hari Prasad" wrote:
> Hi,
> If you are attaching the database with different name in same server, you
> will never get any Login issue. If you are attaching into
> a different server then you have syncronize the Logins and users using the
> system procedure SP_CHANGE_USERS_LOGIN (see Books online)
> Thanks
> Hari
> SQL Server MVP
>
> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
> news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
> > Is it possible detaching one db coping under/ different names /master and
> > lof
> > files and reattaching db again to cause login problems later. if yes why?
> >
>
>|||Hi Hary,
thanks for your reply. Let me try to explain the situation I am in. I had to
make copy for training purposes of a production db. I detached prod db and
copied mdf and ldf in the same data directory but with names test.mdf and
test.ldf. Then I attached our prod db and new test db. During attaching I had
to choose dbowner (domain admin). It seemed to me that i saved time using
this teqniche. But later users reported that they couldnt log in prod and
test db via the application (its in citrix). After short phone talk with
vendors they explained that detaching and attaching caused dbo alias
deleting. They have app login that use dbo alias. I ran sp_addalias 'app,
'dbo' and everithing looks fine with prod db. But its impossible to use test
db. Users that are in sys admin group dont have problem but ordinary users
that connect to applications db via app user cannot login at all. I wonder
whats the problem? all ordinary users are stored in users table in prod db
and have encrypted passwords. app user password is encrypted and stored in
different db.
i will be dismissed : )
"Andrew J. Kelly" wrote:
> Not sure I understand the question but the answer is most likely in one of
> these:
>
> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
> http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
> system DB's
> http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
> http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
> to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
> Restore
> http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
> Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> users
> http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
> Restoring a .mdf
> --
> Andrew J. Kelly SQL MVP
>
> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
> news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
> > Is it possible detaching one db coping under/ different names /master and
> > lof
> > files and reattaching db again to cause login problems later. if yes why?
> >
>
>|||Then you should look at the links I posted. In particular this first one
should be most helpful:
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
--
Andrew J. Kelly SQL MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:62CB085D-5980-4776-8A35-DEAC178F2AEB@.microsoft.com...
> Hi Hary,
> thanks for your reply. Let me try to explain the situation I am in. I had
> to
> make copy for training purposes of a production db. I detached prod db and
> copied mdf and ldf in the same data directory but with names test.mdf and
> test.ldf. Then I attached our prod db and new test db. During attaching I
> had
> to choose dbowner (domain admin). It seemed to me that i saved time using
> this teqniche. But later users reported that they couldnt log in prod and
> test db via the application (its in citrix). After short phone talk with
> vendors they explained that detaching and attaching caused dbo alias
> deleting. They have app login that use dbo alias. I ran sp_addalias 'app,
> 'dbo' and everithing looks fine with prod db. But its impossible to use
> test
> db. Users that are in sys admin group dont have problem but ordinary users
> that connect to applications db via app user cannot login at all. I wonder
> whats the problem? all ordinary users are stored in users table in prod db
> and have encrypted passwords. app user password is encrypted and stored in
> different db.
> i will be dismissed : )
>
> "Andrew J. Kelly" wrote:
>> Not sure I understand the question but the answer is most likely in one
>> of
>> these:
>>
>> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
>> http://www.databasejournal.com/features/mssql/article.php/3379901
>> Moving
>> system DB's
>> http://www.support.microsoft.com/?id=314546 Moving DB's between
>> Servers
>> http://www.support.microsoft.com/?id=224071 Moving SQL Server
>> Databases
>> to a New Location with Detach/Attach
>> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
>> Restore
>> http://www.support.microsoft.com/?id=246133 How To Transfer Logins
>> and
>> Passwords Between SQL Servers
>> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs
>> after a
>> Restore
>> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
>> users
>> http://www.support.microsoft.com/?id=168001 User Logon and/or
>> Permission
>> Errors After Restoring Dump
>> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
>> Issues When a Database Is Moved Between SQL Servers
>> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
>> Restoring a .mdf
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
>> news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
>> > Is it possible detaching one db coping under/ different names /master
>> > and
>> > lof
>> > files and reattaching db again to cause login problems later. if yes
>> > why?
>> >
>>|||Thanx : ) I hope this will help. Just curios if EE could have the same login
problems. May be will be better to upgrade to EE
"Andrew J. Kelly" wrote:
> Then you should look at the links I posted. In particular this first one
> should be most helpful:
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
> Restore
> http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> --
> Andrew J. Kelly SQL MVP
>
> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
> news:62CB085D-5980-4776-8A35-DEAC178F2AEB@.microsoft.com...
> > Hi Hary,
> > thanks for your reply. Let me try to explain the situation I am in. I had
> > to
> > make copy for training purposes of a production db. I detached prod db and
> > copied mdf and ldf in the same data directory but with names test.mdf and
> > test.ldf. Then I attached our prod db and new test db. During attaching I
> > had
> > to choose dbowner (domain admin). It seemed to me that i saved time using
> > this teqniche. But later users reported that they couldnt log in prod and
> > test db via the application (its in citrix). After short phone talk with
> > vendors they explained that detaching and attaching caused dbo alias
> > deleting. They have app login that use dbo alias. I ran sp_addalias 'app,
> > 'dbo' and everithing looks fine with prod db. But its impossible to use
> > test
> > db. Users that are in sys admin group dont have problem but ordinary users
> > that connect to applications db via app user cannot login at all. I wonder
> > whats the problem? all ordinary users are stored in users table in prod db
> > and have encrypted passwords. app user password is encrypted and stored in
> > different db.
> >
> > i will be dismissed : )
> >
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Not sure I understand the question but the answer is most likely in one
> >> of
> >> these:
> >>
> >>
> >> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
> >> http://www.databasejournal.com/features/mssql/article.php/3379901
> >> Moving
> >> system DB's
> >> http://www.support.microsoft.com/?id=314546 Moving DB's between
> >> Servers
> >> http://www.support.microsoft.com/?id=224071 Moving SQL Server
> >> Databases
> >> to a New Location with Detach/Attach
> >> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
> >> Restore
> >> http://www.support.microsoft.com/?id=246133 How To Transfer Logins
> >> and
> >> Passwords Between SQL Servers
> >> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs
> >> after a
> >> Restore
> >> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> >> users
> >> http://www.support.microsoft.com/?id=168001 User Logon and/or
> >> Permission
> >> Errors After Restoring Dump
> >> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> >> Issues When a Database Is Moved Between SQL Servers
> >> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
> >> Restoring a .mdf
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
> >> news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
> >> > Is it possible detaching one db coping under/ different names /master
> >> > and
> >> > lof
> >> > files and reattaching db again to cause login problems later. if yes
> >> > why?
> >> >
> >>
> >>
> >>
>
>|||It would be the same for any edition.
--
Andrew J. Kelly SQL MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:B7FD797D-F1DB-41E4-B6D4-0BDA27B27153@.microsoft.com...
> Thanx : ) I hope this will help. Just curios if EE could have the same
> login
> problems. May be will be better to upgrade to EE
> "Andrew J. Kelly" wrote:
>> Then you should look at the links I posted. In particular this first one
>> should be most helpful:
>> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs
>> after a
>> Restore
>> http://www.support.microsoft.com/?id=168001 User Logon and/or
>> Permission
>> Errors After Restoring Dump
>> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
>> Issues When a Database Is Moved Between SQL Servers
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
>> news:62CB085D-5980-4776-8A35-DEAC178F2AEB@.microsoft.com...
>> > Hi Hary,
>> > thanks for your reply. Let me try to explain the situation I am in. I
>> > had
>> > to
>> > make copy for training purposes of a production db. I detached prod db
>> > and
>> > copied mdf and ldf in the same data directory but with names test.mdf
>> > and
>> > test.ldf. Then I attached our prod db and new test db. During attaching
>> > I
>> > had
>> > to choose dbowner (domain admin). It seemed to me that i saved time
>> > using
>> > this teqniche. But later users reported that they couldnt log in prod
>> > and
>> > test db via the application (its in citrix). After short phone talk
>> > with
>> > vendors they explained that detaching and attaching caused dbo alias
>> > deleting. They have app login that use dbo alias. I ran sp_addalias
>> > 'app,
>> > 'dbo' and everithing looks fine with prod db. But its impossible to use
>> > test
>> > db. Users that are in sys admin group dont have problem but ordinary
>> > users
>> > that connect to applications db via app user cannot login at all. I
>> > wonder
>> > whats the problem? all ordinary users are stored in users table in prod
>> > db
>> > and have encrypted passwords. app user password is encrypted and stored
>> > in
>> > different db.
>> >
>> > i will be dismissed : )
>> >
>> >
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Not sure I understand the question but the answer is most likely in
>> >> one
>> >> of
>> >> these:
>> >>
>> >>
>> >> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
>> >> http://www.databasejournal.com/features/mssql/article.php/3379901
>> >> Moving
>> >> system DB's
>> >> http://www.support.microsoft.com/?id=314546 Moving DB's between
>> >> Servers
>> >> http://www.support.microsoft.com/?id=224071 Moving SQL Server
>> >> Databases
>> >> to a New Location with Detach/Attach
>> >> http://support.microsoft.com/?id=221465 Using WITH MOVE in
>> >> a
>> >> Restore
>> >> http://www.support.microsoft.com/?id=246133 How To Transfer Logins
>> >> and
>> >> Passwords Between SQL Servers
>> >> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs
>> >> after a
>> >> Restore
>> >> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins
>> >> to
>> >> users
>> >> http://www.support.microsoft.com/?id=168001 User Logon and/or
>> >> Permission
>> >> Errors After Restoring Dump
>> >> http://www.support.microsoft.com/?id=240872 How to Resolve
>> >> Permission
>> >> Issues When a Database Is Moved Between SQL Servers
>> >> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
>> >> Restoring a .mdf
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
>> >> news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
>> >> > Is it possible detaching one db coping under/ different names
>> >> > /master
>> >> > and
>> >> > lof
>> >> > files and reattaching db again to cause login problems later. if yes
>> >> > why?
>> >> >
>> >>
>> >>
>> >>
>>sql

Attach DB Without Transaction Logs

Hi, there,

I have multiple transaction log files. I don't need the transaction logs and intended to remove them. I have tried detaching the database, rename the logs and trying to re-attach the database without the logs but to no avail. The error message mentioned something like file activation failed and SQL couldn't rebuild the transaction logs.

Is there a way to get rid of the transaction log files?

Thank you.

Regards,

Yong Hwee

Hi,

you can use sp_attach_single_file_db but it works only on databases that have a single log file.

When sp_attach_single_file_db attaches the database to the server, it builds a new log file. If the database is read-only, the log file is built in its previous location.

...

OR by reading this thread attaching DB without .ldf file ?

tosc

|||

Hi, Tosc,

Thank you for your immediate reply. It seems like there is no workaround for database with multiple log files.

Regards,

Yong Hwee

|||

There are lot of limitation with sp_attach_single_file_db. The database should not have morethan one LOG and Datafile and the database should be properly detached. So in your case this process will not do. You requirement is to remove the Secondary LOG File, if am correct. What you can do is, Remove the secondary log file first using

DBCC SHRINKFILE (,EMPTYFILE)
alter database test remove file .

Then you can trucate the primary log file to any size you want. Please take backup of the database before starting anything

Madhu

Refer : http://madhuottapalam.blogspot.com/search?q=Transaction+Log

Madhu

|||

In SQL Server 2005 you can you use CREATE DATABASE syntax to rebuild the log. The ATTACH_REBUILD_LOG syntax should work even if there are multiple log files.

|||

Hi, there,

Thank you for your reply. I managed to delete the log by first backup with option to truncate log after which I change the database to simple recovery. I can delete the logs this way.

Thank you.

Regards,

Yong Hwee

Attach db without transaction log file

Hi!
Is it possible attach db without transaction log file?
With store procedure like sp_attach_db and sp_attach_single_file_db it isn't go. Server still looking for transaction log file.
Thanks your helpHi,
You can use sp_attach_single_file_db only on databases that have a single
log file.
Instead try to update the database status to emergency mode and create a new
database and use DTS to transafer all objects to new database.
Steps:
1. Update the database to Emergency mode
update sysdatabases set status=32768 where name=<name of the database>
2. Craete a new database
3. Use DTS to transfer all objects
Thanks
Hari
MCDBA
"Ondrik" <anonymous@.discussions.microsoft.com> wrote in message
news:B3D1753B-C678-4A7E-AEFF-5FD7725FCC2C@.microsoft.com...
> Hi!
> Is it possible attach db without transaction log file?
> With store procedure like sp_attach_db and sp_attach_single_file_db it
isn't go. Server still looking for transaction log file.
> Thanks your help|||Try this:
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
--
Andrew J. Kelly
SQL Server MVP
"Ondrik" <anonymous@.discussions.microsoft.com> wrote in message
news:B3D1753B-C678-4A7E-AEFF-5FD7725FCC2C@.microsoft.com...
> Hi!
> Is it possible attach db without transaction log file?
> With store procedure like sp_attach_db and sp_attach_single_file_db it
isn't go. Server still looking for transaction log file.
> Thanks your help|||You must first: sp_detach_db
then: delete the ..._log.sfd
then: sp_attach_single_file_db
Bye
Uwe

Attach Db With Same Files As Existing Db

In SQL Server 2000, I would like to attach a Database that is the file of the Database on another system. I made a copy of my Client's Database at his site. Now I would like to attach it to my server and then copy the data from my Client's DB to mine.

The only problem is they have the same file names, so when I try to attach the copy I get an error message. Is there any way to change the name of the data file and log file that a database points to so that I can attach it to my Server?

Thanks in advance for your help!Look up RESTORE DATABASE in Books Online. You need to use the WITH MOVE option.

RESTORE DATABASE database
WITH
MOVE 'logical data file' TO 'somenewfile.mdf',
MOVE 'logical log file' TO 'somenewfile.ldf'

You can find the name of the files by using: RESTORE FILELISTONLY FROM DISK = 'file.bak'

Attach DB with corrupt .ldf?

Hey gang,

I've been trying to restore a DB from it's MDF and LDF all morning;
the catch? The LDF appears to be corrupt.

When I first started the SQL Server, the db in question was marked as
"Suspect'. I did some research on this and it has caused me to attempt
detaching, backing up, deleting the LDF, using ATTACH DB and even
sp_attach_single_file_db, and sp_add_data_file_recover_suspect_db.

Nothing works.

So, with an MDF and no LDF ... is it possible to recreate this
database somehow? Can I attach the MDF to another (empty) database to
retrieve it's contents? (Tried it, couldn't get it to work), can I
extract the contents of the MDF - even if it's just the objects and
not the data itself - in some capacity?

I've read in various places about attaching an MDF with no LDF and the
system will recreate the LDF as needed, but that doesn't work either.

Ideas are most, most, most welcome.[posted and mailed]

NF (natalyafaden@.bellsouth.net) writes:
> I've been trying to restore a DB from it's MDF and LDF all morning;
> the catch? The LDF appears to be corrupt.
> When I first started the SQL Server, the db in question was marked as
> "Suspect'. I did some research on this and it has caused me to attempt
> detaching, backing up, deleting the LDF, using ATTACH DB and even
> sp_attach_single_file_db, and sp_add_data_file_recover_suspect_db.
> Nothing works.
> So, with an MDF and no LDF ... is it possible to recreate this
> database somehow? Can I attach the MDF to another (empty) database to
> retrieve it's contents? (Tried it, couldn't get it to work), can I
> extract the contents of the MDF - even if it's just the objects and
> not the data itself - in some capacity?
> I've read in various places about attaching an MDF with no LDF and the
> system will recreate the LDF as needed, but that doesn't work either.

There is a way, but I am not going to post it, because it's a
path too dangerous.

My first advice is that you open a case with Microsoft support. Yes,
that will cost you an arm and a leg, but consider how many arms and
legs losing the data will cost you. They have the procedures to recover
as much as possible.

My second advice is to restore from a clean backup.

The way I know means building a log from nothing and at all, and lead
you into the MDF as it was when things went bad. You may find a very
good database. You may also find a database that is just a big mess,
because you got it mid-transaction. And the fact that the LDF is corrupt
is an indication of that. Corruption may exist both in the SQL Server
structures and in your own data.

And if you don't understand the essence of what I'm saying above, don't
ask any further questions, but get on the phone with Microsoft.

If you absolutely want to do this on your own as a last resort before
you throw the database away, drop me a mail.

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

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

attach db with a deleted log

Hello,
How can i attach a db where there is a log deleted.
When i want to attach the db, i cannot click the ok button because a
second log-file is missing.
Anyway to work around this?Don't use Enterprise Manager. Use sp_attach_db or
sp_attach_single_file_db. See Books Online for details and examples of
those commands.
Make sure you have a good backup of your MDF/LDF files before you try
to attach them.
David Portas
SQL Server MVP
--sql

Attach db w/no log file

I had a client run out of disk space because their transaction log grew too
large. They accidentally deleted the tran log (didn't go to Recycle bin
because of size), and their last good backup was from 3 weeks ago. If I try
to attach the database either through Enterprise Manager or QA it tells me
the physical file name for the ldf is incorrect. Is there a way to get
around this error? Can I restore the old backup to a different name and use
its ldf file? Where is the info stored that tells the system what log file
is associated with the database? Can I modify that somehow? Thanks!Look up sp_attach_single_file_db in BOL
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
>I had a client run out of disk space because their transaction log grew too
> large. They accidentally deleted the tran log (didn't go to Recycle bin
> because of size), and their last good backup was from 3 weeks ago. If I
> try
> to attach the database either through Enterprise Manager or QA it tells me
> the physical file name for the ldf is incorrect. Is there a way to get
> around this error? Can I restore the old backup to a different name and
> use
> its ldf file? Where is the info stored that tells the system what log
> file
> is associated with the database? Can I modify that somehow? Thanks!|||Thanks for the reply. I just tried this and it is still telling me that the
ldf file 'may be incorrect.' Can I reattach using the old log file? Or
would that potentially mess up my database? Thanks.
"Michael C#" wrote:
> Look up sp_attach_single_file_db in BOL
> "Roger" <Roger@.discussions.microsoft.com> wrote in message
> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
> >I had a client run out of disk space because their transaction log grew too
> > large. They accidentally deleted the tran log (didn't go to Recycle bin
> > because of size), and their last good backup was from 3 weeks ago. If I
> > try
> > to attach the database either through Enterprise Manager or QA it tells me
> > the physical file name for the ldf is incorrect. Is there a way to get
> > around this error? Can I restore the old backup to a different name and
> > use
> > its ldf file? Where is the info stored that tells the system what log
> > file
> > is associated with the database? Can I modify that somehow? Thanks!
>
>|||You can't add an unmacthing transaction log file to a database. The purpose of a transaction log is
amongst other things to support recovery. SQL Server does a check at startup that the lsn number for
the log records matches with what to expect for the database. If restore isn't a viable option, I
suggest you let MS Support help you. They might have a way to get the database back, in some shape.
And, make sure you give access to the system to people who might do these operations as well as have
a good backup strategy in the future. But I guess you know this by now. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...
> Thanks for the reply. I just tried this and it is still telling me that the
> ldf file 'may be incorrect.' Can I reattach using the old log file? Or
> would that potentially mess up my database? Thanks.
> "Michael C#" wrote:
>> Look up sp_attach_single_file_db in BOL
>> "Roger" <Roger@.discussions.microsoft.com> wrote in message
>> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
>> >I had a client run out of disk space because their transaction log grew too
>> > large. They accidentally deleted the tran log (didn't go to Recycle bin
>> > because of size), and their last good backup was from 3 weeks ago. If I
>> > try
>> > to attach the database either through Enterprise Manager or QA it tells me
>> > the physical file name for the ldf is incorrect. Is there a way to get
>> > around this error? Can I restore the old backup to a different name and
>> > use
>> > its ldf file? Where is the info stored that tells the system what log
>> > file
>> > is associated with the database? Can I modify that somehow? Thanks!
>>|||Check and see if it actually attached. If I recall correctly,
sp_attach_single_file_db will give a warning message when it doesn't find
the log file, but it should still attach successfully.
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...
> Thanks for the reply. I just tried this and it is still telling me that
> the
> ldf file 'may be incorrect.' Can I reattach using the old log file? Or
> would that potentially mess up my database? Thanks.
> "Michael C#" wrote:
>> Look up sp_attach_single_file_db in BOL
>> "Roger" <Roger@.discussions.microsoft.com> wrote in message
>> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
>> >I had a client run out of disk space because their transaction log grew
>> >too
>> > large. They accidentally deleted the tran log (didn't go to Recycle
>> > bin
>> > because of size), and their last good backup was from 3 weeks ago. If
>> > I
>> > try
>> > to attach the database either through Enterprise Manager or QA it tells
>> > me
>> > the physical file name for the ldf is incorrect. Is there a way to get
>> > around this error? Can I restore the old backup to a different name
>> > and
>> > use
>> > its ldf file? Where is the info stored that tells the system what log
>> > file
>> > is associated with the database? Can I modify that somehow? Thanks!
>>|||I double-checked and it is not attached. A few other users on a different
forum mentioned the same thing. Someone else mentioned an Emergency Mode and
a log re buid resolution they received from MS. Hopefully this will help.
Thank you for your responses.
"Michael C#" wrote:
> Check and see if it actually attached. If I recall correctly,
> sp_attach_single_file_db will give a warning message when it doesn't find
> the log file, but it should still attach successfully.
>
> "Roger" <Roger@.discussions.microsoft.com> wrote in message
> news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...
> > Thanks for the reply. I just tried this and it is still telling me that
> > the
> > ldf file 'may be incorrect.' Can I reattach using the old log file? Or
> > would that potentially mess up my database? Thanks.
> >
> > "Michael C#" wrote:
> >
> >> Look up sp_attach_single_file_db in BOL
> >>
> >> "Roger" <Roger@.discussions.microsoft.com> wrote in message
> >> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
> >> >I had a client run out of disk space because their transaction log grew
> >> >too
> >> > large. They accidentally deleted the tran log (didn't go to Recycle
> >> > bin
> >> > because of size), and their last good backup was from 3 weeks ago. If
> >> > I
> >> > try
> >> > to attach the database either through Enterprise Manager or QA it tells
> >> > me
> >> > the physical file name for the ldf is incorrect. Is there a way to get
> >> > around this error? Can I restore the old backup to a different name
> >> > and
> >> > use
> >> > its ldf file? Where is the info stored that tells the system what log
> >> > file
> >> > is associated with the database? Can I modify that somehow? Thanks!
> >>
> >>
> >>
>
>|||One more thing. Make sure the MDF file is not marked READ-ONLY by the
operating system (for instance, if you burned it on a CD, and copied it
back). If it is, make it read/write and try again. Hope you find a
resolution quickly.
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:00FC1F66-801D-4BC6-8C72-3884BBDDB250@.microsoft.com...
>I double-checked and it is not attached. A few other users on a different
> forum mentioned the same thing. Someone else mentioned an Emergency Mode
> and
> a log re buid resolution they received from MS. Hopefully this will help.
> Thank you for your responses.
> "Michael C#" wrote:
>> Check and see if it actually attached. If I recall correctly,
>> sp_attach_single_file_db will give a warning message when it doesn't find
>> the log file, but it should still attach successfully.
>>
>> "Roger" <Roger@.discussions.microsoft.com> wrote in message
>> news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...
>> > Thanks for the reply. I just tried this and it is still telling me
>> > that
>> > the
>> > ldf file 'may be incorrect.' Can I reattach using the old log file?
>> > Or
>> > would that potentially mess up my database? Thanks.
>> >
>> > "Michael C#" wrote:
>> >
>> >> Look up sp_attach_single_file_db in BOL
>> >>
>> >> "Roger" <Roger@.discussions.microsoft.com> wrote in message
>> >> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
>> >> >I had a client run out of disk space because their transaction log
>> >> >grew
>> >> >too
>> >> > large. They accidentally deleted the tran log (didn't go to Recycle
>> >> > bin
>> >> > because of size), and their last good backup was from 3 weeks ago.
>> >> > If
>> >> > I
>> >> > try
>> >> > to attach the database either through Enterprise Manager or QA it
>> >> > tells
>> >> > me
>> >> > the physical file name for the ldf is incorrect. Is there a way to
>> >> > get
>> >> > around this error? Can I restore the old backup to a different name
>> >> > and
>> >> > use
>> >> > its ldf file? Where is the info stored that tells the system what
>> >> > log
>> >> > file
>> >> > is associated with the database? Can I modify that somehow?
>> >> > Thanks!
>> >>
>> >>
>> >>
>>

Attach db w/no log file

Attach db to sql 2005 express edition

My dog ate my sql2000 installation disk! After a recent xp installation I remembered that.

Now I'm in a hurry to get up and running with SQL again. I've installed SQL server 2005 express, but I can't attach any databases, or really do anything for that matter. I have a copy of SQL Server 2005 enterprise edition but that won't install on my development machine, Xp Sp2. Which way should I go, install SQL 2005 enterprise on a Win 2003 server or can I download something to administer the express version?

As always thanks for any and all replies!

/Eskil

You can download the SQL Server 2005 Management Studiohttps://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en)

attach db to .net2 project

Hi,

How do I attach an existing sql2000 database to a .net 2 project in the app_data folder?

thanks

Just detach the database from the SQL 2000 instance, then open your project-> right click the App_Data folder in Solution Explorer->choose Add Existing Items->find the database file and add it.sql

attach db sql in a previous sql server version

Hi,
anyone can tell me if is possible to detach a db from a sql 2000 server and
attach it again into a sql 7.0?
thanks
VTNo, you can not move from 2000 to 7.0 using detach/attach. You can't
use backuo/restore either. As far as I know you have to do it all the
hard way, meaning scripts for the objects and moving the data through
DTS, bulk copy, attached server SELECT/INSERTs, etc..
Roy Harvey
Beacon Falls, CT
On Thu, 20 Jul 2006 06:06:02 -0700, VT <VT@.discussions.microsoft.com>
wrote:

>Hi,
>anyone can tell me if is possible to detach a db from a sql 2000 server and
>attach it again into a sql 7.0?
>thanks
>VT|||No, not possible to 'just' detach from SQL 2000 and reattach to SQL v7.
If you want to move the tables and data, you might look into DTS to transfer
objects and data.
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"VT" <VT@.discussions.microsoft.com> wrote in message
news:C627B49C-91C1-4B17-A0D4-45D3810F6892@.microsoft.com...
> Hi,
> anyone can tell me if is possible to detach a db from a sql 2000 server
> and
> attach it again into a sql 7.0?
> thanks
> VT

attach db sql in a previous sql server version

Hi,
anyone can tell me if is possible to detach a db from a sql 2000 server and
attach it again into a sql 7.0?
thanks
VTNo, you can not move from 2000 to 7.0 using detach/attach. You can't
use backuo/restore either. As far as I know you have to do it all the
hard way, meaning scripts for the objects and moving the data through
DTS, bulk copy, attached server SELECT/INSERTs, etc..
Roy Harvey
Beacon Falls, CT
On Thu, 20 Jul 2006 06:06:02 -0700, VT <VT@.discussions.microsoft.com>
wrote:
>Hi,
>anyone can tell me if is possible to detach a db from a sql 2000 server and
>attach it again into a sql 7.0?
>thanks
>VT|||No, not possible to 'just' detach from SQL 2000 and reattach to SQL v7.
If you want to move the tables and data, you might look into DTS to transfer
objects and data.
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"VT" <VT@.discussions.microsoft.com> wrote in message
news:C627B49C-91C1-4B17-A0D4-45D3810F6892@.microsoft.com...
> Hi,
> anyone can tell me if is possible to detach a db from a sql 2000 server
> and
> attach it again into a sql 7.0?
> thanks
> VT

Attach DB problem

I'm running SQL2000 on my development machine. 2 of our clients use SQL2005.
I've read that it's not possible to attach DB detached from SQL2005 to
SQL2000. But by some reason I can do it with the database of one of the
client. When I try to do the same with the second client database I get an
Error 602.
What is correct - database from SQL2005 cannot be attached to SQL2000, or it
can be attached?
Why I can do it with one DB, but cannot with another one. Both DBs do not
give me any problem if I'm attaching them to SQL2005 running on my second
development machine.
Thank you
vovanMy guess is that the one that works was never attached to a SQL2005 instance
to begin with. Even if by chance it seemed to attach I would not trust it.
The system objects are no longer compatible with 2000 after it has been
attached or restored to a 2005 instance. Bottom line is that it is NOT
backward compatible.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"vovan" <v@.v.com> wrote in message
news:uzIknmq4HHA.5984@.TK2MSFTNGP04.phx.gbl...
> I'm running SQL2000 on my development machine. 2 of our clients use
> SQL2005. I've read that it's not possible to attach DB detached from
> SQL2005 to SQL2000. But by some reason I can do it with the database of
> one of the client. When I try to do the same with the second client
> database I get an Error 602.
> What is correct - database from SQL2005 cannot be attached to SQL2000, or
> it can be attached?
> Why I can do it with one DB, but cannot with another one. Both DBs do not
> give me any problem if I'm attaching them to SQL2005 running on my second
> development machine.
> Thank you
> vovan
>|||Thank you Andrew
I was mislead by the fact that the client uses SQL 2005 Management Studio to
manage their SQL Server. But the version of SQL is 2000.
vovan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eDUnIYr4HHA.5316@.TK2MSFTNGP04.phx.gbl...
> My guess is that the one that works was never attached to a SQL2005
> instance to begin with. Even if by chance it seemed to attach I would not
> trust it. The system objects are no longer compatible with 2000 after it
> has been attached or restored to a 2005 instance. Bottom line is that it
> is NOT backward compatible.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "vovan" <v@.v.com> wrote in message
> news:uzIknmq4HHA.5984@.TK2MSFTNGP04.phx.gbl...
>> I'm running SQL2000 on my development machine. 2 of our clients use
>> SQL2005. I've read that it's not possible to attach DB detached from
>> SQL2005 to SQL2000. But by some reason I can do it with the database of
>> one of the client. When I try to do the same with the second client
>> database I get an Error 602.
>> What is correct - database from SQL2005 cannot be attached to SQL2000, or
>> it can be attached?
>> Why I can do it with one DB, but cannot with another one. Both DBs do not
>> give me any problem if I'm attaching them to SQL2005 running on my second
>> development machine.
>> Thank you
>> vovan
>

Thursday, March 29, 2012

Blog Archive

About Me