restore db from network drive

All of our database servers are doing backups and saving the .bak files out
on a network share. With SQL 2005, how can I point to that share and restore
the database in SQL 2005 environment?
When I do 'restore database --> From Device --> File; when I click the 'ADD;
button, I only see my local drives on the computer. How can I map to my
network share and point to my restore file on the network?Use UNC names: \\servername\sharename\foldername\file.ext. The
account doing the backups - which is to say the account under which
SQL Server is running - needs appropriate rights to the share.
Roy Harvey
Beacon Falls, CT
On Tue, 5 Jun 2007 12:20:10 -0400, "Mike" <Mike@.community.nospam>
wrote:

>All of our database servers are doing backups and saving the .bak files out
>on a network share. With SQL 2005, how can I point to that share and restor
e
>the database in SQL 2005 environment?
>When I do 'restore database --> From Device --> File; when I click the 'ADD
;
>button, I only see my local drives on the computer. How can I map to my
>network share and point to my restore file on the network?
>|||I've tried the UNC path and I get 'network path not found' message
Beacon falls, ct, your in my nekc of the woods.
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:km4b63p0q6hco968a82ngn955tgse4eg47@.
4ax.com...[vbcol=seagreen]
> Use UNC names: \\servername\sharename\foldername\file.ext. The
> account doing the backups - which is to say the account under which
> SQL Server is running - needs appropriate rights to the share.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 5 Jun 2007 12:20:10 -0400, "Mike" <Mike@.community.nospam>
> wrote:
>|||Hello,
In the management studio; go to query window and use RESTORE DATABASE
command. Make sure that you start sql server service using
a domain account which has access to remote share.
RESTORE DATABASE <DBNAME> FROM DISK='\\Servername\Share\filename.bak' with
stats=5
Thanks
Hari
"Mike" <Mike@.community.nospam> wrote in message
news:%23xEIwJ5pHHA.1244@.TK2MSFTNGP04.phx.gbl...
> I've tried the UNC path and I get 'network path not found' message
> Beacon falls, ct, your in my nekc of the woods.
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:km4b63p0q6hco968a82ngn955tgse4eg47@.
4ax.com...
>|||On Tue, 5 Jun 2007 12:56:18 -0400, "Mike" <Mike@.community.nospam>
wrote:

>I've tried the UNC path and I get 'network path not found' message
Perhaps it is an access rights problem. A partial test can be done
using xp_cmdshell:
EXEC master..xp_cmdshell 'dir \\servername\sharename\folder\*.*'

>Beacon falls, ct, your in my nekc of the woods.
Cool! If you ever need more hands-on help remember I'm around!
Hari's advice to use a query window is excellent, by the way.
Roy Harvey
Beacon Falls, CT|||Ok, I think I got it, but now I'm getting this message:
'the media set has 2 media families but only 1 are provided. All members
must be provided.'
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:89eb631lpobifjd281tdpma4q57sct745h@.
4ax.com...
> On Tue, 5 Jun 2007 12:56:18 -0400, "Mike" <Mike@.community.nospam>
> wrote:
>
> Perhaps it is an access rights problem. A partial test can be done
> using xp_cmdshell:
> EXEC master..xp_cmdshell 'dir \\servername\sharename\folder\*.*'
>
> Cool! If you ever need more hands-on help remember I'm around!
> Hari's advice to use a query window is excellent, by the way.
> Roy Harvey
> Beacon Falls, CT|||Hello,
Did you strip the backup into multiple files? Can you execute RESTORE
LABELONLY (see books online) and see how many files are associated with the
backup.
Thanks
Hari
"Mike" <Mike@.community.nospam> wrote in message
news:u8sG$y6pHHA.2156@.TK2MSFTNGP03.phx.gbl...
> Ok, I think I got it, but now I'm getting this message:
> 'the media set has 2 media families but only 1 are provided. All members
> must be provided.'
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:89eb631lpobifjd281tdpma4q57sct745h@.
4ax.com...
>|||No, one file. and now I can't even backup the SQL 2000 database version to
do a restore from.
This is driving me nuts.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23Ov2b29pHHA.3264@.TK2MSFTNGP04.phx.gbl...
> Hello,
> Did you strip the backup into multiple files? Can you execute RESTORE
> LABELONLY (see books online) and see how many files are associated with
> the backup.
> Thanks
> Hari
> "Mike" <Mike@.community.nospam> wrote in message
> news:u8sG$y6pHHA.2156@.TK2MSFTNGP03.phx.gbl...
>sql

restore db from network drive

All of our database servers are doing backups and saving the .bak files out
on a network share. With SQL 2005, how can I point to that share and restore
the database in SQL 2005 environment?
When I do 'restore database --> From Device --> File; when I click the 'ADD;
button, I only see my local drives on the computer. How can I map to my
network share and point to my restore file on the network?Use UNC names: \\servername\sharename\foldername\file.ext. The
account doing the backups - which is to say the account under which
SQL Server is running - needs appropriate rights to the share.
Roy Harvey
Beacon Falls, CT
On Tue, 5 Jun 2007 12:20:10 -0400, "Mike" <Mike@.community.nospam>
wrote:
>All of our database servers are doing backups and saving the .bak files out
>on a network share. With SQL 2005, how can I point to that share and restore
>the database in SQL 2005 environment?
>When I do 'restore database --> From Device --> File; when I click the 'ADD;
>button, I only see my local drives on the computer. How can I map to my
>network share and point to my restore file on the network?
>|||I've tried the UNC path and I get 'network path not found' message
Beacon falls, ct, your in my nekc of the woods.
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:km4b63p0q6hco968a82ngn955tgse4eg47@.4ax.com...
> Use UNC names: \\servername\sharename\foldername\file.ext. The
> account doing the backups - which is to say the account under which
> SQL Server is running - needs appropriate rights to the share.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 5 Jun 2007 12:20:10 -0400, "Mike" <Mike@.community.nospam>
> wrote:
>>All of our database servers are doing backups and saving the .bak files
>>out
>>on a network share. With SQL 2005, how can I point to that share and
>>restore
>>the database in SQL 2005 environment?
>>When I do 'restore database --> From Device --> File; when I click the
>>'ADD;
>>button, I only see my local drives on the computer. How can I map to my
>>network share and point to my restore file on the network?
>>|||Hello,
In the management studio; go to query window and use RESTORE DATABASE
command. Make sure that you start sql server service using
a domain account which has access to remote share.
RESTORE DATABASE <DBNAME> FROM DISK='\\Servername\Share\filename.bak' with
stats=5
Thanks
Hari
"Mike" <Mike@.community.nospam> wrote in message
news:%23xEIwJ5pHHA.1244@.TK2MSFTNGP04.phx.gbl...
> I've tried the UNC path and I get 'network path not found' message
> Beacon falls, ct, your in my nekc of the woods.
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:km4b63p0q6hco968a82ngn955tgse4eg47@.4ax.com...
>> Use UNC names: \\servername\sharename\foldername\file.ext. The
>> account doing the backups - which is to say the account under which
>> SQL Server is running - needs appropriate rights to the share.
>> Roy Harvey
>> Beacon Falls, CT
>> On Tue, 5 Jun 2007 12:20:10 -0400, "Mike" <Mike@.community.nospam>
>> wrote:
>>All of our database servers are doing backups and saving the .bak files
>>out
>>on a network share. With SQL 2005, how can I point to that share and
>>restore
>>the database in SQL 2005 environment?
>>When I do 'restore database --> From Device --> File; when I click the
>>'ADD;
>>button, I only see my local drives on the computer. How can I map to my
>>network share and point to my restore file on the network?
>>
>|||On Tue, 5 Jun 2007 12:56:18 -0400, "Mike" <Mike@.community.nospam>
wrote:
>I've tried the UNC path and I get 'network path not found' message
Perhaps it is an access rights problem. A partial test can be done
using xp_cmdshell:
EXEC master..xp_cmdshell 'dir \\servername\sharename\folder\*.*'
>Beacon falls, ct, your in my nekc of the woods.
Cool! If you ever need more hands-on help remember I'm around!
Hari's advice to use a query window is excellent, by the way.
Roy Harvey
Beacon Falls, CT|||Ok, I think I got it, but now I'm getting this message:
'the media set has 2 media families but only 1 are provided. All members
must be provided.'
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:89eb631lpobifjd281tdpma4q57sct745h@.4ax.com...
> On Tue, 5 Jun 2007 12:56:18 -0400, "Mike" <Mike@.community.nospam>
> wrote:
>>I've tried the UNC path and I get 'network path not found' message
> Perhaps it is an access rights problem. A partial test can be done
> using xp_cmdshell:
> EXEC master..xp_cmdshell 'dir \\servername\sharename\folder\*.*'
>>Beacon falls, ct, your in my nekc of the woods.
> Cool! If you ever need more hands-on help remember I'm around!
> Hari's advice to use a query window is excellent, by the way.
> Roy Harvey
> Beacon Falls, CT|||Hello,
Did you strip the backup into multiple files? Can you execute RESTORE
LABELONLY (see books online) and see how many files are associated with the
backup.
Thanks
Hari
"Mike" <Mike@.community.nospam> wrote in message
news:u8sG$y6pHHA.2156@.TK2MSFTNGP03.phx.gbl...
> Ok, I think I got it, but now I'm getting this message:
> 'the media set has 2 media families but only 1 are provided. All members
> must be provided.'
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:89eb631lpobifjd281tdpma4q57sct745h@.4ax.com...
>> On Tue, 5 Jun 2007 12:56:18 -0400, "Mike" <Mike@.community.nospam>
>> wrote:
>>I've tried the UNC path and I get 'network path not found' message
>> Perhaps it is an access rights problem. A partial test can be done
>> using xp_cmdshell:
>> EXEC master..xp_cmdshell 'dir \\servername\sharename\folder\*.*'
>>Beacon falls, ct, your in my nekc of the woods.
>> Cool! If you ever need more hands-on help remember I'm around!
>> Hari's advice to use a query window is excellent, by the way.
>> Roy Harvey
>> Beacon Falls, CT
>|||No, one file. and now I can't even backup the SQL 2000 database version to
do a restore from.
This is driving me nuts.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23Ov2b29pHHA.3264@.TK2MSFTNGP04.phx.gbl...
> Hello,
> Did you strip the backup into multiple files? Can you execute RESTORE
> LABELONLY (see books online) and see how many files are associated with
> the backup.
> Thanks
> Hari
> "Mike" <Mike@.community.nospam> wrote in message
> news:u8sG$y6pHHA.2156@.TK2MSFTNGP03.phx.gbl...
>> Ok, I think I got it, but now I'm getting this message:
>> 'the media set has 2 media families but only 1 are provided. All members
>> must be provided.'
>>
>> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
>> news:89eb631lpobifjd281tdpma4q57sct745h@.4ax.com...
>> On Tue, 5 Jun 2007 12:56:18 -0400, "Mike" <Mike@.community.nospam>
>> wrote:
>>I've tried the UNC path and I get 'network path not found' message
>> Perhaps it is an access rights problem. A partial test can be done
>> using xp_cmdshell:
>> EXEC master..xp_cmdshell 'dir \\servername\sharename\folder\*.*'
>>Beacon falls, ct, your in my nekc of the woods.
>> Cool! If you ever need more hands-on help remember I'm around!
>> Hari's advice to use a query window is excellent, by the way.
>> Roy Harvey
>> Beacon Falls, CT
>>
>

