How to recreate/restore the database from the data and log files

My development system hard disk crashed and I have lost the Operating
system and the SQL executables and the system cannot be salvaged.
However at the time of the crash the server was not running and the
log files and the data files of the database were on another hard
disk. I have managed to successfully recover the data and log files of
the database.
I would greatly appreciate if you could kindly show me the
procedure/process to restore the data and log files and bring the
database to life on a new SQL instance.
Please give me the command sequence to resurrect the database.
Thanks
Karen
Karen,
1) Reinstall SQL Server and place the data files in the same directory
structure as before.
2) Apply the same service pack level that you had before
3) Stop the MSSQLServer service
4) Copy your old data files into your current DIR structure
5) Start the MSSQLServer service
It should all work beautifully.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Karen Middleton wrote:
> My development system hard disk crashed and I have lost the Operating
> system and the SQL executables and the system cannot be salvaged.
> However at the time of the crash the server was not running and the
> log files and the data files of the database were on another hard
> disk. I have managed to successfully recover the data and log files of
> the database.
> I would greatly appreciate if you could kindly show me the
> procedure/process to restore the data and log files and bring the
> database to life on a new SQL instance.
> Please give me the command sequence to resurrect the database.
> Thanks
> Karen
|||Karen,
1) Reinstall SQL Server and place the data files in the same directory
2)You can use the option 'Attach Database' In Sql EnterPrise manager to attach your log Files....Without restoring the backup (Backup the system after attaching)
3)Open the enterprise manager - Click on your Sql Server group... Click the server ->Right Click on the Databases folder You can get the Attach Database
4) Browse the your Data Files (.mdf) file... and then click on verify to see everything is fine
5) Then click on OK... Then you must be able to run your system as before
Also... You can delete .ldf file (as the depending up on the transactions this might take whole lot of GB from your harddisk..) and then attach your .mdf file..
Thanks
Ramesh...

Quote:

Originally posted by Mark Allison
Karen,
1) Reinstall SQL Server and place the data files in the same directory
structure as before.
2) Apply the same service pack level that you had before
3) Stop the MSSQLServer service
4) Copy your old data files into your current DIR structure
5) Start the MSSQLServer service
It should all work beautifully.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Karen Middleton wrote:
> My development system hard disk crashed and I have lost the Operating
> system and the SQL executables and the system cannot be salvaged.
> However at the time of the crash the server was not running and the
> log files and the data files of the database were on another hard
> disk. I have managed to successfully recover the data and log files of
> the database.
> I would greatly appreciate if you could kindly show me the
> procedure/process to restore the data and log files and bring the
> database to life on a new SQL instance.
> Please give me the command sequence to resurrect the database.
> Thanks
> Karen

How to recreate/restore the database from the data and log files

My development system hard disk crashed and I have lost the Operating
system and the SQL executables and the system cannot be salvaged.
However at the time of the crash the server was not running and the
log files and the data files of the database were on another hard
disk. I have managed to successfully recover the data and log files of
the database.
I would greatly appreciate if you could kindly show me the
procedure/process to restore the data and log files and bring the
database to life on a new SQL instance.
Please give me the command sequence to resurrect the database.
Thanks
Karen
Karen,
1) Reinstall SQL Server and place the data files in the same directory
structure as before.
2) Apply the same service pack level that you had before
3) Stop the MSSQLServer service
4) Copy your old data files into your current DIR structure
5) Start the MSSQLServer service
It should all work beautifully.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Karen Middleton wrote:
> My development system hard disk crashed and I have lost the Operating
> system and the SQL executables and the system cannot be salvaged.
> However at the time of the crash the server was not running and the
> log files and the data files of the database were on another hard
> disk. I have managed to successfully recover the data and log files of
> the database.
> I would greatly appreciate if you could kindly show me the
> procedure/process to restore the data and log files and bring the
> database to life on a new SQL instance.
> Please give me the command sequence to resurrect the database.
> Thanks
> Karen

How to recreate/restore the database from the data and log files

My development system hard disk crashed and I have lost the Operating
system and the SQL executables and the system cannot be salvaged.
However at the time of the crash the server was not running and the
log files and the data files of the database were on another hard
disk. I have managed to successfully recover the data and log files of
the database.
I would greatly appreciate if you could kindly show me the
procedure/process to restore the data and log files and bring the
database to life on a new SQL instance.
Please give me the command sequence to resurrect the database.
Thanks
KarenKaren,
1) Reinstall SQL Server and place the data files in the same directory
structure as before.
2) Apply the same service pack level that you had before
3) Stop the MSSQLServer service
4) Copy your old data files into your current DIR structure
5) Start the MSSQLServer service
It should all work beautifully.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Karen Middleton wrote:
> My development system hard disk crashed and I have lost the Operating
> system and the SQL executables and the system cannot be salvaged.
> However at the time of the crash the server was not running and the
> log files and the data files of the database were on another hard
> disk. I have managed to successfully recover the data and log files of
> the database.
> I would greatly appreciate if you could kindly show me the
> procedure/process to restore the data and log files and bring the
> database to life on a new SQL instance.
> Please give me the command sequence to resurrect the database.
> Thanks
> Karen

