recovery db from files *.mdf and *.ldf

Hi
My server machine had problems, and i had that reinstall,=20
and I only can recover the files *.mdf, and *.ldf.
I tried to recover the one db with the command:
EXEC sp_attach_db @.dbname =3D'viaticos',=20
@.filename1 =3D 'F:\Microsoft SQL=20
Server\MSSQL\Data\Viaticos_Data.mdf',=20
@.filename2 =3D 'F:\Microsoft SQL=20
Server\MSSQL\Data\Viaticos_log.ldf'
and=20
EXEC sp_attach_single_file_db 'viaticos', @.physname=20
=3D 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_Data.MDF'
But, the last commnad can create the db, and generated the=20
follow error;=20
Error de activaci=F3n de dispositivo. El nombre de archivo=20
f=EDsico 'f:\Microsoft
SQL Server\MSSQL\data\Viaticos_Log.LDF' puede ser=20
incorrecto.
Se cre=F3 un nuevo archivo de registro llamado 'F:\Microsoft=20
SQL
Server\MSSQL\Data\viaticos_log.LDF'.
With this option I can see the store procedure and views,=20
but i don't see the data and definitions of tables, and
generated this error:
SQL-DMO(ODBC SQLState:08S01
Error 0: this server has been disconected. You must=20
reconnected to perform this operation
Error 823.
and=20
Error 22528[SQL-DMO] This server connect has been=20
unexpected broken and cannot be reconected.
How I do for recover the data and structure of tables?
than's.
Do you not have proper backups?
You could *try* sp_attach_single_file_db against just the MDF file, but if
SQL Server crashed and it wasn't detached correctly, I'm not very
optimistic.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ivan Alexander Laverde G" <anonymous@.discussions.microsoft.com> wrote in
message news:fe6101c43ea3$ee330950$a501280a@.phx.gbl...
Hi
My server machine had problems, and i had that reinstall,
and I only can recover the files *.mdf, and *.ldf.
I tried to recover the one db with the command:
EXEC sp_attach_db @.dbname ='viaticos',
@.filename1 = 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_Data.mdf',
@.filename2 = 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_log.ldf'
and
EXEC sp_attach_single_file_db 'viaticos', @.physname
= 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_Data.MDF'
But, the last commnad can create the db, and generated the
follow error;
Error de activacin de dispositivo. El nombre de archivo
fsico 'f:\Microsoft
SQL Server\MSSQL\data\Viaticos_Log.LDF' puede ser
incorrecto.
Se cre un nuevo archivo de registro llamado 'F:\Microsoft
SQL
Server\MSSQL\Data\viaticos_log.LDF'.
With this option I can see the store procedure and views,
but i don't see the data and definitions of tables, and
generated this error:
SQL-DMO(ODBC SQLState:08S01
Error 0: this server has been disconected. You must
reconnected to perform this operation
Error 823.
and
Error 22528[SQL-DMO] This server connect has been
unexpected broken and cannot be reconected.
How I do for recover the data and structure of tables?
than's.
|||Hi ,
Try the below steps, it might work if you are lucky.
1. Create a new database with the same name
2. Mention the file names (MDF and LDF ) same as old but in different
3. After you create the new database, stop SQL Server
4. Copy the old database files (MDF and LDF) over the top of the new ones.
5 SQL Server will recognize the database.
6. Database will started in suspect status
7. Update the database status to emergency mode (use below script in Query
Analyzer)
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
8. Now the database will be open for read only (with out using Transaction
log)
9. Create a new database and use DTS to move the data and objects from old
database to new database
Note:
After step-7 try DBCC Checkdb with repair options (See books online) and see
if it can be repaired.
Thanks
Hari
MCDBA
"Ivan Alexander Laverde G" <anonymous@.discussions.microsoft.com> wrote in
message news:fe6101c43ea3$ee330950$a501280a@.phx.gbl...
Hi
My server machine had problems, and i had that reinstall,
and I only can recover the files *.mdf, and *.ldf.
I tried to recover the one db with the command:
EXEC sp_attach_db @.dbname ='viaticos',
@.filename1 = 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_Data.mdf',
@.filename2 = 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_log.ldf'
and
EXEC sp_attach_single_file_db 'viaticos', @.physname
= 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_Data.MDF'
But, the last commnad can create the db, and generated the
follow error;
Error de activacin de dispositivo. El nombre de archivo
fsico 'f:\Microsoft
SQL Server\MSSQL\data\Viaticos_Log.LDF' puede ser
incorrecto.
Se cre un nuevo archivo de registro llamado 'F:\Microsoft
SQL
Server\MSSQL\Data\viaticos_log.LDF'.
With this option I can see the store procedure and views,
but i don't see the data and definitions of tables, and
generated this error:
SQL-DMO(ODBC SQLState:08S01
Error 0: this server has been disconected. You must
reconnected to perform this operation
Error 823.
and
Error 22528[SQL-DMO] This server connect has been
unexpected broken and cannot be reconected.
How I do for recover the data and structure of tables?
than's.

recovery db from files *.mdf and *.ldf

Hi
My server machine had problems, and i had that reinstall,=20
and I only can recover the files *.mdf, and *.ldf.
I tried to recover the one db with the command:
EXEC sp_attach_db @.dbname =3D'viaticos',=20
@.filename1 =3D 'F:\Microsoft SQL=20
Server\MSSQL\Data\Viaticos_Data.mdf',=20
@.filename2 =3D 'F:\Microsoft SQL=20
Server\MSSQL\Data\Viaticos_log.ldf'
and=20
EXEC sp_attach_single_file_db 'viaticos', @.physname=20
=3D 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_Data.MDF'
But, the last commnad can create the db, and generated the=20
follow error;=20
Error de activaci=F3n de dispositivo. El nombre de archivo=20
f=EDsico 'f:\Microsoft
SQL Server\MSSQL\data\Viaticos_Log.LDF' puede ser=20
incorrecto.
Se cre=F3 un nuevo archivo de registro llamado 'F:\Microsoft=20
SQL
Server\MSSQL\Data\viaticos_log.LDF'.
With this option I can see the store procedure and views,=20
but i don't see the data and definitions of tables, and
generated this error:
SQL-DMO(ODBC SQLState:08S01
Error 0: this server has been disconected. You must=20
reconnected to perform this operation
Error 823.
and=20
Error 22528[SQL-DMO] This server connect has been=20
unexpected broken and cannot be reconected.
How I do for recover the data and structure of tables?
than's.Do you not have proper backups?
You could *try* sp_attach_single_file_db against just the MDF file, but if
SQL Server crashed and it wasn't detached correctly, I'm not very
optimistic.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ivan Alexander Laverde G" <anonymous@.discussions.microsoft.com> wrote in
message news:fe6101c43ea3$ee330950$a501280a@.phx.gbl...
Hi
My server machine had problems, and i had that reinstall,
and I only can recover the files *.mdf, and *.ldf.
I tried to recover the one db with the command:
EXEC sp_attach_db @.dbname ='viaticos',
@.filename1 = 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_Data.mdf',
@.filename2 = 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_log.ldf'
and
EXEC sp_attach_single_file_db 'viaticos', @.physname
= 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_Data.MDF'
But, the last commnad can create the db, and generated the
follow error;
Error de activacin de dispositivo. El nombre de archivo
fsico 'f:\Microsoft
SQL Server\MSSQL\data\Viaticos_Log.LDF' puede ser
incorrecto.
Se cre un nuevo archivo de registro llamado 'F:\Microsoft
SQL
Server\MSSQL\Data\viaticos_log.LDF'.
With this option I can see the store procedure and views,
but i don't see the data and definitions of tables, and
generated this error:
SQL-DMO(ODBC SQLState:08S01
Error 0: this server has been disconected. You must
reconnected to perform this operation
Error 823.
and
Error 22528[SQL-DMO] This server connect has been
unexpected broken and cannot be reconected.
How I do for recover the data and structure of tables?
than's.|||Hi ,
Try the below steps, it might work if you are lucky.
1. Create a new database with the same name
2. Mention the file names (MDF and LDF ) same as old but in different
3. After you create the new database, stop SQL Server
4. Copy the old database files (MDF and LDF) over the top of the new ones.
5 SQL Server will recognize the database.
6. Database will started in suspect status
7. Update the database status to emergency mode (use below script in Query
Analyzer)
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
8. Now the database will be open for read only (with out using Transaction
log)
9. Create a new database and use DTS to move the data and objects from old
database to new database
Note:
After step-7 try DBCC Checkdb with repair options (See books online) and see
if it can be repaired.
Thanks
Hari
MCDBA
"Ivan Alexander Laverde G" <anonymous@.discussions.microsoft.com> wrote in
message news:fe6101c43ea3$ee330950$a501280a@.phx.gbl...
Hi
My server machine had problems, and i had that reinstall,
and I only can recover the files *.mdf, and *.ldf.
I tried to recover the one db with the command:
EXEC sp_attach_db @.dbname ='viaticos',
@.filename1 = 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_Data.mdf',
@.filename2 = 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_log.ldf'
and
EXEC sp_attach_single_file_db 'viaticos', @.physname
= 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_Data.MDF'
But, the last commnad can create the db, and generated the
follow error;
Error de activacin de dispositivo. El nombre de archivo
fsico 'f:\Microsoft
SQL Server\MSSQL\data\Viaticos_Log.LDF' puede ser
incorrecto.
Se cre un nuevo archivo de registro llamado 'F:\Microsoft
SQL
Server\MSSQL\Data\viaticos_log.LDF'.
With this option I can see the store procedure and views,
but i don't see the data and definitions of tables, and
generated this error:
SQL-DMO(ODBC SQLState:08S01
Error 0: this server has been disconected. You must
reconnected to perform this operation
Error 823.
and
Error 22528[SQL-DMO] This server connect has been
unexpected broken and cannot be reconected.
How I do for recover the data and structure of tables?
than's.

recovery db from files *.mdf and *.ldf

Hi
My server machine had problems, and i had that reinstall, and I only can recover the files *.mdf, and *.ldf.
I tried to recover the one db with the command:
EXEC sp_attach_db @.dbname =3D'viaticos', @.filename1 =3D 'F:\Microsoft SQL Server\MSSQL\Data\Viaticos_Data.mdf', @.filename2 =3D 'F:\Microsoft SQL Server\MSSQL\Data\Viaticos_log.ldf'
and
EXEC sp_attach_single_file_db 'viaticos', @.physname =3D 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_Data.MDF'
But, the last commnad can create the db, and generated the follow error;
Error de activaci=F3n de dispositivo. El nombre de archivo f=EDsico 'f:\Microsoft
SQL Server\MSSQL\data\Viaticos_Log.LDF' puede ser incorrecto.
Se cre=F3 un nuevo archivo de registro llamado 'F:\Microsoft SQL
Server\MSSQL\Data\viaticos_log.LDF'.
With this option I can see the store procedure and views, but i don't see the data and definitions of tables, and
generated this error:
SQL-DMO(ODBC SQLState:08S01
Error 0: this server has been disconected. You must reconnected to perform this operation
Error 823.
and
Error 22528[SQL-DMO] This server connect has been unexpected broken and cannot be reconected.
How I do for recover the data and structure of tables?
than's.Do you not have proper backups?
You could *try* sp_attach_single_file_db against just the MDF file, but if
SQL Server crashed and it wasn't detached correctly, I'm not very
optimistic.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ivan Alexander Laverde G" <anonymous@.discussions.microsoft.com> wrote in
message news:fe6101c43ea3$ee330950$a501280a@.phx.gbl...
Hi
My server machine had problems, and i had that reinstall,
and I only can recover the files *.mdf, and *.ldf.
I tried to recover the one db with the command:
EXEC sp_attach_db @.dbname ='viaticos',
@.filename1 = 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_Data.mdf',
@.filename2 = 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_log.ldf'
and
EXEC sp_attach_single_file_db 'viaticos', @.physname
= 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_Data.MDF'
But, the last commnad can create the db, and generated the
follow error;
Error de activación de dispositivo. El nombre de archivo
físico 'f:\Microsoft
SQL Server\MSSQL\data\Viaticos_Log.LDF' puede ser
incorrecto.
Se creó un nuevo archivo de registro llamado 'F:\Microsoft
SQL
Server\MSSQL\Data\viaticos_log.LDF'.
With this option I can see the store procedure and views,
but i don't see the data and definitions of tables, and
generated this error:
SQL-DMO(ODBC SQLState:08S01
Error 0: this server has been disconected. You must
reconnected to perform this operation
Error 823.
and
Error 22528[SQL-DMO] This server connect has been
unexpected broken and cannot be reconected.
How I do for recover the data and structure of tables?
than's.|||Hi ,
Try the below steps, it might work if you are lucky.
1. Create a new database with the same name
2. Mention the file names (MDF and LDF ) same as old but in different
3. After you create the new database, stop SQL Server
4. Copy the old database files (MDF and LDF) over the top of the new ones.
5 SQL Server will recognize the database.
6. Database will started in suspect status
7. Update the database status to emergency mode (use below script in Query
Analyzer)
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
8. Now the database will be open for read only (with out using Transaction
log)
9. Create a new database and use DTS to move the data and objects from old
database to new database
Note:
After step-7 try DBCC Checkdb with repair options (See books online) and see
if it can be repaired.
Thanks
Hari
MCDBA
"Ivan Alexander Laverde G" <anonymous@.discussions.microsoft.com> wrote in
message news:fe6101c43ea3$ee330950$a501280a@.phx.gbl...
Hi
My server machine had problems, and i had that reinstall,
and I only can recover the files *.mdf, and *.ldf.
I tried to recover the one db with the command:
EXEC sp_attach_db @.dbname ='viaticos',
@.filename1 = 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_Data.mdf',
@.filename2 = 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_log.ldf'
and
EXEC sp_attach_single_file_db 'viaticos', @.physname
= 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_Data.MDF'
But, the last commnad can create the db, and generated the
follow error;
Error de activación de dispositivo. El nombre de archivo
físico 'f:\Microsoft
SQL Server\MSSQL\data\Viaticos_Log.LDF' puede ser
incorrecto.
Se creó un nuevo archivo de registro llamado 'F:\Microsoft
SQL
Server\MSSQL\Data\viaticos_log.LDF'.
With this option I can see the store procedure and views,
but i don't see the data and definitions of tables, and
generated this error:
SQL-DMO(ODBC SQLState:08S01
Error 0: this server has been disconected. You must
reconnected to perform this operation
Error 823.
and
Error 22528[SQL-DMO] This server connect has been
unexpected broken and cannot be reconected.
How I do for recover the data and structure of tables?
than's.sql

recovering logins

what is the best way to recover logins.
Let's say I need to rebuild a server or if my server crashes and I need to
reinstall sql server. What can I do so that I have a file or something that
saved my logins so that I can get them back on the server?
I'm used to sybase where you can just bcp the syslogins table.Search KB for sp_help_revlogins. It will generate a script for you where the script includes
sp_addlogins and sp_grantlogin for your logins. The SQL Server logins will be created using the
original SID and passwords.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Cinderella" <Cinderella@.discussions.microsoft.com> wrote in message
news:F0314D8C-305E-4B2A-AAA6-01B9DCAD12BA@.microsoft.com...
> what is the best way to recover logins.
> Let's say I need to rebuild a server or if my server crashes and I need to
> reinstall sql server. What can I do so that I have a file or something that
> saved my logins so that I can get them back on the server?
> I'm used to sybase where you can just bcp the syslogins table.|||sorry but what is KB?
"Tibor Karaszi" wrote:
> Search KB for sp_help_revlogins. It will generate a script for you where the script includes
> sp_addlogins and sp_grantlogin for your logins. The SQL Server logins will be created using the
> original SID and passwords.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Cinderella" <Cinderella@.discussions.microsoft.com> wrote in message
> news:F0314D8C-305E-4B2A-AAA6-01B9DCAD12BA@.microsoft.com...
> > what is the best way to recover logins.
> > Let's say I need to rebuild a server or if my server crashes and I need to
> > reinstall sql server. What can I do so that I have a file or something that
> > saved my logins so that I can get them back on the server?
> >
> > I'm used to sybase where you can just bcp the syslogins table.
>
>|||I'm sorry I wasn't thinking - you mean knowledge base articles right?
Ok will do.
Are there any other ways or is this the only way?
"Cinderella" wrote:
> sorry but what is KB?
> "Tibor Karaszi" wrote:
> > Search KB for sp_help_revlogins. It will generate a script for you where the script includes
> > sp_addlogins and sp_grantlogin for your logins. The SQL Server logins will be created using the
> > original SID and passwords.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Cinderella" <Cinderella@.discussions.microsoft.com> wrote in message
> > news:F0314D8C-305E-4B2A-AAA6-01B9DCAD12BA@.microsoft.com...
> > > what is the best way to recover logins.
> > > Let's say I need to rebuild a server or if my server crashes and I need to
> > > reinstall sql server. What can I do so that I have a file or something that
> > > saved my logins so that I can get them back on the server?
> > >
> > > I'm used to sybase where you can just bcp the syslogins table.
> >
> >
> >|||> Are there any other ways or is this the only way?
Why do you want other ways to do this? This is the supported way, and it work just as fine as other
potential ways. The thing is that if you start doing some export of sysxlogins or similar, you are
into unsupported territory, without any benefits...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Cinderella" <Cinderella@.discussions.microsoft.com> wrote in message
news:AA91D7F2-B3E8-4D5F-A690-B037DAD935B5@.microsoft.com...
> I'm sorry I wasn't thinking - you mean knowledge base articles right?
> Ok will do.
> Are there any other ways or is this the only way?
> "Cinderella" wrote:
> > sorry but what is KB?
> >
> > "Tibor Karaszi" wrote:
> >
> > > Search KB for sp_help_revlogins. It will generate a script for you where the script includes
> > > sp_addlogins and sp_grantlogin for your logins. The SQL Server logins will be created using
the
> > > original SID and passwords.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "Cinderella" <Cinderella@.discussions.microsoft.com> wrote in message
> > > news:F0314D8C-305E-4B2A-AAA6-01B9DCAD12BA@.microsoft.com...
> > > > what is the best way to recover logins.
> > > > Let's say I need to rebuild a server or if my server crashes and I need to
> > > > reinstall sql server. What can I do so that I have a file or something that
> > > > saved my logins so that I can get them back on the server?
> > > >
> > > > I'm used to sybase where you can just bcp the syslogins table.
> > >
> > >
> > >

Recovering Database. Urgent.

I have a fairly large database (225Gb) on a test server. The OS (NT 4) drive
has died and I will have to reinstall it from scratch. My question is, I
still have the database files (SQL 7) intact is it possible for me to
reinstall everything, recreate the database and then point it at the
original database files?
GavThe "point" part would be sp_attach_db. It is only documented to work if you first did sp_detach_db, but it
might work.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gavin Metcalfe" <gavin.metcalfe@.btinternet.com> wrote in message news:c8f7u9$ipt$1@.sparta.btinternet.com...
> I have a fairly large database (225Gb) on a test server. The OS (NT 4) drive
> has died and I will have to reinstall it from scratch. My question is, I
> still have the database files (SQL 7) intact is it possible for me to
> reinstall everything, recreate the database and then point it at the
> original database files?
> Gav
>|||Ok, how about if I create a new database, then stop the services and change
the files it has created for the old ones.
Gav
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#QnWSIYPEHA.1048@.tk2msftngp13.phx.gbl...
> The "point" part would be sp_attach_db. It is only documented to work if
you first did sp_detach_db, but it
> might work.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Gavin Metcalfe" <gavin.metcalfe@.btinternet.com> wrote in message
news:c8f7u9$ipt$1@.sparta.btinternet.com...
> > I have a fairly large database (225Gb) on a test server. The OS (NT 4)
drive
> > has died and I will have to reinstall it from scratch. My question is, I
> > still have the database files (SQL 7) intact is it possible for me to
> > reinstall everything, recreate the database and then point it at the
> > original database files?
> >
> > Gav
> >
> >
>|||Yes, look at sp_attachdb in BOL. Try to get SQL Server to the same patch
level as it was previously before doing this though.
Regards,
William D. Bartholomew
http://blog.bartholomew.id.au/
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi,
Yes, you can try that as well. In both the cases you have to create all your
logins , jobs, operators , linked server .....
Another approach which will give back your database server as same as old
stage:-
Once your hard disk drives are accessible, take the copy of MDF and LDF to a
new safe place and do the below steps.
To do this you should have all you system and user databases MDF and LDF
files.
1. Install Windows NT and Service packs. Name computer name as OLD name.
2. Install SQL 7 and install the service pack as old level. Ensure that you
install sql server
with same directory structure.
3. Stop SQL server and SQl sgent service
4. Copy all the MDF and LDF files , Ensure that you copy
the system database files for master, Model, tempdb, msdb databases
5. After the successful copy, start the SQl server
Mostly by doing this steps you can go back to old stage with all the
databases up.
Thanks
Hari
MCDBA
"Gavin Metcalfe" <gavin.metcalfe@.btinternet.com> wrote in message
news:c8f9be$2l9$1@.hercules.btinternet.com...
> Ok, how about if I create a new database, then stop the services and
change
> the files it has created for the old ones.
> Gav
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:#QnWSIYPEHA.1048@.tk2msftngp13.phx.gbl...
> > The "point" part would be sp_attach_db. It is only documented to work if
> you first did sp_detach_db, but it
> > might work.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Gavin Metcalfe" <gavin.metcalfe@.btinternet.com> wrote in message
> news:c8f7u9$ipt$1@.sparta.btinternet.com...
> > > I have a fairly large database (225Gb) on a test server. The OS (NT 4)
> drive
> > > has died and I will have to reinstall it from scratch. My question is,
I
> > > still have the database files (SQL 7) intact is it possible for me to
> > > reinstall everything, recreate the database and then point it at the
> > > original database files?
> > >
> > > Gav
> > >
> > >
> >
> >
>|||Thats is round about what I am planning to do. Database is too large for me
to move somewhere else so I'm going to rename the files to .old to start
with. It is a SAP database so I'm going to reinstall SAP and let it recreate
its standard database and then I'm planning to replace the new files with
the old.
Gav
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:#8zreRYPEHA.3264@.tk2msftngp13.phx.gbl...
> Hi,
> Yes, you can try that as well. In both the cases you have to create all
your
> logins , jobs, operators , linked server .....
> Another approach which will give back your database server as same as old
> stage:-
> Once your hard disk drives are accessible, take the copy of MDF and LDF to
a
> new safe place and do the below steps.
> To do this you should have all you system and user databases MDF and LDF
> files.
> 1. Install Windows NT and Service packs. Name computer name as OLD name.
> 2. Install SQL 7 and install the service pack as old level. Ensure that
you
> install sql server
> with same directory structure.
> 3. Stop SQL server and SQl sgent service
> 4. Copy all the MDF and LDF files , Ensure that you copy
> the system database files for master, Model, tempdb, msdb databases
> 5. After the successful copy, start the SQl server
> Mostly by doing this steps you can go back to old stage with all the
> databases up.
> Thanks
> Hari
> MCDBA
>
>
>
> "Gavin Metcalfe" <gavin.metcalfe@.btinternet.com> wrote in message
> news:c8f9be$2l9$1@.hercules.btinternet.com...
> > Ok, how about if I create a new database, then stop the services and
> change
> > the files it has created for the old ones.
> >
> > Gav
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> > message news:#QnWSIYPEHA.1048@.tk2msftngp13.phx.gbl...
> > > The "point" part would be sp_attach_db. It is only documented to work
if
> > you first did sp_detach_db, but it
> > > might work.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "Gavin Metcalfe" <gavin.metcalfe@.btinternet.com> wrote in message
> > news:c8f7u9$ipt$1@.sparta.btinternet.com...
> > > > I have a fairly large database (225Gb) on a test server. The OS (NT
4)
> > drive
> > > > has died and I will have to reinstall it from scratch. My question
is,
> I
> > > > still have the database files (SQL 7) intact is it possible for me
to
> > > > reinstall everything, recreate the database and then point it at the
> > > > original database files?
> > > >
> > > > Gav
> > > >
> > > >
> > >
> > >
> >
> >
>

Recovering Database. Urgent.

I have a fairly large database (225Gb) on a test server. The OS (NT 4) drive
has died and I will have to reinstall it from scratch. My question is, I
still have the database files (SQL 7) intact is it possible for me to
reinstall everything, recreate the database and then point it at the
original database files?
Gav
The "point" part would be sp_attach_db. It is only documented to work if you first did sp_detach_db, but it
might work.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gavin Metcalfe" <gavin.metcalfe@.btinternet.com> wrote in message news:c8f7u9$ipt$1@.sparta.btinternet.com...
> I have a fairly large database (225Gb) on a test server. The OS (NT 4) drive
> has died and I will have to reinstall it from scratch. My question is, I
> still have the database files (SQL 7) intact is it possible for me to
> reinstall everything, recreate the database and then point it at the
> original database files?
> Gav
>
|||Yes, look at sp_attachdb in BOL. Try to get SQL Server to the same patch
level as it was previously before doing this though.
Regards,
William D. Bartholomew
http://blog.bartholomew.id.au/
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Ok, how about if I create a new database, then stop the services and change
the files it has created for the old ones.
Gav
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#QnWSIYPEHA.1048@.tk2msftngp13.phx.gbl...
> The "point" part would be sp_attach_db. It is only documented to work if
you first did sp_detach_db, but it
> might work.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Gavin Metcalfe" <gavin.metcalfe@.btinternet.com> wrote in message
news:c8f7u9$ipt$1@.sparta.btinternet.com...[vbcol=seagreen]
drive
>
|||Hi,
Yes, you can try that as well. In both the cases you have to create all your
logins , jobs, operators , linked server .....
Another approach which will give back your database server as same as old
stage:-
Once your hard disk drives are accessible, take the copy of MDF and LDF to a
new safe place and do the below steps.
To do this you should have all you system and user databases MDF and LDF
files.
1. Install Windows NT and Service packs. Name computer name as OLD name.
2. Install SQL 7 and install the service pack as old level. Ensure that you
install sql server
with same directory structure.
3. Stop SQL server and SQl sgent service
4. Copy all the MDF and LDF files , Ensure that you copy
the system database files for master, Model, tempdb, msdb databases
5. After the successful copy, start the SQl server
Mostly by doing this steps you can go back to old stage with all the
databases up.
Thanks
Hari
MCDBA
"Gavin Metcalfe" <gavin.metcalfe@.btinternet.com> wrote in message
news:c8f9be$2l9$1@.hercules.btinternet.com...
> Ok, how about if I create a new database, then stop the services and
change
> the files it has created for the old ones.
> Gav
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in[vbcol=seagreen]
> message news:#QnWSIYPEHA.1048@.tk2msftngp13.phx.gbl...
> you first did sp_detach_db, but it
> news:c8f7u9$ipt$1@.sparta.btinternet.com...
> drive
I
>
|||Thats is round about what I am planning to do. Database is too large for me
to move somewhere else so I'm going to rename the files to .old to start
with. It is a SAP database so I'm going to reinstall SAP and let it recreate
its standard database and then I'm planning to replace the new files with
the old.
Gav
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:#8zreRYPEHA.3264@.tk2msftngp13.phx.gbl...
> Hi,
> Yes, you can try that as well. In both the cases you have to create all
your
> logins , jobs, operators , linked server .....
> Another approach which will give back your database server as same as old
> stage:-
> Once your hard disk drives are accessible, take the copy of MDF and LDF to
a
> new safe place and do the below steps.
> To do this you should have all you system and user databases MDF and LDF
> files.
> 1. Install Windows NT and Service packs. Name computer name as OLD name.
> 2. Install SQL 7 and install the service pack as old level. Ensure that
you[vbcol=seagreen]
> install sql server
> with same directory structure.
> 3. Stop SQL server and SQl sgent service
> 4. Copy all the MDF and LDF files , Ensure that you copy
> the system database files for master, Model, tempdb, msdb databases
> 5. After the successful copy, start the SQl server
> Mostly by doing this steps you can go back to old stage with all the
> databases up.
> Thanks
> Hari
> MCDBA
>
>
>
> "Gavin Metcalfe" <gavin.metcalfe@.btinternet.com> wrote in message
> news:c8f9be$2l9$1@.hercules.btinternet.com...
> change
> in
if[vbcol=seagreen]
4)[vbcol=seagreen]
is,[vbcol=seagreen]
> I
to
>

Recovering Database. Urgent.

I have a fairly large database (225Gb) on a test server. The OS (NT 4) drive
has died and I will have to reinstall it from scratch. My question is, I
still have the database files (SQL 7) intact is it possible for me to
reinstall everything, recreate the database and then point it at the
original database files?
GavThe "point" part would be sp_attach_db. It is only documented to work if you
first did sp_detach_db, but it
might work.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gavin Metcalfe" <gavin.metcalfe@.btinternet.com> wrote in message news:c8f7u9$ipt$1@.sparta.b
tinternet.com...
> I have a fairly large database (225Gb) on a test server. The OS (NT 4) dri
ve
> has died and I will have to reinstall it from scratch. My question is, I
> still have the database files (SQL 7) intact is it possible for me to
> reinstall everything, recreate the database and then point it at the
> original database files?
> Gav
>|||Yes, look at sp_attachdb in BOL. Try to get SQL Server to the same patch
level as it was previously before doing this though.
Regards,
William D. Bartholomew
http://blog.bartholomew.id.au/
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Ok, how about if I create a new database, then stop the services and change
the files it has created for the old ones.
Gav
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#QnWSIYPEHA.1048@.tk2msftngp13.phx.gbl...
> The "point" part would be sp_attach_db. It is only documented to work if
you first did sp_detach_db, but it
> might work.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Gavin Metcalfe" <gavin.metcalfe@.btinternet.com> wrote in message
news:c8f7u9$ipt$1@.sparta.btinternet.com...
drive[vbcol=seagreen]
>|||Hi,
Yes, you can try that as well. In both the cases you have to create all your
logins , jobs, operators , linked server .....
Another approach which will give back your database server as same as old
stage:-
Once your hard disk drives are accessible, take the copy of MDF and LDF to a
new safe place and do the below steps.
To do this you should have all you system and user databases MDF and LDF
files.
1. Install Windows NT and Service packs. Name computer name as OLD name.
2. Install SQL 7 and install the service pack as old level. Ensure that you
install sql server
with same directory structure.
3. Stop SQL server and SQl sgent service
4. Copy all the MDF and LDF files , Ensure that you copy
the system database files for master, Model, tempdb, msdb databases
5. After the successful copy, start the SQl server
Mostly by doing this steps you can go back to old stage with all the
databases up.
Thanks
Hari
MCDBA
"Gavin Metcalfe" <gavin.metcalfe@.btinternet.com> wrote in message
news:c8f9be$2l9$1@.hercules.btinternet.com...
> Ok, how about if I create a new database, then stop the services and
change
> the files it has created for the old ones.
> Gav
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:#QnWSIYPEHA.1048@.tk2msftngp13.phx.gbl...
> you first did sp_detach_db, but it
> news:c8f7u9$ipt$1@.sparta.btinternet.com...
> drive
I[vbcol=seagreen]
>|||Thats is round about what I am planning to do. Database is too large for me
to move somewhere else so I'm going to rename the files to .old to start
with. It is a SAP database so I'm going to reinstall SAP and let it recreate
its standard database and then I'm planning to replace the new files with
the old.
Gav
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:#8zreRYPEHA.3264@.tk2msftngp13.phx.gbl...
> Hi,
> Yes, you can try that as well. In both the cases you have to create all
your
> logins , jobs, operators , linked server .....
> Another approach which will give back your database server as same as old
> stage:-
> Once your hard disk drives are accessible, take the copy of MDF and LDF to
a
> new safe place and do the below steps.
> To do this you should have all you system and user databases MDF and LDF
> files.
> 1. Install Windows NT and Service packs. Name computer name as OLD name.
> 2. Install SQL 7 and install the service pack as old level. Ensure that
you
> install sql server
> with same directory structure.
> 3. Stop SQL server and SQl sgent service
> 4. Copy all the MDF and LDF files , Ensure that you copy
> the system database files for master, Model, tempdb, msdb databases
> 5. After the successful copy, start the SQl server
> Mostly by doing this steps you can go back to old stage with all the
> databases up.
> Thanks
> Hari
> MCDBA
>
>
>
> "Gavin Metcalfe" <gavin.metcalfe@.btinternet.com> wrote in message
> news:c8f9be$2l9$1@.hercules.btinternet.com...
> change
> in
if[vbcol=seagreen]
4)[vbcol=seagreen]
is,[vbcol=seagreen]
> I
to[vbcol=seagreen]
>

Recover System Stored Procedure

I mistakenly deleted the system stored procedure 'sp_columns' from my MSDE. Any idea about how to recover/reinstall it back without reinstalling the entire MSDE? Please help. Thanks.

you could restore your master database if you've a recent copy. Alternatively, the following script will recreate it. You'll need to either run it via a GUI tool, or else use osql from Dos.

use master
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/* Procedure for 8.0 server */
CREATE PROCEDURE sp_columns (
@.table_name nvarchar(384),
@.table_owner nvarchar(384) = null,
@.table_qualifier sysname = null,
@.column_name nvarchar(384) = null,
@.ODBCVer int = 2)
AS
DECLARE @.full_table_name nvarchar(769)
DECLARE @.table_id int

if @.ODBCVer <> 3
select @.ODBCVer = 2
if @.column_name is null /* If column name not supplied, match all */
select @.column_name = '%'
if @.table_qualifier is not null
begin
if db_name() <> @.table_qualifier
begin /* If qualifier doesn't match current database */
raiserror (15250, -1,-1)
return
end
end
if @.table_name is null
begin /* If table name not supplied, match all */
select @.table_name = '%'
end
if @.table_owner is null
begin /* If unqualified table name */
SELECT @.full_table_name = quotename(@.table_name)
end
else
begin /* Qualified table name */
if @.table_owner = ''
begin /* If empty owner name */
SELECT @.full_table_name = quotename(@.table_owner)
end
else
begin
SELECT @.full_table_name = quotename(@.table_owner) +
'.' + quotename(@.table_name)
end
end

/* Get Object ID */
SELECT @.table_id = object_id(@.full_table_name)
if ((isnull(charindex('%', @.full_table_name),0) = 0) and
(isnull(charindex('[', @.table_name),0) = 0) and
(isnull(charindex('[', @.table_owner),0) = 0) and
(isnull(charindex('_', @.full_table_name),0) = 0) and
@.table_id <> 0)
begin
/* this block is for the case where there is no pattern
matching required for the table name */

SELECT
TABLE_QUALIFIER = convert(sysname,DB_NAME()),
TABLE_OWNER = convert(sysname,USER_NAME(o.uid)),
TABLE_NAME = convert(sysname,o.name),
COLUMN_NAME = convert(sysname,c.name),
d.DATA_TYPE,
convert (sysname,case
when t.xusertype > 255 then t.name
else d.TYPE_NAME collate database_default
end) TYPE_NAME,
convert(int,case
when d.DATA_TYPE in (6,7) then d.data_precision /* FLOAT/REAL */
else OdbcPrec(c.xtype,c.length,c.xprec)
end) "PRECISION",
convert(int,case
when type_name(d.ss_dtype) IN ('numeric','decimal') then /* decimal/numeric types */
OdbcPrec(c.xtype,c.length,c.xprec)+2
else
isnull(d.length, c.length)
end) LENGTH,
SCALE = convert(smallint, OdbcScale(c.xtype,c.xscale)),
d.RADIX,
NULLABLE = convert(smallint, ColumnProperty (c.id, c.name, 'AllowsNull')),
REMARKS = convert(varchar(254),null), /* Remarks are NULL */
COLUMN_DEF = text,
d.SQL_DATA_TYPE,
d.SQL_DATETIME_SUB,
CHAR_OCTET_LENGTH = isnull(d.length, c.length)+d.charbin,
ORDINAL_POSITION = convert(int,
(
select count(*)
from syscolumns sc
where sc.id = c.id
AND sc.number = c.number
AND sc.colid <= c.colid
)),
IS_NULLABLE = convert(varchar(254),
substring('NO YES',(ColumnProperty (c.id, c.name, 'AllowsNull')*3)+1,3)),
SS_DATA_TYPE = c.type
FROM
sysobjects o,
master.dbo.spt_datatype_info d,
systypes t,
syscolumns c
LEFT OUTER JOIN syscomments m on c.cdefault = m.id
AND m.colid = 1
WHERE
o.id = @.table_id
AND c.id = o.id
AND t.xtype = d.ss_dtype
AND c.length = isnull(d.fixlen, c.length)
AND (d.ODBCVer is null or d.ODBCVer = @.ODBCVer)
AND (o.type not in ('P', 'FN', 'TF', 'IF') OR (o.type in ('TF', 'IF') and c.number = 0))
AND isnull(d.AUTO_INCREMENT,0) = isnull(ColumnProperty (c.id, c.name, 'IsIdentity'),0)
AND c.xusertype = t.xusertype
AND c.name like @.column_name
ORDER BY 17
end
else
begin
/* this block is for the case where there IS pattern
matching done on the table name */

if @.table_owner is null /* If owner not supplied, match all */
select @.table_owner = '%'

SELECT
TABLE_QUALIFIER = convert(sysname,DB_NAME()),
TABLE_OWNER = convert(sysname,USER_NAME(o.uid)),
TABLE_NAME = convert(sysname,o.name),
COLUMN_NAME = convert(sysname,c.name),
d.DATA_TYPE,
convert (sysname,case
when t.xusertype > 255 then t.name
else d.TYPE_NAME collate database_default
end) TYPE_NAME,
convert(int,case
when d.DATA_TYPE in (6,7) then d.data_precision /* FLOAT/REAL */
else OdbcPrec(c.xtype,c.length,c.xprec)
end) "PRECISION",
convert(int,case
when type_name(d.ss_dtype) IN ('numeric','decimal') then /* decimal/numeric types */
OdbcPrec(c.xtype,c.length,c.xprec)+2
else
isnull(d.length, c.length)
end) LENGTH,
SCALE = convert(smallint, OdbcScale(c.xtype,c.xscale)),
d.RADIX,
NULLABLE = convert(smallint, ColumnProperty (c.id, c.name, 'AllowsNull')),
REMARKS = convert(varchar(254),null), /* Remarks are NULL */
COLUMN_DEF = text,
d.SQL_DATA_TYPE,
d.SQL_DATETIME_SUB,
CHAR_OCTET_LENGTH = isnull(d.length, c.length)+d.charbin,
ORDINAL_POSITION = convert(int,
(
select count(*)
from syscolumns sc
where sc.id = c.id
AND sc.number = c.number
AND sc.colid <= c.colid
)),
IS_NULLABLE = convert(varchar(254),
rtrim(substring('NO YES',(ColumnProperty (c.id, c.name, 'AllowsNull')*3)+1,3))),
SS_DATA_TYPE = c.type
FROM
sysobjects o,
master.dbo.spt_datatype_info d,
systypes t,
syscolumns c
LEFT OUTER JOIN syscomments m on c.cdefault = m.id
AND m.colid = 1
WHERE
o.name like @.table_name
AND user_name(o.uid) like @.table_owner
AND o.id = c.id
AND t.xtype = d.ss_dtype
AND c.length = isnull(d.fixlen, c.length)
AND (d.ODBCVer is null or d.ODBCVer = @.ODBCVer)
AND (o.type not in ('P', 'FN', 'TF', 'IF') OR (o.type in ('TF', 'IF') and c.number = 0))
AND isnull(d.AUTO_INCREMENT,0) = isnull(ColumnProperty (c.id, c.name, 'IsIdentity'),0)
AND c.xusertype = t.xusertype
AND c.name like @.column_name
ORDER BY 2, 3, 17
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

|||Cathal, thanks a lot!

recover a stored procedure

If I can't connect to my database and my test server requires a reinstall of SQL Server 2005, how can I get my one stored proc out of there and back it up if I can't even connect to my database? Is there a file that is created that may have this? I know it's probably stored in master or something but not sure if it can be recovered.Before reinstalling the SQL Server, make a copy of the database files (.MDF and .LDF) in which you created your stored proc. When the new instance is installed, attach the DB and retrieve your stored proc from there.