Restore DB to diferent data file struct

Hi,
I 'had' a server with 350GB disk capacity, where we 'had' a DB 3 datafiles
with 90GB each.
The thing is that the server is no more… died…. Kaput…
I have backups of the database and I have a spare server with 6 drive
letters with 80GB of free space eatch (aprox).
Considering that I can’t touch the operating system to change the drive
letters (using stripe sets, or a raid5 vol), I nead to restore the backup of
the sql database to more data files than the original database to spread the
database over the avaible drive letters.
The problem is that when I try to restore, the sql restore utility does’t
allow to add new devices different from the original db file struct.
Can anyone help me on this one?
oh yes ...
i'm using sql 2000 sp3a
same version as on the previous version and same collation.
|||Since it sounds like a bit of an emergency, how about if you use a disk
utility and create compressed drives in the available space so the 90 GB
files will fit. Then restore the original file structure in the compressed
drives, partition them into smaller files, move them around, uncompress the
drives and go from there.
Sounds way out, but it might work. Somebody else here might have a far
better way of doing it.
Bob Castleman
DBA Poseur
"Paulo F" <PauloF@.discussions.microsoft.com> wrote in message
news:EE908619-C160-4E11-AF90-6264D85416EE@.microsoft.com...
> oh yes ...
> i'm using sql 2000 sp3a
> same version as on the previous version and same collation.
>
|||SQL Server need the same amount of database files, each having the same logical file and at least
the same size, as the database had then you performed the backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Paulo F" <Paulo F@.discussions.microsoft.com> wrote in message
news:554C4D1E-B640-4D2B-9D99-71C4C1AB8D04@.microsoft.com...
> Hi,
> I 'had' a server with 350GB disk capacity, where we 'had' a DB 3 datafiles
> with 90GB each.
> The thing is that the server is no more. died.. Kaput.
> I have backups of the database and I have a spare server with 6 drive
> letters with 80GB of free space eatch (aprox).
> Considering that I can't touch the operating system to change the drive
> letters (using stripe sets, or a raid5 vol), I nead to restore the backup of
> the sql database to more data files than the original database to spread the
> database over the avaible drive letters.
> The problem is that when I try to restore, the sql restore utility does't
> allow to add new devices different from the original db file struct.
> Can anyone help me on this one?
>
|||tks Tibor, that's true, and i new that, i was just hopping that someone
already managed to go around that.
But still, i realy don't see wy the hell one can't restore a backup to a
diferent data file struct, since what we nead is the data...
"Tibor Karaszi" wrote:

> SQL Server need the same amount of database files, each having the same logical file and at least
> the same size, as the database had then you performed the backup.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Paulo F" <Paulo F@.discussions.microsoft.com> wrote in message
> news:554C4D1E-B640-4D2B-9D99-71C4C1AB8D04@.microsoft.com...
>
>
|||> But still, i realy don't see wy the hell one can't restore a backup to a
> diferent data file struct, since what we nead is the data...
Backup just copies all the used pages to the backup media. As they are, including pointers to other
pages, allocation pages etc.. This is why restore need to put everything in the right place.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Paulo F" <PauloF@.discussions.microsoft.com> wrote in message
news:1C8895ED-0205-42FE-99B5-C2A4AB4E5137@.microsoft.com...[vbcol=seagreen]
> tks Tibor, that's true, and i new that, i was just hopping that someone
> already managed to go around that.
> But still, i realy don't see wy the hell one can't restore a backup to a
> diferent data file struct, since what we nead is the data...
>
> "Tibor Karaszi" wrote:
sql

Restore DB to diferent data file struct

Hi,
I 'had' a server with 350GB disk capacity, where we 'had' a DB 3 datafiles
with 90GB each.
The thing is that the server is no more… died…. Kaput…
I have backups of the database and I have a spare server with 6 drive
letters with 80GB of free space eatch (aprox).
Considering that I can’t touch the operating system to change the drive
letters (using stripe sets, or a raid5 vol), I nead to restore the backup of
the sql database to more data files than the original database to spread the
database over the avaible drive letters.
The problem is that when I try to restore, the sql restore utility does’t
allow to add new devices different from the original db file struct.
Can anyone help me on this one?oh yes ...
i'm using sql 2000 sp3a
same version as on the previous version and same collation.|||Since it sounds like a bit of an emergency, how about if you use a disk
utility and create compressed drives in the available space so the 90 GB
files will fit. Then restore the original file structure in the compressed
drives, partition them into smaller files, move them around, uncompress the
drives and go from there.
Sounds way out, but it might work. Somebody else here might have a far
better way of doing it.
Bob Castleman
DBA Poseur
"Paulo F" <PauloF@.discussions.microsoft.com> wrote in message
news:EE908619-C160-4E11-AF90-6264D85416EE@.microsoft.com...
> oh yes ...
> i'm using sql 2000 sp3a
> same version as on the previous version and same collation.
>|||SQL Server need the same amount of database files, each having the same logi
cal file and at least
the same size, as the database had then you performed the backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Paulo F" <Paulo F@.discussions.microsoft.com> wrote in message
news:554C4D1E-B640-4D2B-9D99-71C4C1AB8D04@.microsoft.com...
> Hi,
> I 'had' a server with 350GB disk capacity, where we 'had' a DB 3 datafiles
> with 90GB each.
> The thing is that the server is no more. died.. Kaput.
> I have backups of the database and I have a spare server with 6 drive
> letters with 80GB of free space eatch (aprox).
> Considering that I can't touch the operating system to change the drive
> letters (using stripe sets, or a raid5 vol), I nead to restore the backup
of
> the sql database to more data files than the original database to spread t
he
> database over the avaible drive letters.
> The problem is that when I try to restore, the sql restore utility does't
> allow to add new devices different from the original db file struct.
> Can anyone help me on this one?
>|||tks Tibor, that's true, and i new that, i was just hopping that someone
already managed to go around that.
But still, i realy don't see wy the hell one can't restore a backup to a
diferent data file struct, since what we nead is the data...
"Tibor Karaszi" wrote:

> SQL Server need the same amount of database files, each having the same lo
gical file and at least
> the same size, as the database had then you performed the backup.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Paulo F" <Paulo F@.discussions.microsoft.com> wrote in message
> news:554C4D1E-B640-4D2B-9D99-71C4C1AB8D04@.microsoft.com...
>
>|||> But still, i realy don't see wy the hell one can't restore a backup to a
> diferent data file struct, since what we nead is the data...
Backup just copies all the used pages to the backup media. As they are, incl
uding pointers to other
pages, allocation pages etc.. This is why restore need to put everything in
the right place.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Paulo F" <PauloF@.discussions.microsoft.com> wrote in message
news:1C8895ED-0205-42FE-99B5-C2A4AB4E5137@.microsoft.com...[vbcol=seagreen]
> tks Tibor, that's true, and i new that, i was just hopping that someone
> already managed to go around that.
> But still, i realy don't see wy the hell one can't restore a backup to a
> diferent data file struct, since what we nead is the data...
>
> "Tibor Karaszi" wrote:
>

Restore DB to diferent data file struct

