Restore TSQL Scripts

Hi
I would like to creat a job in MS SQL Server 2000 to do the following:
1. Drop Test_DB
2. Restore Current_DB (latest backup in my_dir) as Test_DB
Can anyone help me on this? I think it is a simple as
--start code
DROP DATABASE Test_DB
GO
RESTORE DATABASE Test_DB
FROM Current_DB ?
GO
--end code
It is the second line that bothers me. I want to restore the most
recent backup of Current_DB. How can I get and specify this info?
Also, when I use EM to restore it gives me the option to rename the
files, can I specify this too?
thanks
dabenTry something like:
RESTORE DATABASE Test_DB
FROM DISK='C:\My_Dir\Current_DB.bak'
WITH
MOVE 'Current_DB' TO 'E:\DataFiles\Test_DB.mdf',
MOVE 'Current_DB_Log' TO 'C:\LogFiles\Test_DB_Log.ldf',
REPLACE
GO
You find the names log the logical files for the above command with
RESTORE FILELISTONLY:
RESTORE FILELISTONLY
FROM DISK='C:\MyDir\Current_DB.bak'
GO
See RESTORE in the Books Online for more information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"daben" <dabenpb@.yahoo.com> wrote in message
news:1adbeff3.0309111224.1fe1b9ac@.posting.google.com...
> Hi
> I would like to creat a job in MS SQL Server 2000 to do the following:
> 1. Drop Test_DB
> 2. Restore Current_DB (latest backup in my_dir) as Test_DB
> Can anyone help me on this? I think it is a simple as
> --start code
> DROP DATABASE Test_DB
> GO
> RESTORE DATABASE Test_DB
> FROM Current_DB ?
> GO
> --end code
> It is the second line that bothers me. I want to restore the most
> recent backup of Current_DB. How can I get and specify this info?
> Also, when I use EM to restore it gives me the option to rename the
> files, can I specify this too?
> thanks
> daben

Restore Timing of Full Backup + Transaction Logs

I have a full database backup that kicked off at 3:41am and completed at
3:56am. The backup is step 4 of a multi-step job that begins at 3am, so the
start and completion times are variable depending on how long the prior step
s
took. The database also has hourly transaction log backups scheduled that
take place on the half hour, so in this case, there was a transaction log
backup that kicked off at 3:30 and finished at 3:43.
In this case, it's pretty obvious that I will restore the full backup and
then restore subsequent logs starting with the next log that kicked off at
4:30, but this possibility of overlap got me wondering about a scenario wher
e
the full backup kicks off at say 3:20. and finishes at 3:40. In that case,
is the full backup capturing just the exact snapshot that it has at 3:20, an
d
the 3:30 log would still apply in order to get the changes from 3:20 on? Or
will the full backup account for all data and changes to data from 3:20-3:40
?
--Shelley
SQL Server 2000 Enterprise Edition, sp3I have this experince before
daily fullbackup run at 2.00am and finish at 2.40am
and transaction log run every hours, at 2.30am it will backup transaction
log finish and finish at 2.33am
transaction with backup at 2.30am with restore to another server with same
db and with norecovery mode. when i try to dump this transaction (2.30am)
up, the transaction actually fail to restore
what i do later is not run transaction backup at 2.30am..during fullbackup
is running.
and restoration of transaction is succesfull.
maybe we should not run transaction backup when fullbackup is running?
--soonyu
"smaas@.newsgroups.nospam" wrote:

> I have a full database backup that kicked off at 3:41am and completed at
> 3:56am. The backup is step 4 of a multi-step job that begins at 3am, so t
he
> start and completion times are variable depending on how long the prior st
eps
> took. The database also has hourly transaction log backups scheduled that
> take place on the half hour, so in this case, there was a transaction log
> backup that kicked off at 3:30 and finished at 3:43.
> In this case, it's pretty obvious that I will restore the full backup and
> then restore subsequent logs starting with the next log that kicked off at
> 4:30, but this possibility of overlap got me wondering about a scenario wh
ere
> the full backup kicks off at say 3:20. and finishes at 3:40. In that case
,
> is the full backup capturing just the exact snapshot that it has at 3:20,
and
> the 3:30 log would still apply in order to get the changes from 3:20 on?
Or
> will the full backup account for all data and changes to data from 3:20-3:
40?
> --Shelley
> SQL Server 2000 Enterprise Edition, sp3|||I have this experince before
daily fullbackup run at 2.00am and finish at 2.40am
and transaction log run every hours, at 2.30am it will backup transaction
log finish and finish at 2.33am
transaction with backup at 2.30am with restore to another server with same
db and with norecovery mode. when i try to dump this transaction (2.30am)
up, the transaction actually fail to restore
what i do later is not run transaction backup at 2.30am..during fullbackup
is running.
and restoration of transaction is succesfull.
maybe we should not run transaction backup when fullbackup is running?
--soonyu
"smaas@.newsgroups.nospam" wrote:

> I have a full database backup that kicked off at 3:41am and completed at
> 3:56am. The backup is step 4 of a multi-step job that begins at 3am, so t
he
> start and completion times are variable depending on how long the prior st
eps
> took. The database also has hourly transaction log backups scheduled that
> take place on the half hour, so in this case, there was a transaction log
> backup that kicked off at 3:30 and finished at 3:43.
> In this case, it's pretty obvious that I will restore the full backup and
> then restore subsequent logs starting with the next log that kicked off at
> 4:30, but this possibility of overlap got me wondering about a scenario wh
ere
> the full backup kicks off at say 3:20. and finishes at 3:40. In that case
,
> is the full backup capturing just the exact snapshot that it has at 3:20,
and
> the 3:30 log would still apply in order to get the changes from 3:20 on?
Or
> will the full backup account for all data and changes to data from 3:20-3:
40?
> --Shelley
> SQL Server 2000 Enterprise Edition, sp3|||A database backup will give you a snapshot in time at the end of completion
of the backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"smaas@.newsgroups.nospam" <smaasnewsgroupsnospam@.discussions.microsoft.com>
wrote in message
news:259960FA-0342-45DF-8F7C-9C293001BDDA@.microsoft.com...
>I have a full database backup that kicked off at 3:41am and completed at
> 3:56am. The backup is step 4 of a multi-step job that begins at 3am, so t
he
> start and completion times are variable depending on how long the prior st
eps
> took. The database also has hourly transaction log backups scheduled that
> take place on the half hour, so in this case, there was a transaction log
> backup that kicked off at 3:30 and finished at 3:43.
> In this case, it's pretty obvious that I will restore the full backup and
> then restore subsequent logs starting with the next log that kicked off at
> 4:30, but this possibility of overlap got me wondering about a scenario wh
ere
> the full backup kicks off at say 3:20. and finishes at 3:40. In that case
,
> is the full backup capturing just the exact snapshot that it has at 3:20,
and
> the 3:30 log would still apply in order to get the changes from 3:20 on?
Or
> will the full backup account for all data and changes to data from 3:20-3:
40?
> --Shelley
> SQL Server 2000 Enterprise Edition, sp3|||A database backup will give you a snapshot in time at the end of completion
of the backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"smaas@.newsgroups.nospam" <smaasnewsgroupsnospam@.discussions.microsoft.com>
wrote in message
news:259960FA-0342-45DF-8F7C-9C293001BDDA@.microsoft.com...
>I have a full database backup that kicked off at 3:41am and completed at
> 3:56am. The backup is step 4 of a multi-step job that begins at 3am, so t
he
> start and completion times are variable depending on how long the prior st
eps
> took. The database also has hourly transaction log backups scheduled that
> take place on the half hour, so in this case, there was a transaction log
> backup that kicked off at 3:30 and finished at 3:43.
> In this case, it's pretty obvious that I will restore the full backup and
> then restore subsequent logs starting with the next log that kicked off at
> 4:30, but this possibility of overlap got me wondering about a scenario wh
ere
> the full backup kicks off at say 3:20. and finishes at 3:40. In that case
,
> is the full backup capturing just the exact snapshot that it has at 3:20,
and
> the 3:30 log would still apply in order to get the changes from 3:20 on?
Or
> will the full backup account for all data and changes to data from 3:20-3:
40?
> --Shelley
> SQL Server 2000 Enterprise Edition, sp3|||smaas@.newsgroups.nospam wrote:
> I have a full database backup that kicked off at 3:41am and completed at
> 3:56am. The backup is step 4 of a multi-step job that begins at 3am, so t
he
> start and completion times are variable depending on how long the prior st
eps
> took. The database also has hourly transaction log backups scheduled that
> take place on the half hour, so in this case, there was a transaction log
> backup that kicked off at 3:30 and finished at 3:43.
> In this case, it's pretty obvious that I will restore the full backup and
> then restore subsequent logs starting with the next log that kicked off at
> 4:30, but this possibility of overlap got me wondering about a scenario wh
ere
> the full backup kicks off at say 3:20. and finishes at 3:40. In that case
,
> is the full backup capturing just the exact snapshot that it has at 3:20,
and
> the 3:30 log would still apply in order to get the changes from 3:20 on?
Or
> will the full backup account for all data and changes to data from 3:20-3:
40?
> --Shelley
> SQL Server 2000 Enterprise Edition, sp3
After the full backup finishes, it will report to you xxxx number of
pages backed up from the database file, and yyyy number of pages backed
up from the log file. The backup process takes a snapshot of everything
that has been committed to the database when the backup starts. It then
dumps all pages from the database that contain data. The final step is
to then backup everything from the transaction log that has occurred
since the initial snapshot was taken. The end result is that you have a
complete snapshot of the database as it exists when the backup
completes. Any transaction log backups that are done while the full
backup is running are redundant.
To avoid confusion, it might be best to not run t-log backups at the
same time as a full backup. My full backup job disables my t-log backup
job, re-enabling it when finished:
Job step Task
-- --
1 Disable t-log backup job
2 Full backup, "On success" and "On failure" go to #3
3 Enable t-log backup job|||Tracy McKibben wrote:
> smaas@.newsgroups.nospam wrote:
> After the full backup finishes, it will report to you xxxx number of
> pages backed up from the database file, and yyyy number of pages backed
> up from the log file. The backup process takes a snapshot of everything
> that has been committed to the database when the backup starts. It then
> dumps all pages from the database that contain data. The final step is
> to then backup everything from the transaction log that has occurred
> since the initial snapshot was taken. The end result is that you have a
> complete snapshot of the database as it exists when the backup
> completes. Any transaction log backups that are done while the full
> backup is running are redundant.
> To avoid confusion, it might be best to not run t-log backups at the
> same time as a full backup. My full backup job disables my t-log backup
> job, re-enabling it when finished:
> Job step Task
> -- --
> 1 Disable t-log backup job
> 2 Full backup, "On success" and "On failure" go to #3
> 3 Enable t-log backup job
It's true that the log file backup that are done while the full database
backup runs is redundant, but IF it runs you need the log file if you
have to do a restore. Even though the changes in the log might already
be included in the FULL database backup file, SQL server records that a
log file backup has been performed and it's needed as a part of the
logfile backup sequence.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||Steen Persson (DK) wrote:
> It's true that the log file backup that are done while the full database
> backup runs is redundant, but IF it runs you need the log file if you
> have to do a restore. Even though the changes in the log might already
> be included in the FULL database backup file, SQL server records that a
> log file backup has been performed and it's needed as a part of the
> logfile backup sequence.
>
Good catch, I meant to include that in my "To avoid confusion"
paragraph... :-(|||smaas@.newsgroups.nospam wrote:
> I have a full database backup that kicked off at 3:41am and completed at
> 3:56am. The backup is step 4 of a multi-step job that begins at 3am, so t
he
> start and completion times are variable depending on how long the prior st
eps
> took. The database also has hourly transaction log backups scheduled that
> take place on the half hour, so in this case, there was a transaction log
> backup that kicked off at 3:30 and finished at 3:43.
> In this case, it's pretty obvious that I will restore the full backup and
> then restore subsequent logs starting with the next log that kicked off at
> 4:30, but this possibility of overlap got me wondering about a scenario wh
ere
> the full backup kicks off at say 3:20. and finishes at 3:40. In that case
,
> is the full backup capturing just the exact snapshot that it has at 3:20,
and
> the 3:30 log would still apply in order to get the changes from 3:20 on?
Or
> will the full backup account for all data and changes to data from 3:20-3:
40?
> --Shelley
> SQL Server 2000 Enterprise Edition, sp3
After the full backup finishes, it will report to you xxxx number of
pages backed up from the database file, and yyyy number of pages backed
up from the log file. The backup process takes a snapshot of everything
that has been committed to the database when the backup starts. It then
dumps all pages from the database that contain data. The final step is
to then backup everything from the transaction log that has occurred
since the initial snapshot was taken. The end result is that you have a
complete snapshot of the database as it exists when the backup
completes. Any transaction log backups that are done while the full
backup is running are redundant.
To avoid confusion, it might be best to not run t-log backups at the
same time as a full backup. My full backup job disables my t-log backup
job, re-enabling it when finished:
Job step Task
-- --
1 Disable t-log backup job
2 Full backup, "On success" and "On failure" go to #3
3 Enable t-log backup job|||Tracy McKibben wrote:
> smaas@.newsgroups.nospam wrote:
> After the full backup finishes, it will report to you xxxx number of
> pages backed up from the database file, and yyyy number of pages backed
> up from the log file. The backup process takes a snapshot of everything
> that has been committed to the database when the backup starts. It then
> dumps all pages from the database that contain data. The final step is
> to then backup everything from the transaction log that has occurred
> since the initial snapshot was taken. The end result is that you have a
> complete snapshot of the database as it exists when the backup
> completes. Any transaction log backups that are done while the full
> backup is running are redundant.
> To avoid confusion, it might be best to not run t-log backups at the
> same time as a full backup. My full backup job disables my t-log backup
> job, re-enabling it when finished:
> Job step Task
> -- --
> 1 Disable t-log backup job
> 2 Full backup, "On success" and "On failure" go to #3
> 3 Enable t-log backup job
It's true that the log file backup that are done while the full database
backup runs is redundant, but IF it runs you need the log file if you
have to do a restore. Even though the changes in the log might already
be included in the FULL database backup file, SQL server records that a
log file backup has been performed and it's needed as a part of the
logfile backup sequence.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator

Restore Timing of Full Backup + Transaction Logs

I have a full database backup that kicked off at 3:41am and completed at
3:56am. The backup is step 4 of a multi-step job that begins at 3am, so the
start and completion times are variable depending on how long the prior steps
took. The database also has hourly transaction log backups scheduled that
take place on the half hour, so in this case, there was a transaction log
backup that kicked off at 3:30 and finished at 3:43.
In this case, it's pretty obvious that I will restore the full backup and
then restore subsequent logs starting with the next log that kicked off at
4:30, but this possibility of overlap got me wondering about a scenario where
the full backup kicks off at say 3:20. and finishes at 3:40. In that case,
is the full backup capturing just the exact snapshot that it has at 3:20, and
the 3:30 log would still apply in order to get the changes from 3:20 on? Or
will the full backup account for all data and changes to data from 3:20-3:40?
--Shelley
SQL Server 2000 Enterprise Edition, sp3I have this experince before
daily fullbackup run at 2.00am and finish at 2.40am
and transaction log run every hours, at 2.30am it will backup transaction
log finish and finish at 2.33am
transaction with backup at 2.30am with restore to another server with same
db and with norecovery mode. when i try to dump this transaction (2.30am)
up, the transaction actually fail to restore
what i do later is not run transaction backup at 2.30am..during fullbackup
is running.
and restoration of transaction is succesfull.
maybe we should not run transaction backup when fullbackup is running?
--soonyu
"smaas@.newsgroups.nospam" wrote:
> I have a full database backup that kicked off at 3:41am and completed at
> 3:56am. The backup is step 4 of a multi-step job that begins at 3am, so the
> start and completion times are variable depending on how long the prior steps
> took. The database also has hourly transaction log backups scheduled that
> take place on the half hour, so in this case, there was a transaction log
> backup that kicked off at 3:30 and finished at 3:43.
> In this case, it's pretty obvious that I will restore the full backup and
> then restore subsequent logs starting with the next log that kicked off at
> 4:30, but this possibility of overlap got me wondering about a scenario where
> the full backup kicks off at say 3:20. and finishes at 3:40. In that case,
> is the full backup capturing just the exact snapshot that it has at 3:20, and
> the 3:30 log would still apply in order to get the changes from 3:20 on? Or
> will the full backup account for all data and changes to data from 3:20-3:40?
> --Shelley
> SQL Server 2000 Enterprise Edition, sp3|||A database backup will give you a snapshot in time at the end of completion of the backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"smaas@.newsgroups.nospam" <smaasnewsgroupsnospam@.discussions.microsoft.com> wrote in message
news:259960FA-0342-45DF-8F7C-9C293001BDDA@.microsoft.com...
>I have a full database backup that kicked off at 3:41am and completed at
> 3:56am. The backup is step 4 of a multi-step job that begins at 3am, so the
> start and completion times are variable depending on how long the prior steps
> took. The database also has hourly transaction log backups scheduled that
> take place on the half hour, so in this case, there was a transaction log
> backup that kicked off at 3:30 and finished at 3:43.
> In this case, it's pretty obvious that I will restore the full backup and
> then restore subsequent logs starting with the next log that kicked off at
> 4:30, but this possibility of overlap got me wondering about a scenario where
> the full backup kicks off at say 3:20. and finishes at 3:40. In that case,
> is the full backup capturing just the exact snapshot that it has at 3:20, and
> the 3:30 log would still apply in order to get the changes from 3:20 on? Or
> will the full backup account for all data and changes to data from 3:20-3:40?
> --Shelley
> SQL Server 2000 Enterprise Edition, sp3|||smaas@.newsgroups.nospam wrote:
> I have a full database backup that kicked off at 3:41am and completed at
> 3:56am. The backup is step 4 of a multi-step job that begins at 3am, so the
> start and completion times are variable depending on how long the prior steps
> took. The database also has hourly transaction log backups scheduled that
> take place on the half hour, so in this case, there was a transaction log
> backup that kicked off at 3:30 and finished at 3:43.
> In this case, it's pretty obvious that I will restore the full backup and
> then restore subsequent logs starting with the next log that kicked off at
> 4:30, but this possibility of overlap got me wondering about a scenario where
> the full backup kicks off at say 3:20. and finishes at 3:40. In that case,
> is the full backup capturing just the exact snapshot that it has at 3:20, and
> the 3:30 log would still apply in order to get the changes from 3:20 on? Or
> will the full backup account for all data and changes to data from 3:20-3:40?
> --Shelley
> SQL Server 2000 Enterprise Edition, sp3
After the full backup finishes, it will report to you xxxx number of
pages backed up from the database file, and yyyy number of pages backed
up from the log file. The backup process takes a snapshot of everything
that has been committed to the database when the backup starts. It then
dumps all pages from the database that contain data. The final step is
to then backup everything from the transaction log that has occurred
since the initial snapshot was taken. The end result is that you have a
complete snapshot of the database as it exists when the backup
completes. Any transaction log backups that are done while the full
backup is running are redundant.
To avoid confusion, it might be best to not run t-log backups at the
same time as a full backup. My full backup job disables my t-log backup
job, re-enabling it when finished:
Job step Task
-- --
1 Disable t-log backup job
2 Full backup, "On success" and "On failure" go to #3
3 Enable t-log backup job|||Tracy McKibben wrote:
> smaas@.newsgroups.nospam wrote:
>> I have a full database backup that kicked off at 3:41am and completed
>> at 3:56am. The backup is step 4 of a multi-step job that begins at
>> 3am, so the start and completion times are variable depending on how
>> long the prior steps took. The database also has hourly transaction
>> log backups scheduled that take place on the half hour, so in this
>> case, there was a transaction log backup that kicked off at 3:30 and
>> finished at 3:43.
>> In this case, it's pretty obvious that I will restore the full backup
>> and then restore subsequent logs starting with the next log that
>> kicked off at 4:30, but this possibility of overlap got me wondering
>> about a scenario where the full backup kicks off at say 3:20. and
>> finishes at 3:40. In that case, is the full backup capturing just the
>> exact snapshot that it has at 3:20, and the 3:30 log would still apply
>> in order to get the changes from 3:20 on? Or will the full backup
>> account for all data and changes to data from 3:20-3:40?
>> --Shelley
>> SQL Server 2000 Enterprise Edition, sp3
> After the full backup finishes, it will report to you xxxx number of
> pages backed up from the database file, and yyyy number of pages backed
> up from the log file. The backup process takes a snapshot of everything
> that has been committed to the database when the backup starts. It then
> dumps all pages from the database that contain data. The final step is
> to then backup everything from the transaction log that has occurred
> since the initial snapshot was taken. The end result is that you have a
> complete snapshot of the database as it exists when the backup
> completes. Any transaction log backups that are done while the full
> backup is running are redundant.
> To avoid confusion, it might be best to not run t-log backups at the
> same time as a full backup. My full backup job disables my t-log backup
> job, re-enabling it when finished:
> Job step Task
> -- --
> 1 Disable t-log backup job
> 2 Full backup, "On success" and "On failure" go to #3
> 3 Enable t-log backup job
It's true that the log file backup that are done while the full database
backup runs is redundant, but IF it runs you need the log file if you
have to do a restore. Even though the changes in the log might already
be included in the FULL database backup file, SQL server records that a
log file backup has been performed and it's needed as a part of the
logfile backup sequence.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||Steen Persson (DK) wrote:
>> To avoid confusion, it might be best to not run t-log backups at the
>> same time as a full backup. My full backup job disables my t-log
>> backup job, re-enabling it when finished:
>> Job step Task
>> -- --
>> 1 Disable t-log backup job
>> 2 Full backup, "On success" and "On failure" go to #3
>> 3 Enable t-log backup job
> It's true that the log file backup that are done while the full database
> backup runs is redundant, but IF it runs you need the log file if you
> have to do a restore. Even though the changes in the log might already
> be included in the FULL database backup file, SQL server records that a
> log file backup has been performed and it's needed as a part of the
> logfile backup sequence.
>
Good catch, I meant to include that in my "To avoid confusion"
paragraph... :-(sql

Restore through Job or DTS

I try to schedule either a job or a DTS package that will restore a db from
backup. No other connection exist on that db but apparently I receive the
message that Restore failed because exclusive access to the db could not be
obtained. I assume that the connection that is counted is the connection of
the job itself.
Can anyone help ?Hi ,
Your message confirms that there is some existing connection in the database
when you are try to restore. So please add the below script along with your
DTS package.
use master
go
declare @.x varchar(255)
select @.x = @.x + " kill " + convert(varchar(5), spid)
from master.dbo.sysprocesses
where dbid = db_id ('dbname')
exec (@.x)
go
Please replace the dbname with your database name in the script.
Thanks
Hari - MCDBA
US Software
Trivandrum
"Andreas" <andreasy@.netu.com.cy> wrote in message
news:uZ7hY6aVDHA.2224@.TK2MSFTNGP09.phx.gbl...
> I try to schedule either a job or a DTS package that will restore a db
from
> backup. No other connection exist on that db but apparently I receive the
> message that Restore failed because exclusive access to the db could not
be
> obtained. I assume that the connection that is counted is the connection
of
> the job itself.
> Can anyone help ?
>

Restore runs longer in batch mode

I have created a Job that uses "cmcexec" to execute a SQL script. This Job
brings SQL to single_user mode then restores from a directory that is local
to the server.
1) When I run this restore in the GUI it takes 15 minutes but is still
executing
(1 + hours).
2) How can I tell that it is actually running and not hung up?
Hi,
From Query Analyzer, Could you execute the syetm procedure sp_who2 and see
the status of the backup process.
Repeatedly execute sp2 and check if the CPU time and DISK IO are increasing
for backup process. If the value is increasing
then the process is active.
Thanks
Hari
MCDBA
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:E13E54D2-C77F-4A44-BDAB-630166185032@.microsoft.com...
> I have created a Job that uses "cmcexec" to execute a SQL script. This
Job
> brings SQL to single_user mode then restores from a directory that is
local
> to the server.
> 1) When I run this restore in the GUI it takes 15 minutes but is still
> executing
> (1 + hours).
> 2) How can I tell that it is actually running and not hung up?

Restore Production database to Development

I have a production database with a backup job that creates files with the
naming convention dbname_db_200503291800.bak. I want to schedule a restore
job that will retire yesterdays backup. How can I write my restore statement
so that it will specify the backup file with yesterdays date.

ThanksTerri (terri@.cybernets.com) writes:
> I have a production database with a backup job that creates files with
> the naming convention dbname_db_200503291800.bak. I want to schedule a
> restore job that will retire yesterdays backup. How can I write my
> restore statement so that it will specify the backup file with
> yesterdays date.

DECLARE @.filename sysname
SELECT @.filename = 'dbname_db' +
convert(char(8), dateadd(DAY, -1, getdate()), 112) +
'.bak'
RESTORE DATABASE db FROM disk=@.filename

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Restore problem - It takes a long time (loading...)

Hi,
I have set a restore job for a Database using Enterprise Manager. The job
ran quite quickly but the Database now appears on the database list with
(loading) written after it and none of the objects are visible. It has been
sitting there for about n hours. The database file is only about 1,5Mb and
this is the first time is has been restored. Is this kind of time usual? Is
there something else I need to do?
I´m doing this in 2 servers, one for backup and the other one for restore
tks,
edFirst right click on the databases tab and select refresh ( SEM is bad about
not refreshing.)
If the database is still marked as loading, it means that recovery has not
yet run... If you have finished loading all of the logs etc... then to make
the database available run
restore database prod with recovery
from Query Analyzer... The database will now be available...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it community
of SQL Server professionals.
www.sqlpass.org
"Edmilson" <troqui@.padtec.com.br> wrote in message
news:#xDTrT1VDHA.2012@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have set a restore job for a Database using Enterprise Manager. The job
> ran quite quickly but the Database now appears on the database list with
> (loading) written after it and none of the objects are visible. It has
been
> sitting there for about n hours. The database file is only about 1,5Mb and
> this is the first time is has been restored. Is this kind of time usual?
Is
> there something else I need to do?
> I´m doing this in 2 servers, one for backup and the other one for restore
> tks,
> ed
>