restore db daily with a job but BAK file is diff

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

restore db daily with a job but BAK file is diff

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

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

restore db daily with a job but BAK file is diff

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

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

restore database on SQL Server 6.5

Hi,
I restore a database using a 3rd party software. The
restore job was cancelled but the process "loading
database" is still in the output of "sp_who". I could not
kill it. Do I have to reboot the server to get rid of it
or I can start a job to continue to restore this database?
Thanks for help,
Hi,
Try STOP and START the MSSQL Server service. After that start the Loading of
database again from ISQL_W.
LOAD database <dbname> from disk='c:\backup\dbname.DMP' with stats=10
Note: stats=10 will show the percentage of load completed.
Thanks
Hari
MCDBA
"Julia" <anonymous@.discussions.microsoft.com> wrote in message
news:1d83601c453e7$425a6e30$a401280a@.phx.gbl...
> Hi,
> I restore a database using a 3rd party software. The
> restore job was cancelled but the process "loading
> database" is still in the output of "sp_who". I could not
> kill it. Do I have to reboot the server to get rid of it
> or I can start a job to continue to restore this database?
> Thanks for help,

restore database on SQL Server 6.5

Hi,
I restore a database using a 3rd party software. The
restore job was cancelled but the process "loading
database" is still in the output of "sp_who". I could not
kill it. Do I have to reboot the server to get rid of it
or I can start a job to continue to restore this database?
Thanks for help,Hi,
Try STOP and START the MSSQL Server service. After that start the Loading of
database again from ISQL_W.
LOAD database <dbname> from disk='c:\backup\dbname.DMP' with stats=10
Note: stats=10 will show the percentage of load completed.
Thanks
Hari
MCDBA
"Julia" <anonymous@.discussions.microsoft.com> wrote in message
news:1d83601c453e7$425a6e30$a401280a@.phx
.gbl...
> Hi,
> I restore a database using a 3rd party software. The
> restore job was cancelled but the process "loading
> database" is still in the output of "sp_who". I could not
> kill it. Do I have to reboot the server to get rid of it
> or I can start a job to continue to restore this database?
> Thanks for help,sql

restore database on SQL Server 6.5

Hi,
I restore a database using a 3rd party software. The
restore job was cancelled but the process "loading
database" is still in the output of "sp_who". I could not
kill it. Do I have to reboot the server to get rid of it
or I can start a job to continue to restore this database?
Thanks for help,Hi,
Try STOP and START the MSSQL Server service. After that start the Loading of
database again from ISQL_W.
LOAD database <dbname> from disk='c:\backup\dbname.DMP' with stats=10
Note: stats=10 will show the percentage of load completed.
Thanks
Hari
MCDBA
"Julia" <anonymous@.discussions.microsoft.com> wrote in message
news:1d83601c453e7$425a6e30$a401280a@.phx.gbl...
> Hi,
> I restore a database using a 3rd party software. The
> restore job was cancelled but the process "loading
> database" is still in the output of "sp_who". I could not
> kill it. Do I have to reboot the server to get rid of it
> or I can start a job to continue to restore this database?
> Thanks for help,

Restore Database failed - Excusive Access could not be obtained.

I have a job that runs at 4:00 AM, which refreshes(RESTORE
DATABASE) the read only copy of the database. Last night
the job has failed and the message I got is, Exculsive
access could not be obtained because the database is in
use and the acutal message is as following -
Executed as user: UserNameHere. ...Executing... DTSRun
OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_3 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_3 DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_5 DTSRun OnError:
DTSStep_DTSExecuteSQLTask_5, Error = -2147217900
(80040E14) Error string: RESTORE DATABASE is
terminating abnormally. Error source: Microsoft OLE
DB Provider for SQL Server Help file: Help
context: 0 Error Detail Records: Error: -
2147217900 (80040E14); Provider Error: 3013 (BC5)
Error string: RESTORE DATABASE is terminating
abnormally. Error source: Microsoft OLE DB Provider
for SQL Server Help file: Help context:
0 Error: -2147217900 (80040E14); Provider Error:
3101 (C1D) Error string: Exclusive access could not
be obtained because the database is in use. Error
source: Microsoft OLE DB Provider for SQL Serv. The step
failed.
Is there any option in RESTORE DATABASE that kills all the
logged in users and restores a fresh copy of the database.
Otherwise do I have to kill all the logged in users by
using sysprocesses. Also the error message doesn't show
the user(s) name, is there any way I can find it out now.
Going forward I will start running profiler but is there a
way figure out who logged in last night.
Thanks,
Narendra.Hi,
There is no option in restore command to kill all the users connected to
database. Instead you can use the below
statements along with your Restore database command and schedule this as a
task.
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
restore database command
Thanks
Hari
MCDBA
"Narendra" <nnarendra@.yahoo.com> wrote in message
news:0e1901c3a3b2$1ea23940$a601280a@.phx.gbl...
> I have a job that runs at 4:00 AM, which refreshes(RESTORE
> DATABASE) the read only copy of the database. Last night
> the job has failed and the message I got is, Exculsive
> access could not be obtained because the database is in
> use and the acutal message is as following -
> Executed as user: UserNameHere. ...Executing... DTSRun
> OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_3 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_3 DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_5 DTSRun OnError:
> DTSStep_DTSExecuteSQLTask_5, Error = -2147217900
> (80040E14) Error string: RESTORE DATABASE is
> terminating abnormally. Error source: Microsoft OLE
> DB Provider for SQL Server Help file: Help
> context: 0 Error Detail Records: Error: -
> 2147217900 (80040E14); Provider Error: 3013 (BC5)
> Error string: RESTORE DATABASE is terminating
> abnormally. Error source: Microsoft OLE DB Provider
> for SQL Server Help file: Help context:
> 0 Error: -2147217900 (80040E14); Provider Error:
> 3101 (C1D) Error string: Exclusive access could not
> be obtained because the database is in use. Error
> source: Microsoft OLE DB Provider for SQL Serv. The step
> failed.
>
> Is there any option in RESTORE DATABASE that kills all the
> logged in users and restores a fresh copy of the database.
> Otherwise do I have to kill all the logged in users by
> using sysprocesses. Also the error message doesn't show
> the user(s) name, is there any way I can find it out now.
> Going forward I will start running profiler but is there a
> way figure out who logged in last night.
> Thanks,
> Narendra.|||Instead of this, you could just set the database to
SINGLE_USER MODE for the duration of the restore and then
set it back to MULTI_USER when done. Using the WITH
ROLLBACK IMMEDIATE when setting to SINGLE_USER MODE
alter database AHP set SINGLE_USER with rollback immediate
restore database command
alter database AHP set MULTI_USER
>--Original Message--
>Hi,
>There is no option in restore command to kill all the
users connected to
>database. Instead you can use the below
>statements along with your Restore database command and
schedule this as a
>task.
>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
>restore database command
>Thanks
>Hari
>MCDBA
>
>"Narendra" <nnarendra@.yahoo.com> wrote in message
>news:0e1901c3a3b2$1ea23940$a601280a@.phx.gbl...
>> I have a job that runs at 4:00 AM, which refreshes
(RESTORE
>> DATABASE) the read only copy of the database. Last night
>> the job has failed and the message I got is, Exculsive
>> access could not be obtained because the database is in
>> use and the acutal message is as following -
>> Executed as user: UserNameHere. ...Executing... DTSRun
>> OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
>> DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
>> DTSStep_DTSExecuteSQLTask_3 DTSRun OnFinish:
>> DTSStep_DTSExecuteSQLTask_3 DTSRun OnStart:
>> DTSStep_DTSExecuteSQLTask_5 DTSRun OnError:
>> DTSStep_DTSExecuteSQLTask_5, Error = -2147217900
>> (80040E14) Error string: RESTORE DATABASE is
>> terminating abnormally. Error source: Microsoft
OLE
>> DB Provider for SQL Server Help file: Help
>> context: 0 Error Detail Records: Error: -
>> 2147217900 (80040E14); Provider Error: 3013 (BC5)
>> Error string: RESTORE DATABASE is terminating
>> abnormally. Error source: Microsoft OLE DB
Provider
>> for SQL Server Help file: Help context:
>> 0 Error: -2147217900 (80040E14); Provider
Error:
>> 3101 (C1D) Error string: Exclusive access could
not
>> be obtained because the database is in use. Error
>> source: Microsoft OLE DB Provider for SQL Serv. The
step
>> failed.
>>
>> Is there any option in RESTORE DATABASE that kills all
the
>> logged in users and restores a fresh copy of the
database.
>> Otherwise do I have to kill all the logged in users by
>> using sysprocesses. Also the error message doesn't show
>> the user(s) name, is there any way I can find it out
now.
>> Going forward I will start running profiler but is
there a
>> way figure out who logged in last night.
>> Thanks,
>> Narendra.
>
>.
>|||Van/Hari,
Thanks for the information. I will follow the suggestions.
Narendra.
>--Original Message--
>Instead of this, you could just set the database to
>SINGLE_USER MODE for the duration of the restore and then
>set it back to MULTI_USER when done. Using the WITH
>ROLLBACK IMMEDIATE when setting to SINGLE_USER MODE
>alter database AHP set SINGLE_USER with rollback immediate
>restore database command
>alter database AHP set MULTI_USER
>>--Original Message--
>>Hi,
>>There is no option in restore command to kill all the
>users connected to
>>database. Instead you can use the below
>>statements along with your Restore database command and
>schedule this as a
>>task.
>>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
>>restore database command
>>Thanks
>>Hari
>>MCDBA
>>
>>"Narendra" <nnarendra@.yahoo.com> wrote in message
>>news:0e1901c3a3b2$1ea23940$a601280a@.phx.gbl...
>> I have a job that runs at 4:00 AM, which refreshes
>(RESTORE
>> DATABASE) the read only copy of the database. Last
night
>> the job has failed and the message I got is, Exculsive
>> access could not be obtained because the database is in
>> use and the acutal message is as following -
>> Executed as user: UserNameHere. ...Executing...
DTSRun
>> OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
OnFinish:
>> DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
>> DTSStep_DTSExecuteSQLTask_3 DTSRun OnFinish:
>> DTSStep_DTSExecuteSQLTask_3 DTSRun OnStart:
>> DTSStep_DTSExecuteSQLTask_5 DTSRun OnError:
>> DTSStep_DTSExecuteSQLTask_5, Error = -2147217900
>> (80040E14) Error string: RESTORE DATABASE is
>> terminating abnormally. Error source: Microsoft
>OLE
>> DB Provider for SQL Server Help file: Help
>> context: 0 Error Detail Records: Error: -
>> 2147217900 (80040E14); Provider Error: 3013 (BC5)
>> Error string: RESTORE DATABASE is terminating
>> abnormally. Error source: Microsoft OLE DB
>Provider
>> for SQL Server Help file: Help context:
>> 0 Error: -2147217900 (80040E14); Provider
>Error:
>> 3101 (C1D) Error string: Exclusive access could
>not
>> be obtained because the database is in use. Error
>> source: Microsoft OLE DB Provider for SQL Serv. The
>step
>> failed.
>>
>> Is there any option in RESTORE DATABASE that kills all
>the
>> logged in users and restores a fresh copy of the
>database.
>> Otherwise do I have to kill all the logged in users by
>> using sysprocesses. Also the error message doesn't show
>> the user(s) name, is there any way I can find it out
>now.
>> Going forward I will start running profiler but is
>there a
>> way figure out who logged in last night.
>> Thanks,
>> Narendra.
>>
>>.
>.
>|||FYI...the WITH ROLLBACK IMMEDIATE will rollback anything
that any users connected may have been in the middle of
doing. But that should be a little cleaner than a KILL
command.
Van Jones
MCDBA, MCSE, MCSA, MCAD
>--Original Message--
>Instead of this, you could just set the database to
>SINGLE_USER MODE for the duration of the restore and then
>set it back to MULTI_USER when done. Using the WITH
>ROLLBACK IMMEDIATE when setting to SINGLE_USER MODE
>alter database AHP set SINGLE_USER with rollback immediate
>restore database command
>alter database AHP set MULTI_USER
>>--Original Message--
>>Hi,
>>There is no option in restore command to kill all the
>users connected to
>>database. Instead you can use the below
>>statements along with your Restore database command and
>schedule this as a
>>task.
>>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
>>restore database command
>>Thanks
>>Hari
>>MCDBA
>>
>>"Narendra" <nnarendra@.yahoo.com> wrote in message
>>news:0e1901c3a3b2$1ea23940$a601280a@.phx.gbl...
>> I have a job that runs at 4:00 AM, which refreshes
>(RESTORE
>> DATABASE) the read only copy of the database. Last
night
>> the job has failed and the message I got is, Exculsive
>> access could not be obtained because the database is in
>> use and the acutal message is as following -
>> Executed as user: UserNameHere. ...Executing...
DTSRun
>> OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
OnFinish:
>> DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
>> DTSStep_DTSExecuteSQLTask_3 DTSRun OnFinish:
>> DTSStep_DTSExecuteSQLTask_3 DTSRun OnStart:
>> DTSStep_DTSExecuteSQLTask_5 DTSRun OnError:
>> DTSStep_DTSExecuteSQLTask_5, Error = -2147217900
>> (80040E14) Error string: RESTORE DATABASE is
>> terminating abnormally. Error source: Microsoft
>OLE
>> DB Provider for SQL Server Help file: Help
>> context: 0 Error Detail Records: Error: -
>> 2147217900 (80040E14); Provider Error: 3013 (BC5)
>> Error string: RESTORE DATABASE is terminating
>> abnormally. Error source: Microsoft OLE DB
>Provider
>> for SQL Server Help file: Help context:
>> 0 Error: -2147217900 (80040E14); Provider
>Error:
>> 3101 (C1D) Error string: Exclusive access could
>not
>> be obtained because the database is in use. Error
>> source: Microsoft OLE DB Provider for SQL Serv. The
>step
>> failed.
>>
>> Is there any option in RESTORE DATABASE that kills all
>the
>> logged in users and restores a fresh copy of the
>database.
>> Otherwise do I have to kill all the logged in users by
>> using sysprocesses. Also the error message doesn't show
>> the user(s) name, is there any way I can find it out
>now.
>> Going forward I will start running profiler but is
>there a
>> way figure out who logged in last night.
>> Thanks,
>> Narendra.
>>
>>.
>.
>