Hi,
I 'had' a server with 350GB disk capacity, where we 'had' a DB 3 datafiles
with 90GB each.
The thing is that the server is no moreâ?¦ diedâ?¦. Kaputâ?¦
I have backups of the database and I have a spare server with 6 drive
letters with 80GB of free space eatch (aprox).
Considering that I canâ't touch the operating system to change the drive
letters (using stripe sets, or a raid5 vol), I nead to restore the backup of
the sql database to more data files than the original database to spread the
database over the avaible drive letters.
The problem is that when I try to restore, the sql restore utility doesâ't
allow to add new devices different from the original db file struct.
Can anyone help me on this one?oh yes ...
i'm using sql 2000 sp3a
same version as on the previous version and same collation.|||Since it sounds like a bit of an emergency, how about if you use a disk
utility and create compressed drives in the available space so the 90 GB
files will fit. Then restore the original file structure in the compressed
drives, partition them into smaller files, move them around, uncompress the
drives and go from there.
Sounds way out, but it might work. Somebody else here might have a far
better way of doing it.
Bob Castleman
DBA Poseur
"Paulo F" <PauloF@.discussions.microsoft.com> wrote in message
news:EE908619-C160-4E11-AF90-6264D85416EE@.microsoft.com...
> oh yes ...
> i'm using sql 2000 sp3a
> same version as on the previous version and same collation.
>|||SQL Server need the same amount of database files, each having the same logical file and at least
the same size, as the database had then you performed the backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Paulo F" <Paulo F@.discussions.microsoft.com> wrote in message
news:554C4D1E-B640-4D2B-9D99-71C4C1AB8D04@.microsoft.com...
> Hi,
> I 'had' a server with 350GB disk capacity, where we 'had' a DB 3 datafiles
> with 90GB each.
> The thing is that the server is no more. died.. Kaput.
> I have backups of the database and I have a spare server with 6 drive
> letters with 80GB of free space eatch (aprox).
> Considering that I can't touch the operating system to change the drive
> letters (using stripe sets, or a raid5 vol), I nead to restore the backup of
> the sql database to more data files than the original database to spread the
> database over the avaible drive letters.
> The problem is that when I try to restore, the sql restore utility does't
> allow to add new devices different from the original db file struct.
> Can anyone help me on this one?
>|||tks Tibor, that's true, and i new that, i was just hopping that someone
already managed to go around that.
But still, i realy don't see wy the hell one can't restore a backup to a
diferent data file struct, since what we nead is the data...
"Tibor Karaszi" wrote:
> SQL Server need the same amount of database files, each having the same logical file and at least
> the same size, as the database had then you performed the backup.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Paulo F" <Paulo F@.discussions.microsoft.com> wrote in message
> news:554C4D1E-B640-4D2B-9D99-71C4C1AB8D04@.microsoft.com...
> > Hi,
> > I 'had' a server with 350GB disk capacity, where we 'had' a DB 3 datafiles
> > with 90GB each.
> > The thing is that the server is no more. died.. Kaput.
> > I have backups of the database and I have a spare server with 6 drive
> > letters with 80GB of free space eatch (aprox).
> > Considering that I can't touch the operating system to change the drive
> > letters (using stripe sets, or a raid5 vol), I nead to restore the backup of
> > the sql database to more data files than the original database to spread the
> > database over the avaible drive letters.
> > The problem is that when I try to restore, the sql restore utility does't
> > allow to add new devices different from the original db file struct.
> > Can anyone help me on this one?
> >
>
>|||> But still, i realy don't see wy the hell one can't restore a backup to a
> diferent data file struct, since what we nead is the data...
Backup just copies all the used pages to the backup media. As they are, including pointers to other
pages, allocation pages etc.. This is why restore need to put everything in the right place.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Paulo F" <PauloF@.discussions.microsoft.com> wrote in message
news:1C8895ED-0205-42FE-99B5-C2A4AB4E5137@.microsoft.com...
> tks Tibor, that's true, and i new that, i was just hopping that someone
> already managed to go around that.
> But still, i realy don't see wy the hell one can't restore a backup to a
> diferent data file struct, since what we nead is the data...
>
> "Tibor Karaszi" wrote:
>> SQL Server need the same amount of database files, each having the same logical file and at least
>> the same size, as the database had then you performed the backup.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Paulo F" <Paulo F@.discussions.microsoft.com> wrote in message
>> news:554C4D1E-B640-4D2B-9D99-71C4C1AB8D04@.microsoft.com...
>> > Hi,
>> > I 'had' a server with 350GB disk capacity, where we 'had' a DB 3 datafiles
>> > with 90GB each.
>> > The thing is that the server is no more. died.. Kaput.
>> > I have backups of the database and I have a spare server with 6 drive
>> > letters with 80GB of free space eatch (aprox).
>> > Considering that I can't touch the operating system to change the drive
>> > letters (using stripe sets, or a raid5 vol), I nead to restore the backup of
>> > the sql database to more data files than the original database to spread the
>> > database over the avaible drive letters.
>> > The problem is that when I try to restore, the sql restore utility does't
>> > allow to add new devices different from the original db file struct.
>> > Can anyone help me on this one?
>> >
>>

restore database without some tables records

Hi There,
I'm using following script to restore
Restore Database adminsys_ex_avc from disk =
'\\ppml31\DBBkups\fromPPML0112\adminsys_
ex_avc.bak' with replace
is it possible can i ignore the some table on restore, for e.g audit_log
table and i don't want to use any logs
Thanks
GaneshActually yes
drop database test
go
create database test
GO
alter database test set recovery full
go
create table test..test(id int identity)
create table test..test1(id int identity)
insert test..test default values
insert test..test1 default values
backup database test to disk = 'c:\db.bak' WITH INIT
insert test..test default values
insert test..test1 default values
backup log test to disk = 'c:\log.bak'WITH INIT
insert test..test default values
insert test..test1 default values
backup log test to disk = 'c:\log.bak' WITH NOINIT
insert test..test default values
drop table test..test1
backup log test to disk = 'c:\log.bak' WITH NOINIT
GO
RESTORE DATABASE test FROM disk = 'c:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'c:\log.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'c:\log.bak' WITH FILE = 2, norecovery
RESTORE LOG test FROM disk = 'c:\log.bak' WITH FILE = 3, recovery
GO
select * from test..test
select * from test..test1
--Server: Msg 208, Level 16, State 1, Line 1
--Invalid object name 'test..test1'.
"Ganesh" <gsganesh@.yahoo.com> wrote in message
news:6D4A5AFB-50AD-4E93-889D-2169F92BBB82@.microsoft.com...
> Hi There,
> I'm using following script to restore
> Restore Database adminsys_ex_avc from disk =
> '\\ppml31\DBBkups\fromPPML0112\adminsys_
ex_avc.bak' with replace
> is it possible can i ignore the some table on restore, for e.g audit_log
> table and i don't want to use any logs
> --
> Thanks
> Ganesh|||No,
You can not avoid selected tables.
You can do one thing. Move all unrequired tables to one filegroup which
should not be primary.
Then use partial restore and do not restore selected filegroup.
Look at BOL Partial Database Restore Operations.
Best way is just restore database and delete unwanted tables.
Regards
Amish Shah.

Restore Database with Multiple Files

All
I have a database with multiple files ie. an mdf and two ndf. I have
performed a full database backup using BACKUP DATABASE dbname to DISK =
'filename' and I would like to restore the database to another server and
only have one file ie. PRIMARY mdf instead of the three files. Can anyone
advise how this can be achieved.
Thanks
That is not possible. A SQL Server backup is more or less a binary dump if the data pages, so SQL
Server need to put each page in the same file and on the same page address (as other pages might be
pointing to this page). Restore as original, and do the shuffling after the restore. Or do the
shuffling before the backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David" <David@.discussions.microsoft.com> wrote in message
news:89EB659B-C421-4DB2-BB1B-17D30C6CFB8A@.microsoft.com...
> All
> I have a database with multiple files ie. an mdf and two ndf. I have
> performed a full database backup using BACKUP DATABASE dbname to DISK =
> 'filename' and I would like to restore the database to another server and
> only have one file ie. PRIMARY mdf instead of the three files. Can anyone
> advise how this can be achieved.
> Thanks
>

Restore Database with Multiple Files

All
I have a database with multiple files ie. an mdf and two ndf. I have
performed a full database backup using BACKUP DATABASE dbname to DISK =
'filename' and I would like to restore the database to another server and
only have one file ie. PRIMARY mdf instead of the three files. Can anyone
advise how this can be achieved.
ThanksThat is not possible. A SQL Server backup is more or less a binary dump if t
he data pages, so SQL
Server need to put each page in the same file and on the same page address (
as other pages might be
pointing to this page). Restore as original, and do the shuffling after the
restore. Or do the
shuffling before the backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David" <David@.discussions.microsoft.com> wrote in message
news:89EB659B-C421-4DB2-BB1B-17D30C6CFB8A@.microsoft.com...
> All
> I have a database with multiple files ie. an mdf and two ndf. I have
> performed a full database backup using BACKUP DATABASE dbname to DISK =
> 'filename' and I would like to restore the database to another server and
> only have one file ie. PRIMARY mdf instead of the three files. Can anyone
> advise how this can be achieved.
> Thanks
>

Restore Database with Multiple Files

All
I have a database with multiple files ie. an mdf and two ndf. I have
performed a full database backup using BACKUP DATABASE dbname to DISK = 'filename' and I would like to restore the database to another server and
only have one file ie. PRIMARY mdf instead of the three files. Can anyone
advise how this can be achieved.
ThanksThat is not possible. A SQL Server backup is more or less a binary dump if the data pages, so SQL
Server need to put each page in the same file and on the same page address (as other pages might be
pointing to this page). Restore as original, and do the shuffling after the restore. Or do the
shuffling before the backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David" <David@.discussions.microsoft.com> wrote in message
news:89EB659B-C421-4DB2-BB1B-17D30C6CFB8A@.microsoft.com...
> All
> I have a database with multiple files ie. an mdf and two ndf. I have
> performed a full database backup using BACKUP DATABASE dbname to DISK => 'filename' and I would like to restore the database to another server and
> only have one file ie. PRIMARY mdf instead of the three files. Can anyone
> advise how this can be achieved.
> Thanks
>sql

Restore database to default location on anohter server?

