restore db from device in sql2005

Need to restore the db from network location.
After I click restore from device. The select path box is grayed out. I
could not type the network path.
(I know i could restore it in query analyzer by using restore code.)
Any ideas?
ThanksDid you press the ... button to the right of that text box?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mecn" <mecn2002@.yahoo.com> wrote in message news:ud2imO78GHA.4224@.TK2MSFTNGP02.phx.gbl...
> Need to restore the db from network location.
> After I click restore from device. The select path box is grayed out. I
> could not type the network path.
> (I know i could restore it in query analyzer by using restore code.)
> Any ideas?
> Thanks
>|||I did, Just let you select local divers only
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23mgSe1B9GHA.3916@.TK2MSFTNGP04.phx.gbl...
> Did you press the ... button to the right of that text box?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:ud2imO78GHA.4224@.TK2MSFTNGP02.phx.gbl...
>> Need to restore the db from network location.
>> After I click restore from device. The select path box is grayed out. I
>> could not type the network path.
>> (I know i could restore it in query analyzer by using restore code.)
>> Any ideas?
>> Thanks|||mecn wrote:
> I did, Just let you select local divers only
>
Correct, you can't point/click to a network path, but you can TYPE the
path into the filename field on that same window...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The problem is I could not. The box is grayed out.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4538D8BB.7050509@.realsqlguy.com...
> mecn wrote:
>> I did, Just let you select local divers only
> Correct, you can't point/click to a network path, but you can TYPE the
> path into the filename field on that same window...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||I couldn't type. Is it sort of permission issue?
Thanks
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4538D8BB.7050509@.realsqlguy.com...
> mecn wrote:
>> I did, Just let you select local divers only
> Correct, you can't point/click to a network path, but you can TYPE the
> path into the filename field on that same window...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||sorry. you are saying that I could type the network path in to the file name
box?
i.e. \\network\servover\dir\abc.bak?
Thanks
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4538D8BB.7050509@.realsqlguy.com...
> mecn wrote:
>> I did, Just let you select local divers only
> Correct, you can't point/click to a network path, but you can TYPE the
> path into the filename field on that same window...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||I don't think taht you could type the path and file name into filename box.
Filename box is only for file name like abc.bak. right?!
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4538D8BB.7050509@.realsqlguy.com...
> mecn wrote:
>> I did, Just let you select local divers only
> Correct, you can't point/click to a network path, but you can TYPE the
> path into the filename field on that same window...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||I am sorry, you are right!!!! I could type the network path into file name
box and it works--THANK YOU VERY MUCH!!!@.!!
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4538D8BB.7050509@.realsqlguy.com...
> mecn wrote:
>> I did, Just let you select local divers only
> Correct, you can't point/click to a network path, but you can TYPE the
> path into the filename field on that same window...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||mecn wrote:
> I am sorry, you are right!!!! I could type the network path into file name
> box and it works--THANK YOU VERY MUCH!!!@.!!
>
Great... Now that you've accomplished that, can you explain why this
was easier than just issuing the RESTORE DATABASE command in a query window?
Tracy McKibben
MCDBA
http://www.realsqlguy.com

restore db from device in sql2005

Need to restore the db from network location.
After I click restore from device. The select path box is grayed out. I
could not type the network path.
(I know i could restore it in query analyzer by using restore code.)
Any ideas?
Thanks
Did you press the ... button to the right of that text box?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mecn" <mecn2002@.yahoo.com> wrote in message news:ud2imO78GHA.4224@.TK2MSFTNGP02.phx.gbl...
> Need to restore the db from network location.
> After I click restore from device. The select path box is grayed out. I
> could not type the network path.
> (I know i could restore it in query analyzer by using restore code.)
> Any ideas?
> Thanks
>
|||I did, Just let you select local divers only
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23mgSe1B9GHA.3916@.TK2MSFTNGP04.phx.gbl...[vbcol=seagreen]
> Did you press the ... button to the right of that text box?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:ud2imO78GHA.4224@.TK2MSFTNGP02.phx.gbl...
|||mecn wrote:
> I did, Just let you select local divers only
>
Correct, you can't point/click to a network path, but you can TYPE the
path into the filename field on that same window...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||The problem is I could not. The box is grayed out.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4538D8BB.7050509@.realsqlguy.com...
> mecn wrote:
> Correct, you can't point/click to a network path, but you can TYPE the
> path into the filename field on that same window...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||I couldn't type. Is it sort of permission issue?
Thanks
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4538D8BB.7050509@.realsqlguy.com...
> mecn wrote:
> Correct, you can't point/click to a network path, but you can TYPE the
> path into the filename field on that same window...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||sorry. you are saying that I could type the network path in to the file name
box?
i.e. \\network\servover\dir\abc.bak?
Thanks
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4538D8BB.7050509@.realsqlguy.com...
> mecn wrote:
> Correct, you can't point/click to a network path, but you can TYPE the
> path into the filename field on that same window...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||I don't think taht you could type the path and file name into filename box.
Filename box is only for file name like abc.bak. right?!
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4538D8BB.7050509@.realsqlguy.com...
> mecn wrote:
> Correct, you can't point/click to a network path, but you can TYPE the
> path into the filename field on that same window...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||I am sorry, you are right!!!! I could type the network path into file name
box and it works--THANK YOU VERY MUCH!!!@.!!
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4538D8BB.7050509@.realsqlguy.com...
> mecn wrote:
> Correct, you can't point/click to a network path, but you can TYPE the
> path into the filename field on that same window...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||mecn wrote:
> I am sorry, you are right!!!! I could type the network path into file name
> box and it works--THANK YOU VERY MUCH!!!@.!!
>
Great... Now that you've accomplished that, can you explain why this
was easier than just issuing the RESTORE DATABASE command in a query window?
Tracy McKibben
MCDBA
http://www.realsqlguy.com

restore db from device in sql2005

Need to restore the db from network location.
After I click restore from device. The select path box is grayed out. I
could not type the network path.
(I know i could restore it in query analyzer by using restore code.)
Any ideas?
ThanksDid you press the ... button to the right of that text box?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mecn" <mecn2002@.yahoo.com> wrote in message news:ud2imO78GHA.4224@.TK2MSFTNGP02.phx.gbl...[v
bcol=seagreen]
> Need to restore the db from network location.
> After I click restore from device. The select path box is grayed out. I
> could not type the network path.
> (I know i could restore it in query analyzer by using restore code.)
> Any ideas?
> Thanks
>[/vbcol]|||I did, Just let you select local divers only
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23mgSe1B9GHA.3916@.TK2MSFTNGP04.phx.gbl...[vbcol=seagreen]
> Did you press the ... button to the right of that text box?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:ud2imO78GHA.4224@.TK2MSFTNGP02.phx.gbl...|||mecn wrote:
> I did, Just let you select local divers only
>
Correct, you can't point/click to a network path, but you can TYPE the
path into the filename field on that same window...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The problem is I could not. The box is grayed out.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4538D8BB.7050509@.realsqlguy.com...
> mecn wrote:
> Correct, you can't point/click to a network path, but you can TYPE the
> path into the filename field on that same window...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||I couldn't type. Is it sort of permission issue?
Thanks
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4538D8BB.7050509@.realsqlguy.com...
> mecn wrote:
> Correct, you can't point/click to a network path, but you can TYPE the
> path into the filename field on that same window...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||sorry. you are saying that I could type the network path in to the file name
box?
i.e. \\network\servover\dir\abc.bak?
Thanks
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4538D8BB.7050509@.realsqlguy.com...
> mecn wrote:
> Correct, you can't point/click to a network path, but you can TYPE the
> path into the filename field on that same window...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||I don't think taht you could type the path and file name into filename box.
Filename box is only for file name like abc.bak. right?!
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4538D8BB.7050509@.realsqlguy.com...
> mecn wrote:
> Correct, you can't point/click to a network path, but you can TYPE the
> path into the filename field on that same window...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||I am sorry, you are right!!!! I could type the network path into file name
box and it works--THANK YOU VERY MUCH!!!@.!!
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4538D8BB.7050509@.realsqlguy.com...
> mecn wrote:
> Correct, you can't point/click to a network path, but you can TYPE the
> path into the filename field on that same window...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||mecn wrote:
> I am sorry, you are right!!!! I could type the network path into file name
> box and it works--THANK YOU VERY MUCH!!!@.!!
>
Great... Now that you've accomplished that, can you explain why this
was easier than just issuing the RESTORE DATABASE command in a query window?
Tracy McKibben
MCDBA
http://www.realsqlguy.comsql

