How do I "apply" a log to a database? Urgent...

Hello!
The database is giving the torn page error.
I have yesterday's backup and today's log file ldf.
How do I apply the log file to database? We didn't backup today's log...
Thanks for your help and attention.
Jorge C.
rdc02271@.yahoo.com
You don't. If you want to recover to a point in time which is between database backups, then you
should have proper operating measures for that requirement. I.e., do log backups. This is what log
backups are for.
What is the recovery mode for your database? If it is simple, then the information needed to recover
is most probably not in the ldf file anymore. If it is full or bulk logged, then you can give any of
the log reader tools a try. I have listed some on my links page.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jorge C" <rdc02271@.yahoo.com> wrote in message news:uLIyLmbRFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hello!
> The database is giving the torn page error.
> I have yesterday's backup and today's log file ldf.
> How do I apply the log file to database? We didn't backup today's log...
> Thanks for your help and attention.
> Jorge C.
> rdc02271@.yahoo.com
>
>
|||Jorge,
- Backup current transaction log WITH NO_TRUNCATE
- Restore full backup using NORECOVERY clause
- Restore transaction log backup WITH RECOVERY
See "Restoring a Database to a Prior State" in BOL.
AMB
"Jorge C" wrote:

> Hello!
> The database is giving the torn page error.
> I have yesterday's backup and today's log file ldf.
> How do I apply the log file to database? We didn't backup today's log...
> Thanks for your help and attention.
> Jorge C.
> rdc02271@.yahoo.com
>
>
|||Oops. Perhaps I didn't read the OP close enough. Assuming that you do indeed perform regular
transaction log backups as well as regular database backups, see Alejandro's post. (I read it as you
didn't perform transaction log backups *at all*, which is a big difference from not performed
*today's* transaction log backup yet.
You might want to check out:
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
http://www.karaszi.com/SQLServer/inf...eral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:uDMBL2bRFHA.3880@.tk2msftngp13.phx.gbl...
> You don't. If you want to recover to a point in time which is between database backups, then you
> should have proper operating measures for that requirement. I.e., do log backups. This is what log
> backups are for.
> What is the recovery mode for your database? If it is simple, then the information needed to
> recover is most probably not in the ldf file anymore. If it is full or bulk logged, then you can
> give any of the log reader tools a try. I have listed some on my links page.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jorge C" <rdc02271@.yahoo.com> wrote in message news:uLIyLmbRFHA.204@.TK2MSFTNGP15.phx.gbl...
>

How do I "apply" a log to a database? Urgent...

Hello!
The database is giving the torn page error.
I have yesterday's backup and today's log file ldf.
How do I apply the log file to database? We didn't backup today's log...
Thanks for your help and attention.
Jorge C.
rdc02271@.yahoo.comYou don't. If you want to recover to a point in time which is between databa
se backups, then you
should have proper operating measures for that requirement. I.e., do log bac
kups. This is what log
backups are for.
What is the recovery mode for your database? If it is simple, then the infor
mation needed to recover
is most probably not in the ldf file anymore. If it is full or bulk logged,
then you can give any of
the log reader tools a try. I have listed some on my links page.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jorge C" <rdc02271@.yahoo.com> wrote in message news:uLIyLmbRFHA.204@.TK2MSFTNGP15.phx.gbl...

> Hello!
> The database is giving the torn page error.
> I have yesterday's backup and today's log file ldf.
> How do I apply the log file to database? We didn't backup today's log...
> Thanks for your help and attention.
> Jorge C.
> rdc02271@.yahoo.com
>
>|||Jorge,
- Backup current transaction log WITH NO_TRUNCATE
- Restore full backup using NORECOVERY clause
- Restore transaction log backup WITH RECOVERY
See "Restoring a Database to a Prior State" in BOL.
AMB
"Jorge C" wrote:

> Hello!
> The database is giving the torn page error.
> I have yesterday's backup and today's log file ldf.
> How do I apply the log file to database? We didn't backup today's log...
> Thanks for your help and attention.
> Jorge C.
> rdc02271@.yahoo.com
>
>|||Oops. Perhaps I didn't read the OP close enough. Assuming that you do indeed
perform regular
transaction log backups as well as regular database backups, see Alejandro's
post. (I read it as you
didn't perform transaction log backups *at all*, which is a big difference f
rom not performed
*today's* transaction log backup yet.
You might want to check out:
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
http://www.karaszi.com/SQLServer/in...veral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:uDMBL2bRFHA.3880@.tk2msftngp13.phx.gbl...
> You don't. If you want to recover to a point in time which is between data
base backups, then you
> should have proper operating measures for that requirement. I.e., do log b
ackups. This is what log
> backups are for.
> What is the recovery mode for your database? If it is simple, then the inf
ormation needed to
> recover is most probably not in the ldf file anymore. If it is full or bul
k logged, then you can
> give any of the log reader tools a try. I have listed some on my links pag
e.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jorge C" <rdc02271@.yahoo.com> wrote in message news:uLIyLmbRFHA.204@.TK2MS
FTNGP15.phx.gbl...
>sql

How do I "apply" a log to a database? Urgent...

Hello!
The database is giving the torn page error.
I have yesterday's backup and today's log file ldf.
How do I apply the log file to database? We didn't backup today's log...
Thanks for your help and attention.
Jorge C.
rdc02271@.yahoo.comYou don't. If you want to recover to a point in time which is between database backups, then you
should have proper operating measures for that requirement. I.e., do log backups. This is what log
backups are for.
What is the recovery mode for your database? If it is simple, then the information needed to recover
is most probably not in the ldf file anymore. If it is full or bulk logged, then you can give any of
the log reader tools a try. I have listed some on my links page.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jorge C" <rdc02271@.yahoo.com> wrote in message news:uLIyLmbRFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hello!
> The database is giving the torn page error.
> I have yesterday's backup and today's log file ldf.
> How do I apply the log file to database? We didn't backup today's log...
> Thanks for your help and attention.
> Jorge C.
> rdc02271@.yahoo.com
>
>|||Jorge,
- Backup current transaction log WITH NO_TRUNCATE
- Restore full backup using NORECOVERY clause
- Restore transaction log backup WITH RECOVERY
See "Restoring a Database to a Prior State" in BOL.
AMB
"Jorge C" wrote:
> Hello!
> The database is giving the torn page error.
> I have yesterday's backup and today's log file ldf.
> How do I apply the log file to database? We didn't backup today's log...
> Thanks for your help and attention.
> Jorge C.
> rdc02271@.yahoo.com
>
>|||Oops. Perhaps I didn't read the OP close enough. Assuming that you do indeed perform regular
transaction log backups as well as regular database backups, see Alejandro's post. (I read it as you
didn't perform transaction log backups *at all*, which is a big difference from not performed
*today's* transaction log backup yet.
You might want to check out:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:uDMBL2bRFHA.3880@.tk2msftngp13.phx.gbl...
> You don't. If you want to recover to a point in time which is between database backups, then you
> should have proper operating measures for that requirement. I.e., do log backups. This is what log
> backups are for.
> What is the recovery mode for your database? If it is simple, then the information needed to
> recover is most probably not in the ldf file anymore. If it is full or bulk logged, then you can
> give any of the log reader tools a try. I have listed some on my links page.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jorge C" <rdc02271@.yahoo.com> wrote in message news:uLIyLmbRFHA.204@.TK2MSFTNGP15.phx.gbl...
>> Hello!
>> The database is giving the torn page error.
>> I have yesterday's backup and today's log file ldf.
>> How do I apply the log file to database? We didn't backup today's log...
>> Thanks for your help and attention.
>> Jorge C.
>> rdc02271@.yahoo.com
>>
>

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:[vbcol=seagreen]
> Yaniv,shalom
> Take a look at
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/15f8affd-8f39-4021-b09
2-0379fc6983da.htm
> "ALTER DATABASE" topic in the BOL
>
>
> <yaniv.harpaz@.gmail.com> wrote in message
> news:1153657523.137164.319050@.i42g2000cwa.googlegroups.com...|||<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...
>
> 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:
>|||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:[vbcol=seagreen]
> <yaniv.harpaz@.gmail.com> wrote in message
> news:1153673350.370810.24470@.s13g2000cwa.googlegroups.com...
> Not clear then exactly what you're missing then.
> Build the string for the file name and use dynamic SQL to execute it.
>|||<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:
if[vbcol=seagreen]
possible,[vbcol=seagreen]
>|||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:[vbcol=seagreen]
> <yaniv.harpaz@.gmail.com> wrote in message
> news:1153739359.238308.167670@.m73g2000cwd.googlegroups.com...
> 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.
>
> if
> possible,|||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 Schlter Persson
Databaseadministrator / Systemadministrator

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

How could I restore a backup automatically?

How could I restore a backup automatically?Hi,
You can schedule the below TSQL command using SQL Agent --Jobs using
Enterprise manager.
Restore database <dbname> from disk='c:\backup\dbname.bak'
(replace the directory , file name and database name with yours)
Use WITH MOVE Option incase if the existing database is already available in
the server.
Thanks
Hari
MCDBA
"Xavi" <anonymous@.discussions.microsoft.com> wrote in message
news:AFEDED0D-C7B5-48ED-9EC0-22AC8B77DB71@.microsoft.com...
>|||Xavi,
the syntax for restoring a database in under RESTORE in BOL. To make it
automatic - hmm. In what sense? If you want it to occur automatically at a
specific time, you could schedule a job to run the RESTORE script. If it is
as a response to an 'action query' - insert, delete, update then the RESTORE
script would be in a trigger. Without knowing more about your requirements
it's difficult to be specific. You might also be interested in log shipping
in which a warm stand by server is maintained by transporting backed up logs
and restoring them on another server.
HTH,
Paul Ibison

How could I restore a backup automatically?

How could I restore a backup automatically?Hi,
You can schedule the below TSQL command using SQL Agent --Jobs using
Enterprise manager.
Restore database <dbname> from disk='c:\backup\dbname.bak'
(replace the directory , file name and database name with yours)
Use WITH MOVE Option incase if the existing database is already available in
the server.
Thanks
Hari
MCDBA
"Xavi" <anonymous@.discussions.microsoft.com> wrote in message
news:AFEDED0D-C7B5-48ED-9EC0-22AC8B77DB71@.microsoft.com...
>|||Xavi,
the syntax for restoring a database in under RESTORE in BOL. To make it
automatic - hmm. In what sense? If you want it to occur automatically at a
specific time, you could schedule a job to run the RESTORE script. If it is
as a response to an 'action query' - insert, delete, update then the RESTORE
script would be in a trigger. Without knowing more about your requirements
it's difficult to be specific. You might also be interested in log shipping
in which a warm stand by server is maintained by transporting backed up logs
and restoring them on another server.
HTH,
Paul Ibison

How could I restore a backup automatically?

How could I restore a backup automatically?Hi,
You can schedule the below TSQL command using SQL Agent --Jobs using
Enterprise manager.
Restore database <dbname> from disk='c:\backup\dbname.bak'
(replace the directory , file name and database name with yours)
Use WITH MOVE Option incase if the existing database is already available in
the server.
Thanks
Hari
MCDBA
"Xavi" <anonymous@.discussions.microsoft.com> wrote in message
news:AFEDED0D-C7B5-48ED-9EC0-22AC8B77DB71@.microsoft.com...
>
|||Xavi,
the syntax for restoring a database in under RESTORE in BOL. To make it
automatic - hmm. In what sense? If you want it to occur automatically at a
specific time, you could schedule a job to run the RESTORE script. If it is
as a response to an 'action query' - insert, delete, update then the RESTORE
script would be in a trigger. Without knowing more about your requirements
it's difficult to be specific. You might also be interested in log shipping
in which a warm stand by server is maintained by transporting backed up logs
and restoring them on another server.
HTH,
Paul Ibison

How check database properties?

Hello, Everyone:
There are some databases in a SQL Server. How to check,
1. User database numbers
2. Backup plan
3. Configuration
4. User database size
5. Nightly process
Thanks a lot
ZYTsp_helpdb <DBname> should give you a broad overview of the database.

For the rest of the stuff you can use Enterprise Manager.sql

How can you tell the date of the last full database backup?

How can you tell the date of the last full database backup?
Is there a system table that can be queried?
Robert Alexander
Robert.Alexander@.cca-audit.com
SELECT TOP 3 *
FROM msdb..backupset
WHERE database_name=DB_NAME()
ORDER BY backup_finish_date DESC
http://www.aspfaq.com/
(Reverse address to reply.)
"Robert Alexander" <robert.alexander@.cca-audit.com> wrote in message
news:ev6oXZNyEHA.3376@.TK2MSFTNGP12.phx.gbl...
> How can you tell the date of the last full database backup?
> Is there a system table that can be queried?
> Robert Alexander
> Robert.Alexander@.cca-audit.com
>
>
|||Close. You have to use the type column if there are log and/or differential
backups from that database. 'D' for Database, 'I' for Differential, 'L' for
Log. So that gives:
SELECT TOP 3 *
FROM msdb..backupset
WHERE database_name=DB_NAME()
AND [TYPE] = 'D'
ORDER BY backup_finish_date DESC
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e36MweNyEHA.804@.TK2MSFTNGP12.phx.gbl...
> SELECT TOP 3 *
> FROM msdb..backupset
> WHERE database_name=DB_NAME()
> ORDER BY backup_finish_date DESC
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Robert Alexander" <robert.alexander@.cca-audit.com> wrote in message
> news:ev6oXZNyEHA.3376@.TK2MSFTNGP12.phx.gbl...
>

How can you tell the date of the last full database backup?

How can you tell the date of the last full database backup?
Is there a system table that can be queried?
Robert Alexander
Robert.Alexander@.cca-audit.comSELECT TOP 3 *
FROM msdb..backupset
WHERE database_name=DB_NAME()
ORDER BY backup_finish_date DESC
http://www.aspfaq.com/
(Reverse address to reply.)
"Robert Alexander" <robert.alexander@.cca-audit.com> wrote in message
news:ev6oXZNyEHA.3376@.TK2MSFTNGP12.phx.gbl...
> How can you tell the date of the last full database backup?
> Is there a system table that can be queried?
> Robert Alexander
> Robert.Alexander@.cca-audit.com
>
>|||Close. You have to use the type column if there are log and/or differential
backups from that database. 'D' for Database, 'I' for Differential, 'L' for
Log. So that gives:
SELECT TOP 3 *
FROM msdb..backupset
WHERE database_name=DB_NAME()
AND [TYPE] = 'D'
ORDER BY backup_finish_date DESC
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e36MweNyEHA.804@.TK2MSFTNGP12.phx.gbl...
> SELECT TOP 3 *
> FROM msdb..backupset
> WHERE database_name=DB_NAME()
> ORDER BY backup_finish_date DESC
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Robert Alexander" <robert.alexander@.cca-audit.com> wrote in message
> news:ev6oXZNyEHA.3376@.TK2MSFTNGP12.phx.gbl...
>

How can you tell the date of the last full database backup?

How can you tell the date of the last full database backup?
Is there a system table that can be queried?
Robert Alexander
Robert.Alexander@.cca-audit.comSELECT TOP 3 *
FROM msdb..backupset
WHERE database_name=DB_NAME()
ORDER BY backup_finish_date DESC
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Robert Alexander" <robert.alexander@.cca-audit.com> wrote in message
news:ev6oXZNyEHA.3376@.TK2MSFTNGP12.phx.gbl...
> How can you tell the date of the last full database backup?
> Is there a system table that can be queried?
> Robert Alexander
> Robert.Alexander@.cca-audit.com
>
>|||Close. You have to use the type column if there are log and/or differential
backups from that database. 'D' for Database, 'I' for Differential, 'L' for
Log. So that gives:
SELECT TOP 3 *
FROM msdb..backupset
WHERE database_name=DB_NAME()
AND [TYPE] = 'D'
ORDER BY backup_finish_date DESC
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e36MweNyEHA.804@.TK2MSFTNGP12.phx.gbl...
> SELECT TOP 3 *
> FROM msdb..backupset
> WHERE database_name=DB_NAME()
> ORDER BY backup_finish_date DESC
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Robert Alexander" <robert.alexander@.cca-audit.com> wrote in message
> news:ev6oXZNyEHA.3376@.TK2MSFTNGP12.phx.gbl...
> > How can you tell the date of the last full database backup?
> >
> > Is there a system table that can be queried?
> >
> > Robert Alexander
> > Robert.Alexander@.cca-audit.com
> >
> >
> >
> >
>

How Can We Restore Database To New Database with Applying Diff. and Log Backup?

Hi,
I have trouble restoring database from backup device set. The device is valid. I want to restore the database to a new database. This step is succeed. The problem is that I only restore for full backup only, but I haven't applied differential and
transactional backup. I also want this set of backup move to new database. If I choose diff backup in restore dialog, then its only give one choice. The error message is I have to restore with norevocery or stanby opotion. I've already try from T-SQL, but
its still fail. My questions are:
1. How do I restore backup device to new database with applying diff and log backup?
2. Can I restore the diif and log backup to new location? (with full backup first)
Thanks.
Rahmadi Aji
When you restore a database and want to restore transaction logs right click on the database in question and in the options tab choose Leave database nonoperational but able to restore additional transaction logs. You can then apply transaction logs.
If you want to restore a backup to a different database than the original create the new database and then choose restore with the 'Force restore over existing database' option ticked.
|||>>If you want to restore a backup to a different database than the original create the new database and then choose[vbcol=seagreen]
In this case, I want to restore database to a new database in the same original database. And I already have tried all of
the alternatives including 'Force restore.... Because I want to restore it on different physical name. None are succeceed.
If I use same physical name it will overwrite the original.
|||<< In this case, I want to restore database to a new database in the same original database.>>
Above seems like a contradiction to me. If you post the RESTORE commands you used for all RESTORE commands
(RESTORE DATABASE ..., REDORE LOG ...) it is much easier to spot what might be the problem.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Rahmadi Aji" <anonymous@.discussions.microsoft.com> wrote in message
news:B513D972-EDA8-4823-AC58-ED253E06AC0B@.microsoft.com...[vbcol=seagreen]
choose
> In this case, I want to restore database to a new database in the same original database. And I already have
tried all of
> the alternatives including 'Force restore.... Because I want to restore it on different physical name. None
are succeceed.
> If I use same physical name it will overwrite the original.
|||If you want to restore a Database (from a backup) to a new Database, the 'logical' data and transaction log filenames must be the same and the file locations can be different.
This is how I would do it:
If you want to restore just a full backup to a new database, create the new Database with the SAME logical "Filename" for the data and transaction logs as the original but a different "location". If using EM, right click on the new database and choose
all tasks then restore database. Choose from device, add device and browse out to find the .bak file you want to restore. Back on the options tab, Choose the 'force restore over existing' and specify your new file locations for both the data and tra
nsaction logs under 'move to physical file name' - leave the logical filenames as is - those should be the same as your original database. If you want just to restore a full backup, leave the 'leave database operational' box checked. If you also wan
t to restore transaction log backups, choose one of the other options. if you are restoring more than one transaction log backup, on the LAST transaction log restore you should leave the 'leave database operational' box checked.
Knowledge base article 31456 also explains how to do this using query analyzer as well.
|||I mean in the same machine(server). I want to restore to a new database.
-- Tibor Karaszi wrote: --
<< In this case, I want to restore database to a new database in the same original database.>>
Above seems like a contradiction to me. If you post the RESTORE commands you used for all RESTORE commands
(RESTORE DATABASE ..., REDORE LOG ...) it is much easier to spot what might be the problem.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Rahmadi Aji" <anonymous@.discussions.microsoft.com> wrote in message
news:B513D972-EDA8-4823-AC58-ED253E06AC0B@.microsoft.com...[vbcol=seagreen]
choose[vbcol=seagreen]
tried all of
> the alternatives including 'Force restore.... Because I want to restore it on different physical name. None
are succeceed.
> If I use same physical name it will overwrite the original.
|||You're right about that, but I want to restore with differential and log backup. I have tried you're advice, it didn't work. I restore full backup ('leave database nonoperational') and then I add differential backup ('leave database operational'). When I
add diff. backup I use 'force restore over existing' and 'move to physical file name' with same logical name, It doesn't work.
-- jpotucek wrote: --
If you want to restore a Database (from a backup) to a new Database, the 'logical' data and transaction log filenames must be the same and the file locations can be different.
This is how I would do it:
If you want to restore just a full backup to a new database, create the new Database with the SAME logical "Filename" for the data and transaction logs as the original but a different "location". If using EM, right click on the new database and c
hoose all tasks then restore database. Choose from device, add device and browse out to find the .bak file you want to restore. Back on the options tab, Choose the 'force restore over existing' and specify your new file locations for both the data an
d transaction logs under 'move to physical file name' - leave the logical filenames as is - those should be the same as your original database. If you want just to restore a full backup, leave the 'leave database operational' box checked. If you als
o want to restore transaction log backups, choose one of the other options. if you are restoring more than one transaction log backup, on the LAST transaction log restore you should leave the 'leave database operational' box checked.
Knowledge base article 31456 also explains how to do this using query analyzer as well.
|||Again, If you post the RESTORE commands you used for all RESTORE commands (RESTORE DATABASE ..., RESTORE LOG
...) it is much easier to spot what might be the problem.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Rahmadi Aji" <anonymous@.discussions.microsoft.com> wrote in message
news:6EE5847B-C35E-42E4-9AD4-93C799F70631@.microsoft.com...
> I mean in the same machine(server). I want to restore to a new database.
> -- Tibor Karaszi wrote: --
> << In this case, I want to restore database to a new database in the same original database.>>
> Above seems like a contradiction to me. If you post the RESTORE commands you used for all RESTORE
commands[vbcol=seagreen]
> (RESTORE DATABASE ..., REDORE LOG ...) it is much easier to spot what might be the problem.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Rahmadi Aji" <anonymous@.discussions.microsoft.com> wrote in message
> news:B513D972-EDA8-4823-AC58-ED253E06AC0B@.microsoft.com...
and then[vbcol=seagreen]
> choose
already have[vbcol=seagreen]
> tried all of
name. None
> are succeceed.
>
>

How Can We Restore Database To New Database with Applying Diff. and Log Backup?

Hi,
I have trouble restoring database from backup device set. The device is vali
d. I want to restore the database to a new database. This step is succeed. T
he problem is that I only restore for full backup only, but I haven't applie
d differential and
transactional backup. I also want this set of backup move to new database. I
f I choose diff backup in restore dialog, then its only give one choice. The
error message is I have to restore with norevocery or stanby opotion. I've
already try from T-SQL, but
its still fail. My questions are:
1. How do I restore backup device to new database with applying diff and log
backup?
2. Can I restore the diif and log backup to new location? (with full backup
first)
Thanks.
Rahmadi AjiWhen you restore a database and want to restore transaction logs right click
on the database in question and in the options tab choose Leave database no
noperational but able to restore additional transaction logs. You can then a
pply transaction logs.
If you want to restore a backup to a different database than the original cr
eate the new database and then choose restore with the 'Force restore over e
xisting database' option ticked.|||>>If you want to restore a backup to a different database than the original create the new
database and then choose
In this case, I want to restore database to a new database in the same origi
nal database. And I already have tried all of
the alternatives including 'Force restore.... Because I want to restore it
on different physical name. None are succeceed.
If I use same physical name it will overwrite the original.|||<< In this case, I want to restore database to a new database in the same or
iginal database.>>
Above seems like a contradiction to me. If you post the RESTORE commands you
used for all RESTORE commands
(RESTORE DATABASE ..., REDORE LOG ...) it is much easier to spot what might
be the problem.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Rahmadi Aji" <anonymous@.discussions.microsoft.com> wrote in message
news:B513D972-EDA8-4823-AC58-ED253E06AC0B@.microsoft.com...
choose
> In this case, I want to restore database to a new database in the same original da
tabase. And I already have
tried all of
> the alternatives including 'Force restore.... Because I want to restore it on dif
ferent physical name. None
are succeceed.
> If I use same physical name it will overwrite the original.|||If you want to restore a Database (from a backup) to a new Database, the 'lo
gical' data and transaction log filenames must be the same and the file loca
tions can be different.
This is how I would do it:
If you want to restore just a full backup to a new database, create the new
Database with the SAME logical "Filename" for the data and transaction logs
as the original but a different "location". If using EM, right click on
the new database and choose
all tasks then restore database. Choose from device, add device and brows
e out to find the .bak file you want to restore. Back on the options tab, C
hoose the 'force restore over existing' and specify your new file locations
for both the data and tra
nsaction logs under 'move to physical file name' - leave the logical filena
mes as is - those should be the same as your original database. If you w
ant just to restore a full backup, leave the 'leave database operational' bo
x checked. If you also wan
t to restore transaction log backups, choose one of the other options. if y
ou are restoring more than one transaction log backup, on the LAST transacti
on log restore you should leave the 'leave database operational' box checked
.
Knowledge base article 31456 also explains how to do this using query analyz
er as well.|||I mean in the same machine(server). I want to restore to a new database.
-- Tibor Karaszi wrote: --
<< In this case, I want to restore database to a new database in the same or
iginal database.>>
Above seems like a contradiction to me. If you post the RESTORE commands you
used for all RESTORE commands
(RESTORE DATABASE ..., REDORE LOG ...) it is much easier to spot what might
be the problem.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Rahmadi Aji" <anonymous@.discussions.microsoft.com> wrote in message
news:B513D972-EDA8-4823-AC58-ED253E06AC0B@.microsoft.com...
choose[vbcol=seagreen]
tried all of[vbcol=seagreen]
> the alternatives including 'Force restore.... Because I want to restore it on dif
ferent physical name. None
are succeceed.
> If I use same physical name it will overwrite the original.|||You're right about that, but I want to restore with differential and log bac
kup. I have tried you're advice, it didn't work. I restore full backup ('lea
ve database nonoperational') and then I add differential backup ('leave data
base operational'). When I
add diff. backup I use 'force restore over existing' and 'move to physical
file name' with same logical name, It doesn't work.
-- jpotucek wrote: --
If you want to restore a Database (from a backup) to a new Database, the 'lo
gical' data and transaction log filenames must be the same and the file loca
tions can be different.
This is how I would do it:
If you want to restore just a full backup to a new database, create the new
Database with the SAME logical "Filename" for the data and transaction logs
as the original but a different "location". If using EM, right click on
the new database and c
hoose all tasks then restore database. Choose from device, add device and
browse out to find the .bak file you want to restore. Back on the options
tab, Choose the 'force restore over existing' and specify your new file loc
ations for both the data an
d transaction logs under 'move to physical file name' - leave the logical f
ilenames as is - those should be the same as your original database. If
you want just to restore a full backup, leave the 'leave database operationa
l' box checked. If you als
o want to restore transaction log backups, choose one of the other options.
if you are restoring more than one transaction log backup, on the LAST tran
saction log restore you should leave the 'leave database operational' box ch
ecked.
Knowledge base article 31456 also explains how to do this using query analyz
er as well.|||Again, If you post the RESTORE commands you used for all RESTORE commands (
RESTORE DATABASE ..., RESTORE LOG
...) it is much easier to spot what might be the problem.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Rahmadi Aji" <anonymous@.discussions.microsoft.com> wrote in message
news:6EE5847B-C35E-42E4-9AD4-93C799F70631@.microsoft.com...
> I mean in the same machine(server). I want to restore to a new database.
> -- Tibor Karaszi wrote: --
> << In this case, I want to restore database to a new database in the
same original database.>>
> Above seems like a contradiction to me. If you post the RESTORE commands you
used for all RESTORE
commands
> (RESTORE DATABASE ..., REDORE LOG ...) it is much easier to spot what
might be the problem.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Rahmadi Aji" <anonymous@.discussions.microsoft.com> wrote in message
> news:B513D972-EDA8-4823-AC58-ED253E06AC0B@.microsoft.com...
and then[vbcol=seagreen]
> choose
already have[vbcol=seagreen]
> tried all of
name. None[vbcol=seagreen]
> are succeceed.
>
>

How Can We Restore Database To New Database with Applying Diff. and Log Backup?

Hi
I have trouble restoring database from backup device set. The device is valid. I want to restore the database to a new database. This step is succeed. The problem is that I only restore for full backup only, but I haven't applied differential an
transactional backup. I also want this set of backup move to new database. If I choose diff backup in restore dialog, then its only give one choice. The error message is I have to restore with norevocery or stanby opotion. I've already try from T-SQL, but its still fail. My questions are
1. How do I restore backup device to new database with applying diff and log backup
2. Can I restore the diif and log backup to new location? (with full backup first)
Thanks
Rahmadi AjiWhen you restore a database and want to restore transaction logs right click on the database in question and in the options tab choose Leave database nonoperational but able to restore additional transaction logs. You can then apply transaction logs
If you want to restore a backup to a different database than the original create the new database and then choose restore with the 'Force restore over existing database' option ticked.|||>>If you want to restore a backup to a different database than the original create the new database and then choose
>> restore with the 'Force restore over existing database' option ticked
In this case, I want to restore database to a new database in the same original database. And I already have tried all o
the alternatives including 'Force restore.... Because I want to restore it on different physical name. None are succeceed
If I use same physical name it will overwrite the original.|||<< In this case, I want to restore database to a new database in the same original database.>>
Above seems like a contradiction to me. If you post the RESTORE commands you used for all RESTORE commands
(RESTORE DATABASE ..., REDORE LOG ...) it is much easier to spot what might be the problem.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Rahmadi Aji" <anonymous@.discussions.microsoft.com> wrote in message
news:B513D972-EDA8-4823-AC58-ED253E06AC0B@.microsoft.com...
> >>If you want to restore a backup to a different database than the original create the new database and then
choose
> >> restore with the 'Force restore over existing database' option ticked.
> In this case, I want to restore database to a new database in the same original database. And I already have
tried all of
> the alternatives including 'Force restore.... Because I want to restore it on different physical name. None
are succeceed.
> If I use same physical name it will overwrite the original.|||If you want to restore a Database (from a backup) to a new Database, the 'logical' data and transaction log filenames must be the same and the file locations can be different.
This is how I would do it:
If you want to restore just a full backup to a new database, create the new Database with the SAME logical "Filename" for the data and transaction logs as the original but a different "location". If using EM, right click on the new database and choose all tasks then restore database. Choose from device, add device and browse out to find the .bak file you want to restore. Back on the options tab, Choose the 'force restore over existing' and specify your new file locations for both the data and transaction logs under 'move to physical file name' - leave the logical filenames as is - those should be the same as your original database. If you want just to restore a full backup, leave the 'leave database operational' box checked. If you also want to restore transaction log backups, choose one of the other options. if you are restoring more than one transaction log backup, on the LAST transaction log restore you should leave the 'leave database operational' box checked
Knowledge base article 31456 also explains how to do this using query analyzer as well.|||I mean in the same machine(server). I want to restore to a new database.
-- Tibor Karaszi wrote: --
<< In this case, I want to restore database to a new database in the same original database.>>
Above seems like a contradiction to me. If you post the RESTORE commands you used for all RESTORE commands
(RESTORE DATABASE ..., REDORE LOG ...) it is much easier to spot what might be the problem.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Rahmadi Aji" <anonymous@.discussions.microsoft.com> wrote in message
news:B513D972-EDA8-4823-AC58-ED253E06AC0B@.microsoft.com...
>>If you want to restore a backup to a different database than the original create the new database and then
choose
>> restore with the 'Force restore over existing database' option ticked.
>> In this case, I want to restore database to a new database in the same original database. And I already have
tried all of
> the alternatives including 'Force restore.... Because I want to restore it on different physical name. None
are succeceed.
> If I use same physical name it will overwrite the original.|||You're right about that, but I want to restore with differential and log backup. I have tried you're advice, it didn't work. I restore full backup ('leave database nonoperational') and then I add differential backup ('leave database operational'). When I add diff. backup I use 'force restore over existing' and 'move to physical file name' with same logical name, It doesn't work
-- jpotucek wrote: --
If you want to restore a Database (from a backup) to a new Database, the 'logical' data and transaction log filenames must be the same and the file locations can be different.
This is how I would do it:
If you want to restore just a full backup to a new database, create the new Database with the SAME logical "Filename" for the data and transaction logs as the original but a different "location". If using EM, right click on the new database and choose all tasks then restore database. Choose from device, add device and browse out to find the .bak file you want to restore. Back on the options tab, Choose the 'force restore over existing' and specify your new file locations for both the data and transaction logs under 'move to physical file name' - leave the logical filenames as is - those should be the same as your original database. If you want just to restore a full backup, leave the 'leave database operational' box checked. If you also want to restore transaction log backups, choose one of the other options. if you are restoring more than one transaction log backup, on the LAST transaction log restore you should leave the 'leave database operational' box checked
Knowledge base article 31456 also explains how to do this using query analyzer as well.|||Again, If you post the RESTORE commands you used for all RESTORE commands (RESTORE DATABASE ..., RESTORE LOG
...) it is much easier to spot what might be the problem.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Rahmadi Aji" <anonymous@.discussions.microsoft.com> wrote in message
news:6EE5847B-C35E-42E4-9AD4-93C799F70631@.microsoft.com...
> I mean in the same machine(server). I want to restore to a new database.
> -- Tibor Karaszi wrote: --
> << In this case, I want to restore database to a new database in the same original database.>>
> Above seems like a contradiction to me. If you post the RESTORE commands you used for all RESTORE
commands
> (RESTORE DATABASE ..., REDORE LOG ...) it is much easier to spot what might be the problem.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Rahmadi Aji" <anonymous@.discussions.microsoft.com> wrote in message
> news:B513D972-EDA8-4823-AC58-ED253E06AC0B@.microsoft.com...
> >>If you want to restore a backup to a different database than the original create the new database
and then
> choose
> >> restore with the 'Force restore over existing database' option ticked.
> >> In this case, I want to restore database to a new database in the same original database. And I
already have
> tried all of
> > the alternatives including 'Force restore.... Because I want to restore it on different physical
name. None
> are succeceed.
> > If I use same physical name it will overwrite the original.
>
>

How can we handle terabyte of data?

Hi All
I have some questions to ask, in case some company have terabyte of data in the production server which online 24X7.
1)How can they backup database? Do they use backup database complete into disk? or Tape? How can they verify the backup? How frequent that needs?
2)How can they handle disaster recovery? Is Log shipping and Clustering is good enough?
3)In case need to change some stored procedures or trigger. Do they upload to test in DEV Boxes? In case DEV box have only 80 GB? What is the criteria that should be consider?
4)In case they have already tested in DEV box and want to implement in the online production, How can they minimize the problem if it does occur?
I am sorry to ask a lots of questions. If you can answers item by item or suggest the good resources, this will be the great for me.
Thanks you in advances,
James Jarupan
I'm hoping you can partition your data in the case below.
We did and utilized parititioned views heavily. Breaking apart tables/dbs
when they grew beyond 250 GB so instead of 1 TB DB there were 4 250 GB DBs.
So if 1 DB went down it could be restored in smaller timeframe than 1DB of
TB size. Besides you can backup much faster.
Later we got SAN solutions and size of DB ceased to be so much of an issue
for backup purposes.
Optimizing procs for production is always difficult in such cases, before
releasing the proc to live production box the DBA again performed the actual
execution plan and the estimated execution plan for optimization. You would
still need to follow the Dev- Test- Production box test methodology to
capture most issues.
"James Jarupan" <James@.vincosoft.com> wrote in message
news:5F71E190-F3ED-40E9-8DBF-EC1091D196F2@.microsoft.com...
> Hi All
> I have some questions to ask, in case some company have terabyte of data
in the production server which online 24X7.
> 1)How can they backup database? Do they use backup database complete into
disk? or Tape? How can they verify the backup? How frequent that needs?
> 2)How can they handle disaster recovery? Is Log shipping and Clustering is
good enough?
> 3)In case need to change some stored procedures or trigger. Do they upload
to test in DEV Boxes? In case DEV box have only 80 GB? What is the criteria
that should be consider?
> 4)In case they have already tested in DEV box and want to implement in the
online production, How can they minimize the problem if it does occur?
> I am sorry to ask a lots of questions. If you can answers item by item
or suggest the good resources, this will be the great for me.
> Thanks you in advances,
> James Jarupan
>
|||Thanks you so much.

How can we handle terabyte of data?

Hi All
I have some questions to ask, in case some company have terabyte of data in
the production server which online 24X7.
1)How can they backup database? Do they use backup database complete into di
sk? or Tape? How can they verify the backup? How frequent that needs?
2)How can they handle disaster recovery? Is Log shipping and Clustering is g
ood enough?
3)In case need to change some stored procedures or trigger. Do they upload t
o test in DEV Boxes? In case DEV box have only 80 GB? What is the criteria t
hat should be consider?
4)In case they have already tested in DEV box and want to implement in the o
nline production, How can they minimize the problem if it does occur?
I am sorry to ask a lots of questions. If you can answers item by item or su
ggest the good resources, this will be the great for me.
Thanks you in advances,
James JarupanI'm hoping you can partition your data in the case below.
We did and utilized parititioned views heavily. Breaking apart tables/dbs
when they grew beyond 250 GB so instead of 1 TB DB there were 4 250 GB DBs.
So if 1 DB went down it could be restored in smaller timeframe than 1DB of
TB size. Besides you can backup much faster.
Later we got SAN solutions and size of DB ceased to be so much of an issue
for backup purposes.
Optimizing procs for production is always difficult in such cases, before
releasing the proc to live production box the DBA again performed the actual
execution plan and the estimated execution plan for optimization. You would
still need to follow the Dev- Test- Production box test methodology to
capture most issues.
"James Jarupan" <James@.vincosoft.com> wrote in message
news:5F71E190-F3ED-40E9-8DBF-EC1091D196F2@.microsoft.com...
> Hi All
> I have some questions to ask, in case some company have terabyte of data
in the production server which online 24X7.
> 1)How can they backup database? Do they use backup database complete into
disk? or Tape? How can they verify the backup? How frequent that needs?
> 2)How can they handle disaster recovery? Is Log shipping and Clustering is
good enough?
> 3)In case need to change some stored procedures or trigger. Do they upload
to test in DEV Boxes? In case DEV box have only 80 GB? What is the criteria
that should be consider?
> 4)In case they have already tested in DEV box and want to implement in the
online production, How can they minimize the problem if it does occur?
> I am sorry to ask a lots of questions. If you can answers item by item
or suggest the good resources, this will be the great for me.
> Thanks you in advances,
> James Jarupan
>|||Thanks you so much.

How can we handle terabyte of data?

Hi Al
I have some questions to ask, in case some company have terabyte of data in the production server which online 24X7
1)How can they backup database? Do they use backup database complete into disk? or Tape? How can they verify the backup? How frequent that needs
2)How can they handle disaster recovery? Is Log shipping and Clustering is good enough
3)In case need to change some stored procedures or trigger. Do they upload to test in DEV Boxes? In case DEV box have only 80 GB? What is the criteria that should be consider?
4)In case they have already tested in DEV box and want to implement in the online production, How can they minimize the problem if it does occur
I am sorry to ask a lots of questions. If you can answers item by item or suggest the good resources, this will be the great for me
Thanks you in advances
James JarupaI'm hoping you can partition your data in the case below.
We did and utilized parititioned views heavily. Breaking apart tables/dbs
when they grew beyond 250 GB so instead of 1 TB DB there were 4 250 GB DBs.
So if 1 DB went down it could be restored in smaller timeframe than 1DB of
TB size. Besides you can backup much faster.
Later we got SAN solutions and size of DB ceased to be so much of an issue
for backup purposes.
Optimizing procs for production is always difficult in such cases, before
releasing the proc to live production box the DBA again performed the actual
execution plan and the estimated execution plan for optimization. You would
still need to follow the Dev- Test- Production box test methodology to
capture most issues.
"James Jarupan" <James@.vincosoft.com> wrote in message
news:5F71E190-F3ED-40E9-8DBF-EC1091D196F2@.microsoft.com...
> Hi All
> I have some questions to ask, in case some company have terabyte of data
in the production server which online 24X7.
> 1)How can they backup database? Do they use backup database complete into
disk? or Tape? How can they verify the backup? How frequent that needs?
> 2)How can they handle disaster recovery? Is Log shipping and Clustering is
good enough?
> 3)In case need to change some stored procedures or trigger. Do they upload
to test in DEV Boxes? In case DEV box have only 80 GB? What is the criteria
that should be consider?
> 4)In case they have already tested in DEV box and want to implement in the
online production, How can they minimize the problem if it does occur?
> I am sorry to ask a lots of questions. If you can answers item by item
or suggest the good resources, this will be the great for me.
> Thanks you in advances,
> James Jarupan
>

How can take Back-Up and restore database from a web form wsing asp.net(vb.net)

I want to give a facility to backup database restore database to the customer in my project.
Database is stored in sql server 2000.
From the website the browser can take backup by clicking BACKUP button and
restore database by clicking RESTORE button.
is it possiable in asp.net programatically ?

In SQL Server you backup a database using the "backup database" command. You restore a database using the "restore database" commands. You can lookup the complete syntax to these commands.

You issue these SQL commands from asp.net just like you would any other SQL command. The backup command will work even if others are using the database at the moment you do the backup. The biggest problem would be the restore. If you are going to restore a database over an existing database (overwrite it) then that database must not be in use or you will get an error.

Brian

|||i do not really recommend this approach whether you can or not !!!!
this is an admin job and it is better not to have a wide access to do this kind of operation..... i am not sure about if you can do it or not ... !!|||Its definitely possible, take a look at this page on MSDNhttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_35ww.asp This details the TSQL commands that are necessary to perform the backup/restore. Once you create the commands just execute them with SqlClient.
Hope This Helps

How can same query be way slower on an identical database?

Hi,
I have SQL 2000 server on a P4 box. There are 8 - 10 databases total
and they are all backup/reporting purposes. So there are no other
users.
I have 2 identical databases with identical tables and identical
stored procedures. Only the data is different. Let me name them DB1
and DB2.
I execute the SP1 on DB1, which has more records than DB2, and it
gives me the results in 2 - 5 minutes.
I execute the SP2 (which is the exact copy of SP1) on DB2, which has
less records than the DB1, and it never gives me the result. After
hours it throws an error that is not specific.
I put indexes, tried to tune it etc. But no use. I still add some
records on both and run the query on Query Analyzer, and get results
for DB1 but not for DB2.
Why would it do this? Is this a database issue or the server issue you
think?
Thanks in advance.
- Denwell... they're not really identical if the rows are different... how have
you verified that schema is the same? Are you just 'sure' or have you used a
tool like DBCompare from redgate?
Also... have you looked at the estimate plans? Do they come back the same?
If not... you might have a statistics difference between the DB's... even if
schema is the same it's certainly possible that auto created stats could be
differernt between the db's...
hope this helps a bit,
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"DenoxiS" <google@.deniznet.com> wrote in message
news:d2478899.0402061716.7ffce2d2@.posting.google.com...
> Hi,
> I have SQL 2000 server on a P4 box. There are 8 - 10 databases total
> and they are all backup/reporting purposes. So there are no other
> users.
> I have 2 identical databases with identical tables and identical
> stored procedures. Only the data is different. Let me name them DB1
> and DB2.
> I execute the SP1 on DB1, which has more records than DB2, and it
> gives me the results in 2 - 5 minutes.
> I execute the SP2 (which is the exact copy of SP1) on DB2, which has
> less records than the DB1, and it never gives me the result. After
> hours it throws an error that is not specific.
> I put indexes, tried to tune it etc. But no use. I still add some
> records on both and run the query on Query Analyzer, and get results
> for DB1 but not for DB2.
> Why would it do this? Is this a database issue or the server issue you
> think?
> Thanks in advance.
> - Den|||If you are performing any kind of iterative calculation in the stored
procedure it could be that there is data on DB2 that is causing an infinite
loop. If the data on DB1 is "good" it would not have this problem. I have
run into this most often when handling date calculations.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:u51B%23TS7DHA.3420@.TK2MSFTNGP11.phx.gbl...
> well... they're not really identical if the rows are different... how have
> you verified that schema is the same? Are you just 'sure' or have you used
a
> tool like DBCompare from redgate?
> Also... have you looked at the estimate plans? Do they come back the same?
> If not... you might have a statistics difference between the DB's... even
if
> schema is the same it's certainly possible that auto created stats could
be
> differernt between the db's...
> hope this helps a bit,
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "DenoxiS" <google@.deniznet.com> wrote in message
> news:d2478899.0402061716.7ffce2d2@.posting.google.com...
>|||Take a look at the selectivity of each set of tables. Is the data very simi
lar ie is the distribution of data similar between the tables? Taking a bet
ter look at the generated query plans will provide you with the answers you
are seeking.