How to recreate/restore the database from the data and log files

My development system hard disk crashed and I have lost the Operating
system and the SQL executables and the system cannot be salvaged.
However at the time of the crash the server was not running and the
log files and the data files of the database were on another hard
disk. I have managed to successfully recover the data and log files of
the database.
I would greatly appreciate if you could kindly show me the
procedure/process to restore the data and log files and bring the
database to life on a new SQL instance.
Please give me the command sequence to resurrect the database.
Thanks
KarenKaren,
1) Reinstall SQL Server and place the data files in the same directory
structure as before.
2) Apply the same service pack level that you had before
3) Stop the MSSQLServer service
4) Copy your old data files into your current DIR structure
5) Start the MSSQLServer service
It should all work beautifully.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Karen Middleton wrote:
> My development system hard disk crashed and I have lost the Operating
> system and the SQL executables and the system cannot be salvaged.
> However at the time of the crash the server was not running and the
> log files and the data files of the database were on another hard
> disk. I have managed to successfully recover the data and log files of
> the database.
> I would greatly appreciate if you could kindly show me the
> procedure/process to restore the data and log files and bring the
> database to life on a new SQL instance.
> Please give me the command sequence to resurrect the database.
> Thanks
> Karen

How to recreate replication system objects?

Does anyone know what script I need to run to recreate the
replication system objects in MSSQL?
I believe it's in the Install Directory after installing SQL?
Will it destroy any existing publications/subscriptions on the server?
I have 2 other publications that are running I want to keep.
Thanks
RClick Replication component/ Generate SQL Script.
"Chris" <chriswinstone@.aol.com> wrote in message
news:1125399221.478160.216810@.o13g2000cwo.googlegr oups.com...
> Does anyone know what script I need to run to recreate the
> replication system objects in MSSQL?
> I believe it's in the Install Directory after installing SQL?
> Will it destroy any existing publications/subscriptions on the server?
> I have 2 other publications that are running I want to keep.
> Thanks
>
|||I'm guessing here, but did you have a failed installation? If so, then
reapplying sp4 should do the trick.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul
No I didn't have a failed installation as such. The replication was working
fine for about 18 months then all of a sudden I started getting errors from
the merge agent after executing sp_MSfetchidentityrange saying it could not
retrieve identity range resource for the table [TABLENAME].
A post from Hilary Cotter pointed me in the direction of the
msrepl_identity_range tables, however I decided to script the publication
for backup and try removing it. So I ran sp_removedreplication (probably too
hasty on that one!) then tried to run the puplication gen script thats when
I started getting the invalid object name 'syspublications' This is where I
am at at the moment. no replication at all! I can't even remove replication
manually - documented in Q324401!
I'm really stuck! Can't go forward or back! So I thought maybe recreating
the replication system objects would help me go in at least one direction!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uyQXnTZrFHA.464@.TK2MSFTNGP15.phx.gbl...
> I'm guessing here, but did you have a failed installation? If so, then
> reapplying sp4 should do the trick.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Chris,
what about if you disable replication then reenable, then execute your
scripts?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul
How do you do this? In QA or EM?
Thanks
Paul Ibison wrote:
> Chris,
> what about if you disable replication then reenable, then execute your
> scripts?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
|||Chris,
in EM - 'Tools, Replication, Disable Publishing'.
HTH,
Paul Ibison
|||Thanks Paul
Thats worked!
I now have a problem when creating my scripted publication again! See later
post.
Thanks again!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%236JW0YjrFHA.3060@.TK2MSFTNGP09.phx.gbl...
> Chris,
> in EM - 'Tools, Replication, Disable Publishing'.
> HTH,
> Paul Ibison
>

How to recreate a table and copy its data too?

I have different versions of essentially the same database on two different computers. From Computer B I need to copy one table AND its data to Computer A.

I've read several articles and have tried several things but can't figure out how to do it. Incidentally, I'm running SQL Server Express 2005 on both machines.

Robert Werner
Vancouver, BC

Import \ Export Wizard is the best way to recreate tables to another server with the same structure and data.|||But this does not exist in SQL Server 2005 Express does it?

Robert
|||

How about linked servers?

Or:

Code Snippet

SELECT *

INTO YourTable

FROM OPENROWSET('SQLNCLI', 'Server=SQLServer\SQLEXPRESS;Trusted_Connection=yes;',

'SELECT col1, col2, col3 FROM YourDB.dbo.YourTable') AS a;

Remember you'll need to enable Ad Hoc Queries in Surface Area Configuration.

HTH!

How to recreate a blank copy of dbs

I want to create a clean copy of my DB now that it is done. So it can be moved to another Server. It has some sample data in some tables that I would like to keep and some in other table that I don't. How can I do this?

Hi. I have two possible solutions:

1. You can generate a script with all the database objects: in enterprise manager right click over the database --> all tasks --> generate sql script... and in options tab include indexes, triggers, primary keys, etc.

For the tables you need with data, you could add the necessary inserts commands... but only if there are few rows.

2. Backup yuor database, and restore it in a new database with other name... delete data you don't need and use this new database as your "empty" databse