restore db

How do you restore the backup of a SQL2000 DB to SQL2005 DB? Thanks.Sorry Folks, I got it now. Is the restoration 100% compactible to SQL 2005?
"00KobeBrian" <a@.b.com> wrote in message
news:%23WUHmKR6GHA.3508@.TK2MSFTNGP06.phx.gbl...
> How do you restore the backup of a SQL2000 DB to SQL2005 DB? Thanks.
>|||Yes, you can restore a SQL Server 2000 backup to SQL Server 2005.
You cannot go the other way, however.
--
Keith Kratochvil
"00KobeBrian" <a@.b.com> wrote in message
news:%23zgikYR6GHA.4996@.TK2MSFTNGP04.phx.gbl...
> Sorry Folks, I got it now. Is the restoration 100% compactible to SQL
> 2005?
>
> "00KobeBrian" <a@.b.com> wrote in message
> news:%23WUHmKR6GHA.3508@.TK2MSFTNGP06.phx.gbl...
>> How do you restore the backup of a SQL2000 DB to SQL2005 DB? Thanks.
>

restore db

How do you restore the backup of a SQL2000 DB to SQL2005 DB? Thanks.
Sorry Folks, I got it now. Is the restoration 100% compactible to SQL 2005?
"00KobeBrian" <a@.b.com> wrote in message
news:%23WUHmKR6GHA.3508@.TK2MSFTNGP06.phx.gbl...
> How do you restore the backup of a SQL2000 DB to SQL2005 DB? Thanks.
>
|||Yes, you can restore a SQL Server 2000 backup to SQL Server 2005.
You cannot go the other way, however.
Keith Kratochvil
"00KobeBrian" <a@.b.com> wrote in message
news:%23zgikYR6GHA.4996@.TK2MSFTNGP04.phx.gbl...
> Sorry Folks, I got it now. Is the restoration 100% compactible to SQL
> 2005?
>
> "00KobeBrian" <a@.b.com> wrote in message
> news:%23WUHmKR6GHA.3508@.TK2MSFTNGP06.phx.gbl...
>

restore db

How do you restore the backup of a SQL2000 DB to SQL2005 DB? Thanks.Sorry Folks, I got it now. Is the restoration 100% compactible to SQL 2005?
"00KobeBrian" <a@.b.com> wrote in message
news:%23WUHmKR6GHA.3508@.TK2MSFTNGP06.phx.gbl...
> How do you restore the backup of a SQL2000 DB to SQL2005 DB? Thanks.
>|||Yes, you can restore a SQL Server 2000 backup to SQL Server 2005.
You cannot go the other way, however.
Keith Kratochvil
"00KobeBrian" <a@.b.com> wrote in message
news:%23zgikYR6GHA.4996@.TK2MSFTNGP04.phx.gbl...
> Sorry Folks, I got it now. Is the restoration 100% compactible to SQL
> 2005?
>
> "00KobeBrian" <a@.b.com> wrote in message
> news:%23WUHmKR6GHA.3508@.TK2MSFTNGP06.phx.gbl...
>

Restore database from a network drive