RESTORE DATABASE Error!? (SQL2kSP3)

Hi,
while testing my backup job I've come to a severe error: If certain
conditions are met, RESTORE DATABASE restores databases wrong!
This is what I did:
* Made Full Backup of five databases using SQL Agent job.
* Made Differential Backup of three of those five databases using SQL Agent
job.
* In SQL Server Enterprise Manager I've deleted two of those five databases
(dba and dbc). (This deletes corresponding entries in msdb..backupfile etc.)
* Tried to recover one of those two databases (dbc). ERROR!
The error is that dba(!) is getting restored under the name of dbc!
Did I make a severe error here or did I find one? I'm using SQL Server 2000,
SP3.
TIA,
Axel Dahmen
These are my T-SQL statements:
Complete backup:
BACKUP DATABASE dba
TO DISK = @.buPath
WITH
DESCRIPTION = 'dba complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
INIT, -- Kill file and create
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dba'
BACKUP DATABASE dbb
TO DISK = @.buPath
WITH
DESCRIPTION = 'dbb complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
NOINIT,
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dbb'
BACKUP DATABASE dbc
TO DISK = @.buPath
WITH
DESCRIPTION = 'dbc complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
NOINIT,
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dbc'
...
Restore command:
RESTORE DATABASE dbc
FROM DISK = @.buPath
WITH
PASSWORD = 'mann'
,MEDIAPASSWORD = 'lander'
,REPLACE
You are putting multiple logical backups into the same physical media...
When you use that media to restore, you should include the file = part of
the command to specify which logical backup should be used for the
restore... If you do not specify WHICH backup the first item in the file is
chosen... The backup of DBA is the first thing in the file, so when you try
to restore DBC from the file ( and do not specify FILE =3) it uses the first
item which is the backup of DBA.. The security checks then fail... Change
your restore command to :
RESTORE DATABASE dbc
> FROM DISK = @.buPath
> WITH
> PASSWORD = 'mann'
> ,MEDIAPASSWORD = 'lander'
> ,REPLACE, FILE = 3
>
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's
community of SQL Server professionals.
www.sqlpass.org
"Axel Dahmen" <NO_SPAM@.NoOneKnows.de> wrote in message
news:uP2$w3iHEHA.716@.TK2MSFTNGP12.phx.gbl...
> Hi,
> while testing my backup job I've come to a severe error: If certain
> conditions are met, RESTORE DATABASE restores databases wrong!
> This is what I did:
> * Made Full Backup of five databases using SQL Agent job.
> * Made Differential Backup of three of those five databases using SQL
Agent
> job.
> * In SQL Server Enterprise Manager I've deleted two of those five
databases
> (dba and dbc). (This deletes corresponding entries in msdb..backupfile
etc.)
> * Tried to recover one of those two databases (dbc). ERROR!
> The error is that dba(!) is getting restored under the name of dbc!
> Did I make a severe error here or did I find one? I'm using SQL Server
2000,
> SP3.
> TIA,
> Axel Dahmen
> --
> These are my T-SQL statements:
> Complete backup:
> BACKUP DATABASE dba
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dba complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> INIT, -- Kill file and create
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dba'
> BACKUP DATABASE dbb
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dbb complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> NOINIT,
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dbb'
> BACKUP DATABASE dbc
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dbc complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> NOINIT,
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dbc'
> ...
>
> Restore command:
> RESTORE DATABASE dbc
> FROM DISK = @.buPath
> WITH
> PASSWORD = 'mann'
> ,MEDIAPASSWORD = 'lander'
> ,REPLACE
>

RESTORE DATABASE Error!? (SQL2kSP3)

Hi,
while testing my backup job I've come to a severe error: If certain
conditions are met, RESTORE DATABASE restores databases wrong!
This is what I did:
* Made Full Backup of five databases using SQL Agent job.
* Made Differential Backup of three of those five databases using SQL Agent
job.
* In SQL Server Enterprise Manager I've deleted two of those five databases
(dba and dbc). (This deletes corresponding entries in msdb..backupfile etc.)
* Tried to recover one of those two databases (dbc). ERROR!
The error is that dba(!) is getting restored under the name of dbc!
Did I make a severe error here or did I find one? I'm using SQL Server 2000,
SP3.
TIA,
Axel Dahmen
--
These are my T-SQL statements:
Complete backup:
BACKUP DATABASE dba
TO DISK = @.buPath
WITH
DESCRIPTION = 'dba complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
INIT, -- Kill file and create
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dba'
BACKUP DATABASE dbb
TO DISK = @.buPath
WITH
DESCRIPTION = 'dbb complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
NOINIT,
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dbb'
BACKUP DATABASE dbc
TO DISK = @.buPath
WITH
DESCRIPTION = 'dbc complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
NOINIT,
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dbc'
...
Restore command:
RESTORE DATABASE dbc
FROM DISK = @.buPath
WITH
PASSWORD = 'mann'
,MEDIAPASSWORD = 'lander'
,REPLACEYou are putting multiple logical backups into the same physical media...
When you use that media to restore, you should include the file = part of
the command to specify which logical backup should be used for the
restore... If you do not specify WHICH backup the first item in the file is
chosen... The backup of DBA is the first thing in the file, so when you try
to restore DBC from the file ( and do not specify FILE =3) it uses the first
item which is the backup of DBA.. The security checks then fail... Change
your restore command to :
RESTORE DATABASE dbc
> FROM DISK = @.buPath
> WITH
> PASSWORD = 'mann'
> ,MEDIAPASSWORD = 'lander'
> ,REPLACE, FILE = 3
>
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's
community of SQL Server professionals.
www.sqlpass.org
"Axel Dahmen" <NO_SPAM@.NoOneKnows.de> wrote in message
news:uP2$w3iHEHA.716@.TK2MSFTNGP12.phx.gbl...
> Hi,
> while testing my backup job I've come to a severe error: If certain
> conditions are met, RESTORE DATABASE restores databases wrong!
> This is what I did:
> * Made Full Backup of five databases using SQL Agent job.
> * Made Differential Backup of three of those five databases using SQL
Agent
> job.
> * In SQL Server Enterprise Manager I've deleted two of those five
databases
> (dba and dbc). (This deletes corresponding entries in msdb..backupfile
etc.)
> * Tried to recover one of those two databases (dbc). ERROR!
> The error is that dba(!) is getting restored under the name of dbc!
> Did I make a severe error here or did I find one? I'm using SQL Server
2000,
> SP3.
> TIA,
> Axel Dahmen
> --
> These are my T-SQL statements:
> Complete backup:
> BACKUP DATABASE dba
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dba complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> INIT, -- Kill file and create
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dba'
> BACKUP DATABASE dbb
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dbb complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> NOINIT,
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dbb'
> BACKUP DATABASE dbc
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dbc complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> NOINIT,
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dbc'
> ...
>
> Restore command:
> RESTORE DATABASE dbc
> FROM DISK = @.buPath
> WITH
> PASSWORD = 'mann'
> ,MEDIAPASSWORD = 'lander'
> ,REPLACE
>

