How do auto rename datafiles on a multiple restore?

Hello,
i have a SQL 2005 question.
I create multiple databases from a specific backup file.
I want the database to rename the data & log files automatically if
they already exist (due to the prior creation). I know it is possible,
I do not know how to configure it.
YanivYaniv,shalom
Take a look at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/15f8affd-8f39-4021-b092-0379fc6983da.htm
"ALTER DATABASE" topic in the BOL
<yaniv.harpaz@.gmail.com> wrote in message
news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> Hello,
> i have a SQL 2005 question.
> I create multiple databases from a specific backup file.
> I want the database to rename the data & log files automatically if
> they already exist (due to the prior creation). I know it is possible,
> I do not know how to configure it.
> Yaniv
>|||Hi Uri, thanks for the answer.
Could you gimme a focus (which option are you referring to)?
Shouldn't that be on the instance level?
Yaniv
Uri Dimant wrote:
> Yaniv,shalom
> Take a look at
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/15f8affd-8f39-4021-b092-0379fc6983da.htm
> "ALTER DATABASE" topic in the BOL
>
>
> <yaniv.harpaz@.gmail.com> wrote in message
> news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> > Hello,
> > i have a SQL 2005 question.
> > I create multiple databases from a specific backup file.
> >
> > I want the database to rename the data & log files automatically if
> > they already exist (due to the prior creation). I know it is possible,
> > I do not know how to configure it.
> >
> > Yaniv
> >|||<yaniv.harpaz@.gmail.com> wrote in message
news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> Hello,
> i have a SQL 2005 question.
> I create multiple databases from a specific backup file.
> I want the database to rename the data & log files automatically if
> they already exist (due to the prior creation). I know it is possible,
> I do not know how to configure it.
>
Check out RESTORE DATABASE ... WITH MOVE in BOL
David|||That's what I am using currently.
I want to be able automatically restore any file I get,
without manually rename each logical file into a new physical one.
David Browne wrote:
> <yaniv.harpaz@.gmail.com> wrote in message
> news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> > Hello,
> > i have a SQL 2005 question.
> > I create multiple databases from a specific backup file.
> >
> > I want the database to rename the data & log files automatically if
> > they already exist (due to the prior creation). I know it is possible,
> > I do not know how to configure it.
> >
>
> Check out RESTORE DATABASE ... WITH MOVE in BOL
>
> David|||<yaniv.harpaz@.gmail.com> wrote in message
news:1153673350.370810.24470@.s13g2000cwa.googlegroups.com...
> That's what I am using currently.
> I want to be able automatically restore any file I get,
> without manually rename each logical file into a new physical one.
Not clear then exactly what you're missing then.
Build the string for the file name and use dynamic SQL to execute it.
> David Browne wrote:
> > <yaniv.harpaz@.gmail.com> wrote in message
> > news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> > > Hello,
> > > i have a SQL 2005 question.
> > > I create multiple databases from a specific backup file.
> > >
> > > I want the database to rename the data & log files automatically if
> > > they already exist (due to the prior creation). I know it is possible,
> > > I do not know how to configure it.
> > >
> >
> >
> > Check out RESTORE DATABASE ... WITH MOVE in BOL
> >
> >
> > David
>|||I wanted to know if I can manage without it.
Restore and let the server generate the data & log files automatically
on the default locations.
Greg D. Moore (Strider) wrote:
> <yaniv.harpaz@.gmail.com> wrote in message
> news:1153673350.370810.24470@.s13g2000cwa.googlegroups.com...
> > That's what I am using currently.
> > I want to be able automatically restore any file I get,
> > without manually rename each logical file into a new physical one.
> Not clear then exactly what you're missing then.
> Build the string for the file name and use dynamic SQL to execute it.
>
> >
> > David Browne wrote:
> > > <yaniv.harpaz@.gmail.com> wrote in message
> > > news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> > > > Hello,
> > > > i have a SQL 2005 question.
> > > > I create multiple databases from a specific backup file.
> > > >
> > > > I want the database to rename the data & log files automatically if
> > > > they already exist (due to the prior creation). I know it is possible,
> > > > I do not know how to configure it.
> > > >
> > >
> > >
> > > Check out RESTORE DATABASE ... WITH MOVE in BOL
> > >
> > >
> > > David
> >|||<yaniv.harpaz@.gmail.com> wrote in message
news:1153739359.238308.167670@.m73g2000cwd.googlegroups.com...
> I wanted to know if I can manage without it.
> Restore and let the server generate the data & log files automatically
> on the default locations.
No. The restore process restores the database to the location indicated
within the backup.
The only way I'm aware of changing this is the WITH MOVE option.
> Greg D. Moore (Strider) wrote:
> > <yaniv.harpaz@.gmail.com> wrote in message
> > news:1153673350.370810.24470@.s13g2000cwa.googlegroups.com...
> > > That's what I am using currently.
> > > I want to be able automatically restore any file I get,
> > > without manually rename each logical file into a new physical one.
> >
> > Not clear then exactly what you're missing then.
> >
> > Build the string for the file name and use dynamic SQL to execute it.
> >
> >
> > >
> > > David Browne wrote:
> > > > <yaniv.harpaz@.gmail.com> wrote in message
> > > > news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> > > > > Hello,
> > > > > i have a SQL 2005 question.
> > > > > I create multiple databases from a specific backup file.
> > > > >
> > > > > I want the database to rename the data & log files automatically
if
> > > > > they already exist (due to the prior creation). I know it is
possible,
> > > > > I do not know how to configure it.
> > > > >
> > > >
> > > >
> > > > Check out RESTORE DATABASE ... WITH MOVE in BOL
> > > >
> > > >
> > > > David
> > >
>|||I am trying this on one of my servers and it is working and on the
other it doesn't. how come?
I am using the same backup file.
create database "George_1Month001"
go
restore database "George_1Month001" from
disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
go
-- Restore into a new database
create database "George_1Month002"
go
restore database "George_1Month002" from
disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
go
-- Restore into a new database
create database "George_1Month003"
go
restore database "George_1Month003" from
disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
go
Greg D. Moore (Strider) wrote:
> <yaniv.harpaz@.gmail.com> wrote in message
> news:1153739359.238308.167670@.m73g2000cwd.googlegroups.com...
> > I wanted to know if I can manage without it.
> > Restore and let the server generate the data & log files automatically
> > on the default locations.
> No. The restore process restores the database to the location indicated
> within the backup.
> The only way I'm aware of changing this is the WITH MOVE option.
>
> >
> > Greg D. Moore (Strider) wrote:
> > > <yaniv.harpaz@.gmail.com> wrote in message
> > > news:1153673350.370810.24470@.s13g2000cwa.googlegroups.com...
> > > > That's what I am using currently.
> > > > I want to be able automatically restore any file I get,
> > > > without manually rename each logical file into a new physical one.
> > >
> > > Not clear then exactly what you're missing then.
> > >
> > > Build the string for the file name and use dynamic SQL to execute it.
> > >
> > >
> > > >
> > > > David Browne wrote:
> > > > > <yaniv.harpaz@.gmail.com> wrote in message
> > > > > news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...
> > > > > > Hello,
> > > > > > i have a SQL 2005 question.
> > > > > > I create multiple databases from a specific backup file.
> > > > > >
> > > > > > I want the database to rename the data & log files automatically
> if
> > > > > > they already exist (due to the prior creation). I know it is
> possible,
> > > > > > I do not know how to configure it.
> > > > > >
> > > > >
> > > > >
> > > > > Check out RESTORE DATABASE ... WITH MOVE in BOL
> > > > >
> > > > >
> > > > > David
> > > >
> >|||yaniv.harpaz@.gmail.com wrote:
> I am trying this on one of my servers and it is working and on the
> other it doesn't. how come?
> I am using the same backup file.
> create database "George_1Month001"
> go
> restore database "George_1Month001" from
> disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> go
> -- Restore into a new database
> create database "George_1Month002"
> go
> restore database "George_1Month002" from
> disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> go
> -- Restore into a new database
> create database "George_1Month003"
> go
> restore database "George_1Month003" from
> disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> go
>
>
Could you post the error message(s) you get ?
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||for Example -
Msg 1834, Level 16, State 1, Line 1
The file 'c:\MSSQL\data\CleanDB_1001371.mdf' cannot be overwritten. It
is being used by database 'Cyprus_1Month002'.
Msg 3156, Level 16, State 4, Line 1
File 'CleanDB_1001371' cannot be restored to
'c:\MSSQL\data\CleanDB_1001371.mdf'. Use WITH MOVE to identify a valid
location for the file.
Msg 1834, Level 16, State 1, Line 1
The file 'c:\MSSQL\data\CleanDB_1001371_log.LDF' cannot be overwritten.
It is being used by database 'Cyprus_1Month002'.
Msg 3156, Level 16, State 4, Line 1
File 'CleanDB_1001371_log' cannot be restored to
'c:\MSSQL\data\CleanDB_1001371_log.LDF'. Use WITH MOVE to identify a
valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement.
Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Steen Persson (DK) wrote:
> yaniv.harpaz@.gmail.com wrote:
> > I am trying this on one of my servers and it is working and on the
> > other it doesn't. how come?
> > I am using the same backup file.
> >
> > create database "George_1Month001"
> > go
> >
> > restore database "George_1Month001" from
> > disk=3D'c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> > go
> >
> > -- Restore into a new database
> > create database "George_1Month002"
> > go
> >
> > restore database "George_1Month002" from
> > disk=3D'c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> > go
> >
> > -- Restore into a new database
> > create database "George_1Month003"
> > go
> >
> > restore database "George_1Month003" from
> > disk=3D'c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> > go
> >
> >
> >
> Could you post the error message(s) you get ?
>
> --
> Regards
> Steen Schl=FCter Persson
> Databaseadministrator / Systemadministrator|||yaniv.harpaz@.gmail.com wrote:
> for Example -
> Msg 1834, Level 16, State 1, Line 1
> The file 'c:\MSSQL\data\CleanDB_1001371.mdf' cannot be overwritten. It
> is being used by database 'Cyprus_1Month002'.
> Msg 3156, Level 16, State 4, Line 1
> File 'CleanDB_1001371' cannot be restored to
> 'c:\MSSQL\data\CleanDB_1001371.mdf'. Use WITH MOVE to identify a valid
> location for the file.
> Msg 1834, Level 16, State 1, Line 1
> The file 'c:\MSSQL\data\CleanDB_1001371_log.LDF' cannot be overwritten.
> It is being used by database 'Cyprus_1Month002'.
> Msg 3156, Level 16, State 4, Line 1
> File 'CleanDB_1001371_log' cannot be restored to
> 'c:\MSSQL\data\CleanDB_1001371_log.LDF'. Use WITH MOVE to identify a
> valid location for the file.
> Msg 3119, Level 16, State 1, Line 1
> Problems were identified while planning for the RESTORE statement.
> Previous messages provide details.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
But then you have the solution in the error message - use the MOVE
option. SQL server tries to restore to the original files but those
files are in use by another database.
Look up RESTORE command in Books On Line - that will give you all the
options and syntax you need.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||have a look at one of the prior posts I wrote.
I want it to take place automatically, without manually telling the SQL
Server which physical path the data files should have.
there is one server in which I can do it, but I do not know what is the
difference in the configuration. this one works:
create database "George_1Month001"
go
restore database "George_1Month001" from
disk=3D'c:\MSSQL\BackupFiles\Ver141-Sample' with replace
go
-- Restore into a new database
create database "George_1Month002"
go
restore database "George_1Month002" from
disk=3D'c:\MSSQL\BackupFiles\Ver141-Sample' with replace
go
Steen Persson (DK) wrote:
> yaniv.harpaz@.gmail.com wrote:
> > for Example -
> > Msg 1834, Level 16, State 1, Line 1
> > The file 'c:\MSSQL\data\CleanDB_1001371.mdf' cannot be overwritten. It
> > is being used by database 'Cyprus_1Month002'.
> > Msg 3156, Level 16, State 4, Line 1
> > File 'CleanDB_1001371' cannot be restored to
> > 'c:\MSSQL\data\CleanDB_1001371.mdf'. Use WITH MOVE to identify a valid
> > location for the file.
> > Msg 1834, Level 16, State 1, Line 1
> > The file 'c:\MSSQL\data\CleanDB_1001371_log.LDF' cannot be overwritten.
> > It is being used by database 'Cyprus_1Month002'.
> > Msg 3156, Level 16, State 4, Line 1
> > File 'CleanDB_1001371_log' cannot be restored to
> > 'c:\MSSQL\data\CleanDB_1001371_log.LDF'. Use WITH MOVE to identify a
> > valid location for the file.
> > Msg 3119, Level 16, State 1, Line 1
> > Problems were identified while planning for the RESTORE statement.
> > Previous messages provide details.
> > Msg 3013, Level 16, State 1, Line 1
> > RESTORE DATABASE is terminating abnormally.
> >
> >
> But then you have the solution in the error message - use the MOVE
> option. SQL server tries to restore to the original files but those
> files are in use by another database.
> Look up RESTORE command in Books On Line - that will give you all the
> options and syntax you need.
>
> --
> Regards
> Steen Schl=FCter Persson
> Databaseadministrator / Systemadministrator|||yaniv.harpaz@.gmail.com wrote:
> have a look at one of the prior posts I wrote.
> I want it to take place automatically, without manually telling the SQL
> Server which physical path the data files should have.
> there is one server in which I can do it, but I do not know what is the
> difference in the configuration. this one works:
> create database "George_1Month001"
> go
> restore database "George_1Month001" from
> disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> go
> -- Restore into a new database
> create database "George_1Month002"
> go
> restore database "George_1Month002" from
> disk='c:\MSSQL\BackupFiles\Ver141-Sample' with replace
> go
>
There must be a difference in these 2 servers. The errormessage you
posted, says that the mdf file can't be overwritten because it's used by
another database. Try to check if any other database on that server, is
using that mdf file. What about the disk an directory layout - is that
the same on both servers?
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator