restore master database: February 2012

Saturday, February 25, 2012

restore MSDB or transfer jobs?

I restored MSDB onto a new box and my jobs won't run now:
Unable to connect to SQL Server '(local)'. The step failed.
Am I better off to use the Transfer Jobs Task in DTS or is there a simple
answer to this? If so, what about moving the DTS Packages?
SQL2K SP3
TIA, ChrisRI would scrip them out an apply on new server. But you might be able
to fix them on the new server
declare @.NewServerName varchar(50)
select @.NewServerName = cast(serverproperty ('ServerName') as
nvarchar(50))
update msdb..sysjobs
set originating_server = @.NewServerName
GO
checkpoint
GO|||The query converts the @.NewServerName to nothing:
Server: Msg 515, Level 16, State 2, Line 3
Cannot insert the value NULL into column 'originating_server', table
'msdb.dbo.sysjobs'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
declare @.NewServerName varchar(50)
select @.NewServerName = cast(serverproperty ('MyBox') as nvarchar(50))
print @.NewServerName
However, I did do an old fashioned:
Update SysJobs
set originating_server = 'MyBox'
but still I have the same problem.
If I did the script method, what would I do with my DTS Packages? Just save
to the new box? Would I be missing anything then from NOT restoring MSDB?
What do most of you do in this scenario?
"bert" <bertcord@.gmail.com> wrote in message
news:1106868585.875164.102430@.z14g2000cwz.googlegroups.com...
> I would scrip them out an apply on new server. But you might be able
> to fix them on the new server
> declare @.NewServerName varchar(50)
> select @.NewServerName = cast(serverproperty ('ServerName') as
> nvarchar(50))
> update msdb..sysjobs
> set originating_server = @.NewServerName
> GO
> checkpoint
> GO
>|||select serverproperty ('ServerName') returns null?
Sorry cant help you with the DTS jobs... I stay away from DTS.
did you stop and restart SQL
Bert|||Have a look here: http://www.sqldts.com/default.aspx?204
Andrew J. Kelly SQL MVP
"ChrisR" <bla@.noemail.com> wrote in message
news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
>I restored MSDB onto a new box and my jobs won't run now:
> Unable to connect to SQL Server '(local)'. The step failed.
> Am I better off to use the Transfer Jobs Task in DTS or is there a simple
> answer to this? If so, what about moving the DTS Packages?
> --
> SQL2K SP3
> TIA, ChrisR
>|||Not an option. Thanks Bert.
"bert" <bertcord@.gmail.com> wrote in message
news:1106878056.175663.124700@.z14g2000cwz.googlegroups.com...
> select serverproperty ('ServerName') returns null?
> Sorry cant help you with the DTS jobs... I stay away from DTS.
> did you stop and restart SQL
> Bert
>|||Thanks Andrew.
The "Save as" option suits me just fine. But assuming I do that in place of
Restoring MSDB, what about the jobs? Both the jobs to run these DTS Packages
and those that don't. I know the other options are to script them or to use
DTS to transfer them. Is anyone aware of the pros/ cons for either one? If I
do either of these methods, will I be missing something vital from NOT
Restoring MSDB? I don't think so based on past experience, but its been a
while.
TIA, ChrisR
"ChrisR" <bla@.noemail.com> wrote in message
news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
> I restored MSDB onto a new box and my jobs won't run now:
> Unable to connect to SQL Server '(local)'. The step failed.
> Am I better off to use the Transfer Jobs Task in DTS or is there a simple
> answer to this? If so, what about moving the DTS Packages?
> --
> SQL2K SP3
> TIA, ChrisR
>|||I prefer to script the jobs myself but I don't know of any thing that says
that is better or worse than moving MSDB per say. Even the KB that talks
about moving DB's suggests moving jobs via scripting.
http://www.support.microsoft.com/?id=314546
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:uQAGA5PBFHA.3524@.TK2MSFTNGP15.phx.gbl...
> Thanks Andrew.
> The "Save as" option suits me just fine. But assuming I do that in place
> of
> Restoring MSDB, what about the jobs? Both the jobs to run these DTS
> Packages
> and those that don't. I know the other options are to script them or to
> use
> DTS to transfer them. Is anyone aware of the pros/ cons for either one? If
> I
> do either of these methods, will I be missing something vital from NOT
> Restoring MSDB? I don't think so based on past experience, but its been a
> while.
> TIA, ChrisR
>
> "ChrisR" <bla@.noemail.com> wrote in message
> news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
>|||Thanks again Andrew. I think Im going to change my strategy on this whole
thing. It seems like to "Save as" for the DTS Packages and Script the Jobs/
Operators/ and Alerts I could get everything that I would from Restoring
MSDB minus a few headaches.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#AVGIwUBFHA.3924@.TK2MSFTNGP10.phx.gbl...
> I prefer to script the jobs myself but I don't know of any thing that says
> that is better or worse than moving MSDB per say. Even the KB that talks
> about moving DB's suggests moving jobs via scripting.
> http://www.support.microsoft.com/?id=314546
>
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <ChrisR@.noEmail.com> wrote in message
> news:uQAGA5PBFHA.3524@.TK2MSFTNGP15.phx.gbl...
If[vbcol=seagreen]
a[vbcol=seagreen]
simple[vbcol=seagreen]
>

Restore msdb file

I had a problem with operating system so copy copied all of my database
backup files to another. I reinstalled the operating system and restored al
l
of the except the msdb database.
I received the following error when trying to restore msdb database.
The file () cannot be overwritten. It is being used by database msdb use
WITH MOVE to identify a valid location for the file.
Please help me resolve this issue.Use the MOVE option of the RESTORE command. Documented in Books Online. Star
t by investigating the
file layout of your backup using RESTORE HEADERONLY and RESTORE FILELISTONLY
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8744E2D0-F15A-4338-91D1-2F21B4BC9669@.microsoft.com...
> I had a problem with operating system so copy copied all of my database
> backup files to another. I reinstalled the operating system and restored
all
> of the except the msdb database.
> I received the following error when trying to restore msdb database.
> The file () cannot be overwritten. It is being used by database msdb use
> WITH MOVE to identify a valid location for the file.
> Please help me resolve this issue.
>

Restore msdb file

I had a problem with operating system so copy copied all of my database
backup files to another. I reinstalled the operating system and restored all
of the except the msdb database.
I received the following error when trying to restore msdb database.
The file () cannot be overwritten. It is being used by database msdb use
WITH MOVE to identify a valid location for the file.
Please help me resolve this issue.Use the MOVE option of the RESTORE command. Documented in Books Online. Start by investigating the
file layout of your backup using RESTORE HEADERONLY and RESTORE FILELISTONLY.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8744E2D0-F15A-4338-91D1-2F21B4BC9669@.microsoft.com...
> I had a problem with operating system so copy copied all of my database
> backup files to another. I reinstalled the operating system and restored all
> of the except the msdb database.
> I received the following error when trying to restore msdb database.
> The file () cannot be overwritten. It is being used by database msdb use
> WITH MOVE to identify a valid location for the file.
> Please help me resolve this issue.
>

Restore msdb and master db from files.

Hi all,
My SQL Server crashed and I don't have any backup. So, I install new server, I attach user databases, everythink ok, but i need restore master and msdb databases from master.mdf , master.ldf and msdb.mdf and msdb.ldf. Is there any way to do it?
Thanks a lot
Systemspecialist,
Build your SQL Server directory structure *exactly* the same as it was
set up on the old server, and place the .mdf and .ldf files in the same
locations, then start SQL Server. It should just start up. Make sure you
have the same service pack applied on the new box as you did on the old,
before copying the data files over.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Systemspecialist wrote:

> Hi all,
> My SQL Server crashed and I don't have any backup. So, I install new server, I attach user databases, everythink ok, but i need restore master and msdb databases from master.mdf , master.ldf and msdb.mdf and msdb.ldf. Is there any way to do it?
> Thanks a lot
|||Hi,
I was about to say the same, but Systemspecialist has already installed,
applied service pack and attached the user databases.
The steps you supposed to do is:-
1. Copy all the MDF and LDF (Including system databases) to a safe directory
2. Install SQl server in same directory as old and apply the same service
pack as old
3. Stop SQL server and SQL Agent
4. Copy all the MDF and LDF files to the same folder
5. STart sql server.
This will start the sql server with the same setup as old.
Thanks
Hari
MCDBA
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:up6u5nJREHA.3300@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Systemspecialist,
> Build your SQL Server directory structure *exactly* the same as it was
> set up on the old server, and place the .mdf and .ldf files in the same
> locations, then start SQL Server. It should just start up. Make sure you
> have the same service pack applied on the new box as you did on the old,
> before copying the data files over.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Systemspecialist wrote:
server, I attach user databases, everythink ok, but i need restore master
and msdb databases from master.mdf , master.ldf and msdb.mdf and msdb.ldf.
Is there any way to do it?
>
|||Thanks Hari, you're saying the same thing as me, just better.
Hari wrote:
> Hi,
> I was about to say the same, but Systemspecialist has already installed,
> applied service pack and attached the user databases.
> The steps you supposed to do is:-
> 1. Copy all the MDF and LDF (Including system databases) to a safe directory
> 2. Install SQl server in same directory as old and apply the same service
> pack as old
> 3. Stop SQL server and SQL Agent
> 4. Copy all the MDF and LDF files to the same folder
> 5. STart sql server.
> This will start the sql server with the same setup as old.
> Thanks
> Hari
> MCDBA
>

Restore msdb and master db from files.

Hi all
My SQL Server crashed and I don't have any backup. So, I install new server, I attach user databases, everythink ok, but i need restore master and msdb databases from master.mdf , master.ldf and msdb.mdf and msdb.ldf. Is there any way to do it
Thanks a lotSystemspecialist,
Build your SQL Server directory structure *exactly* the same as it was
set up on the old server, and place the .mdf and .ldf files in the same
locations, then start SQL Server. It should just start up. Make sure you
have the same service pack applied on the new box as you did on the old,
before copying the data files over.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Systemspecialist wrote:
> Hi all,
> My SQL Server crashed and I don't have any backup. So, I install new server, I attach user databases, everythink ok, but i need restore master and msdb databases from master.mdf , master.ldf and msdb.mdf and msdb.ldf. Is there any way to do it?
> Thanks a lot|||Hi,
I was about to say the same, but Systemspecialist has already installed,
applied service pack and attached the user databases.
The steps you supposed to do is:-
1. Copy all the MDF and LDF (Including system databases) to a safe directory
2. Install SQl server in same directory as old and apply the same service
pack as old
3. Stop SQL server and SQL Agent
4. Copy all the MDF and LDF files to the same folder
5. STart sql server.
This will start the sql server with the same setup as old.
Thanks
Hari
MCDBA
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:up6u5nJREHA.3300@.TK2MSFTNGP09.phx.gbl...
> Systemspecialist,
> Build your SQL Server directory structure *exactly* the same as it was
> set up on the old server, and place the .mdf and .ldf files in the same
> locations, then start SQL Server. It should just start up. Make sure you
> have the same service pack applied on the new box as you did on the old,
> before copying the data files over.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Systemspecialist wrote:
> > Hi all,
> > My SQL Server crashed and I don't have any backup. So, I install new
server, I attach user databases, everythink ok, but i need restore master
and msdb databases from master.mdf , master.ldf and msdb.mdf and msdb.ldf.
Is there any way to do it?
> >
> > Thanks a lot
>|||Thanks Hari, you're saying the same thing as me, just better.
Hari wrote:
> Hi,
> I was about to say the same, but Systemspecialist has already installed,
> applied service pack and attached the user databases.
> The steps you supposed to do is:-
> 1. Copy all the MDF and LDF (Including system databases) to a safe directory
> 2. Install SQl server in same directory as old and apply the same service
> pack as old
> 3. Stop SQL server and SQL Agent
> 4. Copy all the MDF and LDF files to the same folder
> 5. STart sql server.
> This will start the sql server with the same setup as old.
> Thanks
> Hari
> MCDBA
>

Restore msdb and master db from files.

Hi all,
My SQL Server crashed and I don't have any backup. So, I install new server,
I attach user databases, everythink ok, but i need restore master and msdb
databases from master.mdf , master.ldf and msdb.mdf and msdb.ldf. Is there a
ny way to do it?
Thanks a lotSystemspecialist,
Build your SQL Server directory structure *exactly* the same as it was
set up on the old server, and place the .mdf and .ldf files in the same
locations, then start SQL Server. It should just start up. Make sure you
have the same service pack applied on the new box as you did on the old,
before copying the data files over.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Systemspecialist wrote:

> Hi all,
> My SQL Server crashed and I don't have any backup. So, I install new serve
r, I attach user databases, everythink ok, but i need restore master and msd
b databases from master.mdf , master.ldf and msdb.mdf and msdb.ldf. Is there
any way to do it?
> Thanks a lot|||Hi,
I was about to say the same, but Systemspecialist has already installed,
applied service pack and attached the user databases.
The steps you supposed to do is:-
1. Copy all the MDF and LDF (Including system databases) to a safe directory
2. Install SQl server in same directory as old and apply the same service
pack as old
3. Stop SQL server and SQL Agent
4. Copy all the MDF and LDF files to the same folder
5. STart sql server.
This will start the sql server with the same setup as old.
Thanks
Hari
MCDBA
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:up6u5nJREHA.3300@.TK2MSFTNGP09.phx.gbl...
> Systemspecialist,
> Build your SQL Server directory structure *exactly* the same as it was
> set up on the old server, and place the .mdf and .ldf files in the same
> locations, then start SQL Server. It should just start up. Make sure you
> have the same service pack applied on the new box as you did on the old,
> before copying the data files over.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Systemspecialist wrote:
>
server, I attach user databases, everythink ok, but i need restore master
and msdb databases from master.mdf , master.ldf and msdb.mdf and msdb.ldf.
Is there any way to do it?[vbcol=seagreen]
>|||Thanks Hari, you're saying the same thing as me, just better.
Hari wrote:
> Hi,
> I was about to say the same, but Systemspecialist has already installed,
> applied service pack and attached the user databases.
> The steps you supposed to do is:-
> 1. Copy all the MDF and LDF (Including system databases) to a safe directo
ry
> 2. Install SQl server in same directory as old and apply the same service
> pack as old
> 3. Stop SQL server and SQL Agent
> 4. Copy all the MDF and LDF files to the same folder
> 5. STart sql server.
> This will start the sql server with the same setup as old.
> Thanks
> Hari
> MCDBA
>

restore msdb - MSDE 2000

hi. i'm trying to restore msdb (dmp)... the dmp was taken from MS Desktop Engine 2000 and i'm trying to restore it to ms sql server 2000. is this possible? if not, how can i restore msdb to MSDE 2000? can't remember the command. (matter is a little urgent)

Thanks!

First, Stop the SQL Server Agent Service, then start the SQL Server Service and execute it:

RESTORE DATABASE msdb FROM DISK='c:\backup\msdb_backup.bak'

Regards and Happy new Year,

|||

The structure does not differ from MSDE or a greater version of SQL Server 2k. use the kb article here to go the offical way.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||http://sqlserver2000.databases.aspfaq.com/how-do-i-upsize-my-msde-database-to-full-blown-sql-server-2000.html a good one.

Restore MSDB

A quick question on MSDB and database backups:
We're moving one database to another. This database has a ton of jobs
associated with it. W're going ot be moving this database to a fresh install
of SQL Server.
My question is: if I wanted to maintain the jobs that I've created, would
just backing up MSDB and restoring it to the new box keep those jobs intact?
Would there be any issues with datqabase id's, etc. getting screwed up or ar
e
we good to go with just the restore of MSDB?
Thanks!!If you know the jobs that is associated to this database, you can generate
the SQL scripts for those jobs and run the script on the new server.
"A. Robinson" wrote:

> A quick question on MSDB and database backups:
> We're moving one database to another. This database has a ton of jobs
> associated with it. W're going ot be moving this database to a fresh insta
ll
> of SQL Server.
> My question is: if I wanted to maintain the jobs that I've created, would
> just backing up MSDB and restoring it to the new box keep those jobs intac
t?
> Would there be any issues with datqabase id's, etc. getting screwed up or
are
> we good to go with just the restore of MSDB?
> Thanks!!

restore msdb

hi, all
how to resotore msdb
thansks
Hi,
1. Stop the SQL Agent service
2. Execute the below command to set the databse to sinle user and restore.
Alter database msdb set single_user with rollback immediate
go
restore database msdb from disk='c:\backup\msdb_backup.bak'
go
Alter database msdb set multi_user with rollback immediate
3. If you are restoring the MSDB from a differnt server you need to update
the SYSJOBS table wth current sql server name,
Because the servername in the table will be source server and all the jobs
will fail.
How to update:-
UPDATE MSDB..SYSJOBS
SET Originating Server = @.@.Servername
WHERE Originating Server <> @.@.Servername
4. start sql agent service
Thanks
Hari
MCDBA
"metoonyc" <metoonyc@.yahoo.com> wrote in message
news:eH75WUSSEHA.3968@.TK2MSFTNGP09.phx.gbl...
> hi, all
> how to resotore msdb
> thansks
>

restore msdb

hi, all
how to resotore msdb
thansksHi,
1. Stop the SQL Agent service
2. Execute the below command to set the databse to sinle user and restore.
Alter database msdb set single_user with rollback immediate
go
restore database msdb from disk='c:\backup\msdb_backup.bak'
go
Alter database msdb set multi_user with rollback immediate
3. If you are restoring the MSDB from a differnt server you need to update
the SYSJOBS table wth current sql server name,
Because the servername in the table will be source server and all the jobs
will fail.
How to update:-
UPDATE MSDB..SYSJOBS
SET Originating Server = @.@.Servername
WHERE Originating Server <> @.@.Servername
4. start sql agent service
Thanks
Hari
MCDBA
"metoonyc" <metoonyc@.yahoo.com> wrote in message
news:eH75WUSSEHA.3968@.TK2MSFTNGP09.phx.gbl...
> hi, all
> how to resotore msdb
> thansks
>

restore msdb

hi, all
how to resotore msdb
thansksHi,
1. Stop the SQL Agent service
2. Execute the below command to set the databse to sinle user and restore.
Alter database msdb set single_user with rollback immediate
go
restore database msdb from disk='c:\backup\msdb_backup.bak'
go
Alter database msdb set multi_user with rollback immediate
3. If you are restoring the MSDB from a differnt server you need to update
the SYSJOBS table wth current sql server name,
Because the servername in the table will be source server and all the jobs
will fail.
How to update:-
UPDATE MSDB..SYSJOBS
SET Originating Server = @.@.Servername
WHERE Originating Server <> @.@.Servername
4. start sql agent service
Thanks
Hari
MCDBA
"metoonyc" <metoonyc@.yahoo.com> wrote in message
news:eH75WUSSEHA.3968@.TK2MSFTNGP09.phx.gbl...
> hi, all
> how to resotore msdb
> thansks
>

Restore MSDB

Hello,
can i restore a msdb database to an other database name such as test_msdb ?Yes.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"JKUhr" <anonymous@.discussions.microsoft.com> wrote in message
news:9D587948-3B5B-49A3-A751-84CDCA4B9A6E@.microsoft.com...
> Hello,
> can i restore a msdb database to an other database name such as test_msdb
?
>

restore MS SQL2000 DB using log files and no backups?

Hello!
I have the serious problem and can't find any ways to solve it. So, greatly
appreciate for any help!
I have corrupted MS SQL Server 2000 DataBase and no BackUp's. I also have
Transaction Log of this Database. Could anybody show me anyway how to
restore a database, using only transaction log file?
Thanks in forvard!
DenisIt is impossible to restore a database with only logs.
"Denis" <denis_vb@.pisem.net> wrote in message
news:bvoe2s$uap$1@.sky.kavkaz.elektra.ru...
> Hello!
> I have the serious problem and can't find any ways to solve it. So,
greatly
> appreciate for any help!
> I have corrupted MS SQL Server 2000 DataBase and no BackUp's. I also have
> Transaction Log of this Database. Could anybody show me anyway how to
> restore a database, using only transaction log file?
> Thanks in forvard!
> Denis
>|||If what you describe is true, then your database is toast.
Log backups alone are only of value if you have a full backup to restore
from first.
Jim
"Denis" <denis_vb@.pisem.net> wrote in message
news:bvoe2s$uap$1@.sky.kavkaz.elektra.ru...
> Hello!
> I have the serious problem and can't find any ways to solve it. So,
greatly
> appreciate for any help!
> I have corrupted MS SQL Server 2000 DataBase and no BackUp's. I also have
> Transaction Log of this Database. Could anybody show me anyway how to
> restore a database, using only transaction log file?
> Thanks in forvard!
> Denis
>

restore MS SQL2000 DB using log files and no backups?

quote:

> Hello!
> I have the serious problem and can't find any ways to solve it. So,

greatly

quote:

> appreciate for any help!
> I have corrupted MS SQL Server 2000 DataBase and no BackUp's. I also have
> Transaction Log of this Database. Could anybody show me anyway how to
> restore a database, using only transaction log file?
> Thanks in forvard!
> Denis
>

|||If what you describe is true, then your database is toast.
Log backups alone are only of value if you have a full backup to restore
from first.
Jim
"Denis" <denis_vb@.pisem.net> wrote in message
news:bvoe2s$uap$1@.sky.kavkaz.elektra.ru...

quote:

> Hello!
> I have the serious problem and can't find any ways to solve it. So,

greatly

quote:

> appreciate for any help!
> I have corrupted MS SQL Server 2000 DataBase and no BackUp's. I also have
> Transaction Log of this Database. Could anybody show me anyway how to
> restore a database, using only transaction log file?
> Thanks in forvard!
> Denis
>

Restore Move

Okay so I am new to the SQL arena! Just wanted to get that out of the way!

I have just set up a new SBS 2000 Server and am using SQL 2000 on it. I need to move a database from the old server, using SQL 7, to the new one. I have a backup of the old server db. However, I need to use the move command when restoring because the backup was done from drive D: and the new system is on drive c:.

I can't seem to figure out the proper syntax for a batch file to use osql to restore/move the database. Any suggestion would be appreciated.

Thanks,

RANHowdy

Use Enterprise Manager ( Its easier ), unless of course you need to automate the task...

in EM, right click on the database, choose All Tasks, Restore Database.

Under the "General" tab, type the database name you want to create during the restore.

Go to the "Options " tab.
Change the "Restore As" path for each file ( MDF & LDF ) to be on the required drive & directory.

All should work fine.

Post back if problems.

Cheers

SG.

restore microsoft SQL express 2005 database to another host.

Hi,
Very new to microsoft SQL server 2005 express, so please forgive a
simple question.
I am trying to restore a backup taken on one machine to another. I have
installed 2005 express on both machines along with Microsfot server
management Studio Express.
Step and failures recieved.
1. backed up database bugnet with SQL Server management / database /
tasks / backup.
2. Copied backup to another machine.
3. Created database bugnet ( created same schema as original ).
4. Tried SQL server managment restore and received the following:
system.data.SqlClient.SqlError: the backup set holds a backup from
a database other than the existing 'bugnet'database.
Is there an easy way to restore a 2005 express database to another
machine
Any help is this bread 'n' butter task would be so welcome.
Thanks.
Hi,
The best way:
1. You delete the database created by you.
2. Right Click the databases on the server where you want to restore the
database.
3. Click the restore database.
4.Then one screen will come with the name as Restore database
a) In General>> "To database" you write the name of the database.
b) Select from device option, then select the backup file from where you
want to restore the database
c) Select the Backup sets to restore.
5. Then go to the "option" option in left hand side pannel of Restore
Database Screen
a) Here you have to specify the actual path of the MDF and LDF file,
because it will show you the path as per the server from where you took the
backup.
If the file structure is same for both the sql server you can click OK to
restore the database.
Note: If created the database, without delete database step you can carry on
with all other steps with in "option", you have to choose overwrite the
existing database.
Regards
Swaprakash
"lotstolearn" wrote:

> Hi,
> Very new to microsoft SQL server 2005 express, so please forgive a
> simple question.
> I am trying to restore a backup taken on one machine to another. I have
> installed 2005 express on both machines along with Microsfot server
> management Studio Express.
> Step and failures recieved.
> 1. backed up database bugnet with SQL Server management / database /
> tasks / backup.
> 2. Copied backup to another machine.
> 3. Created database bugnet ( created same schema as original ).
> 4. Tried SQL server managment restore and received the following:
> system.data.SqlClient.SqlError: the backup set holds a backup from
> a database other than the existing 'bugnet'database.
> Is there an easy way to restore a 2005 express database to another
> machine
> Any help is this bread 'n' butter task would be so welcome.
> Thanks.
>

restore microsoft SQL express 2005 database to another host.

Hi,
Very new to microsoft SQL server 2005 express, so please forgive a
simple question.
I am trying to restore a backup taken on one machine to another. I have
installed 2005 express on both machines along with Microsfot server
management Studio Express.
Step and failures recieved.
1. backed up database bugnet with SQL Server management / database /
tasks / backup.
2. Copied backup to another machine.
3. Created database bugnet ( created same schema as original ).
4. Tried SQL server managment restore and received the following:
system.data.SqlClient.SqlError: the backup set holds a backup from
a database other than the existing 'bugnet'database.
Is there an easy way to restore a 2005 express database to another
machine
Any help is this bread 'n' butter task would be so welcome.
Thanks.Hi,
The best way:
1. You delete the database created by you.
2. Right Click the databases on the server where you want to restore the
database.
3. Click the restore database.
4.Then one screen will come with the name as Restore database
a) In General>> "To database" you write the name of the database.
b) Select from device option, then select the backup file from where you
want to restore the database
c) Select the Backup sets to restore.
5. Then go to the "option" option in left hand side pannel of Restore
Database Screen
a) Here you have to specify the actual path of the MDF and LDF file,
because it will show you the path as per the server from where you took the
backup.
If the file structure is same for both the sql server you can click OK to
restore the database.
Note: If created the database, without delete database step you can carry on
with all other steps with in "option", you have to choose overwrite the
existing database.
Regards
Swaprakash
"lotstolearn" wrote:

> Hi,
> Very new to microsoft SQL server 2005 express, so please forgive a
> simple question.
> I am trying to restore a backup taken on one machine to another. I have
> installed 2005 express on both machines along with Microsfot server
> management Studio Express.
> Step and failures recieved.
> 1. backed up database bugnet with SQL Server management / database /
> tasks / backup.
> 2. Copied backup to another machine.
> 3. Created database bugnet ( created same schema as original ).
> 4. Tried SQL server managment restore and received the following:
> system.data.SqlClient.SqlError: the backup set holds a backup from
> a database other than the existing 'bugnet'database.
> Is there an easy way to restore a 2005 express database to another
> machine
> Any help is this bread 'n' butter task would be so welcome.
> Thanks.
>

restore microsoft SQL express 2005 database to another host.

Hi,
Very new to microsoft SQL server 2005 express, so please forgive a
simple question.
I am trying to restore a backup taken on one machine to another. I have
installed 2005 express on both machines along with Microsfot server
management Studio Express.
Step and failures recieved.
1. backed up database bugnet with SQL Server management / database /
tasks / backup.
2. Copied backup to another machine.
3. Created database bugnet ( created same schema as original ).
4. Tried SQL server managment restore and received the following:
system.data.SqlClient.SqlError: the backup set holds a backup from
a database other than the existing 'bugnet'database.
Is there an easy way to restore a 2005 express database to another
machine
Any help is this bread 'n' butter task would be so welcome.
Thanks.Hi,
The best way:
1. You delete the database created by you.
2. Right Click the databases on the server where you want to restore the
database.
3. Click the restore database.
4.Then one screen will come with the name as Restore database
a) In General>> "To database" you write the name of the database.
b) Select from device option, then select the backup file from where you
want to restore the database
c) Select the Backup sets to restore.
5. Then go to the "option" option in left hand side pannel of Restore
Database Screen
a) Here you have to specify the actual path of the MDF and LDF file,
because it will show you the path as per the server from where you took the
backup.
If the file structure is same for both the sql server you can click OK to
restore the database.
Note: If created the database, without delete database step you can carry on
with all other steps with in "option", you have to choose overwrite the
existing database.
Regards
Swaprakash
"lotstolearn" wrote:
> Hi,
> Very new to microsoft SQL server 2005 express, so please forgive a
> simple question.
> I am trying to restore a backup taken on one machine to another. I have
> installed 2005 express on both machines along with Microsfot server
> management Studio Express.
> Step and failures recieved.
> 1. backed up database bugnet with SQL Server management / database /
> tasks / backup.
> 2. Copied backup to another machine.
> 3. Created database bugnet ( created same schema as original ).
> 4. Tried SQL server managment restore and received the following:
> system.data.SqlClient.SqlError: the backup set holds a backup from
> a database other than the existing 'bugnet'database.
> Is there an easy way to restore a 2005 express database to another
> machine
> Any help is this bread 'n' butter task would be so welcome.
> Thanks.
>

Restore Messed Up Table Names

Using SQL Server 2000 and moving to a new computer. We did a full backup of
the existing database to tape, brought up the new computer with a clean
install using the same server name and IP address, and did a full restore.
Not only were some permissions messed up, but Crystal Reports 10 and some
Access Data Projects refused to run. I finally discovered while running an
SP_WHO that the individual database names that we'd created (meaning not
'master' and the other standard tables) had several dozen blanks appended
onto the end of them. Looking at dbnames in the SP_WHO made it clear that
this had happened, and once I knew what I was looking for it was apparent in
Enterprise Manager as well when I'd select a database name in the left pane.
Interestingly, VB6 applications have no trouble connecting to these tables
without modification of the connection string. Every single CR10 report so
far has had to have it's tables relinked, and this has broken some other
code that looks at dbnames.

1: How could something like this happen?

2: How is it best fixed?

Thanks!
DavidDavid C. Barber (david@.NOSPAMdbarber.com) writes:

Quote:

Originally Posted by

Using SQL Server 2000 and moving to a new computer. We did a full
backup of the existing database to tape, brought up the new computer
with a clean install using the same server name and IP address, and did
a full restore. Not only were some permissions messed up, but Crystal
Reports 10 and some Access Data Projects refused to run. I finally
discovered while running an SP_WHO that the individual database names
that we'd created (meaning not 'master' and the other standard tables)
had several dozen blanks appended onto the end of them. Looking at
dbnames in the SP_WHO made it clear that this had happened, and once I
knew what I was looking for it was apparent in Enterprise Manager as
well when I'd select a database name in the left pane. Interestingly,
VB6 applications have no trouble connecting to these tables without
modification of the connection string. Every single CR10 report so far
has had to have it's tables relinked, and this has broken some other
code that looks at dbnames.
>
1: How could something like this happen?
>
2: How is it best fixed?

I'm a little confused. You first say "existing database" in singular,
and then you say "individual database names" in plural. Your subject
talks about messed up table names, but table names do not display in
sp_who. Then again, you call master a table.

I'm sorry if I'm picky, but if I don't understand what you mean, it's
difficult to answer. But I try to address it as good as I can:

1) If the database are messed up, this is probably because you added
the spaces when you restored the databases on the new server. The
database names are not carried over from the old server. Or did you
copy master too?

2) If the table names have been altered this would be very strange.
I would even say that it is impossible.

3) What typically does gets messed up when you move databases like this
is the mapping between server logins and database users. This can easily
be examined with sp_helpuser. If you have a random mapping, then you
have this problem. The stored procedure sp_changes_users_login can
be used to address this.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns9832738788829Yazorman@.127.0.0.1...

Quote:

Originally Posted by

David C. Barber (david@.NOSPAMdbarber.com) writes:

Quote:

Originally Posted by

Using SQL Server 2000 and moving to a new computer. We did a full
backup of the existing database to tape, brought up the new computer
with a clean install using the same server name and IP address, and did
a full restore. Not only were some permissions messed up, but Crystal
Reports 10 and some Access Data Projects refused to run. I finally
discovered while running an SP_WHO that the individual database names
that we'd created (meaning not 'master' and the other standard tables)
had several dozen blanks appended onto the end of them. Looking at
dbnames in the SP_WHO made it clear that this had happened, and once I
knew what I was looking for it was apparent in Enterprise Manager as
well when I'd select a database name in the left pane. Interestingly,
VB6 applications have no trouble connecting to these tables without
modification of the connection string. Every single CR10 report so far
has had to have it's tables relinked, and this has broken some other
code that looks at dbnames.

1: How could something like this happen?

2: How is it best fixed?

>
I'm a little confused. You first say "existing database" in singular,
and then you say "individual database names" in plural. Your subject
talks about messed up table names, but table names do not display in
sp_who. Then again, you call master a table.
>
I'm sorry if I'm picky, but if I don't understand what you mean, it's
difficult to answer. But I try to address it as good as I can:
>
1) If the database are messed up, this is probably because you added
the spaces when you restored the databases on the new server. The
database names are not carried over from the old server. Or did you
copy master too?
>
2) If the table names have been altered this would be very strange.
I would even say that it is impossible.
>
3) What typically does gets messed up when you move databases like this
is the mapping between server logins and database users. This can easily
be examined with sp_helpuser. If you have a random mapping, then you
have this problem. The stored procedure sp_changes_users_login can
be used to address this.

Sorry that I wasn't more clear before. Comes of being in too much of a
hurry.

We were upgrading the server hardware for our SQL Server 2000.

We backed up the entire existing server as a full backup.

We installed the new hardware and software, named the new server to be
identical to the old server which was taken off-line entirely.

We used the same IP address with the new server box.

We did a full restore of the backed up server databases.

The resulting individual databases, except for the standard ones like master
that are created by SQL Server itself all restored with several dozen blanks
appended to the end of the existing database name, which appears to break
Crystal Reports 10, Access Data Projects, and some VB6 code, although not
the basic connection to the database using VB6.

We don't know why this happened, nor the best way(s) to fix it.

David|||David C. Barber (david@.NOSPAMdbarber.com) writes:

Quote:

Originally Posted by

We were upgrading the server hardware for our SQL Server 2000.
>
We backed up the entire existing server as a full backup.

So you backed all files on the server with Windows backup, and you did not
backup the individual databases through SQL Servers own BACKUP command?

Did you stop SQL Server prior to starting this backup?

Quote:

Originally Posted by

The resulting individual databases, except for the standard ones like
master that are created by SQL Server itself all restored with several
dozen blanks appended to the end of the existing database name, which
appears to break Crystal Reports 10, Access Data Projects, and some VB6
code, although not the basic connection to the database using VB6.

Could you post the output of this query:

SELECT len(name), datalength(name)/2, name
FROM master..sysdatabases
ORDER BY name

Is the old hardware available, so you can start it, and run the same
query there?

Restore MDF problem

typical "user didn't backup database before performing transactions" case. (note: below I have renamed my mdf file as my_data.mdf for privacy purposes)

I'm trying to attach an mdf that I pulled off my server into my local database in SQL 2005 but get this error during the attach process:

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'BG-PC43'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:1022464; actual 0:0). It occurred during a read of page (1:1022464) in database ID 9 at offset 0x000001f3400000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\my_Data.MDF'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Could not open new database 'RMTEST'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 824)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=824&LinkId=20476

BUTTONS:

looks like that database is corrupted. You should restore from an earlier/different backup.

Restore MDF problem

typical "user didn't backup database before performing transactions" case. (note: below I have renamed my mdf file as my_data.mdf for privacy purposes)

I'm trying to attach an mdf that I pulled off my server into my local database in SQL 2005 but get this error during the attach process:

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'BG-PC43'. (Microsoft.SqlServer.Smo)

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=824&LinkId=20476

BUTTONS:

looks like that database is corrupted. You should restore from an earlier/different backup.

Restore MDF file