RESTORE DATABASE Error!? (SQL2kSP3)

Hi,
while testing my backup job I've come to a severe error: If certain
conditions are met, RESTORE DATABASE restores databases wrong!
This is what I did:
* Made Full Backup of five databases using SQL Agent job.
* Made Differential Backup of three of those five databases using SQL Agent
job.
* In SQL Server Enterprise Manager I've deleted two of those five databases
(dba and dbc). (This deletes corresponding entries in msdb..backupfile etc.)
* Tried to recover one of those two databases (dbc). ERROR!
The error is that dba(!) is getting restored under the name of dbc!
Did I make a severe error here or did I find one? I'm using SQL Server 2000,
SP3.
TIA,
Axel Dahmen
These are my T-SQL statements:
Complete backup:
BACKUP DATABASE dba
TO DISK = @.buPath
WITH
DESCRIPTION = 'dba complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
INIT, -- Kill file and create
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dba'
BACKUP DATABASE dbb
TO DISK = @.buPath
WITH
DESCRIPTION = 'dbb complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
NOINIT,
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dbb'
BACKUP DATABASE dbc
TO DISK = @.buPath
WITH
DESCRIPTION = 'dbc complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
NOINIT,
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dbc'
...
Restore command:
RESTORE DATABASE dbc
FROM DISK = @.buPath
WITH
PASSWORD = 'mann'
,MEDIAPASSWORD = 'lander'
,REPLACEYou are putting multiple logical backups into the same physical media...
When you use that media to restore, you should include the file = part of
the command to specify which logical backup should be used for the
restore... If you do not specify WHICH backup the first item in the file is
chosen... The backup of DBA is the first thing in the file, so when you try
to restore DBC from the file ( and do not specify FILE =3) it uses the first
item which is the backup of DBA.. The security checks then fail... Change
your restore command to :
RESTORE DATABASE dbc
> FROM DISK = @.buPath
> WITH
> PASSWORD = 'mann'
> ,MEDIAPASSWORD = 'lander'
> ,REPLACE, FILE = 3
>
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's
community of SQL Server professionals.
www.sqlpass.org
"Axel Dahmen" <NO_SPAM@.NoOneKnows.de> wrote in message
news:uP2$w3iHEHA.716@.TK2MSFTNGP12.phx.gbl...
> Hi,
> while testing my backup job I've come to a severe error: If certain
> conditions are met, RESTORE DATABASE restores databases wrong!
> This is what I did:
> * Made Full Backup of five databases using SQL Agent job.
> * Made Differential Backup of three of those five databases using SQL
Agent
> job.
> * In SQL Server Enterprise Manager I've deleted two of those five
databases
> (dba and dbc). (This deletes corresponding entries in msdb..backupfile
etc.)
> * Tried to recover one of those two databases (dbc). ERROR!
> The error is that dba(!) is getting restored under the name of dbc!
> Did I make a severe error here or did I find one? I'm using SQL Server
2000,
> SP3.
> TIA,
> Axel Dahmen
> --
> These are my T-SQL statements:
> Complete backup:
> BACKUP DATABASE dba
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dba complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> INIT, -- Kill file and create
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dba'
> BACKUP DATABASE dbb
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dbb complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> NOINIT,
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dbb'
> BACKUP DATABASE dbc
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dbc complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> NOINIT,
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dbc'
> ...
>
> Restore command:
> RESTORE DATABASE dbc
> FROM DISK = @.buPath
> WITH
> PASSWORD = 'mann'
> ,MEDIAPASSWORD = 'lander'
> ,REPLACE
>