Hi guys,
I have Windows 2003 server and SQL2005 Oct. CPT running. I have backed up
file .BAK on a network. So in my SQL server, I mapped the folder and try to
restore database from there, I have gotten an error. So i thought might be
the backup file is bad. So I copied my model backedup file to the network
drive and ran FilelistOnly. I got exact same error return,
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'q:\model_backup_200601240100.bak'. Operating
system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
Has any one seem this error? I don't think I have restored a database from
network drive but I know I can access to the network drive without an issue.
Below is the simple steps what I did on my sql server.
on dos prompt win, net use q: \\myserver\backupfile.bak
q:
dir *.bak
I saw the backup file.
In query windows, 2005 SQL management studio.
restore filelistonly from disk = 'q:\mybackupfile.bak'
Is it not possible to restore it from network drive?
Any input will be appreciated.
Sunny,
Restoring TO and FROM NETWORK drives is possible, but rather than
mapped drives you should use the UNC drives rather than mapped driver
letters. This has many implications, one of them is that you have to
make sure that if e.g. you use the SQL Agent you have to make sure that
the sevrice account has the driver letter mapped in his logon script
(which is a hazzle). So rather use a UNC path with the appropiate
permissions.
HTH, Jens Suessmeyer.
|||Hi Jens
Thanks for the input. I just realized that the SQL server serive is running
as LocalSystem. I thought I changed all SQL services to run as a domain user
ID.
I think that's why. I have to wait until 5 PM and try it if that's the case
or not.
Will post once I find out.
Thanks,
"Jens" wrote:

> Restoring TO and FROM NETWORK drives is possible, but rather than
> mapped drives you should use the UNC drives rather than mapped driver
> letters. This has many implications, one of them is that you have to
> make sure that if e.g. you use the SQL Agent you have to make sure that
> the sevrice account has the driver letter mapped in his logon script
> (which is a hazzle). So rather use a UNC path with the appropiate
> permissions.
> HTH, Jens Suessmeyer.
>

Restore database from a network drive

Hi guys,
I have Windows 2003 server and SQL2005 Oct. CPT running. I have backed up
file .BAK on a network. So in my SQL server, I mapped the folder and try to
restore database from there, I have gotten an error. So i thought might be
the backup file is bad. So I copied my model backedup file to the network
drive and ran FilelistOnly. I got exact same error return,
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'q:\model_backup_200601240100.bak'. Operating
system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
Has any one seem this error? I don't think I have restored a database from
network drive but I know I can access to the network drive without an issue.
Below is the simple steps what I did on my sql server.
on dos prompt win, net use q: \\myserver\backupfile.bak
q:
dir *.bak
I saw the backup file.
In query windows, 2005 SQL management studio.
restore filelistonly from disk = 'q:\mybackupfile.bak'
Is it not possible to restore it from network drive'
Any input will be appreciated.
Sunny,Restoring TO and FROM NETWORK drives is possible, but rather than
mapped drives you should use the UNC drives rather than mapped driver
letters. This has many implications, one of them is that you have to
make sure that if e.g. you use the SQL Agent you have to make sure that
the sevrice account has the driver letter mapped in his logon script
(which is a hazzle). So rather use a UNC path with the appropiate
permissions.
HTH, Jens Suessmeyer.|||Hi Jens
Thanks for the input. I just realized that the SQL server serive is running
as LocalSystem. I thought I changed all SQL services to run as a domain use
r
ID.
I think that's why. I have to wait until 5 PM and try it if that's the case
or not.
Will post once I find out.
Thanks,
"Jens" wrote:

> Restoring TO and FROM NETWORK drives is possible, but rather than
> mapped drives you should use the UNC drives rather than mapped driver
> letters. This has many implications, one of them is that you have to
> make sure that if e.g. you use the SQL Agent you have to make sure that
> the sevrice account has the driver letter mapped in his logon script
> (which is a hazzle). So rather use a UNC path with the appropiate
> permissions.
> HTH, Jens Suessmeyer.
>sql

Restore database from a network drive