I have a database called "test_db" that has been backed up with the
command:
BACKUP DATABASE test_db TO DISK = 'c:\temp\test_db.bak'
I want to restore it to another Sql Server and call it
'Test_Restored_DB', as well as save it as a different physical file
name. So I copy the file over to the new server into the folder
c:\temp\ and then execute the command:
RESTORE DATABASE Test_Restored_DB
FROM DISK = 'c:\temp\test_db.bak'
WITH MOVE 'test_db' TO 'c:\AnotherTempDirectory\Test_Restored_DB.mdf',
MOVE 'Test_db_log' TO 'c:\marcustemp\Test_Restored_DB.ldf'
This works fine. But what I really want to do is restore the database
to the default location on Sql Server 2005, i.e. typically "C:\Program
Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" without actually
hard-coding this path in. This is because I am assuming that some Sql
Servers may be configured differently and have their default data
directory located in another folder (I have no control on the
configuration of various sql servers where this will be run). Is there
a way to restore a database to another server, give it a new name,
change its physical file name, and have it placed in the default data
directory without specifying the data directory? Or perhaps there is a
stored procedure (not xp_cmdshell) or some other t-sql code that will
tell me where the default data directory is located?
Thanks for any feedback,
MarcusHi,
I have never tried that but this is what SQL Server does in the background
to find these locations
declare @.SmoDefaultFile nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData',
@.SmoDefaultFile OUTPUT
print @.SmoDefaultFile
declare @.SmoDefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @.SmoDefaultLog
OUTPUT
print @.SmoDefaultLog
declare @.SmoRoot nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @.SmoRoot OUTPUT
print @.SmoRoot
Ben Nevarez, MCDBA, OCP
Database Administrator
"Marcus" wrote:
> I have a database called "test_db" that has been backed up with the
> command:
> BACKUP DATABASE test_db TO DISK = 'c:\temp\test_db.bak'
> I want to restore it to another Sql Server and call it
> 'Test_Restored_DB', as well as save it as a different physical file
> name. So I copy the file over to the new server into the folder
> c:\temp\ and then execute the command:
> RESTORE DATABASE Test_Restored_DB
> FROM DISK = 'c:\temp\test_db.bak'
> WITH MOVE 'test_db' TO 'c:\AnotherTempDirectory\Test_Restored_DB.mdf',
> MOVE 'Test_db_log' TO 'c:\marcustemp\Test_Restored_DB.ldf'
> This works fine. But what I really want to do is restore the database
> to the default location on Sql Server 2005, i.e. typically "C:\Program
> Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" without actually
> hard-coding this path in. This is because I am assuming that some Sql
> Servers may be configured differently and have their default data
> directory located in another folder (I have no control on the
> configuration of various sql servers where this will be run). Is there
> a way to restore a database to another server, give it a new name,
> change its physical file name, and have it placed in the default data
> directory without specifying the data directory? Or perhaps there is a
> stored procedure (not xp_cmdshell) or some other t-sql code that will
> tell me where the default data directory is located?
> Thanks for any feedback,
> Marcus
>|||Excellent, thanks, Ben! With a little bit more googling of
xp_instance_regread lead me to this page which more stuff that I could
use:
http://www.sqldev.net/misc/SQLLocationFunctions.htm
Cheers,
Marcus
Ben Nevarez wrote:
> Hi,
> I have never tried that but this is what SQL Server does in the background
> to find these locations
> declare @.SmoDefaultFile nvarchar(512)
> exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
> N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData',
> @.SmoDefaultFile OUTPUT
> print @.SmoDefaultFile
> declare @.SmoDefaultLog nvarchar(512)
> exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
> N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @.SmoDefaultLog
> OUTPUT
> print @.SmoDefaultLog
> declare @.SmoRoot nvarchar(512)
> exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
> N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @.SmoRoot OUTPUT
> print @.SmoRoot
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Marcus" wrote:
> > I have a database called "test_db" that has been backed up with the
> > command:
> >
> > BACKUP DATABASE test_db TO DISK = 'c:\temp\test_db.bak'
> >
> > I want to restore it to another Sql Server and call it
> > 'Test_Restored_DB', as well as save it as a different physical file
> > name. So I copy the file over to the new server into the folder
> > c:\temp\ and then execute the command:
> >
> > RESTORE DATABASE Test_Restored_DB
> > FROM DISK = 'c:\temp\test_db.bak'
> > WITH MOVE 'test_db' TO 'c:\AnotherTempDirectory\Test_Restored_DB.mdf',
> > MOVE 'Test_db_log' TO 'c:\marcustemp\Test_Restored_DB.ldf'
> >
> > This works fine. But what I really want to do is restore the database
> > to the default location on Sql Server 2005, i.e. typically "C:\Program
> > Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" without actually
> > hard-coding this path in. This is because I am assuming that some Sql
> > Servers may be configured differently and have their default data
> > directory located in another folder (I have no control on the
> > configuration of various sql servers where this will be run). Is there
> > a way to restore a database to another server, give it a new name,
> > change its physical file name, and have it placed in the default data
> > directory without specifying the data directory? Or perhaps there is a
> > stored procedure (not xp_cmdshell) or some other t-sql code that will
> > tell me where the default data directory is located?
> >
> > Thanks for any feedback,
> > Marcus
> >
> >sql

Restore database to default location on anohter server?

I have a database called "test_db" that has been backed up with the
command:
BACKUP DATABASE test_db TO DISK = 'c:\temp\test_db.bak'
I want to restore it to another Sql Server and call it
'Test_Restored_DB', as well as save it as a different physical file
name. So I copy the file over to the new server into the folder
c:\temp\ and then execute the command:
RESTORE DATABASE Test_Restored_DB
FROM DISK = 'c:\temp\test_db.bak'
WITH MOVE 'test_db' TO 'c:\AnotherTempDirectory\Test_Restored_D
B.mdf',
MOVE 'Test_db_log' TO 'c:\marcustemp\Test_Restored_DB.ldf'
This works fine. But what I really want to do is restore the database
to the default location on Sql Server 2005, i.e. typically "C:\Program
Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" without actually
hard-coding this path in. This is because I am assuming that some Sql
Servers may be configured differently and have their default data
directory located in another folder (I have no control on the
configuration of various sql servers where this will be run). Is there
a way to restore a database to another server, give it a new name,
change its physical file name, and have it placed in the default data
directory without specifying the data directory? Or perhaps there is a
stored procedure (not xp_cmdshell) or some other t-sql code that will
tell me where the default data directory is located?
Thanks for any feedback,
MarcusHi,
I have never tried that but this is what SQL Server does in the background
to find these locations
declare @.SmoDefaultFile nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLSe
rver', N'DefaultData',
@.SmoDefaultFile OUTPUT
print @.SmoDefaultFile
declare @.SmoDefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLSe
rver', N'DefaultLog', @.SmoDefaultLog
OUTPUT
print @.SmoDefaultLog
declare @.SmoRoot nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\Setup',
N'SQLPath', @.SmoRoot OUTPUT
print @.SmoRoot
Ben Nevarez, MCDBA, OCP
Database Administrator
"Marcus" wrote:

> I have a database called "test_db" that has been backed up with the
> command:
> BACKUP DATABASE test_db TO DISK = 'c:\temp\test_db.bak'
> I want to restore it to another Sql Server and call it
> 'Test_Restored_DB', as well as save it as a different physical file
> name. So I copy the file over to the new server into the folder
> c:\temp\ and then execute the command:
> RESTORE DATABASE Test_Restored_DB
> FROM DISK = 'c:\temp\test_db.bak'
> WITH MOVE 'test_db' TO 'c:\AnotherTempDirectory\Test_Restored_D
B.mdf',
> MOVE 'Test_db_log' TO 'c:\marcustemp\Test_Restored_DB.ldf'
> This works fine. But what I really want to do is restore the database
> to the default location on Sql Server 2005, i.e. typically "C:\Program
> Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" without actually
> hard-coding this path in. This is because I am assuming that some Sql
> Servers may be configured differently and have their default data
> directory located in another folder (I have no control on the
> configuration of various sql servers where this will be run). Is there
> a way to restore a database to another server, give it a new name,
> change its physical file name, and have it placed in the default data
> directory without specifying the data directory? Or perhaps there is a
> stored procedure (not xp_cmdshell) or some other t-sql code that will
> tell me where the default data directory is located?
> Thanks for any feedback,
> Marcus
>|||Excellent, thanks, Ben! With a little bit more googling of
xp_instance_regread lead me to this page which more stuff that I could
use:
http://www.sqldev.net/misc/SQLLocationFunctions.htm
Cheers,
Marcus
Ben Nevarez wrote:[vbcol=seagreen]
> Hi,
> I have never tried that but this is what SQL Server does in the background
> to find these locations
> declare @.SmoDefaultFile nvarchar(512)
> exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
> N'Software\Microsoft\MSSQLServer\MSSQLSe
rver', N'DefaultData',
> @.SmoDefaultFile OUTPUT
> print @.SmoDefaultFile
> declare @.SmoDefaultLog nvarchar(512)
> exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
> N'Software\Microsoft\MSSQLServer\MSSQLSe
rver', N'DefaultLog', @.SmoDefaultL
og
> OUTPUT
> print @.SmoDefaultLog
> declare @.SmoRoot nvarchar(512)
> exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
> N'SOFTWARE\Microsoft\MSSQLServer\Setup',
N'SQLPath', @.SmoRoot OUTPUT
> print @.SmoRoot
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Marcus" wrote:
>

RESTORE DATABASE Timeout in SQL 2000 with large backup files

Hello,

I am attempting to restore the database from within VB.NET application I am making the following 3 calls:

RESTORE FileListOnly FROM DISK = 'C:\MyDatabase.dat'

USE Master RESTORE DATABASE MyDatabase FROM DISK = 'C:\MyDatabase.dat' WITH NORECOVERY, MOVE 'MyDatabase' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDatabase.mdf', MOVE 'MyDatabase_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\LDF\MyDatabase.ldf', REPLACE

RESTORE DATABASE MyDatabase FROM DISK = 'C:\MyDatabase.dat'

using SMO. This logic works fine with small *.dat files, however when using *.dat file of about 4Gb I get an error on the 3d restore database call:

ExecuteNonQuery failed for Database 'master'.

An exception occurred while executing a Transact-SQL statement or batch.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Operator aborted backup or restore. See the error messages returned to the console for more details.

ExecuteNonQuery failed for Database 'master'.

An exception occurred while executing a Transact-SQL statement or batch.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Operator aborted backup or restore. See the error messages returned to the console for more details.

The same program/logic also works fine when I use MS SQL 2005 and it runs fine from MS SQL 2005 Query Analyzer for both 2005 and 2000 databases. There seem to be only problem with MS SQL 2000 from within VB.NET. Anybody has any idea? I'd appreciate any response. Thanks

Eugene

Have you tried changing the timeout setting of the ADO.NET Command?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

As Wesley indicated you should change the query timeout.