Hi all,
I need a urgent help. My server crashed yesterday because of the harddisk
problem. I lost my backup file and only got this *.mdf and *.ldf file.
I tried to restore these using attach database method and also by creating a
database...stop the sql service... overwrite the created database with the m
y
*.mdf and *.ldf file.
First method was showing this message "The File you've specified is not a
valid SQL server database file"
Second method ... Database is in Suspect mode ...If I tried to click on the
database, it will show "Error 823: I/O error (bad page ID) detected during
read at offset (0x0000000012000 in the file 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\GTS_MY.mdf"
Pls advise what I have to do to restore the above file...
Million Thanks in advance for any help regarding this...
Regards
SajithHi
Try these methods , I hope it will help you.
create a new database of the same name and log file and location as the old
database and log file
get rid of the old database.
you may be able to right click delete it in this situation or used
sp_removedb
create a new database of the right size and shape with correct log and data
file locations
stop sql
rename the new databases.mdf or delete it if you don't have enough space -
do not touch the .ldf
move back in the old database .mdf file or rename it back again
restart sql server
it should come up suspect
--
1. From a query window, set the status so that you can update the system
tables by running the following query:
use Master
go
sp_configure "allow", 1
go
reconfigure with override
go
2. Then set the status of the DB that is giving you the problem (XXXXX) into
Emergency Mode by running the following query:
update sysdatabases set status = 32768 where name = '<DBName>'
go
checkpoint
go
shutdown with nowait
go
3. Go into the data directory (MSSQL2000\DATA) and rename the log file
associated
the DB in question (XXXX.ldf) to some
temporary name, such as XXXX.TMP.
4. Exit the query window.
5. Then start up SQL Server from a DOS command window by issuing:
sqlservr -c -T3608 -T4022.
6. Bring up another query window and verify that the DB is in emergency mode
by issuing:
select Name, Status from Sysdatabases where name = '<DB_Name>'
7. Verify that the status is 32768. If it is, then issue the query:
dbcc traceon(3604)
dbcc rebuild_log('<DB_Name>','<log_filename>') <-- You will need
the quotation marks
REBUILD_LOG should take less than 5 minutes even on a very large
database. It should complete with the message
DBCC execution completed
8. Take the database out of bypass recovery mode by issuing the command
update sysdatabases set status = 0 where name = '<DBName>'
9. Exit the query window and then shutdown (Ctrl-C in the DOS window) and
restart SQL server. Verify the status of the
database by running DBCC NEWALLOC and DBCC CHECKDB on the database.
"Sajith" <Sajith@.discussions.microsoft.com> wrote in message
news:94C942B9-D3FA-49BC-8468-147071C844F7@.microsoft.com...
> Hi all,
> I need a urgent help. My server crashed yesterday because of the harddisk
> problem. I lost my backup file and only got this *.mdf and *.ldf file.
> I tried to restore these using attach database method and also by creating
> a
> database...stop the sql service... overwrite the created database with the
> my
> *.mdf and *.ldf file.
> First method was showing this message "The File you've specified is not a
> valid SQL server database file"
> Second method ... Database is in Suspect mode ...If I tried to click on
> the
> database, it will show "Error 823: I/O error (bad page ID) detected during
> read at offset (0x0000000012000 in the file 'C:\Program Files\Microsoft
> SQL
> Server\MSSQL\Data\GTS_MY.mdf"
> Pls advise what I have to do to restore the above file...
> Million Thanks in advance for any help regarding this...
> Regards
> Sajith|||Please do not multipost, answered in .server.
HTH, Jens Suessmeyer.|||Hi,
First let me thank you for replying to my issue...
I have followed through the steps you have mentioned... I had problem with
7th step. I am pasting the error message from the query analyser
dbcc traceon(3604)
dbcc rebuild_log('GTS_MY','GTS_MY_log')
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Server: Msg 5105, Level 16, State 2, Line 2
Device activation error. The physical file name 'GTS_MY_log' may be incorrec
t.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Pls advise...
Thanks a million...
Regards
Sajith
"Uri Dimant" wrote:

> Hi
> Try these methods , I hope it will help you.
>
> create a new database of the same name and log file and location as the ol
d
> database and log file
> get rid of the old database.
> you may be able to right click delete it in this situation or used
> sp_removedb
> create a new database of the right size and shape with correct log and dat
a
> file locations
> stop sql
> rename the new databases.mdf or delete it if you don't have enough space -
> do not touch the .ldf
> move back in the old database .mdf file or rename it back again
> restart sql server
> it should come up suspect
> --
> 1. From a query window, set the status so that you can update the system
> tables by running the following query:
> use Master
> go
> sp_configure "allow", 1
> go
> reconfigure with override
> go
> 2. Then set the status of the DB that is giving you the problem (XXXXX) in
to
> Emergency Mode by running the following query:
> update sysdatabases set status = 32768 where name = '<DBName>'
> go
> checkpoint
> go
> shutdown with nowait
> go
> 3. Go into the data directory (MSSQL2000\DATA) and rename the log file
> associated
> the DB in question (XXXX.ldf) to some
> temporary name, such as XXXX.TMP.
> 4. Exit the query window.
> 5. Then start up SQL Server from a DOS command window by issuing:
> sqlservr -c -T3608 -T4022.
> 6. Bring up another query window and verify that the DB is in emergency mo
de
> by issuing:
> select Name, Status from Sysdatabases where name = '<DB_Name>'
> 7. Verify that the status is 32768. If it is, then issue the query:
> dbcc traceon(3604)
> dbcc rebuild_log('<DB_Name>','<log_filename>') <-- You will need
> the quotation marks
> REBUILD_LOG should take less than 5 minutes even on a very large
> database. It should complete with the message
> DBCC execution completed
> 8. Take the database out of bypass recovery mode by issuing the command
> update sysdatabases set status = 0 where name = '<DBName>'
> 9. Exit the query window and then shutdown (Ctrl-C in the DOS window) and
> restart SQL server. Verify the status of the
> database by running DBCC NEWALLOC and DBCC CHECKDB on the database.
>
>
>
> "Sajith" <Sajith@.discussions.microsoft.com> wrote in message
> news:94C942B9-D3FA-49BC-8468-147071C844F7@.microsoft.com...
>
>|||Ok , make sure that the 'old' path of the LOG file is the same as a 'new'
one
How many Logs files do you have?
http://support.microsoft.com/defaul...kb;en-us;271223
"Sajith" <Sajith@.discussions.microsoft.com> wrote in message
news:49501D5E-487F-42AC-9566-E64985793726@.microsoft.com...
> Hi,
> First let me thank you for replying to my issue...
> I have followed through the steps you have mentioned... I had problem
> with
> 7th step. I am pasting the error message from the query analyser
> dbcc traceon(3604)
> dbcc rebuild_log('GTS_MY','GTS_MY_log')
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Server: Msg 5105, Level 16, State 2, Line 2
> Device activation error. The physical file name 'GTS_MY_log' may be
> incorrect.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Pls advise...
> Thanks a million...
> Regards
> Sajith
> "Uri Dimant" wrote:
>|||Hi,
Thanks very much for the reply.
you are right. It is not the same location. Earlier it was in
E:\Pro...mssql\data ... Right now our server has got only two drives. c: an
d
d:... I was trying d drive for this... so log files need to be in this
location. ? Is there any way to change this part for restoration... ?
Thanks again...
Regards
sajith
"Uri Dimant" wrote:

> Ok , make sure that the 'old' path of the LOG file is the same as a 'new'
> one
> How many Logs files do you have?
> http://support.microsoft.com/defaul...kb;en-us;271223
>
>
> "Sajith" <Sajith@.discussions.microsoft.com> wrote in message
> news:49501D5E-487F-42AC-9566-E64985793726@.microsoft.com...
>
>|||Please take a look at WITH MOVE option in the BOL
"Sajith" <Sajith@.discussions.microsoft.com> wrote in message
news:B8095D52-E924-4C56-90F6-7C35453329F7@.microsoft.com...
> Hi,
> Thanks very much for the reply.
> you are right. It is not the same location. Earlier it was in
> E:\Pro...mssql\data ... Right now our server has got only two drives. c:
> and
> d:... I was trying d drive for this... so log files need to be in this
> location. ? Is there any way to change this part for restoration... ?
> Thanks again...
> Regards
> sajith
> "Uri Dimant" wrote:
>|||Hi,
managed to restore the files in the same locations as it was earlier(before
the crash). But I still have the same problem in thelast step(during dbcc
rebuiid_Log). I have only one error log file.
pls advise
Thanks a ton for the help you are providing now...
Regards
Sajith
"Uri Dimant" wrote:

> Please take a look at WITH MOVE option in the BOL
>
>
> "Sajith" <Sajith@.discussions.microsoft.com> wrote in message
> news:B8095D52-E924-4C56-90F6-7C35453329F7@.microsoft.com...
>
>|||Try
EXEC sp_attach_single_file_db @.dbname = 'dbname',
@.physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\dbname.mdf'
"Sajith" <Sajith@.discussions.microsoft.com> wrote in message
news:08B47B26-BAD2-4FAB-823D-33641DFDE9A8@.microsoft.com...
> Hi,
> managed to restore the files in the same locations as it was
> earlier(before
> the crash). But I still have the same problem in thelast step(during dbcc
> rebuiid_Log). I have only one error log file.
> pls advise
> Thanks a ton for the help you are providing now...
> Regards
> Sajith
>
> "Uri Dimant" wrote:
>|||I am getting the error 823
I/O erro(bad page id)....
Right now i am trying to get the data from old files... i thikn it is
corrupted now... its heavy work for me...
Thanks very much for supporting for my issue...even though it is not
solved.. you have spent very long time for this. I really appreciate ...
Thanks again
Best Regards
Sajith
"Uri Dimant" wrote:

> Try
> EXEC sp_attach_single_file_db @.dbname = 'dbname',
> @.physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\dbname.md
f'
>
>
>
> "Sajith" <Sajith@.discussions.microsoft.com> wrote in message
> news:08B47B26-BAD2-4FAB-823D-33641DFDE9A8@.microsoft.com...
>
>

Restore mdf file

Restore MDF and NDF to a single DB on a new location

I would like to combine my .MDF and .NDF into a single file when I perform a
restore from a .BAK file. Is there a way of doing such task. The restore
will be done to a new database on a different SQL server which will only have
a .MDF.
environment:SQL 2000
o.s. Windows 2000.
Thank you,
Max
No you must restore it and then use the DBCC SHRINKFILE with the EMPTYFILE
option. See BOL for more details.
Andrew J. Kelly SQL MVP
"Max" <Max@.discussions.microsoft.com> wrote in message
news:0614BF32-7F21-4694-9B53-9EBB83F2D7BE@.microsoft.com...
>I would like to combine my .MDF and .NDF into a single file when I perform
>a
> restore from a .BAK file. Is there a way of doing such task. The restore
> will be done to a new database on a different SQL server which will only
> have
> a .MDF.
> environment:SQL 2000
> o.s. Windows 2000.
> Thank you,
> Max

Restore MDF and NDF to a single DB on a new location

I would like to combine my .MDF and .NDF into a single file when I perform a
restore from a .BAK file. Is there a way of doing such task. The restore
will be done to a new database on a different SQL server which will only hav
e
a .MDF.
environment:SQL 2000
o.s. Windows 2000.
Thank you,
MaxNo you must restore it and then use the DBCC SHRINKFILE with the EMPTYFILE
option. See BOL for more details.
Andrew J. Kelly SQL MVP
"Max" <Max@.discussions.microsoft.com> wrote in message
news:0614BF32-7F21-4694-9B53-9EBB83F2D7BE@.microsoft.com...
>I would like to combine my .MDF and .NDF into a single file when I perform
>a
> restore from a .BAK file. Is there a way of doing such task. The restore
> will be done to a new database on a different SQL server which will only
> have
> a .MDF.
> environment:SQL 2000
> o.s. Windows 2000.
> Thank you,
> Max

Restore MDF and NDF to a single DB on a new location

I would like to combine my .MDF and .NDF into a single file when I perform a
restore from a .BAK file. Is there a way of doing such task. The restore
will be done to a new database on a different SQL server which will only have
a .MDF.
environment:SQL 2000
o.s. Windows 2000.
Thank you,
MaxNo you must restore it and then use the DBCC SHRINKFILE with the EMPTYFILE
option. See BOL for more details.
--
Andrew J. Kelly SQL MVP
"Max" <Max@.discussions.microsoft.com> wrote in message
news:0614BF32-7F21-4694-9B53-9EBB83F2D7BE@.microsoft.com...
>I would like to combine my .MDF and .NDF into a single file when I perform
>a
> restore from a .BAK file. Is there a way of doing such task. The restore
> will be done to a new database on a different SQL server which will only
> have
> a .MDF.
> environment:SQL 2000
> o.s. Windows 2000.
> Thank you,
> Max

Restore mdf after system crash in sqls2k

During a system crash I was able to manually copy my .mdf but not the log.
After restoring the system sql runs an older version of the file, which i
want to replace with the copied mdf. Sql manager denies attaching the file
because it doesn't mach the log-file.
Is there a way of resetting the database or any other way for me to restore
the .mdf without dataloss?
Try sp_attach_single_file_db (complete syntax in Books Online).
However, it is not _guaranteed_ to work. It is only guaranteed to work if
you explicitly detach the database via EM or sp_detach_db. You might be
lucky though.
Jacco Schalkwijk
SQL Server MVP
"peteroff" <peteroff@.discussions.microsoft.com> wrote in message
news:D9111F32-7436-488A-A4E6-7EB62CC00DA8@.microsoft.com...
> During a system crash I was able to manually copy my .mdf but not the log.
> After restoring the system sql runs an older version of the file, which i
> want to replace with the copied mdf. Sql manager denies attaching the file
> because it doesn't mach the log-file.
> Is there a way of resetting the database or any other way for me to
> restore
> the .mdf without dataloss?
|||Hi,
Best option will be restore using good full database backup and apply your
transaction log backup. This
will provide you the data integrity. Attaching MDF file only might cause you
a data integrity problem
since you have not detached the database.
Thanks
Hari
MCDBA
"Jacco Schalkwijk" wrote:

> Try sp_attach_single_file_db (complete syntax in Books Online).
> However, it is not _guaranteed_ to work. It is only guaranteed to work if
> you explicitly detach the database via EM or sp_detach_db. You might be
> lucky though.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "peteroff" <peteroff@.discussions.microsoft.com> wrote in message
> news:D9111F32-7436-488A-A4E6-7EB62CC00DA8@.microsoft.com...
>
>
|||thanks for the posts. I tried sp_attach_single_file_db. The server denied and
gave 9003 mistake. The problem is, that I do not have a valid transaction log
file. I only was able to copy the *.mdf
I restored a good version, I tried building up a whole new set of files, but
none of these would allow me to sneak in with my old *.mdf.
Seems like the security system between *.mdf and *.ldf is too perfect.
Or does anybody know a reliable trick to put an *.mdf and an "empty" *.ldf
together?
"peteroff" wrote:

> During a system crash I was able to manually copy my .mdf but not the log.
> After restoring the system sql runs an older version of the file, which i
> want to replace with the copied mdf. Sql manager denies attaching the file
> because it doesn't mach the log-file.
> Is there a way of resetting the database or any other way for me to restore
> the .mdf without dataloss?
|||"peteroff" <peteroff@.discussions.microsoft.com> wrote in message
news:39D11384-A905-49C9-8C93-F128ADC192E0@.microsoft.com...
> thanks for the posts. I tried sp_attach_single_file_db. The server denied
and
> gave 9003 mistake. The problem is, that I do not have a valid transaction
log
> file. I only was able to copy the *.mdf
> I restored a good version, I tried building up a whole new set of files,
but
> none of these would allow me to sneak in with my old *.mdf.
> Seems like the security system between *.mdf and *.ldf is too perfect.
> Or does anybody know a reliable trick to put an *.mdf and an "empty" *.ldf
> together?
Microsoft can provide a method, but you will need to open a ticket with them
and they'll basically warn you that corruption is very likely.

Restore mdf after system crash in sqls2k

During a system crash I was able to manually copy my .mdf but not the log.
After restoring the system sql runs an older version of the file, which i
want to replace with the copied mdf. Sql manager denies attaching the file
because it doesn't mach the log-file.
Is there a way of resetting the database or any other way for me to restore
the .mdf without dataloss?Try sp_attach_single_file_db (complete syntax in Books Online).
However, it is not _guaranteed_ to work. It is only guaranteed to work if
you explicitly detach the database via EM or sp_detach_db. You might be
lucky though.
--
Jacco Schalkwijk
SQL Server MVP
"peteroff" <peteroff@.discussions.microsoft.com> wrote in message
news:D9111F32-7436-488A-A4E6-7EB62CC00DA8@.microsoft.com...
> During a system crash I was able to manually copy my .mdf but not the log.
> After restoring the system sql runs an older version of the file, which i
> want to replace with the copied mdf. Sql manager denies attaching the file
> because it doesn't mach the log-file.
> Is there a way of resetting the database or any other way for me to
> restore
> the .mdf without dataloss?|||Hi,
Best option will be restore using good full database backup and apply your
transaction log backup. This
will provide you the data integrity. Attaching MDF file only might cause you
a data integrity problem
since you have not detached the database.
Thanks
Hari
MCDBA
"Jacco Schalkwijk" wrote:
> Try sp_attach_single_file_db (complete syntax in Books Online).
> However, it is not _guaranteed_ to work. It is only guaranteed to work if
> you explicitly detach the database via EM or sp_detach_db. You might be
> lucky though.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "peteroff" <peteroff@.discussions.microsoft.com> wrote in message
> news:D9111F32-7436-488A-A4E6-7EB62CC00DA8@.microsoft.com...
> > During a system crash I was able to manually copy my .mdf but not the log.
> > After restoring the system sql runs an older version of the file, which i
> > want to replace with the copied mdf. Sql manager denies attaching the file
> > because it doesn't mach the log-file.
> > Is there a way of resetting the database or any other way for me to
> > restore
> > the .mdf without dataloss?
>
>|||thanks for the posts. I tried sp_attach_single_file_db. The server denied and
gave 9003 mistake. The problem is, that I do not have a valid transaction log
file. I only was able to copy the *.mdf
I restored a good version, I tried building up a whole new set of files, but
none of these would allow me to sneak in with my old *.mdf.
Seems like the security system between *.mdf and *.ldf is too perfect.
Or does anybody know a reliable trick to put an *.mdf and an "empty" *.ldf
together?
"peteroff" wrote:
> During a system crash I was able to manually copy my .mdf but not the log.
> After restoring the system sql runs an older version of the file, which i
> want to replace with the copied mdf. Sql manager denies attaching the file
> because it doesn't mach the log-file.
> Is there a way of resetting the database or any other way for me to restore
> the .mdf without dataloss?|||"peteroff" <peteroff@.discussions.microsoft.com> wrote in message
news:39D11384-A905-49C9-8C93-F128ADC192E0@.microsoft.com...
> thanks for the posts. I tried sp_attach_single_file_db. The server denied
and
> gave 9003 mistake. The problem is, that I do not have a valid transaction
log
> file. I only was able to copy the *.mdf
> I restored a good version, I tried building up a whole new set of files,
but
> none of these would allow me to sneak in with my old *.mdf.
> Seems like the security system between *.mdf and *.ldf is too perfect.
> Or does anybody know a reliable trick to put an *.mdf and an "empty" *.ldf
> together?
Microsoft can provide a method, but you will need to open a ticket with them
and they'll basically warn you that corruption is very likely.

Restore MB/Sec

What sort of MB/Sec do you have for a restore operation?
I have a 15 Disk RAID10 Array for the data, a 2 Disk RAID1 Mirror for the
log, and the backup is coming from a 6 Disk RAID10 array.. ..the database I
just restored reported "RESTORE DATABASE successfully processed 14937911
pages in 5809.377 seconds (21.064 MB/sec).", from perfmon I can see the
database I am restoring now is experiencing average sec/write of 0.08 -
0.10, and has a throughput of around 40/50MB.. ..I thought it would be a bit
better than this and just wanted to benchmark this against other
observations...
(Currently nothing else is occuring on the box apart from the restore)"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:91DF00F5-6938-41BD-928E-5942F3CB88E6@.microsoft.com...
> What sort of MB/Sec do you have for a restore operation?
> I have a 15 Disk RAID10 Array for the data, a 2 Disk RAID1 Mirror for the
> log, and the backup is coming from a 6 Disk RAID10 array.. ..the database
> I
> just restored reported "RESTORE DATABASE successfully processed 14937911
> pages in 5809.377 seconds (21.064 MB/sec).", from perfmon I can see the
> database I am restoring now is experiencing average sec/write of 0.08 -
> 0.10, and has a throughput of around 40/50MB.. ..I thought it would be a
> bit
> better than this and just wanted to benchmark this against other
> observations...
Hard to say.
I know when I did some testing on our servers, the limiting factor was the
source drives.
(btw, 15 disk RAID 10, I have to assume one is a hot spare, since otherwise
that doesn't work).
Also, you could be hitting network limitations.
> (Currently nothing else is occuring on the box apart from the restore)
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com

Restore MB/Sec

What sort of MB/Sec do you have for a restore operation?
I have a 15 Disk RAID10 Array for the data, a 2 Disk RAID1 Mirror for the
log, and the backup is coming from a 6 Disk RAID10 array.. ..the database I
just restored reported "RESTORE DATABASE successfully processed 14937911
pages in 5809.377 seconds (21.064 MB/sec).", from perfmon I can see the
database I am restoring now is experiencing average sec/write of 0.08 -
0.10, and has a throughput of around 40/50MB.. ..I thought it would be a bit
better than this and just wanted to benchmark this against other
observations...
(Currently nothing else is occuring on the box apart from the restore)"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:91DF00F5-6938-41BD-928E-5942F3CB88E6@.microsoft.com...
> What sort of MB/Sec do you have for a restore operation?
> I have a 15 Disk RAID10 Array for the data, a 2 Disk RAID1 Mirror for the
> log, and the backup is coming from a 6 Disk RAID10 array.. ..the database
> I
> just restored reported "RESTORE DATABASE successfully processed 14937911
> pages in 5809.377 seconds (21.064 MB/sec).", from perfmon I can see the
> database I am restoring now is experiencing average sec/write of 0.08 -
> 0.10, and has a throughput of around 40/50MB.. ..I thought it would be a
> bit
> better than this and just wanted to benchmark this against other
> observations...
Hard to say.
I know when I did some testing on our servers, the limiting factor was the
source drives.
(btw, 15 disk RAID 10, I have to assume one is a hot spare, since otherwise
that doesn't work).
Also, you could be hitting network limitations.

> (Currently nothing else is occuring on the box apart from the restore)
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com

restore master without sqlserver running?

due to a snafu on the reconfiguring of a server, a raid with the
database files was reformatted. unfortunately, our backup software does
not backup the db files since they are in use at the time of backup.
now, sqlserver won't start because there is no master.mdf file. i do
have a backup of the master db. is there any way i can restore that
without sqlserver running? or will i have to reinstall sqlserver and
then restore master along with all the other db's?This is a multi-part message in MIME format.
--=_NextPart_000_0135_01C3A9CE.AE173720
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
You can use the rebuildm.exe utility to rebuild master and then restore your
backup once that is done.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"chxxx" <chxxx@.dontemailme.com> wrote in message
news:3FB39E43.BBB2A260@.dontemailme.com...
due to a snafu on the reconfiguring of a server, a raid with the
database files was reformatted. unfortunately, our backup software does
not backup the db files since they are in use at the time of backup.
now, sqlserver won't start because there is no master.mdf file. i do
have a backup of the master db. is there any way i can restore that
without sqlserver running? or will i have to reinstall sqlserver and
then restore master along with all the other db's?
--=_NextPart_000_0135_01C3A9CE.AE173720
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

You can use the rebuildm.exe utility =to rebuild master and then restore your backup once that is done.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"chxxx" =wrote in message news:3FB39E43.BBB2A260@.do=ntemailme.com...due to a snafu on the reconfiguring of a server, a raid with thedatabase =files was reformatted. unfortunately, our backup software doesnot =backup the db files since they are in use at the time of backup.now, sqlserver =won't start because there is no master.mdf file. i dohave a backup =of the master db. is there any way i can restore thatwithout =sqlserver running? or will i have to reinstall sqlserver andthen restore =master along with all the other db's?

--=_NextPart_000_0135_01C3A9CE.AE173720--|||Ch
Look in books on line for how to restore your master
database. If you have a copy of the master.mdf, you may be
able to copy it to the location it should be in (d:mssql
or similar) and start sql server without needing to do
anything else.
If you can't restore master either way. If you still have
your user database you can do a fresh install and then
attach your databases. You will need to add your user
logins to the server before you can use it.
If you can not recover the msdb database you will also
need to recreate any jobs you had.
Hope this helps
John|||chxxx wrote:
> due to a snafu on the reconfiguring of a server, a raid with the
> database files was reformatted. unfortunately, our backup software
> does not backup the db files since they are in use at the time of
> backup. now, sqlserver won't start because there is no master.mdf
> file. i do have a backup of the master db. is there any way i can
> restore that without sqlserver running? or will i have to reinstall
> sqlserver and then restore master along with all the other db's?
when you say you have a backup of the master file is it a sql backup or
a standard file backup. If latter then you should be able to restore
that to its location and restart the server. If you are saying you have
a sql backup then you will need sql started in order to restore. You
can rebuild the master (rebuildm) and once sql is started you can
restore back from your sql backup to return to the original one.
--
Br,
Mark Broadbent
mcdba , mcse+i
=============|||Mark Broadbent wrote:
> chxxx wrote:
> > due to a snafu on the reconfiguring of a server, a raid with the
> > database files was reformatted. unfortunately, our backup software
> > does not backup the db files since they are in use at the time of
> > backup. now, sqlserver won't start because there is no master.mdf
> > file. i do have a backup of the master db. is there any way i can
> > restore that without sqlserver running? or will i have to reinstall
> > sqlserver and then restore master along with all the other db's?
> when you say you have a backup of the master file is it a sql backup or
> a standard file backup. If latter then you should be able to restore
> that to its location and restart the server. If you are saying you have
> a sql backup then you will need sql started in order to restore. You
> can rebuild the master (rebuildm) and once sql is started you can
> restore back from your sql backup to return to the original one.
the only backups i have are the sql backup files (.bak). the .mdf and .ldf
files are gone due to backup software not being able to back those up.
after reading bol, looks like i'll be able to use rebuildm to get sqlserver
running again.
then i'll be able to restore master and all other db's from the .bak files
i have on disk.|||chxxx wrote:
>
> Mark Broadbent wrote:
> > chxxx wrote:
> >
> > > due to a snafu on the reconfiguring of a server, a raid with the
> > > database files was reformatted. unfortunately, our backup
> > > software does not backup the db files since they are in use at
> > > the time of backup. now, sqlserver won't start because there is
> > > no master.mdf file. i do have a backup of the master db. is
> > > there any way i can restore that without sqlserver running? or
> > > will i have to reinstall sqlserver and then restore master along
> > > with all the other db's?
> >
> > when you say you have a backup of the master file is it a sql
> > backup or a standard file backup. If latter then you should be able
> > to restore that to its location and restart the server. If you are
> > saying you have a sql backup then you will need sql started in
> > order to restore. You can rebuild the master (rebuildm) and once
> > sql is started you can restore back from your sql backup to return
> > to the original one.
> the only backups i have are the sql backup files (.bak). the .mdf
> and .ldf files are gone due to backup software not being able to back
> those up.
> after reading bol, looks like i'll be able to use rebuildm to get
> sqlserver running again.
> then i'll be able to restore master and all other db's from the .bak
> files i have on disk.
Correct :?)
--
Br,
Mark Broadbent
mcdba , mcse+i
=============

Saturday, February 25, 2012

restore MSDB or transfer jobs?

Restore msdb file

Restore msdb file

Restore msdb and master db from files.

Restore msdb and master db from files.

Restore msdb and master db from files.

restore msdb - MSDE 2000

Restore MSDB

restore msdb

restore msdb

restore msdb

Restore MSDB

restore MS SQL2000 DB using log files and no backups?

restore MS SQL2000 DB using log files and no backups?

Restore Move

restore microsoft SQL express 2005 database to another host.

restore microsoft SQL express 2005 database to another host.

restore microsoft SQL express 2005 database to another host.

Restore Messed Up Table Names

Restore MDF problem

Restore MDF problem

Restore MDF file

Restore mdf file

Restore mdf file

Restore mdf file

Restore MDF and NDF to a single DB on a new location

Restore MDF and NDF to a single DB on a new location

Restore MDF and NDF to a single DB on a new location

Restore mdf after system crash in sqls2k

Restore mdf after system crash in sqls2k

Restore MB/Sec

Restore MB/Sec

restore master without sqlserver running?

restore master database

Blog Archive

About Me