Hi guys,
I have Windows 2003 server and SQL2005 Oct. CPT running. I have backed up
file .BAK on a network. So in my SQL server, I mapped the folder and try to
restore database from there, I have gotten an error. So i thought might be
the backup file is bad. So I copied my model backedup file to the network
drive and ran FilelistOnly. I got exact same error return,
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'q:\model_backup_200601240100.bak'. Operating
system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
Has any one seem this error? I don't think I have restored a database from
network drive but I know I can access to the network drive without an issue.
Below is the simple steps what I did on my sql server.
on dos prompt win, net use q: \\myserver\backupfile.bak
q:
dir *.bak
I saw the backup file.
In query windows, 2005 SQL management studio.
restore filelistonly from disk = 'q:\mybackupfile.bak'
Is it not possible to restore it from network drive'
Any input will be appreciated.
Sunny,Restoring TO and FROM NETWORK drives is possible, but rather than
mapped drives you should use the UNC drives rather than mapped driver
letters. This has many implications, one of them is that you have to
make sure that if e.g. you use the SQL Agent you have to make sure that
the sevrice account has the driver letter mapped in his logon script
(which is a hazzle). So rather use a UNC path with the appropiate
permissions.
HTH, Jens Suessmeyer.|||Hi Jens
Thanks for the input. I just realized that the SQL server serive is running
as LocalSystem. I thought I changed all SQL services to run as a domain user
ID.
I think that's why. I have to wait until 5 PM and try it if that's the case
or not.
Will post once I find out.
Thanks,
"Jens" wrote:
> Restoring TO and FROM NETWORK drives is possible, but rather than
> mapped drives you should use the UNC drives rather than mapped driver
> letters. This has many implications, one of them is that you have to
> make sure that if e.g. you use the SQL Agent you have to make sure that
> the sevrice account has the driver letter mapped in his logon script
> (which is a hazzle). So rather use a UNC path with the appropiate
> permissions.
> HTH, Jens Suessmeyer.
>

restore could not start database

Hi,

I am trying to restore SQL2K DB backup to SQL2005 Express. The process goes fine, but at the end of the process (100% progress), it shows error Restore Could not start database.

It does not provide more detail to remedy the error.

Anybody faced this problem and have any solution?

Thanks,

Hiten

What happens when you manually start the sql server services?|||

SQL server services are running, but the database is not online. It puts the database in to suspect state.

I looked in to event logs, and it turned out that the database I was restoring already had 'sys' sql user , since in 2005 sys user is reserved, the restore operation tried to rename it to something else, the log shows following message

Warning: User "sys" (principal_id = 24) in database "Test" has been renamed to "_ID_0x00000018_RENAMED_FROM_sys". "sys" is a reserved user or schema name in this version of SQL Server.

There were few other error/warnings for objects used by sys.

Warning: A column nullability inconsistency was detected in the metadata of index "_WA_Sys_CurrentStatusID_7080FE81" (index_id = 2) on object ID 1887501953 in database "Test". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.

Warning: A column nullability inconsistency was detected in the metadata of index "_WA_Sys_NextStatusID_7080FE81" (index_id = 3) on object ID 1887501953 in database "Test". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.

Warning: A column nullability inconsistency was detected in the metadata of index "_WA_Sys_TransitionString_7080FE81" (index_id = 4) on object ID 1887501953 in database "Test". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.

and at last

Warning: Database "Test" was marked suspect because of actions taken during upgrade. See errorlog or eventlog for more information. Use ALTER DATABASE to bring the database online. The database will come online in restricted_user state.

RESTORE could not start database 'Test'.

The database 'Test' is marked RESTORING and is in a state that does not allow recovery to be run.

In the end, it puts database to suspect. I don't know how to bring it back to online for multiple users. When I tried to bring it online using ALTER DATABASE command, it brought it online for single user only, which did not serve my purpose, and I don't know what needs to be cleaned up to this database.

I hope this will give u some idea of what happened.

I would appreciate any suggestion..

Thanks,

Hiten

restore back up from sql 2005 beta 2 to sql 2005 original

hai,

When i try to restore the backup which has been taken in sql2005 beta 2 and restore it in sql2005 original it is displaying the following error message.

System.Data.SqlClient.SqlError: The database was backed up on a server running version 9.00.0852. That version is incompatible with this server, which is running version 9.00.1399. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.Smo)

please let me know how i can restore the back.

I am moving this to the SQL Server Database Engine forum.|||You may have to script out the schema and then copy the data from the Beta2 database to the RTM (1399) version database. You might be able to use Visual Studio for Database Professionals, aka "DataDude" to do this.|||Your only other alternative is to restore from sql 2005 beta 2 to sql 2005 beta 3 and then from sql 2005 beta 3 to sql 2005 RTM

Restore a set of databases from a SQL2000 server to a SQL2005 server