SQL 2000 does not have fast file initialization so it can take awhile to create and initialize the files.

|||

Yes I forgot to mention. The object which executes query is SMO.Database and it runs

Smo.Database.ExecuteNonQuery(sSQL)

So I set the timeout on the Smo.Server.Connection = 0.

I've also tried to set it to Smo.Server.Connection=3600 but the timeout occure in about 12 min so

sorry It did not help...

|||

For those of you who might be looking for answer to the similar problem - I found the solution. SMO has StatementTimeout property. This is different from the one I was trying to change - which was a Connection timeout.

That is the one which needed to be increased.

I do appreciate the responses to my post though. It helped. Thanks

sql

RESTORE DATABASE Timeout in SQL 2000 with large backup files

Hello,

I am attempting to restore the database from within VB.NET application I am making the following 3 calls:

RESTORE FileListOnly FROM DISK = 'C:\MyDatabase.dat'

USE Master RESTORE DATABASE MyDatabase FROM DISK = 'C:\MyDatabase.dat' WITH NORECOVERY, MOVE 'MyDatabase' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDatabase.mdf', MOVE 'MyDatabase_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\LDF\MyDatabase.ldf', REPLACE

RESTORE DATABASE MyDatabase FROM DISK = 'C:\MyDatabase.dat'

using SMO. This logic works fine with small *.dat files, however when using *.dat file of about 4Gb I get an error on the 3d restore database call:

ExecuteNonQuery failed for Database 'master'.

An exception occurred while executing a Transact-SQL statement or batch.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Operator aborted backup or restore. See the error messages returned to the console for more details.

ExecuteNonQuery failed for Database 'master'.

An exception occurred while executing a Transact-SQL statement or batch.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Operator aborted backup or restore. See the error messages returned to the console for more details.

The same program/logic also works fine when I use MS SQL 2005 and it runs fine from MS SQL 2005 Query Analyzer for both 2005 and 2000 databases. There seem to be only problem with MS SQL 2000 from within VB.NET. Anybody has any idea? I'd appreciate any response. Thanks

Eugene

Have you tried changing the timeout setting of the ADO.NET Command?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

As Wesley indicated you should change the query timeout.

SQL 2000 does not have fast file initialization so it can take awhile to create and initialize the files.

|||

Yes I forgot to mention. The object which executes query is SMO.Database and it runs

Smo.Database.ExecuteNonQuery(sSQL)

So I set the timeout on the Smo.Server.Connection = 0.

I've also tried to set it to Smo.Server.Connection=3600 but the timeout occure in about 12 min so

sorry It did not help...

|||

For those of you who might be looking for answer to the similar problem - I found the solution. SMO has StatementTimeout property. This is different from the one I was trying to change - which was a Connection timeout.

That is the one which needed to be increased.

I do appreciate the responses to my post though. It helped. Thanks

Restore Database problem

Hi Gurus,
I am doing Backup and Restore.
But while restore it restore from the previous backup.
What am I doing wrong.
BACKUP DATABASE TestDB TO DISK = 'c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\TestDB.bak'
RESTORE DATABASE TestDb FROM disk='c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\TestDB.bak'
Please reply. I am desperate.
RCDo you have more than one backup in that device? If so you must specify
which one.
Andrew J. Kelly SQL MVP
"Richard" <Richard@.nospam.com> wrote in message
news:e2az9GbHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> Hi Gurus,
> I am doing Backup and Restore.
> But while restore it restore from the previous backup.
> What am I doing wrong.
> BACKUP DATABASE TestDB TO DISK = 'c:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\TestDB.bak'
> RESTORE DATABASE TestDb FROM disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\TestDB.bak'
> Please reply. I am desperate.
> RC
>|||Yes, there are few backups. But how do I specify which one.
Thanks, Kartic
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23LN5HNbHFHA.2852@.TK2MSFTNGP12.phx.gbl...
> Do you have more than one backup in that device? If so you must specify
> which one.
> --
> Andrew J. Kelly SQL MVP
>
> "Richard" <Richard@.nospam.com> wrote in message
> news:e2az9GbHFHA.3352@.TK2MSFTNGP10.phx.gbl...
>|||Use the FILE option for the backup command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Richard" <Richard@.nospam.com> wrote in message news:OHtzzVbHFHA.3572@.TK2MSFTNGP14.phx.gbl.
.
> Yes, there are few backups. But how do I specify which one.
> Thanks, Kartic
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23LN5HNbHFHA.2852@.TK2MSFTNGP12.phx.gbl...
>

RESTORE DATABASE is terminating abnormally

Hi DBA there,
I am restoring a bakup from CD with SQL Statement:
RESTORE DATABASE WITE
FROM disk ='e:\wite.bak'
WITH RECOVERY,
MOVE 'wfield_dat' TO 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\wite_mdf.mdf',
MOVE 'wfield_log' TO 'c:\Program Files\Microsoft SQL
Server\MSSQL\log\wite_log.ldf'
i have also use RESTORE FILELISTONLY to ensure the logical name
Unfortunately, the restore is unsuccessful with the result:
Server: Msg 3203, Level 16, State 1, Line 1
Read on 'e:\wite.bak' failed, status = 1. See the SQL Server error log for
more details.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Both physical files wite_data.mdf and wite_log.ldf are in the respective
folders already. On Enterprise manager, the databse WITE status shows
'Loading'.
Please help...
HawleyPermission thing, perhaps? Does the service account have permissions to access the backup file?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"HawleyBeach" <HawleyBeach@.discussions.microsoft.com> wrote in message
news:A07CD252-F19A-4CED-82D7-A5D7185DC181@.microsoft.com...
> Hi DBA there,
> I am restoring a bakup from CD with SQL Statement:
> RESTORE DATABASE WITE
> FROM disk ='e:\wite.bak'
> WITH RECOVERY,
> MOVE 'wfield_dat' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data\wite_mdf.mdf',
> MOVE 'wfield_log' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\log\wite_log.ldf'
> i have also use RESTORE FILELISTONLY to ensure the logical name
> Unfortunately, the restore is unsuccessful with the result:
> Server: Msg 3203, Level 16, State 1, Line 1
> Read on 'e:\wite.bak' failed, status = 1. See the SQL Server error log for
> more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Both physical files wite_data.mdf and wite_log.ldf are in the respective
> folders already. On Enterprise manager, the databse WITE status shows
> 'Loading'.
> Please help...
> Hawley
>|||Copy the back file from CD to hard disk and then try restoring.
Thanks
Hari
"HawleyBeach" <HawleyBeach@.discussions.microsoft.com> wrote in message
news:A07CD252-F19A-4CED-82D7-A5D7185DC181@.microsoft.com...
> Hi DBA there,
> I am restoring a bakup from CD with SQL Statement:
> RESTORE DATABASE WITE
> FROM disk ='e:\wite.bak'
> WITH RECOVERY,
> MOVE 'wfield_dat' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data\wite_mdf.mdf',
> MOVE 'wfield_log' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\log\wite_log.ldf'
> i have also use RESTORE FILELISTONLY to ensure the logical name
> Unfortunately, the restore is unsuccessful with the result:
> Server: Msg 3203, Level 16, State 1, Line 1
> Read on 'e:\wite.bak' failed, status = 1. See the SQL Server error log for
> more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Both physical files wite_data.mdf and wite_log.ldf are in the respective
> folders already. On Enterprise manager, the databse WITE status shows
> 'Loading'.
> Please help...
> Hawley
>

RESTORE DATABASE is terminating abnormally

Hi DBA there,
I am restoring a bakup from CD with SQL Statement:
RESTORE DATABASE WITE
FROM disk ='e:\wite.bak'
WITH RECOVERY,
MOVE 'wfield_dat' TO 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\wite_mdf.mdf',
MOVE 'wfield_log' TO 'c:\Program Files\Microsoft SQL
Server\MSSQL\log\wite_log.ldf'
i have also use RESTORE FILELISTONLY to ensure the logical name
Unfortunately, the restore is unsuccessful with the result:
Server: Msg 3203, Level 16, State 1, Line 1
Read on 'e:\wite.bak' failed, status = 1. See the SQL Server error log for
more details.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Both physical files wite_data.mdf and wite_log.ldf are in the respective
folders already. On Enterprise manager, the databse WITE status shows
'Loading'.
Please help...
Hawley
Permission thing, perhaps? Does the service account have permissions to access the backup file?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"HawleyBeach" <HawleyBeach@.discussions.microsoft.com> wrote in message
news:A07CD252-F19A-4CED-82D7-A5D7185DC181@.microsoft.com...
> Hi DBA there,
> I am restoring a bakup from CD with SQL Statement:
> RESTORE DATABASE WITE
> FROM disk ='e:\wite.bak'
> WITH RECOVERY,
> MOVE 'wfield_dat' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data\wite_mdf.mdf',
> MOVE 'wfield_log' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\log\wite_log.ldf'
> i have also use RESTORE FILELISTONLY to ensure the logical name
> Unfortunately, the restore is unsuccessful with the result:
> Server: Msg 3203, Level 16, State 1, Line 1
> Read on 'e:\wite.bak' failed, status = 1. See the SQL Server error log for
> more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Both physical files wite_data.mdf and wite_log.ldf are in the respective
> folders already. On Enterprise manager, the databse WITE status shows
> 'Loading'.
> Please help...
> Hawley
>
|||Copy the back file from CD to hard disk and then try restoring.
Thanks
Hari
"HawleyBeach" <HawleyBeach@.discussions.microsoft.com> wrote in message
news:A07CD252-F19A-4CED-82D7-A5D7185DC181@.microsoft.com...
> Hi DBA there,
> I am restoring a bakup from CD with SQL Statement:
> RESTORE DATABASE WITE
> FROM disk ='e:\wite.bak'
> WITH RECOVERY,
> MOVE 'wfield_dat' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data\wite_mdf.mdf',
> MOVE 'wfield_log' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\log\wite_log.ldf'
> i have also use RESTORE FILELISTONLY to ensure the logical name
> Unfortunately, the restore is unsuccessful with the result:
> Server: Msg 3203, Level 16, State 1, Line 1
> Read on 'e:\wite.bak' failed, status = 1. See the SQL Server error log for
> more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Both physical files wite_data.mdf and wite_log.ldf are in the respective
> folders already. On Enterprise manager, the databse WITE status shows
> 'Loading'.
> Please help...
> Hawley
>