Restore Database

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

Restore database

Hi Guys,
I have question regarding restore database from dynamic database name. I
want to restore a database everyday automately in job from backup that has a
dynamic name(maintainance plan produced name). how can I get name automately.
Thanks.
Hello,
All the backup information and backup file names will be stored in a system
table backupset in MSDB database. You could use that information to
automatically restore
the database. Take a look into the below URL to get the file names.
http://www.sqlmag.com/Article/ArticleID/50183/sql_server_50183.html
Thanks
Hari
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:76E8B869-82EF-46BF-AC1D-A61DEB95918C@.microsoft.com...
> Hi Guys,
> I have question regarding restore database from dynamic database name. I
> want to restore a database everyday automately in job from backup that has
> a
> dynamic name(maintainance plan produced name). how can I get name
> automately.
> Thanks.

Restore database

Hi Guys,
I have question regarding restore database from dynamic database name. I
want to restore a database everyday automately in job from backup that has a
dynamic name(maintainance plan produced name). how can I get name automately.
Thanks.Hello,
All the backup information and backup file names will be stored in a system
table backupset in MSDB database. You could use that information to
automatically restore
the database. Take a look into the below URL to get the file names.
http://www.sqlmag.com/Article/ArticleID/50183/sql_server_50183.html
Thanks
Hari
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:76E8B869-82EF-46BF-AC1D-A61DEB95918C@.microsoft.com...
> Hi Guys,
> I have question regarding restore database from dynamic database name. I
> want to restore a database everyday automately in job from backup that has
> a
> dynamic name(maintainance plan produced name). how can I get name
> automately.
> Thanks.

Restore Database

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

Restore database

Hi Guys,
I have question regarding restore database from dynamic database name. I
want to restore a database everyday automately in job from backup that has a
dynamic name(maintainance plan produced name). how can I get name automately
.
Thanks.Hello,
All the backup information and backup file names will be stored in a system
table backupset in MSDB database. You could use that information to
automatically restore
the database. Take a look into the below URL to get the file names.
http://www.sqlmag.com/Article/Artic...rver_50183.html
Thanks
Hari
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:76E8B869-82EF-46BF-AC1D-A61DEB95918C@.microsoft.com...
> Hi Guys,
> I have question regarding restore database from dynamic database name. I
> want to restore a database everyday automately in job from backup that has
> a
> dynamic name(maintainance plan produced name). how can I get name
> automately.
> Thanks.

Restore Database

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

Restore backup differential on a differente server