I can restore databases one by one, setting .mdf and .ldf destination paths.

How can I restore all my Databases at the same time?

Thanks

G. Zanghi

You will either have restore or attach them one by one.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

In SQL2000 i have a script that permits me to restore, at the same time, a group of databases backed up with a maintenance plan.

In SQL2005 this script doesn't run. Do you know a similar one?

Restoring one by one it's an hard work....

|||COuld you send me over the script, perhaps I can try a transistion. My EMail is accessible through my profile.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Your Email doesn't run!

check this link: fengyu.china.com/source/sp_CSS_RestoreDir.sql

It's very similar to mine.

|||My Email works, you actually will have to remove the SPAMfuscator to make it work. Which error do you get ? If you are not sure if the statements are right composed you can use the PRINT Command to print our the composed commands for debugging.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

This is the output of the query:

Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
RESTORING DATABASE dataprova1
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "E:\dataprova1Data\dataprova1.mdf" failed with the operating system error 1008(An attempt was made to reference a token that does not exist.).
Msg 3156, Level 16, State 3, Line 1
File 'dataprova1ForRecovery_Data' cannot be restored to 'E:\dataprova1Data\dataprova1.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5184, Level 16, State 2, Line 1
Cannot use file 'D:\dataprova1Log\dataprova1_log.ldf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used.
Msg 3156, Level 16, State 3, Line 1
File 'dataprova1ForRecovery_Log' cannot be restored to 'D:\dataprova1Log\dataprova1_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.

Thanks!!!

Kind Regards Gianpaolo

Restore a set of databases from a SQL2000 server to a SQL2005 server

I can restore databases one by one, setting .mdf and .ldf destination paths.

How can I restore all my Databases at the same time?

Thanks

G. Zanghi

You will either have restore or attach them one by one.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

In SQL2000 i have a script that permits me to restore, at the same time, a group of databases backed up with a maintenance plan.

In SQL2005 this script doesn't run. Do you know a similar one?

Restoring one by one it's an hard work....

|||COuld you send me over the script, perhaps I can try a transistion. My EMail is accessible through my profile.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Your Email doesn't run!

check this link: fengyu.china.com/source/sp_CSS_RestoreDir.sql

It's very similar to mine.

|||My Email works, you actually will have to remove the SPAMfuscator to make it work. Which error do you get ? If you are not sure if the statements are right composed you can use the PRINT Command to print our the composed commands for debugging.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

This is the output of the query:

Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
RESTORING DATABASE dataprova1
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "E:\dataprova1Data\dataprova1.mdf" failed with the operating system error 1008(An attempt was made to reference a token that does not exist.).
Msg 3156, Level 16, State 3, Line 1
File 'dataprova1ForRecovery_Data' cannot be restored to 'E:\dataprova1Data\dataprova1.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5184, Level 16, State 2, Line 1
Cannot use file 'D:\dataprova1Log\dataprova1_log.ldf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used.
Msg 3156, Level 16, State 3, Line 1
File 'dataprova1ForRecovery_Log' cannot be restored to 'D:\dataprova1Log\dataprova1_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.

Thanks!!!

Kind Regards Gianpaolo

restore a mssql2005 DB to another sql2005 server authentication pr