RESTORE DATABASE is terminating abnormally

Hi DBA there,
I am restoring a bakup from CD with SQL Statement:
RESTORE DATABASE WITE
FROM disk ='e:\wite.bak'
WITH RECOVERY,
MOVE 'wfield_dat' TO 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\wite_mdf.mdf',
MOVE 'wfield_log' TO 'c:\Program Files\Microsoft SQL
Server\MSSQL\log\wite_log.ldf'
i have also use RESTORE FILELISTONLY to ensure the logical name
Unfortunately, the restore is unsuccessful with the result:
Server: Msg 3203, Level 16, State 1, Line 1
Read on 'e:\wite.bak' failed, status = 1. See the SQL Server error log for
more details.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Both physical files wite_data.mdf and wite_log.ldf are in the respective
folders already. On Enterprise manager, the databse WITE status shows
'Loading'.
Please help...
HawleyPermission thing, perhaps? Does the service account have permissions to acce
ss the backup file?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"HawleyBeach" <HawleyBeach@.discussions.microsoft.com> wrote in message
news:A07CD252-F19A-4CED-82D7-A5D7185DC181@.microsoft.com...
> Hi DBA there,
> I am restoring a bakup from CD with SQL Statement:
> RESTORE DATABASE WITE
> FROM disk ='e:\wite.bak'
> WITH RECOVERY,
> MOVE 'wfield_dat' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data\wite_mdf.mdf',
> MOVE 'wfield_log' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\log\wite_log.ldf'
> i have also use RESTORE FILELISTONLY to ensure the logical name
> Unfortunately, the restore is unsuccessful with the result:
> Server: Msg 3203, Level 16, State 1, Line 1
> Read on 'e:\wite.bak' failed, status = 1. See the SQL Server error log for
> more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Both physical files wite_data.mdf and wite_log.ldf are in the respective
> folders already. On Enterprise manager, the databse WITE status shows
> 'Loading'.
> Please help...
> Hawley
>|||Copy the back file from CD to hard disk and then try restoring.
Thanks
Hari
"HawleyBeach" <HawleyBeach@.discussions.microsoft.com> wrote in message
news:A07CD252-F19A-4CED-82D7-A5D7185DC181@.microsoft.com...
> Hi DBA there,
> I am restoring a bakup from CD with SQL Statement:
> RESTORE DATABASE WITE
> FROM disk ='e:\wite.bak'
> WITH RECOVERY,
> MOVE 'wfield_dat' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data\wite_mdf.mdf',
> MOVE 'wfield_log' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\log\wite_log.ldf'
> i have also use RESTORE FILELISTONLY to ensure the logical name
> Unfortunately, the restore is unsuccessful with the result:
> Server: Msg 3203, Level 16, State 1, Line 1
> Read on 'e:\wite.bak' failed, status = 1. See the SQL Server error log for
> more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Both physical files wite_data.mdf and wite_log.ldf are in the respective
> folders already. On Enterprise manager, the databse WITE status shows
> 'Loading'.
> Please help...
> Hawley
>sql

Restore database fails only when source is read from another machine ( server),even with U