I got TWO sqlserver .
The first is the main server , every one hour I make a differential
backup of a Db ( TestDB ) with a Job that save de file .BAK in a
folder of the second server .
Now I need to create a Job that restore the db on the second server
How can I do this ? I'm only able to restore the full backup not
differential backup :-(
please help me
thanksHave you looked at this?
http://msdn2.microsoft.com/en-us/library/ms175510.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<emanuele.pasinetti@.gmail.com> wrote in message
news:1163436938.589340.226360@.f16g2000cwb.googlegroups.com...
>I got TWO sqlserver .
> The first is the main server , every one hour I make a differential
> backup of a Db ( TestDB ) with a Job that save de file .BAK in a
> folder of the second server .
> Now I need to create a Job that restore the db on the second server
> How can I do this ? I'm only able to restore the full backup not
> differential backup :-(
> please help me
> thanks
>|||I'm working with SqlServer 2000 , I think this is about 2005, true ?
Roger Wolter[MSFT] ha scritto:
[vbcol=seagreen]
> Have you looked at this?
> http://msdn2.microsoft.com/en-us/library/ms175510.aspx
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> <emanuele.pasinetti@.gmail.com> wrote in message
> news:1163436938.589340.226360@.f16g2000cwb.googlegroups.com...|||All you need to do is to schedule a job in which you have a job step executi
ng the RESTORE DATABASE
command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Pasi" <emanuele.pasinetti@.gmail.com> wrote in message
news:1163449604.753979.234130@.h48g2000cwc.googlegroups.com...
> I'm working with SqlServer 2000 , I think this is about 2005, true ?
> Roger Wolter[MSFT] ha scritto:
>
>|||Sorry, I must have missed where you said your were using SQL 2000
http://msdn.microsoft.com/library/d... />
t_7tv7.asp
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Pasi" <emanuele.pasinetti@.gmail.com> wrote in message
news:1163449604.753979.234130@.h48g2000cwc.googlegroups.com...
> I'm working with SqlServer 2000 , I think this is about 2005, true ?
> Roger Wolter[MSFT] ha scritto:
>
>

Restore backup differential on a differente server

I got TWO sqlserver .
The first is the main server , every one hour I make a differential
backup of a Db ( TestDB ) with a Job that save de file .BAK in a
folder of the second server .
Now I need to create a Job that restore the db on the second server
How can I do this ? I'm only able to restore the full backup not
differential backup :-(
please help me
thanks
Have you looked at this?
http://msdn2.microsoft.com/en-us/library/ms175510.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<emanuele.pasinetti@.gmail.com> wrote in message
news:1163436938.589340.226360@.f16g2000cwb.googlegr oups.com...
>I got TWO sqlserver .
> The first is the main server , every one hour I make a differential
> backup of a Db ( TestDB ) with a Job that save de file .BAK in a
> folder of the second server .
> Now I need to create a Job that restore the db on the second server
> How can I do this ? I'm only able to restore the full backup not
> differential backup :-(
> please help me
> thanks
>
|||I'm working with SqlServer 2000 , I think this is about 2005, true ?
Roger Wolter[MSFT] ha scritto:
[vbcol=seagreen]
> Have you looked at this?
> http://msdn2.microsoft.com/en-us/library/ms175510.aspx
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> <emanuele.pasinetti@.gmail.com> wrote in message
> news:1163436938.589340.226360@.f16g2000cwb.googlegr oups.com...
|||Sorry, I must have missed where you said your were using SQL 2000
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_7tv7.asp
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Pasi" <emanuele.pasinetti@.gmail.com> wrote in message
news:1163449604.753979.234130@.h48g2000cwc.googlegr oups.com...
> I'm working with SqlServer 2000 , I think this is about 2005, true ?
> Roger Wolter[MSFT] ha scritto:
>

Restore backup differential on a differente server

I got TWO sqlserver .
The first is the main server , every one hour I make a differential
backup of a Db ( TestDB ) with a Job that save de file .BAK in a
folder of the second server .
Now I need to create a Job that restore the db on the second server
How can I do this ? I'm only able to restore the full backup not
differential backup :-(
please help me
thanksHave you looked at this?
http://msdn2.microsoft.com/en-us/library/ms175510.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<emanuele.pasinetti@.gmail.com> wrote in message
news:1163436938.589340.226360@.f16g2000cwb.googlegroups.com...
>I got TWO sqlserver .
> The first is the main server , every one hour I make a differential
> backup of a Db ( TestDB ) with a Job that save de file .BAK in a
> folder of the second server .
> Now I need to create a Job that restore the db on the second server
> How can I do this ? I'm only able to restore the full backup not
> differential backup :-(
> please help me
> thanks
>|||I'm working with SqlServer 2000 , I think this is about 2005, true ?
Roger Wolter[MSFT] ha scritto:
> Have you looked at this?
> http://msdn2.microsoft.com/en-us/library/ms175510.aspx
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> <emanuele.pasinetti@.gmail.com> wrote in message
> news:1163436938.589340.226360@.f16g2000cwb.googlegroups.com...
> >I got TWO sqlserver .
> > The first is the main server , every one hour I make a differential
> > backup of a Db ( TestDB ) with a Job that save de file .BAK in a
> > folder of the second server .
> >
> > Now I need to create a Job that restore the db on the second server
> > How can I do this ? I'm only able to restore the full backup not
> > differential backup :-(
> > please help me
> > thanks
> >|||All you need to do is to schedule a job in which you have a job step executing the RESTORE DATABASE
command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Pasi" <emanuele.pasinetti@.gmail.com> wrote in message
news:1163449604.753979.234130@.h48g2000cwc.googlegroups.com...
> I'm working with SqlServer 2000 , I think this is about 2005, true ?
> Roger Wolter[MSFT] ha scritto:
>> Have you looked at this?
>> http://msdn2.microsoft.com/en-us/library/ms175510.aspx
>>
>> --
>> This posting is provided "AS IS" with no warranties, and confers no rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> <emanuele.pasinetti@.gmail.com> wrote in message
>> news:1163436938.589340.226360@.f16g2000cwb.googlegroups.com...
>> >I got TWO sqlserver .
>> > The first is the main server , every one hour I make a differential
>> > backup of a Db ( TestDB ) with a Job that save de file .BAK in a
>> > folder of the second server .
>> >
>> > Now I need to create a Job that restore the db on the second server
>> > How can I do this ? I'm only able to restore the full backup not
>> > differential backup :-(
>> > please help me
>> > thanks
>> >
>|||Sorry, I must have missed where you said your were using SQL 2000
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_7tv7.asp
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Pasi" <emanuele.pasinetti@.gmail.com> wrote in message
news:1163449604.753979.234130@.h48g2000cwc.googlegroups.com...
> I'm working with SqlServer 2000 , I think this is about 2005, true ?
> Roger Wolter[MSFT] ha scritto:
>> Have you looked at this?
>> http://msdn2.microsoft.com/en-us/library/ms175510.aspx
>>
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> <emanuele.pasinetti@.gmail.com> wrote in message
>> news:1163436938.589340.226360@.f16g2000cwb.googlegroups.com...
>> >I got TWO sqlserver .
>> > The first is the main server , every one hour I make a differential
>> > backup of a Db ( TestDB ) with a Job that save de file .BAK in a
>> > folder of the second server .
>> >
>> > Now I need to create a Job that restore the db on the second server
>> > How can I do this ? I'm only able to restore the full backup not
>> > differential backup :-(
>> > please help me
>> > thanks
>> >
>

Restore .BAK and incremental .TRN each day?

Hello,
Each morning, I have a plan fire at 2 AM that backs up my boss's
favorite database. I also have a 1 hour job that starts at 3 AM which
backs up the transaction log. The database is in FULL recovery and we
don't truncate the logs manually. I would like to know if anyone has
a script or suggestion for restoring the .TRN files all day long.
I can't spend $ on another product so TSQL is likely the answer. I do
not need the database operational at all times, as it is being
restored 'just in case'. I have read that restoring the .TRN files
WITH NORECOVERY is the best method as I don't want to lose anything.
Is it possible to do something like a JOB that uses XP_CMDSHELL to
list the .TRN files and just keeps restoring them? Or should I use an
external script completely? Also, what if the MASTER database
changes? I already looked into a DOS script that adds -m -c, restarts
MSSQL, restores the MASTER, removes the startup switches and then
restarts. Is that the right approach?
I just want to keep the two databases in sync, but cannot use
replication directly do to business rules. BTW, I am using an XCOPY
between the running MSSQL server and the 'just in case' one.
Any insight, TSQL, software recommendations or experiences would help.
Gracias,
TimTim,
What you are describing is referred to as Log Shipping. If you have
Enterprise edition of SQL Server you can use the maintenance wizard to set
this up. If you only have Std you can find scripts in the Resource Kit to
help with this. There is lots of information out there on this subject.
You can also do a google search for Log Shipping and I am sure you will find
some past posts that show some sample scripts.
--
Andrew J. Kelly
SQL Server MVP
"Tim" <SQLRESTORE@.CFAPOSTLE.COM> wrote in message
news:947a9f8a.0311211714.3f670726@.posting.google.com...
> Hello,
> Each morning, I have a plan fire at 2 AM that backs up my boss's
> favorite database. I also have a 1 hour job that starts at 3 AM which
> backs up the transaction log. The database is in FULL recovery and we
> don't truncate the logs manually. I would like to know if anyone has
> a script or suggestion for restoring the .TRN files all day long.
> I can't spend $ on another product so TSQL is likely the answer. I do
> not need the database operational at all times, as it is being
> restored 'just in case'. I have read that restoring the .TRN files
> WITH NORECOVERY is the best method as I don't want to lose anything.
> Is it possible to do something like a JOB that uses XP_CMDSHELL to
> list the .TRN files and just keeps restoring them? Or should I use an
> external script completely? Also, what if the MASTER database
> changes? I already looked into a DOS script that adds -m -c, restarts
> MSSQL, restores the MASTER, removes the startup switches and then
> restarts. Is that the right approach?
> I just want to keep the two databases in sync, but cannot use
> replication directly do to business rules. BTW, I am using an XCOPY
> between the running MSSQL server and the 'just in case' one.
> Any insight, TSQL, software recommendations or experiences would help.
> Gracias,
> Tim