Hi.
I want to restore a db that is created on a 2005 server to another 2005
server. From my isp i got a .BAK file.
I created a new DB on the new server with same login information as the old
database. I restored the database to the other server all went fine.
Now when i want to login on the database via web, or via enterprisemanager
(SQL authentication), it says that login failed. I tried to change the
password from the user, but that does not work.
Can anyone help me, on how to restore a db to another server?
thanks.Hi
Take a look at sp_change_users_login (Transact-SQL) in the BOL
Or you can DROP USER in the new restored database and then recreate him
mapped to the existing LOGIN
"janwillem" <janwillem@.discussions.microsoft.com> wrote in message
news:21CECAD4-CEFC-430F-A860-6F70098D3981@.microsoft.com...
> Hi.
> I want to restore a db that is created on a 2005 server to another 2005
> server. From my isp i got a .BAK file.
> I created a new DB on the new server with same login information as the
> old
> database. I restored the database to the other server all went fine.
> Now when i want to login on the database via web, or via enterprisemanager
> (SQL authentication), it says that login failed. I tried to change the
> password from the user, but that does not work.
> Can anyone help me, on how to restore a db to another server?
> thanks.|||thanks. the problem also is that when i try to delete the user in the
existing DB, i get the following error:
Drop failed for user 'xxx'
The database principal owns a schema in the database, and cannot be dropped
( microsoft SQL server, Error: 15138)
"Uri Dimant" wrote:
> Hi
> Take a look at sp_change_users_login (Transact-SQL) in the BOL
> Or you can DROP USER in the new restored database and then recreate him
> mapped to the existing LOGIN
>
> "janwillem" <janwillem@.discussions.microsoft.com> wrote in message
> news:21CECAD4-CEFC-430F-A860-6F70098D3981@.microsoft.com...
> > Hi.
> >
> > I want to restore a db that is created on a 2005 server to another 2005
> > server. From my isp i got a .BAK file.
> >
> > I created a new DB on the new server with same login information as the
> > old
> > database. I restored the database to the other server all went fine.
> >
> > Now when i want to login on the database via web, or via enterprisemanager
> > (SQL authentication), it says that login failed. I tried to change the
> > password from the user, but that does not work.
> >
> > Can anyone help me, on how to restore a db to another server?
> >
> > thanks.
>
>|||Hi
Yep, you first, need to drop a SCHEMA that owned by the user
"janwillem" <janwillem@.discussions.microsoft.com> wrote in message
news:E38A99E6-1414-47FF-9526-190F49F0CEED@.microsoft.com...
> thanks. the problem also is that when i try to delete the user in the
> existing DB, i get the following error:
>
> Drop failed for user 'xxx'
> The database principal owns a schema in the database, and cannot be
> dropped
> ( microsoft SQL server, Error: 15138)
>
> "Uri Dimant" wrote:
>> Hi
>> Take a look at sp_change_users_login (Transact-SQL) in the BOL
>> Or you can DROP USER in the new restored database and then recreate him
>> mapped to the existing LOGIN
>>
>> "janwillem" <janwillem@.discussions.microsoft.com> wrote in message
>> news:21CECAD4-CEFC-430F-A860-6F70098D3981@.microsoft.com...
>> > Hi.
>> >
>> > I want to restore a db that is created on a 2005 server to another 2005
>> > server. From my isp i got a .BAK file.
>> >
>> > I created a new DB on the new server with same login information as the
>> > old
>> > database. I restored the database to the other server all went fine.
>> >
>> > Now when i want to login on the database via web, or via
>> > enterprisemanager
>> > (SQL authentication), it says that login failed. I tried to change the
>> > password from the user, but that does not work.
>> >
>> > Can anyone help me, on how to restore a db to another server?
>> >
>> > thanks.
>>

restore a mssql2005 DB to another sql2005 server authentication pr

Hi.
I want to restore a db that is created on a 2005 server to another 2005
server. From my isp i got a .BAK file.
I created a new DB on the new server with same login information as the old
database. I restored the database to the other server all went fine.
Now when i want to login on the database via web, or via enterprisemanager
(SQL authentication), it says that login failed. I tried to change the
password from the user, but that does not work.
Can anyone help me, on how to restore a db to another server?
thanks.Hi
Take a look at sp_change_users_login (Transact-SQL) in the BOL
Or you can DROP USER in the new restored database and then recreate him
mapped to the existing LOGIN
"janwillem" <janwillem@.discussions.microsoft.com> wrote in message
news:21CECAD4-CEFC-430F-A860-6F70098D3981@.microsoft.com...
> Hi.
> I want to restore a db that is created on a 2005 server to another 2005
> server. From my isp i got a .BAK file.
> I created a new DB on the new server with same login information as the
> old
> database. I restored the database to the other server all went fine.
> Now when i want to login on the database via web, or via enterprisemanager
> (SQL authentication), it says that login failed. I tried to change the
> password from the user, but that does not work.
> Can anyone help me, on how to restore a db to another server?
> thanks.