Steen,
Thank you, but it fails with the same message when using an UNC like
disk='\\Peter\C\backups\dottietest.bak', instead of disk =
'K:\backups\dottietest.bak'
where \\Peter\C\backups is copied from the address bar of the Windows
Explorer, so its syntax is correct.
If I use an UNC pointing to the local machine, i.e. the one I am executing
it from and on which the target server is, like
disk='\\thisserver\C\Temp\dottietest.bak', then the UNC format works.
What could it be?
PKuhne
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:OG1uxTjJFHA.2628@.tk2msftngp13.phx.gbl...
> You can't use a mapped drive, so you'll have to use the UNC path instead -
> that will do the trick....
> Regards
> Steen
> PKuhne wrote:
>
The account that's running the backups needs to have permissions to the
share on the other machine. You have to make sure that:
1. You are using a UNC. You can't use mapped drives for directories on
other machines.
2. The account that's running the backup has write permissions on the share
for the other machine.
"PKuhne" <peter@.chsoft.com> wrote in message
news:OLFH4jmJFHA.4012@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Steen,
> Thank you, but it fails with the same message when using an UNC like
> disk='\\Peter\C\backups\dottietest.bak', instead of disk =
> 'K:\backups\dottietest.bak'
> where \\Peter\C\backups is copied from the address bar of the Windows
> Explorer, so its syntax is correct.
> If I use an UNC pointing to the local machine, i.e. the one I am executing
> it from and on which the target server is, like
> disk='\\thisserver\C\Temp\dottietest.bak', then the UNC format works.
> What could it be?
> PKuhne
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:OG1uxTjJFHA.2628@.tk2msftngp13.phx.gbl...
instead -
>
|||Derrick,
1. When I execute this command on a Win2k Prof (drive d like
" restore database restoretest
from disk = 'k:\backups\dottietest.bak'
with move 'vam_system_data' to 'd:\vamdata\sample\vamsystemdata.mdf',
....",
where K: is a Win2k server and d: is on the Win2kProf machine then it works
without UNC.
2. When I reverse this, ie I execute this command on the WIN2k Server (drive
d to restore from the Win2kProf machine like
" restore database restoretest
from disk = '\\win2kprof\d\backups\dottietest.bak'
with move 'vam_system_data' to 'd:\vamdata\sample\vamsystemdata.mdf',
....",
then I get the original error "... Operating system error = 5(Access is
denied.)."
At this time the Property page's Security tab for the dottietest.bak file on
the winProf machine, looked at from the Win2kServer via WinExplorer, has:
'Everyone' as group name, with all these perimissions checked, but greyed
out:
Full control,Modify, Read & Execute,Read, write, and no other group or user.
The win2k server and win2k prof machines are on the same network (belonging
to the same workgroup, with no domain present)
3. Do you really mean that ".. the account that's running the backup needs
to have permissions to the
share on the other machine." ? I could have gotten the *.bak file that I
try to restore from any server in the world, and that is why I think that
you mean ".. the account that's running the RESTORE needs to have
permissions to the share on the other machine.
If this is so, then all this boils down now to "assigning the permissions".
I wonder how this is done, i.e. what more has to be done beyond the server
having rights to reading and writing from/to the Win2kProf machine (at least
via drag and drop of files through Win Explorer).
It is strange that when I execute this from a batch file:
" copy \\Laptop\Laptop_C\vamdata6\sample\sample.bak c:\
isql /S . /U vamlogin /P go /i restore_db.sql "
...with this in restore_db.sql :
"restore database sample
from disk = 'c:\sample.bak'
with move 'vsm_system_data' to 'd:\temp\vsmsystemdata.ndf' ,
move 'vsm_user_data' to 'd:\temp\vsmuserdata.ndf',
move 'vsm_log' to 'd:\temp\vsmlog.ldf',
stats=10"
... then the restore works, i.e. I seem to be able to copy the file from the
win2kProf machine, but it fails to execute the "restore" from that same
machine (as shown further up).
Perhaps this can only work on a domain controller system, which I do not
intend to establish.
Any suggestion?
TIA
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:etVuFJxJFHA.4064@.tk2msftngp13.phx.gbl...
> The account that's running the backups needs to have permissions to the
> share on the other machine. You have to make sure that:
> 1. You are using a UNC. You can't use mapped drives for directories on
> other machines.
> 2. The account that's running the backup has write permissions on the
share[vbcol=seagreen]
> for the other machine.
>
>
> "PKuhne" <peter@.chsoft.com> wrote in message
> news:OLFH4jmJFHA.4012@.TK2MSFTNGP09.phx.gbl...
executing[vbcol=seagreen]
> instead -
'd:\vamdata\sample\vamsystemdata.mdf',[vbcol=seagreen]
"[vbcol=seagreen]
the[vbcol=seagreen]
'k:\backups\dottietest.bak'[vbcol=seagreen]
is
>
|||Run that copy command from an xp_cmdshell and see if you get the permissions
error. Also, on the other machine set up a specific share for the backup
directory. You can do this my right-clicking on My Computer and going to
Manage. Click on Shared Folders/Shares/Right-click on Shares to Add
Share, then add the backups. Put full permissions to everyone. You will
then map the restore with like this:
\\win2kprof\sharename\backupfile.bak
The mapped drive might work when you are logged in; however, it will not
work when you log off. Remember that mapped drive is for your profile.
"PKuhne" <p.kuhne@.verizon.net> wrote in message
news:#Mpzr$yJFHA.2736@.TK2MSFTNGP09.phx.gbl...
> Derrick,
> 1. When I execute this command on a Win2k Prof (drive d like
> " restore database restoretest
> from disk = 'k:\backups\dottietest.bak'
> with move 'vam_system_data' to 'd:\vamdata\sample\vamsystemdata.mdf',
> ...",
> where K: is a Win2k server and d: is on the Win2kProf machine then it
works
> without UNC.
> 2. When I reverse this, ie I execute this command on the WIN2k Server
(drive
> d to restore from the Win2kProf machine like
> " restore database restoretest
> from disk = '\\win2kprof\d\backups\dottietest.bak'
> with move 'vam_system_data' to 'd:\vamdata\sample\vamsystemdata.mdf',
> ...",
> then I get the original error "... Operating system error = 5(Access is
> denied.)."
> At this time the Property page's Security tab for the dottietest.bak file
on
> the winProf machine, looked at from the Win2kServer via WinExplorer, has:
> 'Everyone' as group name, with all these perimissions checked, but greyed
> out:
> Full control,Modify, Read & Execute,Read, write, and no other group or
user.
> The win2k server and win2k prof machines are on the same network
(belonging
> to the same workgroup, with no domain present)
> 3. Do you really mean that ".. the account that's running the backup needs
> to have permissions to the
> share on the other machine." ? I could have gotten the *.bak file that I
> try to restore from any server in the world, and that is why I think that
> you mean ".. the account that's running the RESTORE needs to have
> permissions to the share on the other machine.
> If this is so, then all this boils down now to "assigning the
permissions".
> I wonder how this is done, i.e. what more has to be done beyond the server
> having rights to reading and writing from/to the Win2kProf machine (at
least
> via drag and drop of files through Win Explorer).
> It is strange that when I execute this from a batch file:
> " copy \\Laptop\Laptop_C\vamdata6\sample\sample.bak c:\
> isql /S . /U vamlogin /P go /i restore_db.sql "
> ..with this in restore_db.sql :
> "restore database sample
> from disk = 'c:\sample.bak'
> with move 'vsm_system_data' to 'd:\temp\vsmsystemdata.ndf' ,
> move 'vsm_user_data' to 'd:\temp\vsmuserdata.ndf',
> move 'vsm_log' to 'd:\temp\vsmlog.ldf',
> stats=10"
> .. then the restore works, i.e. I seem to be able to copy the file from
the[vbcol=seagreen]
> win2kProf machine, but it fails to execute the "restore" from that same
> machine (as shown further up).
> Perhaps this can only work on a domain controller system, which I do not
> intend to establish.
> Any suggestion?
> TIA
>
>
>
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:etVuFJxJFHA.4064@.tk2msftngp13.phx.gbl...
> share
> executing
target[vbcol=seagreen]
> 'd:\vamdata\sample\vamsystemdata.mdf',
> "
"
> the
> 'k:\backups\dottietest.bak'
> is
>
|||Hi
You also need to make sure that the account that runs the SQL Server Service
(and maybe also the SQL Server Agent service) has access to the shares you
need to access. It's this account that determines the access - not the
account you are logged on with.
Regards
Steen
PKuhne wrote:[vbcol=seagreen]
> Derrick,
> 1. When I execute this command on a Win2k Prof (drive d like
> " restore database restoretest
> from disk = 'k:\backups\dottietest.bak'
> with move 'vam_system_data' to
> 'd:\vamdata\sample\vamsystemdata.mdf', ...",
> where K: is a Win2k server and d: is on the Win2kProf machine then it
> works without UNC.
> 2. When I reverse this, ie I execute this command on the WIN2k Server
> (drive d to restore from the Win2kProf machine like
> " restore database restoretest
> from disk = '\\win2kprof\d\backups\dottietest.bak'
> with move 'vam_system_data' to
> 'd:\vamdata\sample\vamsystemdata.mdf', ...",
> then I get the original error "... Operating system error = 5(Access
> is denied.)."
> At this time the Property page's Security tab for the dottietest.bak
> file on the winProf machine, looked at from the Win2kServer via
> WinExplorer, has: 'Everyone' as group name, with all these
> perimissions checked, but greyed out:
> Full control,Modify, Read & Execute,Read, write, and no other group
> or user.
> The win2k server and win2k prof machines are on the same network
> (belonging to the same workgroup, with no domain present)
> 3. Do you really mean that ".. the account that's running the backup
> needs to have permissions to the
> share on the other machine." ? I could have gotten the *.bak file
> that I try to restore from any server in the world, and that is why I
> think that you mean ".. the account that's running the RESTORE needs
> to have permissions to the share on the other machine.
> If this is so, then all this boils down now to "assigning the
> permissions". I wonder how this is done, i.e. what more has to be
> done beyond the server having rights to reading and writing from/to
> the Win2kProf machine (at least via drag and drop of files through
> Win Explorer).
> It is strange that when I execute this from a batch file:
> " copy \\Laptop\Laptop_C\vamdata6\sample\sample.bak c:\
> isql /S . /U vamlogin /P go /i restore_db.sql "
> ..with this in restore_db.sql :
> "restore database sample
> from disk = 'c:\sample.bak'
> with move 'vsm_system_data' to 'd:\temp\vsmsystemdata.ndf' ,
> move 'vsm_user_data' to 'd:\temp\vsmuserdata.ndf',
> move 'vsm_log' to 'd:\temp\vsmlog.ldf',
> stats=10"
> .. then the restore works, i.e. I seem to be able to copy the file
> from the win2kProf machine, but it fails to execute the "restore"
> from that same machine (as shown further up).
> Perhaps this can only work on a domain controller system, which I do
> not intend to establish.
> Any suggestion?
> TIA
>
>
>
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:etVuFJxJFHA.4064@.tk2msftngp13.phx.gbl...
> 'd:\vamdata\sample\vamsystemdata.mdf',
sql

Restore database fails only when source is read from another machine ( server),even wi

Steen,
Thank you, but it fails with the same message when using an UNC like
disk='\\Peter\C\backups\dottietest.bak', instead of disk =
'K:\backups\dottietest.bak'
where \\Peter\C\backups is copied from the address bar of the Windows
Explorer, so its syntax is correct.
If I use an UNC pointing to the local machine, i.e. the one I am executing
it from and on which the target server is, like
disk='\\thisserver\C\Temp\dottietest.bak', then the UNC format works.
What could it be?
PKuhne
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:OG1uxTjJFHA.2628@.tk2msftngp13.phx.gbl...
> You can't use a mapped drive, so you'll have to use the UNC path instead -
> that will do the trick....
> Regards
> Steen
> PKuhne wrote:
>The account that's running the backups needs to have permissions to the
share on the other machine. You have to make sure that:
1. You are using a UNC. You can't use mapped drives for directories on
other machines.
2. The account that's running the backup has write permissions on the share
for the other machine.
"PKuhne" <peter@.chsoft.com> wrote in message
news:OLFH4jmJFHA.4012@.TK2MSFTNGP09.phx.gbl...
> Steen,
> Thank you, but it fails with the same message when using an UNC like
> disk='\\Peter\C\backups\dottietest.bak', instead of disk =
> 'K:\backups\dottietest.bak'
> where \\Peter\C\backups is copied from the address bar of the Windows
> Explorer, so its syntax is correct.
> If I use an UNC pointing to the local machine, i.e. the one I am executing
> it from and on which the target server is, like
> disk='\\thisserver\C\Temp\dottietest.bak', then the UNC format works.
> What could it be?
> PKuhne
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:OG1uxTjJFHA.2628@.tk2msftngp13.phx.gbl...
instead -[vbcol=seagreen]
>|||Derrick,
1. When I execute this command on a Win2k Prof (drive d like
" restore database restoretest
from disk = 'k:\backups\dottietest.bak'
with move 'vam_system_data' to 'd:\vamdata\sample\vamsystemdata.mdf',
...",
where K: is a Win2k server and d: is on the Win2kProf machine then it works
without UNC.
2. When I reverse this, ie I execute this command on the WIN2k Server (drive
d to restore from the Win2kProf machine like
" restore database restoretest
from disk = '\\win2kprof\d\backups\dottietest.bak'
with move 'vam_system_data' to 'd:\vamdata\sample\vamsystemdata.mdf',
...",
then I get the original error "... Operating system error = 5(Access is
denied.)."
At this time the Property page's Security tab for the dottietest.bak file on
the winProf machine, looked at from the Win2kServer via WinExplorer, has:
'Everyone' as group name, with all these perimissions checked, but greyed
out:
Full control,Modify, Read & Execute,Read, write, and no other group or user.
The win2k server and win2k prof machines are on the same network (belonging
to the same workgroup, with no domain present)
3. Do you really mean that ".. the account that's running the backup needs
to have permissions to the
share on the other machine." ? I could have gotten the *.bak file that I
try to restore from any server in the world, and that is why I think that
you mean ".. the account that's running the RESTORE needs to have
permissions to the share on the other machine.
If this is so, then all this boils down now to "assigning the permissions".
I wonder how this is done, i.e. what more has to be done beyond the server
having rights to reading and writing from/to the Win2kProf machine (at least
via drag and drop of files through Win Explorer).
It is strange that when I execute this from a batch file:
" copy \\Laptop\Laptop_C\vamdata6\sample\sample
.bak c:\
isql /S . /U vamlogin /P go /i restore_db.sql "
..with this in restore_db.sql :
"restore database sample
from disk = 'c:\sample.bak'
with move 'vsm_system_data' to 'd:\temp\vsmsystemdata.ndf' ,
move 'vsm_user_data' to 'd:\temp\vsmuserdata.ndf',
move 'vsm_log' to 'd:\temp\vsmlog.ldf',
stats=10"
.. then the restore works, i.e. I seem to be able to copy the file from the
win2kProf machine, but it fails to execute the "restore" from that same
machine (as shown further up).
Perhaps this can only work on a domain controller system, which I do not
intend to establish.
Any suggestion?
TIA
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:etVuFJxJFHA.4064@.tk2msftngp13.phx.gbl...
> The account that's running the backups needs to have permissions to the
> share on the other machine. You have to make sure that:
> 1. You are using a UNC. You can't use mapped drives for directories on
> other machines.
> 2. The account that's running the backup has write permissions on the
share
> for the other machine.
>
>
> "PKuhne" <peter@.chsoft.com> wrote in message
> news:OLFH4jmJFHA.4012@.TK2MSFTNGP09.phx.gbl...
executing[vbcol=seagreen]
> instead -
'd:\vamdata\sample\vamsystemdata.mdf',[vbcol=seagreen]
"[vbcol=seagreen]
the[vbcol=seagreen]
'k:\backups\dottietest.bak'[vbcol=seagreen]
is[vbcol=seagreen]
>|||Run that copy command from an xp_cmdshell and see if you get the permissions
error. Also, on the other machine set up a specific share for the backup
directory. You can do this my right-clicking on My Computer and going to
Manage. Click on Shared Folders/Shares/Right-click on Shares to Add
Share, then add the backups. Put full permissions to everyone. You will
then map the restore with like this:
\\win2kprof\sharename\backupfile.bak
The mapped drive might work when you are logged in; however, it will not
work when you log off. Remember that mapped drive is for your profile.
"PKuhne" <p.kuhne@.verizon.net> wrote in message
news:#Mpzr$yJFHA.2736@.TK2MSFTNGP09.phx.gbl...
> Derrick,
> 1. When I execute this command on a Win2k Prof (drive d like
> " restore database restoretest
> from disk = 'k:\backups\dottietest.bak'
> with move 'vam_system_data' to 'd:\vamdata\sample\vamsystemdata.mdf',
> ...",
> where K: is a Win2k server and d: is on the Win2kProf machine then it
works
> without UNC.
> 2. When I reverse this, ie I execute this command on the WIN2k Server
(drive
> d to restore from the Win2kProf machine like
> " restore database restoretest
> from disk = '\\win2kprof\d\backups\dottietest.bak'
> with move 'vam_system_data' to 'd:\vamdata\sample\vamsystemdata.mdf',
> ...",
> then I get the original error "... Operating system error = 5(Access is
> denied.)."
> At this time the Property page's Security tab for the dottietest.bak file
on
> the winProf machine, looked at from the Win2kServer via WinExplorer, has:
> 'Everyone' as group name, with all these perimissions checked, but greyed
> out:
> Full control,Modify, Read & Execute,Read, write, and no other group or
user.
> The win2k server and win2k prof machines are on the same network
(belonging
> to the same workgroup, with no domain present)
> 3. Do you really mean that ".. the account that's running the backup needs
> to have permissions to the
> share on the other machine." ? I could have gotten the *.bak file that I
> try to restore from any server in the world, and that is why I think that
> you mean ".. the account that's running the RESTORE needs to have
> permissions to the share on the other machine.
> If this is so, then all this boils down now to "assigning the
permissions".
> I wonder how this is done, i.e. what more has to be done beyond the server
> having rights to reading and writing from/to the Win2kProf machine (at
least
> via drag and drop of files through Win Explorer).
> It is strange that when I execute this from a batch file:
> " copy \\Laptop\Laptop_C\vamdata6\sample\sample
.bak c:\
> isql /S . /U vamlogin /P go /i restore_db.sql "
> ..with this in restore_db.sql :
> "restore database sample
> from disk = 'c:\sample.bak'
> with move 'vsm_system_data' to 'd:\temp\vsmsystemdata.ndf' ,
> move 'vsm_user_data' to 'd:\temp\vsmuserdata.ndf',
> move 'vsm_log' to 'd:\temp\vsmlog.ldf',
> stats=10"
> .. then the restore works, i.e. I seem to be able to copy the file from
the
> win2kProf machine, but it fails to execute the "restore" from that same
> machine (as shown further up).
> Perhaps this can only work on a domain controller system, which I do not
> intend to establish.
> Any suggestion?
> TIA
>
>
>
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:etVuFJxJFHA.4064@.tk2msftngp13.phx.gbl...
> share
> executing
target[vbcol=seagreen]
> 'd:\vamdata\sample\vamsystemdata.mdf',
> "
"[vbcol=seagreen]
> the
> 'k:\backups\dottietest.bak'
> is
>|||Hi
You also need to make sure that the account that runs the SQL Server Service
(and maybe also the SQL Server Agent service) has access to the shares you
need to access. It's this account that determines the access - not the
account you are logged on with.
Regards
Steen
PKuhne wrote:[vbcol=seagreen]
> Derrick,
> 1. When I execute this command on a Win2k Prof (drive d like
> " restore database restoretest
> from disk = 'k:\backups\dottietest.bak'
> with move 'vam_system_data' to
> 'd:\vamdata\sample\vamsystemdata.mdf', ...",
> where K: is a Win2k server and d: is on the Win2kProf machine then it
> works without UNC.
> 2. When I reverse this, ie I execute this command on the WIN2k Server
> (drive d to restore from the Win2kProf machine like
> " restore database restoretest
> from disk = '\\win2kprof\d\backups\dottietest.bak'
> with move 'vam_system_data' to
> 'd:\vamdata\sample\vamsystemdata.mdf', ...",
> then I get the original error "... Operating system error = 5(Access
> is denied.)."
> At this time the Property page's Security tab for the dottietest.bak
> file on the winProf machine, looked at from the Win2kServer via
> WinExplorer, has: 'Everyone' as group name, with all these
> perimissions checked, but greyed out:
> Full control,Modify, Read & Execute,Read, write, and no other group
> or user.
> The win2k server and win2k prof machines are on the same network
> (belonging to the same workgroup, with no domain present)
> 3. Do you really mean that ".. the account that's running the backup
> needs to have permissions to the
> share on the other machine." ? I could have gotten the *.bak file
> that I try to restore from any server in the world, and that is why I
> think that you mean ".. the account that's running the RESTORE needs
> to have permissions to the share on the other machine.
> If this is so, then all this boils down now to "assigning the
> permissions". I wonder how this is done, i.e. what more has to be
> done beyond the server having rights to reading and writing from/to
> the Win2kProf machine (at least via drag and drop of files through
> Win Explorer).
> It is strange that when I execute this from a batch file:
> " copy \\Laptop\Laptop_C\vamdata6\sample\sample
.bak c:\
> isql /S . /U vamlogin /P go /i restore_db.sql "
> ..with this in restore_db.sql :
> "restore database sample
> from disk = 'c:\sample.bak'
> with move 'vsm_system_data' to 'd:\temp\vsmsystemdata.ndf' ,
> move 'vsm_user_data' to 'd:\temp\vsmuserdata.ndf',
> move 'vsm_log' to 'd:\temp\vsmlog.ldf',
> stats=10"
> .. then the restore works, i.e. I seem to be able to copy the file
> from the win2kProf machine, but it fails to execute the "restore"
> from that same machine (as shown further up).
> Perhaps this can only work on a domain controller system, which I do
> not intend to establish.
> Any suggestion?
> TIA
>
>
>
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:etVuFJxJFHA.4064@.tk2msftngp13.phx.gbl...
> 'd:\vamdata\sample\vamsystemdata.mdf',

Restore database fails only when source is read from another machine ( server)

When executing this from Query Analyzer on server1, where the target is
located,
restore database restoretest
from disk = 'd:\backups\dottietest.bak'
with move 'vam_system_data' to 'd:\vamdata\sample\vamsystemdata.mdf',
move 'vam_user_data' to 'd:\vamdata\sample\vamuserdata.ndf',
move 'vam_log' to 'd:\vamdata\sample\vamlog.ldf',
stats=10,
REPLACE
... it succeeds if " .. from disk = 'd:\backups\dottietest.bak' "
but fails with " .. from disk = 'K:\backups\dottietest.bak' "
It fails when K: is used, i.e. the drive mapping to another win2k server's
D: drive, whereas it succeeds when the *.BAK file is on the local D; drive.
The sql server log shows:
"BackupDiskFile::OpenMedia: Backup device 'k:\backups\dottietest.bak' failed
to open. Operating system error = 5(Access is denied.)."
I can open all other files like XLS, etc from this server on K:.
The dottietest.BAK file was created by another SQL server, which is not on
server1.
Which permission am I missing?
Please help.
TIA
You can't use a mapped drive, so you'll have to use the UNC path instead -
that will do the trick....
Regards
Steen
PKuhne wrote:
> When executing this from Query Analyzer on server1, where the target
> is located,
> restore database restoretest
> from disk = 'd:\backups\dottietest.bak'
> with move 'vam_system_data' to 'd:\vamdata\sample\vamsystemdata.mdf',
> move 'vam_user_data' to 'd:\vamdata\sample\vamuserdata.ndf',
> move 'vam_log' to 'd:\vamdata\sample\vamlog.ldf',
> stats=10,
> REPLACE
> .. it succeeds if " .. from disk = 'd:\backups\dottietest.bak' "
> but fails with " .. from disk = 'K:\backups\dottietest.bak' "
> It fails when K: is used, i.e. the drive mapping to another win2k
> server's D: drive, whereas it succeeds when the *.BAK file is on the
> local D; drive. The sql server log shows:
> "BackupDiskFile::OpenMedia: Backup device 'k:\backups\dottietest.bak'
> failed to open. Operating system error = 5(Access is denied.)."
> I can open all other files like XLS, etc from this server on K:.
> The dottietest.BAK file was created by another SQL server, which is
> not on server1.
> Which permission am I missing?
> Please help.
> TIA

Restore database fails only when source is read from another machine ( server)

When executing this from Query Analyzer on server1, where the target is
located,
restore database restoretest
from disk = 'd:\backups\dottietest.bak'
with move 'vam_system_data' to 'd:\vamdata\sample\vamsystemdata.mdf',
move 'vam_user_data' to 'd:\vamdata\sample\vamuserdata.ndf',
move 'vam_log' to 'd:\vamdata\sample\vamlog.ldf',
stats=10,
REPLACE
.. it succeeds if " .. from disk = 'd:\backups\dottietest.bak' "
but fails with " .. from disk = 'K:\backups\dottietest.bak' "
It fails when K: is used, i.e. the drive mapping to another win2k server's
D: drive, whereas it succeeds when the *.BAK file is on the local D; drive.
The sql server log shows:
"BackupDiskFile::OpenMedia: Backup device 'k:\backups\dottietest.bak' failed
to open. Operating system error = 5(Access is denied.)."
I can open all other files like XLS, etc from this server on K:.
The dottietest.BAK file was created by another SQL server, which is not on
server1.
Which permission am I missing?
Please help.
TIAYou can't use a mapped drive, so you'll have to use the UNC path instead -
that will do the trick....
Regards
Steen
PKuhne wrote:
> When executing this from Query Analyzer on server1, where the target
> is located,
> restore database restoretest
> from disk = 'd:\backups\dottietest.bak'
> with move 'vam_system_data' to 'd:\vamdata\sample\vamsystemdata.mdf',
> move 'vam_user_data' to 'd:\vamdata\sample\vamuserdata.ndf',
> move 'vam_log' to 'd:\vamdata\sample\vamlog.ldf',
> stats=10,
> REPLACE
> .. it succeeds if " .. from disk = 'd:\backups\dottietest.bak' "
> but fails with " .. from disk = 'K:\backups\dottietest.bak' "
> It fails when K: is used, i.e. the drive mapping to another win2k
> server's D: drive, whereas it succeeds when the *.BAK file is on the
> local D; drive. The sql server log shows:
> "BackupDiskFile::OpenMedia: Backup device 'k:\backups\dottietest.bak'
> failed to open. Operating system error = 5(Access is denied.)."
> I can open all other files like XLS, etc from this server on K:.
> The dottietest.BAK file was created by another SQL server, which is
> not on server1.
> Which permission am I missing?
> Please help.
> TIA

Restore Database

Hi everyone,
I have a SQL Server JOB that restores a database every day at 2 AM.
Here's the script:
USE master
RESTORE DATABASE SomeDatabase
FROM DISK = 'E:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\SomeDatabase.bak'
WITH RECOVERY,
MOVE 'SomeDatabase_Data' TO 'E:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\SomeDatabase.mdf',
MOVE 'SomeDatabase_Log' TO 'E:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Log\SomeDatabase.ldf'
GO
However, if I had a SQL Query window open, the job will fail and I get
an error stating that the database is in use. Is there any way that I
can still restore even if the database is in use?
*** Sent via Developersdex http://www.codecomments.com ***
Audrey,
You need to chase any users out of the database first. One way is to add to
your script.
ALTER DATABASE SomeDatabase SET RESTRICTED_USER
ALTER DATABASE SomeDatabase SET SINGLE_USER
This will first limit future connections to db_owner, db_creator and
sysadmin, then switching to single user will kick other connections out.
Once you have restored, remember to do:
ALTER DATABASE SomeDatabase SET MULTI_USER
Make sure that the SET MULTI_USER works even if the restore cannot run for
the reason that some other sysadmin got in during the moment between the
ALTER and the RESTORE.
RLF
"Audrey Ng" <odd26uk@.yahoo.co.uk> wrote in message
news:%23r%23NwEJbHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Hi everyone,
> I have a SQL Server JOB that restores a database every day at 2 AM.
> Here's the script:
> USE master
> RESTORE DATABASE SomeDatabase
> FROM DISK = 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Backup\SomeDatabase.bak'
> WITH RECOVERY,
> MOVE 'SomeDatabase_Data' TO 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\SomeDatabase.mdf',
> MOVE 'SomeDatabase_Log' TO 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Log\SomeDatabase.ldf'
> GO
> However, if I had a SQL Query window open, the job will fail and I get
> an error stating that the database is in use. Is there any way that I
> can still restore even if the database is in use?
> *** Sent via Developersdex http://www.codecomments.com ***
|||Ok great! I will try that! Thanks!
*** Sent via Developersdex http://www.codecomments.com ***
|||I disagree RLF. Restricted_user only limits connections to db_owner, not
the number of them. And Single User will only work if you use this specific
syntax:
ALTER DATABASE mydatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Note however that that WILL yank the run out from under currently inprocess
transactions.
Another method is to loop through all currently open spids connected to that
database and issue the KILL command, thusly:
WHILE 1 = 1
BEGIN
SELECT @.activespid = spid
FROM master.dbo.sysprocesses (NOLOCK)
WHERE db_name(dbid) = @.dbname
AND SPID > 50
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
SET @.sql = 'KILL ' + cast(@.activespid as varchar(10))
EXEC (@.sql) --can't seem to directly EXECute a kill !
--give server time to finalize kill before looping
WAITFOR DELAY '00:00:00.1'
END
SET @.i = @.i + 1
IF @.i > 100 --need to exit IF hung
BEGIN
BREAK
END
END
IF @.i > 100 --couldn't kill all spids
BEGIN --raise error, then move on
RAISERROR(60000,10,1,@.dbname) WITH LOG
GOTO NextFile
END
The looping code has legacy stuff from sql7/2000 where I experienced
problems with KILL working properly on a very infrequent basis. I do not
know if SQL2K5 exhibits the same behavior.
TheSQLGuru
President
Indicium Resources, Inc.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eWY3WMJbHHA.1240@.TK2MSFTNGP04.phx.gbl...
> Audrey,
> You need to chase any users out of the database first. One way is to add
> to your script.
> ALTER DATABASE SomeDatabase SET RESTRICTED_USER
> ALTER DATABASE SomeDatabase SET SINGLE_USER
> This will first limit future connections to db_owner, db_creator and
> sysadmin, then switching to single user will kick other connections out.
> Once you have restored, remember to do:
> ALTER DATABASE SomeDatabase SET MULTI_USER
> Make sure that the SET MULTI_USER works even if the restore cannot run for
> the reason that some other sysadmin got in during the moment between the
> ALTER and the RESTORE.
> RLF
> "Audrey Ng" <odd26uk@.yahoo.co.uk> wrote in message
> news:%23r%23NwEJbHHA.4772@.TK2MSFTNGP05.phx.gbl...
>
|||Of course, you are right to add the clause on the SET SINGLE_USER, or ...
ALTER DATABASE mydatabase SET SINGLE_USER ROLLBACK AFTER integer SECONDS
However, the switching to RESTRICTED is useful since it limits who else can
connect. I have had switching to SINGLE_USER mode fail to give me the
needed results if I did not do RESTRICTED first. In one case another
process connected to the database in the instant between going to
SINGLE_USER mode and doing my next scripted command. That was a pain in the
neck to resolve.
Of course, KILL also clobbers currently inprocess transactions. I began
using the ALTER approach (using WITH ROLLBACK IMMEDIATE, of course) because
some transactions would not kill reliably in 2000, as you noted.
I don't know either about SS2005 overall, but I have experienced one
unkillable connection on 2005 that was holding some locks. I finally had to
stop and restart SQL Server.
FWIW,
RLF
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:OqNNg6ZbHHA.4888@.TK2MSFTNGP06.phx.gbl...
>I disagree RLF. Restricted_user only limits connections to db_owner, not
>the number of them. And Single User will only work if you use this
>specific syntax:
> ALTER DATABASE mydatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> Note however that that WILL yank the run out from under currently
> inprocess transactions.
> Another method is to loop through all currently open spids connected to
> that database and issue the KILL command, thusly:
> WHILE 1 = 1
> BEGIN
> SELECT @.activespid = spid
> FROM master.dbo.sysprocesses (NOLOCK)
> WHERE db_name(dbid) = @.dbname
> AND SPID > 50
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> SET @.sql = 'KILL ' + cast(@.activespid as varchar(10))
> EXEC (@.sql) --can't seem to directly EXECute a kill !
> --give server time to finalize kill before looping
> WAITFOR DELAY '00:00:00.1'
> END
> SET @.i = @.i + 1
> IF @.i > 100 --need to exit IF hung
> BEGIN
> BREAK
> END
> END
>
> IF @.i > 100 --couldn't kill all spids
> BEGIN --raise error, then move on
> RAISERROR(60000,10,1,@.dbname) WITH LOG
> GOTO NextFile
> END
>
> The looping code has legacy stuff from sql7/2000 where I experienced
> problems with KILL working properly on a very infrequent basis. I do not
> know if SQL2K5 exhibits the same behavior.
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:eWY3WMJbHHA.1240@.TK2MSFTNGP04.phx.gbl...
>