restore db daily with a job but BAK file is diff

i have to restore database from production bakup to development
everyday, there is a program which does this bakup and names the file
like PROD_20070523.BAK based on the date.
is there any way to run this with SQL agent and make sure everytime
the current file is backed up. can i rename the file to a different
name everyday automatically.
Also this file is a zip file so can i unzip this automatically.Yes you can run this using a job in SQL Agent and you can name the name using
a datetime stamp. I do all my backups this way. Then you can run code to
delete the older files. If you want to copy then to another server everyday,
I do this as a VBScript that is called by a SQL Agent job but you could use a
DOS copy command executed by xp_cmdshell as well. Many Zip programs have a
command line interface (not the one that comes with Windows XP but others) so
you could script this as well via xp_cmdshell.
"ashley.sql@.gmail.com" wrote:
> i have to restore database from production bakup to development
> everyday, there is a program which does this bakup and names the file
> like PROD_20070523.BAK based on the date.
> is there any way to run this with SQL agent and make sure everytime
> the current file is backed up. can i rename the file to a different
> name everyday automatically.
> Also this file is a zip file so can i unzip this automatically.
>

restore db daily with a job but BAK file is diff

i have to restore database from production bakup to development
everyday, there is a program which does this bakup and names the file
like PROD_20070523.BAK based on the date.
is there any way to run this with SQL agent and make sure everytime
the current file is backed up. can i rename the file to a different
name everyday automatically.
Also this file is a zip file so can i unzip this automatically.
Yes you can run this using a job in SQL Agent and you can name the name using
a datetime stamp. I do all my backups this way. Then you can run code to
delete the older files. If you want to copy then to another server everyday,
I do this as a VBScript that is called by a SQL Agent job but you could use a
DOS copy command executed by xp_cmdshell as well. Many Zip programs have a
command line interface (not the one that comes with Windows XP but others) so
you could script this as well via xp_cmdshell.
"ashley.sql@.gmail.com" wrote:

> i have to restore database from production bakup to development
> everyday, there is a program which does this bakup and names the file
> like PROD_20070523.BAK based on the date.
> is there any way to run this with SQL agent and make sure everytime
> the current file is backed up. can i rename the file to a different
> name everyday automatically.
> Also this file is a zip file so can i unzip this automatically.
>

restore db daily with a job but BAK file is diff

i have to restore database from production bakup to development
everyday, there is a program which does this bakup and names the file
like PROD_20070523.BAK based on the date.
is there any way to run this with SQL agent and make sure everytime
the current file is backed up. can i rename the file to a different
name everyday automatically.
Also this file is a zip file so can i unzip this automatically.Yes you can run this using a job in SQL Agent and you can name the name usin
g
a datetime stamp. I do all my backups this way. Then you can run code to
delete the older files. If you want to copy then to another server everyday
,
I do this as a VBScript that is called by a SQL Agent job but you could use
a
DOS copy command executed by xp_cmdshell as well. Many Zip programs have a
command line interface (not the one that comes with Windows XP but others) s
o
you could script this as well via xp_cmdshell.
"ashley.sql@.gmail.com" wrote:

> i have to restore database from production bakup to development
> everyday, there is a program which does this bakup and names the file
> like PROD_20070523.BAK based on the date.
> is there any way to run this with SQL agent and make sure everytime
> the current file is backed up. can i rename the file to a different
> name everyday automatically.
> Also this file is a zip file so can i unzip this automatically.
>

restore db

Hi, I have two file: db_mydb and db_mydb.BAK.
how to restore it to a new server? Thansk.RESTORE DATABASE db_mydb FROM db_mydb(assuming db_mydb is a BAK file)
--
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"js" <js@.someone.com> wrote in message
news:%23kbPhZZLGHA.668@.TK2MSFTNGP11.phx.gbl...
> Hi, I have two file: db_mydb and db_mydb.BAK.
> how to restore it to a new server? Thansk.
>

restore database without mdf or ldf file

Hi,

Is it possible to restore a database with only a .bak file??
When I try to restore the system comes up with 2 paths where probably the original .mdf and .ldf files existed, but on my system they don't exist. How can I avoid this problem? (it's not possible to get the ldf and mdf files anymore...)

Grtz
Carloscreate a new database like so...

CREATE DATABASE MyDATABASE

use sp_helpdb to get the filenames for the mdf and the ldf you just created

sp_helpdb MYDATABASE

use RESTORE FILELISTONLY to get the logical filenames of the mdf and ldf in the backup file like

RESTORE FILELISTONLY
FROM DISK = 'C:\Mybackup.bak'

Use RESTORE with MOVE like so

RESTORE DATABASE MyDatabase
FROM DISK = 'C:\Mybackup.bak'
WITH REPLACE,RECOVERY,
MOVE 'Logicalfile_Data' TO 'D:\Microsoft SQL Server\MSSQL\data\physicalfile.mdf' , MOVE 'Logicalfile_Log' TO 'D:\Microsoft SQL Server\MSSQL\data\physicalfile_log.LDF'|||Its quite possible to restore the db even if the underlying db is not preset. U need to provide the correct path where u want the mdf and ldf files to be created.|||Thnx for the quick reply!

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 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...
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 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 database from a bak file

Can I restore a sql server 2000 database to msde from a bak file ?
if so how ?
Without more details, from a command line, something like
osql -E (local)\<instancename> -Q"RESTORE DATABASE <dbname> FROM
DISK='c:\<file>.bak'"
Obviously, replace <data> with real values.
http://www.aspfaq.com/
(Reverse address to reply.)
"TJS" <nospam@.here.com> wrote in message
news:10qpf3idvcegna9@.corp.supernews.com...
> Can I restore a sql server 2000 database to msde from a bak file ?
> if so how ?
>
|||the following fails so what else might be required ?
there 's only one copy of msde running
osql -E (local) -Q "RESTORE DATABASE 'restored' FROM DISK='C:\export.bak'"
|||hi,
"TJS" <nospam@.here.com> ha scritto nel messaggio
news:10qtlu69djgjh1a@.corp.supernews.com
> the following fails so what else might be required ?
> there 's only one copy of msde running
>
> osql -E (local) -Q "RESTORE DATABASE 'restored' FROM
> DISK='C:\export.bak'"
try
c:\>osql.exe -E -S(local) -Q "RESTORE DATABASE [restored] FROM DISK =
'C:\export.bak'"
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||It says it cannot find "C:\>osql"
|||Wow.
Start typing at osql. The C:\> was supposed to look like the command
prompt.
http://www.aspfaq.com/
(Reverse address to reply.)
"TJS" <nospam@.here.com> wrote in message
news:10qutlo817krn2b@.corp.supernews.com...
> It says it cannot find "C:\>osql"
>
>
|||nothing happens, the dos window just flashes by
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uYYJlwK2EHA.3452@.TK2MSFTNGP14.phx.gbl...
> Wow.
> Start typing at osql. The C:\> was supposed to look like the command
> prompt.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "TJS" <nospam@.here.com> wrote in message
> news:10qutlo817krn2b@.corp.supernews.com...
>
|||Wow again.
OPEN a command prompt. (Start | Run | CMD, enter)
Type "osql..." without the quotes, and hit enter.
Now the dos window won't "flash by"...
http://www.aspfaq.com/
(Reverse address to reply.)
"TJS" <nospam@.here.com> wrote in message
news:10qv11fjl5u9864@.corp.supernews.com...
> nothing happens, the dos window just flashes by
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:uYYJlwK2EHA.3452@.TK2MSFTNGP14.phx.gbl...
>
|||that worked...
thanks to all

Restore database from a backup and specify a new log file name

Hi All,
I am trying to restore a database from a bak file and looking out for a way
if we can provide a new log file name while trying to restore. (I dont have
enought space on drive to accomodate the .ldf file existing in the bak file)
.
Thanks in advance..
ManojYou can use the move option. Check BOL for restore database for more
details.
Adi
On Feb 5, 9:55 pm, xyz <x...@.discussions.microsoft.com> wrote:
> Hi All,
> I am trying to restore a database from a bak file and looking out for a wa
y
> if we can provide a new log file name while trying to restore. (I dont hav
e
> enought space on drive to accomodate the .ldf file existing in the bak fil
e).
> Thanks in advance..
> Manoj|||Hello,
We can give new path and new file name along with RESTORE DATABASE command.
RESTORE DATABASE <dbname> FROM Disk='D:\backup\dbname.bak'
With Move 'logical_mdf_name' to 'D:\mssql\dbname.mdf',
Move 'Logical_ldf_name' to 'K:\mssql\dbname_log.ldf', stats=5
Phisical file name and path can be chnaged based on your requirement.
Thanks
Hari
"xyz" <xyz@.discussions.microsoft.com> wrote in message
news:8CB7CFD5-1462-4E01-90EA-3FAC07A43302@.microsoft.com...
> Hi All,
> I am trying to restore a database from a bak file and looking out for a
> way
> if we can provide a new log file name while trying to restore. (I dont
> have
> enought space on drive to accomodate the .ldf file existing in the bak
> file).
> Thanks in advance..
> Manoj

Restore database from a backup and specify a new log file name

Hi All,
I am trying to restore a database from a bak file and looking out for a way
if we can provide a new log file name while trying to restore. (I dont have
enought space on drive to accomodate the .ldf file existing in the bak file).
Thanks in advance..
Manoj
You can use the move option. Check BOL for restore database for more
details.
Adi
On Feb 5, 9:55 pm, xyz <x...@.discussions.microsoft.com> wrote:
> Hi All,
> I am trying to restore a database from a bak file and looking out for a way
> if we can provide a new log file name while trying to restore. (I dont have
> enought space on drive to accomodate the .ldf file existing in the bak file).
> Thanks in advance..
> Manoj
|||Hello,
We can give new path and new file name along with RESTORE DATABASE command.
RESTORE DATABASE <dbname> FROM Disk='D:\backup\dbname.bak'
With Move 'logical_mdf_name' to 'D:\mssql\dbname.mdf',
Move 'Logical_ldf_name' to 'K:\mssql\dbname_log.ldf', stats=5
Phisical file name and path can be chnaged based on your requirement.
Thanks
Hari
"xyz" <xyz@.discussions.microsoft.com> wrote in message
news:8CB7CFD5-1462-4E01-90EA-3FAC07A43302@.microsoft.com...
> Hi All,
> I am trying to restore a database from a bak file and looking out for a
> way
> if we can provide a new log file name while trying to restore. (I dont
> have
> enought space on drive to accomodate the .ldf file existing in the bak
> file).
> Thanks in advance..
> Manoj

Restore database from a backup and specify a new log file name

Hi All,
I am trying to restore a database from a bak file and looking out for a way
if we can provide a new log file name while trying to restore. (I dont have
enought space on drive to accomodate the .ldf file existing in the bak file).
Thanks in advance..
ManojYou can use the move option. Check BOL for restore database for more
details.
Adi
On Feb 5, 9:55 pm, xyz <x...@.discussions.microsoft.com> wrote:
> Hi All,
> I am trying to restore a database from a bak file and looking out for a way
> if we can provide a new log file name while trying to restore. (I dont have
> enought space on drive to accomodate the .ldf file existing in the bak file).
> Thanks in advance..
> Manoj|||Hello,
We can give new path and new file name along with RESTORE DATABASE command.
RESTORE DATABASE <dbname> FROM Disk='D:\backup\dbname.bak'
With Move 'logical_mdf_name' to 'D:\mssql\dbname.mdf',
Move 'Logical_ldf_name' to 'K:\mssql\dbname_log.ldf', stats=5
Phisical file name and path can be chnaged based on your requirement.
Thanks
Hari
"xyz" <xyz@.discussions.microsoft.com> wrote in message
news:8CB7CFD5-1462-4E01-90EA-3FAC07A43302@.microsoft.com...
> Hi All,
> I am trying to restore a database from a bak file and looking out for a
> way
> if we can provide a new log file name while trying to restore. (I dont
> have
> enought space on drive to accomodate the .ldf file existing in the bak
> file).
> Thanks in advance..
> Manojsql

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',