Hi
I have a 60 GB sql 2000 STD edition database backup.
I take backup on a remote machine A where I can restore it.
But I have to restore the same database on another machine B where it can
not restore it.
I have already copied the file over a very slow network and when I say
restore and select the backup file I do not see any datafiles listed. I clic
k
on "VIEW CONTENTS" and there is nothing.
What could cause this as same backup seen on the Machine A is fine but when
moved to machine B I can not "view the contents" and restore it.
All the version across all these machines are same.
Tks
MangeshHi
I found the reason. The copy command is still running on the Machine A for
12 hrs.
I did not know that copy command creates the file size as that of source
when it starts copy unlike ftp.
Thanks
Mangesh
"Mangesh Deshpande" wrote:
> Hi
> I have a 60 GB sql 2000 STD edition database backup.
> I take backup on a remote machine A where I can restore it.
> But I have to restore the same database on another machine B where it can
> not restore it.
> I have already copied the file over a very slow network and when I say
> restore and select the backup file I do not see any datafiles listed. I cl
ick
> on "VIEW CONTENTS" and there is nothing.
> What could cause this as same backup seen on the Machine A is fine but whe
n
> moved to machine B I can not "view the contents" and restore it.
> All the version across all these machines are same.
> Tks
> Mangesh|||Hi Mangesh,
You can try using Query Analyzer from ServerB to restore DB's from remote
locations. It is possible the backup gets corrupt during the copy. At leas
t
you can try listing the contents remotely; try this:
--
-- i.e. EXEC xp_cmdshell 'command' <-- remove any <ENTER> bewteen quotes (''
)
-- ServerA is the remote DB Server
-- Share the remote backup folder, in this example is ShareDriveA
-- account is a domain account with access to the shared folder
-- accountpassword (account password)
-- ACCOUNTDOMAIN is the domain where 'account' belongs to
EXEC xp_cmdshell 'net use \\ServerA\ShareDriveA accountpassword
/USER:ACCOUNTDOMAIN\account'
GO
-- to test if the account you used has access to the remote folder
EXEC xp_cmdshell 'dir \\ServerA\ShareFolderA\BackupFolder'
GO
-- to list the backup contents
RESTORE FILELISTONLY
FROM DISK = '\\ServerA\ShareFolder\BackupFolder\Back
upFile.BAK'
-- to finally restore the backup
RESTORE DATABASE DatabaseName
FROM DISK = '\\ServerA\ShareFolder\BackupFolder\Back
upFile.BAK'
WITH REPLACE,
MOVE 'LogicalFileName_Data' TO 'C:\SQLdata\DatabaseName.MDF',
MOVE 'LogicalFileName_Log' TO 'C:\SQLdata\DatabaseName.LDF'
--
"Mangesh Deshpande" wrote:
> Hi
> I have a 60 GB sql 2000 STD edition database backup.
> I take backup on a remote machine A where I can restore it.
> But I have to restore the same database on another machine B where it can
> not restore it.
> I have already copied the file over a very slow network and when I say
> restore and select the backup file I do not see any datafiles listed. I cl
ick
> on "VIEW CONTENTS" and there is nothing.
> What could cause this as same backup seen on the Machine A is fine but whe
n
> moved to machine B I can not "view the contents" and restore it.
> All the version across all these machines are same.
> Tks
> Mangesh
Showing posts with label hii. Show all posts
Showing posts with label hii. Show all posts
Tuesday, March 20, 2012
restore question on sql2000
Hi
I have a 60 GB sql 2000 STD edition database backup.
I take backup on a remote machine A where I can restore it.
But I have to restore the same database on another machine B where it can
not restore it.
I have already copied the file over a very slow network and when I say
restore and select the backup file I do not see any datafiles listed. I click
on "VIEW CONTENTS" and there is nothing.
What could cause this as same backup seen on the Machine A is fine but when
moved to machine B I can not "view the contents" and restore it.
All the version across all these machines are same.
Tks
Mangesh
Hi
I found the reason. The copy command is still running on the Machine A for
12 hrs.
I did not know that copy command creates the file size as that of source
when it starts copy unlike ftp.
Thanks
Mangesh
"Mangesh Deshpande" wrote:
> Hi
> I have a 60 GB sql 2000 STD edition database backup.
> I take backup on a remote machine A where I can restore it.
> But I have to restore the same database on another machine B where it can
> not restore it.
> I have already copied the file over a very slow network and when I say
> restore and select the backup file I do not see any datafiles listed. I click
> on "VIEW CONTENTS" and there is nothing.
> What could cause this as same backup seen on the Machine A is fine but when
> moved to machine B I can not "view the contents" and restore it.
> All the version across all these machines are same.
> Tks
> Mangesh
|||Hi Mangesh,
You can try using Query Analyzer from ServerB to restore DB's from remote
locations. It is possible the backup gets corrupt during the copy. At least
you can try listing the contents remotely; try this:
-- i.e. EXEC xp_cmdshell 'command' <-- remove any <ENTER> bewteen quotes ('')
-- ServerA is the remote DB Server
-- Share the remote backup folder, in this example is ShareDriveA
-- account is a domain account with access to the shared folder
-- accountpassword (account password)
-- ACCOUNTDOMAIN is the domain where 'account' belongs to
EXEC xp_cmdshell 'net use \\ServerA\ShareDriveA accountpassword
/USER:ACCOUNTDOMAIN\account'
GO
-- to test if the account you used has access to the remote folder
EXEC xp_cmdshell 'dir \\ServerA\ShareFolderA\BackupFolder'
GO
-- to list the backup contents
RESTORE FILELISTONLY
FROM DISK = '\\ServerA\ShareFolder\BackupFolder\BackupFile.BAK '
-- to finally restore the backup
RESTORE DATABASE DatabaseName
FROM DISK = '\\ServerA\ShareFolder\BackupFolder\BackupFile.BAK '
WITH REPLACE,
MOVE 'LogicalFileName_Data' TO 'C:\SQLdata\DatabaseName.MDF',
MOVE 'LogicalFileName_Log' TO 'C:\SQLdata\DatabaseName.LDF'
"Mangesh Deshpande" wrote:
> Hi
> I have a 60 GB sql 2000 STD edition database backup.
> I take backup on a remote machine A where I can restore it.
> But I have to restore the same database on another machine B where it can
> not restore it.
> I have already copied the file over a very slow network and when I say
> restore and select the backup file I do not see any datafiles listed. I click
> on "VIEW CONTENTS" and there is nothing.
> What could cause this as same backup seen on the Machine A is fine but when
> moved to machine B I can not "view the contents" and restore it.
> All the version across all these machines are same.
> Tks
> Mangesh
I have a 60 GB sql 2000 STD edition database backup.
I take backup on a remote machine A where I can restore it.
But I have to restore the same database on another machine B where it can
not restore it.
I have already copied the file over a very slow network and when I say
restore and select the backup file I do not see any datafiles listed. I click
on "VIEW CONTENTS" and there is nothing.
What could cause this as same backup seen on the Machine A is fine but when
moved to machine B I can not "view the contents" and restore it.
All the version across all these machines are same.
Tks
Mangesh
Hi
I found the reason. The copy command is still running on the Machine A for
12 hrs.
I did not know that copy command creates the file size as that of source
when it starts copy unlike ftp.
Thanks
Mangesh
"Mangesh Deshpande" wrote:
> Hi
> I have a 60 GB sql 2000 STD edition database backup.
> I take backup on a remote machine A where I can restore it.
> But I have to restore the same database on another machine B where it can
> not restore it.
> I have already copied the file over a very slow network and when I say
> restore and select the backup file I do not see any datafiles listed. I click
> on "VIEW CONTENTS" and there is nothing.
> What could cause this as same backup seen on the Machine A is fine but when
> moved to machine B I can not "view the contents" and restore it.
> All the version across all these machines are same.
> Tks
> Mangesh
|||Hi Mangesh,
You can try using Query Analyzer from ServerB to restore DB's from remote
locations. It is possible the backup gets corrupt during the copy. At least
you can try listing the contents remotely; try this:
-- i.e. EXEC xp_cmdshell 'command' <-- remove any <ENTER> bewteen quotes ('')
-- ServerA is the remote DB Server
-- Share the remote backup folder, in this example is ShareDriveA
-- account is a domain account with access to the shared folder
-- accountpassword (account password)
-- ACCOUNTDOMAIN is the domain where 'account' belongs to
EXEC xp_cmdshell 'net use \\ServerA\ShareDriveA accountpassword
/USER:ACCOUNTDOMAIN\account'
GO
-- to test if the account you used has access to the remote folder
EXEC xp_cmdshell 'dir \\ServerA\ShareFolderA\BackupFolder'
GO
-- to list the backup contents
RESTORE FILELISTONLY
FROM DISK = '\\ServerA\ShareFolder\BackupFolder\BackupFile.BAK '
-- to finally restore the backup
RESTORE DATABASE DatabaseName
FROM DISK = '\\ServerA\ShareFolder\BackupFolder\BackupFile.BAK '
WITH REPLACE,
MOVE 'LogicalFileName_Data' TO 'C:\SQLdata\DatabaseName.MDF',
MOVE 'LogicalFileName_Log' TO 'C:\SQLdata\DatabaseName.LDF'
"Mangesh Deshpande" wrote:
> Hi
> I have a 60 GB sql 2000 STD edition database backup.
> I take backup on a remote machine A where I can restore it.
> But I have to restore the same database on another machine B where it can
> not restore it.
> I have already copied the file over a very slow network and when I say
> restore and select the backup file I do not see any datafiles listed. I click
> on "VIEW CONTENTS" and there is nothing.
> What could cause this as same backup seen on the Machine A is fine but when
> moved to machine B I can not "view the contents" and restore it.
> All the version across all these machines are same.
> Tks
> Mangesh
restore question
Hi
I am taking a backup of SQLSERVER on remote machine. I need to restore the
database on a daily basis on the remote machine.
I take backup of transaction log using maintenance plan every 30 mins.
It generates 48 transaciton log per day.
How can write a script to restore the database applying those 48 logs.
I can create linked server to production but what table keeps the backup log
information that I can use to build this.
Thanks
Mangesh
I keep my own table to track which log files I've applied
I store the table in a DB named "Admin"
then I select the logfiles on the file system that have not yet been applied
sorted in datetime order for example
then I apply them one at a time.
hope this makes sense.
I can dig up the scripts if you really need (But it will cost you some
Sushi)
Greg Jackson
PDX, Oregon
|||You can get all the DB backup history from the msdb database.
Specifically, you get the media_set_id values for the backups you wish
to restore from dbo.backupset based on DB name, DB vs LOG backups, dates
& times, etc., etc. And then you can get the physical file location for
those backups from dbo.backupmediafamily (using
dbo.backupset.media_set_id, which corresponds to
dbo.backupmediafamily.media_set_id). Something like:
select bs.backup_start_date, bs.type, bmf.physical_device_name
from msdb.dbo.backupset bs
inner join msdb.dbo.backupmediafamily bmf
on bmf.media_set_id = bs.media_set_id
where bs.database_name = N'MyDB'
order by bs.backup_start_date
Based on that kind of query it should be a piece of cake to automate a
restore procedure given some constraints around the restore (like
DBName, start datetime, finish datetime, etc.) also not forgetting to
restore a "D" backup followed by zero or more "L" backups.
HTH,
Mike.
PS. Do I get Sushi now?
pdxJaxon wrote:
> I keep my own table to track which log files I've applied
> I store the table in a DB named "Admin"
> then I select the logfiles on the file system that have not yet been applied
> sorted in datetime order for example
> then I apply them one at a time.
>
> hope this makes sense.
> I can dig up the scripts if you really need (But it will cost you some
> Sushi)
>
> Greg Jackson
> PDX, Oregon
>
C
|||Hi Mangesh,
If your objective is to have a standby database, have you thought of SQL
Server log-shipping as an option? Its pretty simple to set-up and get it
running.
Thanks
Yogish
I am taking a backup of SQLSERVER on remote machine. I need to restore the
database on a daily basis on the remote machine.
I take backup of transaction log using maintenance plan every 30 mins.
It generates 48 transaciton log per day.
How can write a script to restore the database applying those 48 logs.
I can create linked server to production but what table keeps the backup log
information that I can use to build this.
Thanks
Mangesh
I keep my own table to track which log files I've applied
I store the table in a DB named "Admin"
then I select the logfiles on the file system that have not yet been applied
sorted in datetime order for example
then I apply them one at a time.
hope this makes sense.
I can dig up the scripts if you really need (But it will cost you some
Sushi)
Greg Jackson
PDX, Oregon
|||You can get all the DB backup history from the msdb database.
Specifically, you get the media_set_id values for the backups you wish
to restore from dbo.backupset based on DB name, DB vs LOG backups, dates
& times, etc., etc. And then you can get the physical file location for
those backups from dbo.backupmediafamily (using
dbo.backupset.media_set_id, which corresponds to
dbo.backupmediafamily.media_set_id). Something like:
select bs.backup_start_date, bs.type, bmf.physical_device_name
from msdb.dbo.backupset bs
inner join msdb.dbo.backupmediafamily bmf
on bmf.media_set_id = bs.media_set_id
where bs.database_name = N'MyDB'
order by bs.backup_start_date
Based on that kind of query it should be a piece of cake to automate a
restore procedure given some constraints around the restore (like
DBName, start datetime, finish datetime, etc.) also not forgetting to
restore a "D" backup followed by zero or more "L" backups.
HTH,
Mike.
PS. Do I get Sushi now?
pdxJaxon wrote:
> I keep my own table to track which log files I've applied
> I store the table in a DB named "Admin"
> then I select the logfiles on the file system that have not yet been applied
> sorted in datetime order for example
> then I apply them one at a time.
>
> hope this makes sense.
> I can dig up the scripts if you really need (But it will cost you some
> Sushi)
>
> Greg Jackson
> PDX, Oregon
>
C
|||Hi Mangesh,
If your objective is to have a standby database, have you thought of SQL
Server log-shipping as an option? Its pretty simple to set-up and get it
running.
Thanks
Yogish
restore question
Hi
I am taking a backup of SQLSERVER on remote machine. I need to restore the
database on a daily basis on the remote machine.
I take backup of transaction log using maintenance plan every 30 mins.
It generates 48 transaciton log per day.
How can write a script to restore the database applying those 48 logs.
I can create linked server to production but what table keeps the backup log
information that I can use to build this.
Thanks
MangeshI keep my own table to track which log files I've applied
I store the table in a DB named "Admin"
then I select the logfiles on the file system that have not yet been applied
sorted in datetime order for example
then I apply them one at a time.
hope this makes sense.
I can dig up the scripts if you really need (But it will cost you some
Sushi)
Greg Jackson
PDX, Oregon|||You can get all the DB backup history from the msdb database.
Specifically, you get the media_set_id values for the backups you wish
to restore from dbo.backupset based on DB name, DB vs LOG backups, dates
& times, etc., etc. And then you can get the physical file location for
those backups from dbo.backupmediafamily (using
dbo.backupset.media_set_id, which corresponds to
dbo.backupmediafamily.media_set_id). Something like:
select bs.backup_start_date, bs.type, bmf.physical_device_name
from msdb.dbo.backupset bs
inner join msdb.dbo.backupmediafamily bmf
on bmf.media_set_id = bs.media_set_id
where bs.database_name = N'MyDB'
order by bs.backup_start_date
Based on that kind of query it should be a piece of cake to automate a
restore procedure given some constraints around the restore (like
DBName, start datetime, finish datetime, etc.) also not forgetting to
restore a "D" backup followed by zero or more "L" backups.
HTH,
Mike.
PS. Do I get Sushi now?
pdxJaxon wrote:
> I keep my own table to track which log files I've applied
> I store the table in a DB named "Admin"
> then I select the logfiles on the file system that have not yet been appli
ed
> sorted in datetime order for example
> then I apply them one at a time.
>
> hope this makes sense.
> I can dig up the scripts if you really need (But it will cost you some
> Sushi)
>
> Greg Jackson
> PDX, Oregon
>
C|||Hi Mangesh,
If your objective is to have a standby database, have you thought of SQL
Server log-shipping as an option? Its pretty simple to set-up and get it
running.
Thanks
Yogish
I am taking a backup of SQLSERVER on remote machine. I need to restore the
database on a daily basis on the remote machine.
I take backup of transaction log using maintenance plan every 30 mins.
It generates 48 transaciton log per day.
How can write a script to restore the database applying those 48 logs.
I can create linked server to production but what table keeps the backup log
information that I can use to build this.
Thanks
MangeshI keep my own table to track which log files I've applied
I store the table in a DB named "Admin"
then I select the logfiles on the file system that have not yet been applied
sorted in datetime order for example
then I apply them one at a time.
hope this makes sense.
I can dig up the scripts if you really need (But it will cost you some
Sushi)
Greg Jackson
PDX, Oregon|||You can get all the DB backup history from the msdb database.
Specifically, you get the media_set_id values for the backups you wish
to restore from dbo.backupset based on DB name, DB vs LOG backups, dates
& times, etc., etc. And then you can get the physical file location for
those backups from dbo.backupmediafamily (using
dbo.backupset.media_set_id, which corresponds to
dbo.backupmediafamily.media_set_id). Something like:
select bs.backup_start_date, bs.type, bmf.physical_device_name
from msdb.dbo.backupset bs
inner join msdb.dbo.backupmediafamily bmf
on bmf.media_set_id = bs.media_set_id
where bs.database_name = N'MyDB'
order by bs.backup_start_date
Based on that kind of query it should be a piece of cake to automate a
restore procedure given some constraints around the restore (like
DBName, start datetime, finish datetime, etc.) also not forgetting to
restore a "D" backup followed by zero or more "L" backups.
HTH,
Mike.
PS. Do I get Sushi now?
pdxJaxon wrote:
> I keep my own table to track which log files I've applied
> I store the table in a DB named "Admin"
> then I select the logfiles on the file system that have not yet been appli
ed
> sorted in datetime order for example
> then I apply them one at a time.
>
> hope this makes sense.
> I can dig up the scripts if you really need (But it will cost you some
> Sushi)
>
> Greg Jackson
> PDX, Oregon
>
C|||Hi Mangesh,
If your objective is to have a standby database, have you thought of SQL
Server log-shipping as an option? Its pretty simple to set-up and get it
running.
Thanks
Yogish
Monday, March 12, 2012
restore problem
Hi
i started a restore on a little db, it is still running (more than 15 mins),
how can i understand what is it doing?
Carlo
How little is the database?
In the future you can use the WITH STATS option to show you the percent
complete.
I am guessing that the SQL Server's disks are busy creating the database
files that will hold the data.
Keith
"Carlo" <marcocci@.ingv.it> wrote in message
news:utG3YN8fEHA.704@.TK2MSFTNGP09.phx.gbl...
> Hi
> i started a restore on a little db, it is still running (more than 15
mins),
> how can i understand what is it doing?
> Carlo
> --
>
>
|||the size of db is around 9 mb, it is a test db, it is still loading, how can
i stop the restore?
|||Are you able to see the new data/log files created in OS?
Also check to see if there's blocks.
"Carlo" <marcocci@.ingv.it> wrote in message
news:%23KaggZ8fEHA.2984@.tk2msftngp13.phx.gbl...
> the size of db is around 9 mb, it is a test db, it is still loading, how
can
> i stop the restore?
>
|||9MB. That is darn small. It has not finished yet? 22 minutes have gone by
since your original post and your second post. That is a too long to wait
for a 9MB database.
What did you use to begin the restore? Query Analyzer? Enterprise Manager?
Perhaps you could try closing the app. You could also kill that specific
connection, but that would be harsh.
Keith
"Carlo" <marcocci@.ingv.it> wrote in message
news:%23KaggZ8fEHA.2984@.tk2msftngp13.phx.gbl...
> the size of db is around 9 mb, it is a test db, it is still loading, how
can
> i stop the restore?
>
|||i stop the service and start it again, the db is alwais loading, i found in
the log this message:
Bypassing recovery for database 'db_rsnd' because it is marked IN LOAD.
what have i to do?
i'd like to know if i can delete the db from the server, but i cant do it
from the manager because it is loading and i suppose i cant delete it from
the console too. is there a way to delete the "row" in the master db that
let it know that my db exists? i hope you understand what i mean.
Carlo
********************************************
Carlo Marcocci
Istituto Nazionale di Geofisica e Vulcanologia
Via di Vigna Murata 605, 00143 Roma
Tel. +39-6-51860391
Fax. +39-6-51860507
mailto: marcocci@.ingv.it
******************************************** L&P
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> ha scritto nel messaggio
news:ufwtZL9fEHA.3024@.TK2MSFTNGP10.phx.gbl...
> 9MB. That is darn small. It has not finished yet? 22 minutes have gone
by
> since your original post and your second post. That is a too long to wait
> for a 9MB database.
> What did you use to begin the restore? Query Analyzer? Enterprise
Manager?
> Perhaps you could try closing the app. You could also kill that specific
> connection, but that would be harsh.
> --
> Keith
>
> "Carlo" <marcocci@.ingv.it> wrote in message
> news:%23KaggZ8fEHA.2984@.tk2msftngp13.phx.gbl...
> can
>
|||may i use this ?
UPDATE sysdatabases
SET status=16
WHERE name='daCancellare'
does it change anything?
********************************************
Carlo Marcocci
Istituto Nazionale di Geofisica e Vulcanologia
Via di Vigna Murata 605, 00143 Roma
Tel. +39-6-51860391
Fax. +39-6-51860507
mailto: marcocci@.ingv.it
******************************************** L&P
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> ha scritto nel messaggio
news:ufwtZL9fEHA.3024@.TK2MSFTNGP10.phx.gbl...
> 9MB. That is darn small. It has not finished yet? 22 minutes have gone
by
> since your original post and your second post. That is a too long to wait
> for a 9MB database.
> What did you use to begin the restore? Query Analyzer? Enterprise
Manager?
> Perhaps you could try closing the app. You could also kill that specific
> connection, but that would be harsh.
> --
> Keith
>
> "Carlo" <marcocci@.ingv.it> wrote in message
> news:%23KaggZ8fEHA.2984@.tk2msftngp13.phx.gbl...
> can
>
|||I would try using DROP DATABASE
Information within Books Online (within the SQL Server program group).
Keith
"Carlo" <marcocci@.ingv.it> wrote in message
news:OyzQU09fEHA.3916@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> may i use this ?
> UPDATE sysdatabases
> SET status=16
> WHERE name='daCancellare'
> does it change anything?
>
> --
>
> ********************************************
> Carlo Marcocci
> Istituto Nazionale di Geofisica e Vulcanologia
> Via di Vigna Murata 605, 00143 Roma
> Tel. +39-6-51860391
> Fax. +39-6-51860507
> mailto: marcocci@.ingv.it
> ******************************************** L&P
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> ha scritto nel messaggio
> news:ufwtZL9fEHA.3024@.TK2MSFTNGP10.phx.gbl...
gone[vbcol=seagreen]
> by
wait[vbcol=seagreen]
> Manager?
specific[vbcol=seagreen]
how
>
|||Read up on DROP DATABASE within Books Online (within the SQL Server program
group).
Keith
"Carlo" <marcocci@.ingv.it> wrote in message
news:ex%23jop9fEHA.3932@.TK2MSFTNGP09.phx.gbl...
> i stop the service and start it again, the db is alwais loading, i found
in[vbcol=seagreen]
> the log this message:
> Bypassing recovery for database 'db_rsnd' because it is marked IN LOAD.
> what have i to do?
> i'd like to know if i can delete the db from the server, but i cant do it
> from the manager because it is loading and i suppose i cant delete it from
> the console too. is there a way to delete the "row" in the master db that
> let it know that my db exists? i hope you understand what i mean.
> Carlo
> --
>
> ********************************************
> Carlo Marcocci
> Istituto Nazionale di Geofisica e Vulcanologia
> Via di Vigna Murata 605, 00143 Roma
> Tel. +39-6-51860391
> Fax. +39-6-51860507
> mailto: marcocci@.ingv.it
> ******************************************** L&P
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> ha scritto nel messaggio
> news:ufwtZL9fEHA.3024@.TK2MSFTNGP10.phx.gbl...
gone[vbcol=seagreen]
> by
wait[vbcol=seagreen]
> Manager?
specific[vbcol=seagreen]
how
>
|||By now you have fixed your problem. Some added thoughts.
I had a similar issue running SQL Server on a small PC that runs 24x7 (far
too little memory). There were times when a process that normally took 15
minutes ran for more than 3 hours.
1. Sometimes a reboot would speed up the process. Evidently, SQL Server
would grow and grow far beyond the physical memory. The PC spent all its
time paging memory in and out. There is a property to prevent this and set
the maximum memory that SQL Server can use.
2. I also had to set logging to a simple type rather than the full logging.
This is not a production database and we perform daily backups since it is
small. Simple logging is all that is required. With full logging, my tiny
databases (under 50 MB) grew to almost a gigabyte.
3. Wrote a stored procedure that runs in the SQL Server job scheduler every
night that performs a reindex on every table, then gets rid of the log ,
then shrinks the database. Issue has pretty much gone away.
"Carlo" <marcocci@.ingv.it> wrote in message
news:utG3YN8fEHA.704@.TK2MSFTNGP09.phx.gbl...
> Hi
> i started a restore on a little db, it is still running (more than 15
mins),
> how can i understand what is it doing?
> Carlo
> --
>
>
i started a restore on a little db, it is still running (more than 15 mins),
how can i understand what is it doing?
Carlo
How little is the database?
In the future you can use the WITH STATS option to show you the percent
complete.
I am guessing that the SQL Server's disks are busy creating the database
files that will hold the data.
Keith
"Carlo" <marcocci@.ingv.it> wrote in message
news:utG3YN8fEHA.704@.TK2MSFTNGP09.phx.gbl...
> Hi
> i started a restore on a little db, it is still running (more than 15
mins),
> how can i understand what is it doing?
> Carlo
> --
>
>
|||the size of db is around 9 mb, it is a test db, it is still loading, how can
i stop the restore?
|||Are you able to see the new data/log files created in OS?
Also check to see if there's blocks.
"Carlo" <marcocci@.ingv.it> wrote in message
news:%23KaggZ8fEHA.2984@.tk2msftngp13.phx.gbl...
> the size of db is around 9 mb, it is a test db, it is still loading, how
can
> i stop the restore?
>
|||9MB. That is darn small. It has not finished yet? 22 minutes have gone by
since your original post and your second post. That is a too long to wait
for a 9MB database.
What did you use to begin the restore? Query Analyzer? Enterprise Manager?
Perhaps you could try closing the app. You could also kill that specific
connection, but that would be harsh.
Keith
"Carlo" <marcocci@.ingv.it> wrote in message
news:%23KaggZ8fEHA.2984@.tk2msftngp13.phx.gbl...
> the size of db is around 9 mb, it is a test db, it is still loading, how
can
> i stop the restore?
>
|||i stop the service and start it again, the db is alwais loading, i found in
the log this message:
Bypassing recovery for database 'db_rsnd' because it is marked IN LOAD.
what have i to do?
i'd like to know if i can delete the db from the server, but i cant do it
from the manager because it is loading and i suppose i cant delete it from
the console too. is there a way to delete the "row" in the master db that
let it know that my db exists? i hope you understand what i mean.
Carlo
********************************************
Carlo Marcocci
Istituto Nazionale di Geofisica e Vulcanologia
Via di Vigna Murata 605, 00143 Roma
Tel. +39-6-51860391
Fax. +39-6-51860507
mailto: marcocci@.ingv.it
******************************************** L&P
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> ha scritto nel messaggio
news:ufwtZL9fEHA.3024@.TK2MSFTNGP10.phx.gbl...
> 9MB. That is darn small. It has not finished yet? 22 minutes have gone
by
> since your original post and your second post. That is a too long to wait
> for a 9MB database.
> What did you use to begin the restore? Query Analyzer? Enterprise
Manager?
> Perhaps you could try closing the app. You could also kill that specific
> connection, but that would be harsh.
> --
> Keith
>
> "Carlo" <marcocci@.ingv.it> wrote in message
> news:%23KaggZ8fEHA.2984@.tk2msftngp13.phx.gbl...
> can
>
|||may i use this ?
UPDATE sysdatabases
SET status=16
WHERE name='daCancellare'
does it change anything?
********************************************
Carlo Marcocci
Istituto Nazionale di Geofisica e Vulcanologia
Via di Vigna Murata 605, 00143 Roma
Tel. +39-6-51860391
Fax. +39-6-51860507
mailto: marcocci@.ingv.it
******************************************** L&P
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> ha scritto nel messaggio
news:ufwtZL9fEHA.3024@.TK2MSFTNGP10.phx.gbl...
> 9MB. That is darn small. It has not finished yet? 22 minutes have gone
by
> since your original post and your second post. That is a too long to wait
> for a 9MB database.
> What did you use to begin the restore? Query Analyzer? Enterprise
Manager?
> Perhaps you could try closing the app. You could also kill that specific
> connection, but that would be harsh.
> --
> Keith
>
> "Carlo" <marcocci@.ingv.it> wrote in message
> news:%23KaggZ8fEHA.2984@.tk2msftngp13.phx.gbl...
> can
>
|||I would try using DROP DATABASE
Information within Books Online (within the SQL Server program group).
Keith
"Carlo" <marcocci@.ingv.it> wrote in message
news:OyzQU09fEHA.3916@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> may i use this ?
> UPDATE sysdatabases
> SET status=16
> WHERE name='daCancellare'
> does it change anything?
>
> --
>
> ********************************************
> Carlo Marcocci
> Istituto Nazionale di Geofisica e Vulcanologia
> Via di Vigna Murata 605, 00143 Roma
> Tel. +39-6-51860391
> Fax. +39-6-51860507
> mailto: marcocci@.ingv.it
> ******************************************** L&P
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> ha scritto nel messaggio
> news:ufwtZL9fEHA.3024@.TK2MSFTNGP10.phx.gbl...
gone[vbcol=seagreen]
> by
wait[vbcol=seagreen]
> Manager?
specific[vbcol=seagreen]
how
>
|||Read up on DROP DATABASE within Books Online (within the SQL Server program
group).
Keith
"Carlo" <marcocci@.ingv.it> wrote in message
news:ex%23jop9fEHA.3932@.TK2MSFTNGP09.phx.gbl...
> i stop the service and start it again, the db is alwais loading, i found
in[vbcol=seagreen]
> the log this message:
> Bypassing recovery for database 'db_rsnd' because it is marked IN LOAD.
> what have i to do?
> i'd like to know if i can delete the db from the server, but i cant do it
> from the manager because it is loading and i suppose i cant delete it from
> the console too. is there a way to delete the "row" in the master db that
> let it know that my db exists? i hope you understand what i mean.
> Carlo
> --
>
> ********************************************
> Carlo Marcocci
> Istituto Nazionale di Geofisica e Vulcanologia
> Via di Vigna Murata 605, 00143 Roma
> Tel. +39-6-51860391
> Fax. +39-6-51860507
> mailto: marcocci@.ingv.it
> ******************************************** L&P
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> ha scritto nel messaggio
> news:ufwtZL9fEHA.3024@.TK2MSFTNGP10.phx.gbl...
gone[vbcol=seagreen]
> by
wait[vbcol=seagreen]
> Manager?
specific[vbcol=seagreen]
how
>
|||By now you have fixed your problem. Some added thoughts.
I had a similar issue running SQL Server on a small PC that runs 24x7 (far
too little memory). There were times when a process that normally took 15
minutes ran for more than 3 hours.
1. Sometimes a reboot would speed up the process. Evidently, SQL Server
would grow and grow far beyond the physical memory. The PC spent all its
time paging memory in and out. There is a property to prevent this and set
the maximum memory that SQL Server can use.
2. I also had to set logging to a simple type rather than the full logging.
This is not a production database and we perform daily backups since it is
small. Simple logging is all that is required. With full logging, my tiny
databases (under 50 MB) grew to almost a gigabyte.
3. Wrote a stored procedure that runs in the SQL Server job scheduler every
night that performs a reindex on every table, then gets rid of the log ,
then shrinks the database. Issue has pretty much gone away.
"Carlo" <marcocci@.ingv.it> wrote in message
news:utG3YN8fEHA.704@.TK2MSFTNGP09.phx.gbl...
> Hi
> i started a restore on a little db, it is still running (more than 15
mins),
> how can i understand what is it doing?
> Carlo
> --
>
>
restore problem
hi
I have a problem with restore db (can not obtain exclusice access to db)
the question is:
how to obtain exclusive access to database using jdbc driver ?
how to log off all users and prerform restore ?
thanks for help
Artur
| From: "Monika" <monika@.o2.pl>
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| Subject: restore problem
| Date: Fri, 22 Oct 2004 09:57:42 +0200
| Organization: tp.internet - http://www.tpi.pl/
| Lines: 12
| Message-ID: <claeo7$80f$1@.nemesis.news.tpi.pl>
| NNTP-Posting-Host: acn10.neoplus.adsl.tpnet.pl
| X-Trace: nemesis.news.tpi.pl 1098432071 8207 83.25.65.10 (22 Oct 2004
08:01:11 GMT)
| X-Complaints-To: usenet@.tpi.pl
| NNTP-Posting-Date: Fri, 22 Oct 2004 08:01:11 +0000 (UTC)
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFT NGP08.phx.gbl!newsfeed00.s
ul.t-online.de!t-online.de!news.zanker.org!nntp.idg.pl!newsfeed.gaz eta.pl!ne
ws.nask.pl!newsfeed.tpinternet.pl!atlantis.news.tp i.pl!news.tpi.pl!not-for-m
ail
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6416
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| hi
|
| I have a problem with restore db (can not obtain exclusice access to db)
| the question is:
| how to obtain exclusive access to database using jdbc driver ?
| how to log off all users and prerform restore ?
|
| thanks for help
|
| Artur
|
|
|
Hello Artur,
I'm not clear on what your end goal is. Are you asking how to restore a
database, or are you asking how to restrict database access to a single
user?
In SQL Server, you can issue an ALTER DATABASE command to set the
SINGLE_USER state option for a given database. This effectively allows
only one user to access the database at a time. This is a Transact-SQL
command, and it is not specific to JDBC. The syntax for this operation
would be:
ALTER DATABASE pubs SET SINGLE_USER WITH ROLLBACK IMMEDIATE
So, you could connect to your database using JDBC code and execute the
statement above. This will rollback any pending transactions and then
close all other connections to the same database.
===========
If you are trying to restore a database, then you should use the RESTORE
DATABASE Transact-SQL command:
USE master
GO
RESTORE DATABASE pubs FROM DISK='c:\pubs.bak' WITH REPLACE
You cannot be using the database that you intend to restore. This is why
the command above changes the context to Master first. If you are finding
yourself contending with access to the database for the restore operation,
you may consider taking the database offline first and then restoring a new
copy.
I recommend consulting the SQL Server 2000 Books Online for further
information.
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
|||Hello
my goal is to write a code (java), which will restore a database from file
I've tried to do it by executing sql statement
conn.createStatement().executeUpdate("RESTORE DATABASE pubs FROM
DISK='c:\pubs.bak' WITH REPLACE")
but I've got the following exception:
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer]Exclusive access could not be obtained because the database
is in use.
my question is: how to restore a database from java (assuming that some
clients may be connected to database from another pc's) ?
(my first idea was to force exclusive access - but how to do it ?)
thanks again
Artur
Uytkownik ""Carb Simien [MSFT]"" <CarbinoS@.online.microsoft.com> napisa w
wiadomoci news:ZKRQvy4uEHA.3436@.cpmsftngxa10.phx.gbl...
> --
> | From: "Monika" <monika@.o2.pl>
> | Newsgroups: microsoft.public.sqlserver.jdbcdriver
> | Subject: restore problem
> | Date: Fri, 22 Oct 2004 09:57:42 +0200
> | Organization: tp.internet - http://www.tpi.pl/
> | Lines: 12
> | Message-ID: <claeo7$80f$1@.nemesis.news.tpi.pl>
> | NNTP-Posting-Host: acn10.neoplus.adsl.tpnet.pl
> | X-Trace: nemesis.news.tpi.pl 1098432071 8207 83.25.65.10 (22 Oct 2004
> 08:01:11 GMT)
> | X-Complaints-To: usenet@.tpi.pl
> | NNTP-Posting-Date: Fri, 22 Oct 2004 08:01:11 +0000 (UTC)
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
> | Path:
>
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFT NGP08.phx.gbl!newsfeed00.s
>
ul.t-online.de!t-online.de!news.zanker.org!nntp.idg.pl!newsfeed.gaz eta.pl!ne
>
ws.nask.pl!newsfeed.tpinternet.pl!atlantis.news.tp i.pl!news.tpi.pl!not-for-m
> ail
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6416
> | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
> |
> | hi
> |
> | I have a problem with restore db (can not obtain exclusice access to db)
> | the question is:
> | how to obtain exclusive access to database using jdbc driver ?
> | how to log off all users and prerform restore ?
> |
> | thanks for help
> |
> | Artur
> |
> |
> |
> Hello Artur,
> I'm not clear on what your end goal is. Are you asking how to restore a
> database, or are you asking how to restrict database access to a single
> user?
> In SQL Server, you can issue an ALTER DATABASE command to set the
> SINGLE_USER state option for a given database. This effectively allows
> only one user to access the database at a time. This is a Transact-SQL
> command, and it is not specific to JDBC. The syntax for this operation
> would be:
> ALTER DATABASE pubs SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> So, you could connect to your database using JDBC code and execute the
> statement above. This will rollback any pending transactions and then
> close all other connections to the same database.
> ===========
> If you are trying to restore a database, then you should use the RESTORE
> DATABASE Transact-SQL command:
> USE master
> GO
> RESTORE DATABASE pubs FROM DISK='c:\pubs.bak' WITH REPLACE
> You cannot be using the database that you intend to restore. This is why
> the command above changes the context to Master first. If you are finding
> yourself contending with access to the database for the restore operation,
> you may consider taking the database offline first and then restoring a
new
> copy.
> I recommend consulting the SQL Server 2000 Books Online for further
> information.
> Carb Simien, MCSE MCDBA MCAD
> Microsoft Developer Support - Web Data
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
>
|||| From: "Artur" <monika@.o2.pl>
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| Subject: Re: restore problem
| Date: Wed, 3 Nov 2004 22:18:59 +0100
| Organization: tp.internet - http://www.tpi.pl/
| Lines: 116
| Message-ID: <cmbi6u$nm2$1@.nemesis.news.tpi.pl>
| References: <claeo7$80f$1@.nemesis.news.tpi.pl>
<ZKRQvy4uEHA.3436@.cpmsftngxa10.phx.gbl>
| NNTP-Posting-Host: acs36.neoplus.adsl.tpnet.pl
| X-Trace: nemesis.news.tpi.pl 1099516958 24258 83.25.70.36 (3 Nov 2004
21:22:38 GMT)
| X-Complaints-To: usenet@.tpi.pl
| NNTP-Posting-Date: Wed, 3 Nov 2004 21:22:38 +0000 (UTC)
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFT NGP08.phx.gbl!newsfeed00.s
ul.t-online.de!t-online.de!news.zanker.org!nntp.idg.pl!newsfeed.gaz eta.pl!ne
ws.nask.pl!newsfeed.tpinternet.pl!atlantis.news.tp i.pl!news.tpi.pl!not-for-m
ail
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6443
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hello
|
| my goal is to write a code (java), which will restore a database from file
| I've tried to do it by executing sql statement
| conn.createStatement().executeUpdate("RESTORE DATABASE pubs FROM
| DISK='c:\pubs.bak' WITH REPLACE")
| but I've got the following exception:
| java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
| JDBC][SQLServer]Exclusive access could not be obtained because the
database
| is in use.
|
| my question is: how to restore a database from java (assuming that some
| clients may be connected to database from another pc's) ?
|
| (my first idea was to force exclusive access - but how to do it ?)
|
| thanks again
|
| Artur
|
|
| Uytkownik ""Carb Simien [MSFT]"" <CarbinoS@.online.microsoft.com> napisa
w
| wiadomoci news:ZKRQvy4uEHA.3436@.cpmsftngxa10.phx.gbl...
| >
| > --
| > | From: "Monika" <monika@.o2.pl>
| > | Newsgroups: microsoft.public.sqlserver.jdbcdriver
| > | Subject: restore problem
| > | Date: Fri, 22 Oct 2004 09:57:42 +0200
| > | Organization: tp.internet - http://www.tpi.pl/
| > | Lines: 12
| > | Message-ID: <claeo7$80f$1@.nemesis.news.tpi.pl>
| > | NNTP-Posting-Host: acn10.neoplus.adsl.tpnet.pl
| > | X-Trace: nemesis.news.tpi.pl 1098432071 8207 83.25.65.10 (22 Oct 2004
| > 08:01:11 GMT)
| > | X-Complaints-To: usenet@.tpi.pl
| > | NNTP-Posting-Date: Fri, 22 Oct 2004 08:01:11 +0000 (UTC)
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
| > | Path:
| >
|
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFT NGP08.phx.gbl!newsfeed00.s
| >
|
ul.t-online.de!t-online.de!news.zanker.org!nntp.idg.pl!newsfeed.gaz eta.pl!ne
| >
|
ws.nask.pl!newsfeed.tpinternet.pl!atlantis.news.tp i.pl!news.tpi.pl!not-for-m
| > ail
| > | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6416
| > | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
| > |
| > | hi
| > |
| > | I have a problem with restore db (can not obtain exclusice access to
db)
| > | the question is:
| > | how to obtain exclusive access to database using jdbc driver ?
| > | how to log off all users and prerform restore ?
| > |
| > | thanks for help
| > |
| > | Artur
| > |
| > |
| > |
| >
| > Hello Artur,
| >
| > I'm not clear on what your end goal is. Are you asking how to restore a
| > database, or are you asking how to restrict database access to a single
| > user?
| >
| > In SQL Server, you can issue an ALTER DATABASE command to set the
| > SINGLE_USER state option for a given database. This effectively allows
| > only one user to access the database at a time. This is a Transact-SQL
| > command, and it is not specific to JDBC. The syntax for this operation
| > would be:
| >
| > ALTER DATABASE pubs SET SINGLE_USER WITH ROLLBACK IMMEDIATE
| >
| > So, you could connect to your database using JDBC code and execute the
| > statement above. This will rollback any pending transactions and then
| > close all other connections to the same database.
| >
| > ===========
| >
| > If you are trying to restore a database, then you should use the RESTORE
| > DATABASE Transact-SQL command:
| >
| > USE master
| > GO
| > RESTORE DATABASE pubs FROM DISK='c:\pubs.bak' WITH REPLACE
| >
| > You cannot be using the database that you intend to restore. This is
why
| > the command above changes the context to Master first. If you are
finding
| > yourself contending with access to the database for the restore
operation,
| > you may consider taking the database offline first and then restoring a
| new
| > copy.
| >
| > I recommend consulting the SQL Server 2000 Books Online for further
| > information.
| >
| > Carb Simien, MCSE MCDBA MCAD
| > Microsoft Developer Support - Web Data
| >
| > Please reply only to the newsgroups.
| > This posting is provided "AS IS" with no warranties, and confers no
| rights.
| >
| > Are you secure? For information about the Strategic Technology
Protection
| > Program and to order your FREE Security Tool Kit, please visit
| > http://www.microsoft.com/security.
| >
|
|
|
Hello,
Thanks for the clarification. As I stated before, you can take the
database offline and forcibly kick other users out of the database. Any
pending transactions will be rolled back to maintain proper consistency.
Once this is done, you can restore the database as suggested. Below are
the queries you can run from your code to accomplish this task:
ALTER DATABASE pubs SET offline WITH rollback immediate
RESTORE DATABASE pubs FROM DISK='c:\pubsbak.bak' WITH REPLACE
These are all SQL Server concepts, so they are not specific to Java or
JDBC. Please consult the SQL Server 2000 Books Online for further
information.
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
I have a problem with restore db (can not obtain exclusice access to db)
the question is:
how to obtain exclusive access to database using jdbc driver ?
how to log off all users and prerform restore ?
thanks for help
Artur
| From: "Monika" <monika@.o2.pl>
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| Subject: restore problem
| Date: Fri, 22 Oct 2004 09:57:42 +0200
| Organization: tp.internet - http://www.tpi.pl/
| Lines: 12
| Message-ID: <claeo7$80f$1@.nemesis.news.tpi.pl>
| NNTP-Posting-Host: acn10.neoplus.adsl.tpnet.pl
| X-Trace: nemesis.news.tpi.pl 1098432071 8207 83.25.65.10 (22 Oct 2004
08:01:11 GMT)
| X-Complaints-To: usenet@.tpi.pl
| NNTP-Posting-Date: Fri, 22 Oct 2004 08:01:11 +0000 (UTC)
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFT NGP08.phx.gbl!newsfeed00.s
ul.t-online.de!t-online.de!news.zanker.org!nntp.idg.pl!newsfeed.gaz eta.pl!ne
ws.nask.pl!newsfeed.tpinternet.pl!atlantis.news.tp i.pl!news.tpi.pl!not-for-m
ail
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6416
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| hi
|
| I have a problem with restore db (can not obtain exclusice access to db)
| the question is:
| how to obtain exclusive access to database using jdbc driver ?
| how to log off all users and prerform restore ?
|
| thanks for help
|
| Artur
|
|
|
Hello Artur,
I'm not clear on what your end goal is. Are you asking how to restore a
database, or are you asking how to restrict database access to a single
user?
In SQL Server, you can issue an ALTER DATABASE command to set the
SINGLE_USER state option for a given database. This effectively allows
only one user to access the database at a time. This is a Transact-SQL
command, and it is not specific to JDBC. The syntax for this operation
would be:
ALTER DATABASE pubs SET SINGLE_USER WITH ROLLBACK IMMEDIATE
So, you could connect to your database using JDBC code and execute the
statement above. This will rollback any pending transactions and then
close all other connections to the same database.
===========
If you are trying to restore a database, then you should use the RESTORE
DATABASE Transact-SQL command:
USE master
GO
RESTORE DATABASE pubs FROM DISK='c:\pubs.bak' WITH REPLACE
You cannot be using the database that you intend to restore. This is why
the command above changes the context to Master first. If you are finding
yourself contending with access to the database for the restore operation,
you may consider taking the database offline first and then restoring a new
copy.
I recommend consulting the SQL Server 2000 Books Online for further
information.
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
|||Hello
my goal is to write a code (java), which will restore a database from file
I've tried to do it by executing sql statement
conn.createStatement().executeUpdate("RESTORE DATABASE pubs FROM
DISK='c:\pubs.bak' WITH REPLACE")
but I've got the following exception:
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer]Exclusive access could not be obtained because the database
is in use.
my question is: how to restore a database from java (assuming that some
clients may be connected to database from another pc's) ?
(my first idea was to force exclusive access - but how to do it ?)
thanks again
Artur
Uytkownik ""Carb Simien [MSFT]"" <CarbinoS@.online.microsoft.com> napisa w
wiadomoci news:ZKRQvy4uEHA.3436@.cpmsftngxa10.phx.gbl...
> --
> | From: "Monika" <monika@.o2.pl>
> | Newsgroups: microsoft.public.sqlserver.jdbcdriver
> | Subject: restore problem
> | Date: Fri, 22 Oct 2004 09:57:42 +0200
> | Organization: tp.internet - http://www.tpi.pl/
> | Lines: 12
> | Message-ID: <claeo7$80f$1@.nemesis.news.tpi.pl>
> | NNTP-Posting-Host: acn10.neoplus.adsl.tpnet.pl
> | X-Trace: nemesis.news.tpi.pl 1098432071 8207 83.25.65.10 (22 Oct 2004
> 08:01:11 GMT)
> | X-Complaints-To: usenet@.tpi.pl
> | NNTP-Posting-Date: Fri, 22 Oct 2004 08:01:11 +0000 (UTC)
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
> | Path:
>
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFT NGP08.phx.gbl!newsfeed00.s
>
ul.t-online.de!t-online.de!news.zanker.org!nntp.idg.pl!newsfeed.gaz eta.pl!ne
>
ws.nask.pl!newsfeed.tpinternet.pl!atlantis.news.tp i.pl!news.tpi.pl!not-for-m
> ail
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6416
> | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
> |
> | hi
> |
> | I have a problem with restore db (can not obtain exclusice access to db)
> | the question is:
> | how to obtain exclusive access to database using jdbc driver ?
> | how to log off all users and prerform restore ?
> |
> | thanks for help
> |
> | Artur
> |
> |
> |
> Hello Artur,
> I'm not clear on what your end goal is. Are you asking how to restore a
> database, or are you asking how to restrict database access to a single
> user?
> In SQL Server, you can issue an ALTER DATABASE command to set the
> SINGLE_USER state option for a given database. This effectively allows
> only one user to access the database at a time. This is a Transact-SQL
> command, and it is not specific to JDBC. The syntax for this operation
> would be:
> ALTER DATABASE pubs SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> So, you could connect to your database using JDBC code and execute the
> statement above. This will rollback any pending transactions and then
> close all other connections to the same database.
> ===========
> If you are trying to restore a database, then you should use the RESTORE
> DATABASE Transact-SQL command:
> USE master
> GO
> RESTORE DATABASE pubs FROM DISK='c:\pubs.bak' WITH REPLACE
> You cannot be using the database that you intend to restore. This is why
> the command above changes the context to Master first. If you are finding
> yourself contending with access to the database for the restore operation,
> you may consider taking the database offline first and then restoring a
new
> copy.
> I recommend consulting the SQL Server 2000 Books Online for further
> information.
> Carb Simien, MCSE MCDBA MCAD
> Microsoft Developer Support - Web Data
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
>
|||| From: "Artur" <monika@.o2.pl>
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| Subject: Re: restore problem
| Date: Wed, 3 Nov 2004 22:18:59 +0100
| Organization: tp.internet - http://www.tpi.pl/
| Lines: 116
| Message-ID: <cmbi6u$nm2$1@.nemesis.news.tpi.pl>
| References: <claeo7$80f$1@.nemesis.news.tpi.pl>
<ZKRQvy4uEHA.3436@.cpmsftngxa10.phx.gbl>
| NNTP-Posting-Host: acs36.neoplus.adsl.tpnet.pl
| X-Trace: nemesis.news.tpi.pl 1099516958 24258 83.25.70.36 (3 Nov 2004
21:22:38 GMT)
| X-Complaints-To: usenet@.tpi.pl
| NNTP-Posting-Date: Wed, 3 Nov 2004 21:22:38 +0000 (UTC)
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFT NGP08.phx.gbl!newsfeed00.s
ul.t-online.de!t-online.de!news.zanker.org!nntp.idg.pl!newsfeed.gaz eta.pl!ne
ws.nask.pl!newsfeed.tpinternet.pl!atlantis.news.tp i.pl!news.tpi.pl!not-for-m
ail
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6443
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hello
|
| my goal is to write a code (java), which will restore a database from file
| I've tried to do it by executing sql statement
| conn.createStatement().executeUpdate("RESTORE DATABASE pubs FROM
| DISK='c:\pubs.bak' WITH REPLACE")
| but I've got the following exception:
| java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
| JDBC][SQLServer]Exclusive access could not be obtained because the
database
| is in use.
|
| my question is: how to restore a database from java (assuming that some
| clients may be connected to database from another pc's) ?
|
| (my first idea was to force exclusive access - but how to do it ?)
|
| thanks again
|
| Artur
|
|
| Uytkownik ""Carb Simien [MSFT]"" <CarbinoS@.online.microsoft.com> napisa
w
| wiadomoci news:ZKRQvy4uEHA.3436@.cpmsftngxa10.phx.gbl...
| >
| > --
| > | From: "Monika" <monika@.o2.pl>
| > | Newsgroups: microsoft.public.sqlserver.jdbcdriver
| > | Subject: restore problem
| > | Date: Fri, 22 Oct 2004 09:57:42 +0200
| > | Organization: tp.internet - http://www.tpi.pl/
| > | Lines: 12
| > | Message-ID: <claeo7$80f$1@.nemesis.news.tpi.pl>
| > | NNTP-Posting-Host: acn10.neoplus.adsl.tpnet.pl
| > | X-Trace: nemesis.news.tpi.pl 1098432071 8207 83.25.65.10 (22 Oct 2004
| > 08:01:11 GMT)
| > | X-Complaints-To: usenet@.tpi.pl
| > | NNTP-Posting-Date: Fri, 22 Oct 2004 08:01:11 +0000 (UTC)
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
| > | Path:
| >
|
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFT NGP08.phx.gbl!newsfeed00.s
| >
|
ul.t-online.de!t-online.de!news.zanker.org!nntp.idg.pl!newsfeed.gaz eta.pl!ne
| >
|
ws.nask.pl!newsfeed.tpinternet.pl!atlantis.news.tp i.pl!news.tpi.pl!not-for-m
| > ail
| > | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6416
| > | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
| > |
| > | hi
| > |
| > | I have a problem with restore db (can not obtain exclusice access to
db)
| > | the question is:
| > | how to obtain exclusive access to database using jdbc driver ?
| > | how to log off all users and prerform restore ?
| > |
| > | thanks for help
| > |
| > | Artur
| > |
| > |
| > |
| >
| > Hello Artur,
| >
| > I'm not clear on what your end goal is. Are you asking how to restore a
| > database, or are you asking how to restrict database access to a single
| > user?
| >
| > In SQL Server, you can issue an ALTER DATABASE command to set the
| > SINGLE_USER state option for a given database. This effectively allows
| > only one user to access the database at a time. This is a Transact-SQL
| > command, and it is not specific to JDBC. The syntax for this operation
| > would be:
| >
| > ALTER DATABASE pubs SET SINGLE_USER WITH ROLLBACK IMMEDIATE
| >
| > So, you could connect to your database using JDBC code and execute the
| > statement above. This will rollback any pending transactions and then
| > close all other connections to the same database.
| >
| > ===========
| >
| > If you are trying to restore a database, then you should use the RESTORE
| > DATABASE Transact-SQL command:
| >
| > USE master
| > GO
| > RESTORE DATABASE pubs FROM DISK='c:\pubs.bak' WITH REPLACE
| >
| > You cannot be using the database that you intend to restore. This is
why
| > the command above changes the context to Master first. If you are
finding
| > yourself contending with access to the database for the restore
operation,
| > you may consider taking the database offline first and then restoring a
| new
| > copy.
| >
| > I recommend consulting the SQL Server 2000 Books Online for further
| > information.
| >
| > Carb Simien, MCSE MCDBA MCAD
| > Microsoft Developer Support - Web Data
| >
| > Please reply only to the newsgroups.
| > This posting is provided "AS IS" with no warranties, and confers no
| rights.
| >
| > Are you secure? For information about the Strategic Technology
Protection
| > Program and to order your FREE Security Tool Kit, please visit
| > http://www.microsoft.com/security.
| >
|
|
|
Hello,
Thanks for the clarification. As I stated before, you can take the
database offline and forcibly kick other users out of the database. Any
pending transactions will be rolled back to maintain proper consistency.
Once this is done, you can restore the database as suggested. Below are
the queries you can run from your code to accomplish this task:
ALTER DATABASE pubs SET offline WITH rollback immediate
RESTORE DATABASE pubs FROM DISK='c:\pubsbak.bak' WITH REPLACE
These are all SQL Server concepts, so they are not specific to Java or
JDBC. Please consult the SQL Server 2000 Books Online for further
information.
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
restore problem
Hi
i started a restore on a little db, it is still running (more than 15 mins),
how can i understand what is it doing?
Carlo
How little is the database?
In the future you can use the WITH STATS option to show you the percent
complete.
I am guessing that the SQL Server's disks are busy creating the database
files that will hold the data.
Keith
"Carlo" <marcocci@.ingv.it> wrote in message
news:utG3YN8fEHA.704@.TK2MSFTNGP09.phx.gbl...
> Hi
> i started a restore on a little db, it is still running (more than 15
mins),
> how can i understand what is it doing?
> Carlo
> --
>
>
|||the size of db is around 9 mb, it is a test db, it is still loading, how can
i stop the restore?
|||Are you able to see the new data/log files created in OS?
Also check to see if there's blocks.
"Carlo" <marcocci@.ingv.it> wrote in message
news:%23KaggZ8fEHA.2984@.tk2msftngp13.phx.gbl...
> the size of db is around 9 mb, it is a test db, it is still loading, how
can
> i stop the restore?
>
|||9MB. That is darn small. It has not finished yet? 22 minutes have gone by
since your original post and your second post. That is a too long to wait
for a 9MB database.
What did you use to begin the restore? Query Analyzer? Enterprise Manager?
Perhaps you could try closing the app. You could also kill that specific
connection, but that would be harsh.
Keith
"Carlo" <marcocci@.ingv.it> wrote in message
news:%23KaggZ8fEHA.2984@.tk2msftngp13.phx.gbl...
> the size of db is around 9 mb, it is a test db, it is still loading, how
can
> i stop the restore?
>
|||i stop the service and start it again, the db is alwais loading, i found in
the log this message:
Bypassing recovery for database 'db_rsnd' because it is marked IN LOAD.
what have i to do?
i'd like to know if i can delete the db from the server, but i cant do it
from the manager because it is loading and i suppose i cant delete it from
the console too. is there a way to delete the "row" in the master db that
let it know that my db exists? i hope you understand what i mean.
Carlo
********************************************
Carlo Marcocci
Istituto Nazionale di Geofisica e Vulcanologia
Via di Vigna Murata 605, 00143 Roma
Tel. +39-6-51860391
Fax. +39-6-51860507
mailto: marcocci@.ingv.it
******************************************** L&P
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> ha scritto nel messaggio
news:ufwtZL9fEHA.3024@.TK2MSFTNGP10.phx.gbl...
> 9MB. That is darn small. It has not finished yet? 22 minutes have gone
by
> since your original post and your second post. That is a too long to wait
> for a 9MB database.
> What did you use to begin the restore? Query Analyzer? Enterprise
Manager?
> Perhaps you could try closing the app. You could also kill that specific
> connection, but that would be harsh.
> --
> Keith
>
> "Carlo" <marcocci@.ingv.it> wrote in message
> news:%23KaggZ8fEHA.2984@.tk2msftngp13.phx.gbl...
> can
>
|||may i use this ?
UPDATE sysdatabases
SET status=16
WHERE name='daCancellare'
does it change anything?
********************************************
Carlo Marcocci
Istituto Nazionale di Geofisica e Vulcanologia
Via di Vigna Murata 605, 00143 Roma
Tel. +39-6-51860391
Fax. +39-6-51860507
mailto: marcocci@.ingv.it
******************************************** L&P
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> ha scritto nel messaggio
news:ufwtZL9fEHA.3024@.TK2MSFTNGP10.phx.gbl...
> 9MB. That is darn small. It has not finished yet? 22 minutes have gone
by
> since your original post and your second post. That is a too long to wait
> for a 9MB database.
> What did you use to begin the restore? Query Analyzer? Enterprise
Manager?
> Perhaps you could try closing the app. You could also kill that specific
> connection, but that would be harsh.
> --
> Keith
>
> "Carlo" <marcocci@.ingv.it> wrote in message
> news:%23KaggZ8fEHA.2984@.tk2msftngp13.phx.gbl...
> can
>
|||I would try using DROP DATABASE
Information within Books Online (within the SQL Server program group).
Keith
"Carlo" <marcocci@.ingv.it> wrote in message
news:OyzQU09fEHA.3916@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> may i use this ?
> UPDATE sysdatabases
> SET status=16
> WHERE name='daCancellare'
> does it change anything?
>
> --
>
> ********************************************
> Carlo Marcocci
> Istituto Nazionale di Geofisica e Vulcanologia
> Via di Vigna Murata 605, 00143 Roma
> Tel. +39-6-51860391
> Fax. +39-6-51860507
> mailto: marcocci@.ingv.it
> ******************************************** L&P
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> ha scritto nel messaggio
> news:ufwtZL9fEHA.3024@.TK2MSFTNGP10.phx.gbl...
gone[vbcol=seagreen]
> by
wait[vbcol=seagreen]
> Manager?
specific[vbcol=seagreen]
how
>
|||Read up on DROP DATABASE within Books Online (within the SQL Server program
group).
Keith
"Carlo" <marcocci@.ingv.it> wrote in message
news:ex%23jop9fEHA.3932@.TK2MSFTNGP09.phx.gbl...
> i stop the service and start it again, the db is alwais loading, i found
in[vbcol=seagreen]
> the log this message:
> Bypassing recovery for database 'db_rsnd' because it is marked IN LOAD.
> what have i to do?
> i'd like to know if i can delete the db from the server, but i cant do it
> from the manager because it is loading and i suppose i cant delete it from
> the console too. is there a way to delete the "row" in the master db that
> let it know that my db exists? i hope you understand what i mean.
> Carlo
> --
>
> ********************************************
> Carlo Marcocci
> Istituto Nazionale di Geofisica e Vulcanologia
> Via di Vigna Murata 605, 00143 Roma
> Tel. +39-6-51860391
> Fax. +39-6-51860507
> mailto: marcocci@.ingv.it
> ******************************************** L&P
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> ha scritto nel messaggio
> news:ufwtZL9fEHA.3024@.TK2MSFTNGP10.phx.gbl...
gone[vbcol=seagreen]
> by
wait[vbcol=seagreen]
> Manager?
specific[vbcol=seagreen]
how
>
|||By now you have fixed your problem. Some added thoughts.
I had a similar issue running SQL Server on a small PC that runs 24x7 (far
too little memory). There were times when a process that normally took 15
minutes ran for more than 3 hours.
1. Sometimes a reboot would speed up the process. Evidently, SQL Server
would grow and grow far beyond the physical memory. The PC spent all its
time paging memory in and out. There is a property to prevent this and set
the maximum memory that SQL Server can use.
2. I also had to set logging to a simple type rather than the full logging.
This is not a production database and we perform daily backups since it is
small. Simple logging is all that is required. With full logging, my tiny
databases (under 50 MB) grew to almost a gigabyte.
3. Wrote a stored procedure that runs in the SQL Server job scheduler every
night that performs a reindex on every table, then gets rid of the log ,
then shrinks the database. Issue has pretty much gone away.
"Carlo" <marcocci@.ingv.it> wrote in message
news:utG3YN8fEHA.704@.TK2MSFTNGP09.phx.gbl...
> Hi
> i started a restore on a little db, it is still running (more than 15
mins),
> how can i understand what is it doing?
> Carlo
> --
>
>
i started a restore on a little db, it is still running (more than 15 mins),
how can i understand what is it doing?
Carlo
How little is the database?
In the future you can use the WITH STATS option to show you the percent
complete.
I am guessing that the SQL Server's disks are busy creating the database
files that will hold the data.
Keith
"Carlo" <marcocci@.ingv.it> wrote in message
news:utG3YN8fEHA.704@.TK2MSFTNGP09.phx.gbl...
> Hi
> i started a restore on a little db, it is still running (more than 15
mins),
> how can i understand what is it doing?
> Carlo
> --
>
>
|||the size of db is around 9 mb, it is a test db, it is still loading, how can
i stop the restore?
|||Are you able to see the new data/log files created in OS?
Also check to see if there's blocks.
"Carlo" <marcocci@.ingv.it> wrote in message
news:%23KaggZ8fEHA.2984@.tk2msftngp13.phx.gbl...
> the size of db is around 9 mb, it is a test db, it is still loading, how
can
> i stop the restore?
>
|||9MB. That is darn small. It has not finished yet? 22 minutes have gone by
since your original post and your second post. That is a too long to wait
for a 9MB database.
What did you use to begin the restore? Query Analyzer? Enterprise Manager?
Perhaps you could try closing the app. You could also kill that specific
connection, but that would be harsh.
Keith
"Carlo" <marcocci@.ingv.it> wrote in message
news:%23KaggZ8fEHA.2984@.tk2msftngp13.phx.gbl...
> the size of db is around 9 mb, it is a test db, it is still loading, how
can
> i stop the restore?
>
|||i stop the service and start it again, the db is alwais loading, i found in
the log this message:
Bypassing recovery for database 'db_rsnd' because it is marked IN LOAD.
what have i to do?
i'd like to know if i can delete the db from the server, but i cant do it
from the manager because it is loading and i suppose i cant delete it from
the console too. is there a way to delete the "row" in the master db that
let it know that my db exists? i hope you understand what i mean.
Carlo
********************************************
Carlo Marcocci
Istituto Nazionale di Geofisica e Vulcanologia
Via di Vigna Murata 605, 00143 Roma
Tel. +39-6-51860391
Fax. +39-6-51860507
mailto: marcocci@.ingv.it
******************************************** L&P
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> ha scritto nel messaggio
news:ufwtZL9fEHA.3024@.TK2MSFTNGP10.phx.gbl...
> 9MB. That is darn small. It has not finished yet? 22 minutes have gone
by
> since your original post and your second post. That is a too long to wait
> for a 9MB database.
> What did you use to begin the restore? Query Analyzer? Enterprise
Manager?
> Perhaps you could try closing the app. You could also kill that specific
> connection, but that would be harsh.
> --
> Keith
>
> "Carlo" <marcocci@.ingv.it> wrote in message
> news:%23KaggZ8fEHA.2984@.tk2msftngp13.phx.gbl...
> can
>
|||may i use this ?
UPDATE sysdatabases
SET status=16
WHERE name='daCancellare'
does it change anything?
********************************************
Carlo Marcocci
Istituto Nazionale di Geofisica e Vulcanologia
Via di Vigna Murata 605, 00143 Roma
Tel. +39-6-51860391
Fax. +39-6-51860507
mailto: marcocci@.ingv.it
******************************************** L&P
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> ha scritto nel messaggio
news:ufwtZL9fEHA.3024@.TK2MSFTNGP10.phx.gbl...
> 9MB. That is darn small. It has not finished yet? 22 minutes have gone
by
> since your original post and your second post. That is a too long to wait
> for a 9MB database.
> What did you use to begin the restore? Query Analyzer? Enterprise
Manager?
> Perhaps you could try closing the app. You could also kill that specific
> connection, but that would be harsh.
> --
> Keith
>
> "Carlo" <marcocci@.ingv.it> wrote in message
> news:%23KaggZ8fEHA.2984@.tk2msftngp13.phx.gbl...
> can
>
|||I would try using DROP DATABASE
Information within Books Online (within the SQL Server program group).
Keith
"Carlo" <marcocci@.ingv.it> wrote in message
news:OyzQU09fEHA.3916@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> may i use this ?
> UPDATE sysdatabases
> SET status=16
> WHERE name='daCancellare'
> does it change anything?
>
> --
>
> ********************************************
> Carlo Marcocci
> Istituto Nazionale di Geofisica e Vulcanologia
> Via di Vigna Murata 605, 00143 Roma
> Tel. +39-6-51860391
> Fax. +39-6-51860507
> mailto: marcocci@.ingv.it
> ******************************************** L&P
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> ha scritto nel messaggio
> news:ufwtZL9fEHA.3024@.TK2MSFTNGP10.phx.gbl...
gone[vbcol=seagreen]
> by
wait[vbcol=seagreen]
> Manager?
specific[vbcol=seagreen]
how
>
|||Read up on DROP DATABASE within Books Online (within the SQL Server program
group).
Keith
"Carlo" <marcocci@.ingv.it> wrote in message
news:ex%23jop9fEHA.3932@.TK2MSFTNGP09.phx.gbl...
> i stop the service and start it again, the db is alwais loading, i found
in[vbcol=seagreen]
> the log this message:
> Bypassing recovery for database 'db_rsnd' because it is marked IN LOAD.
> what have i to do?
> i'd like to know if i can delete the db from the server, but i cant do it
> from the manager because it is loading and i suppose i cant delete it from
> the console too. is there a way to delete the "row" in the master db that
> let it know that my db exists? i hope you understand what i mean.
> Carlo
> --
>
> ********************************************
> Carlo Marcocci
> Istituto Nazionale di Geofisica e Vulcanologia
> Via di Vigna Murata 605, 00143 Roma
> Tel. +39-6-51860391
> Fax. +39-6-51860507
> mailto: marcocci@.ingv.it
> ******************************************** L&P
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> ha scritto nel messaggio
> news:ufwtZL9fEHA.3024@.TK2MSFTNGP10.phx.gbl...
gone[vbcol=seagreen]
> by
wait[vbcol=seagreen]
> Manager?
specific[vbcol=seagreen]
how
>
|||By now you have fixed your problem. Some added thoughts.
I had a similar issue running SQL Server on a small PC that runs 24x7 (far
too little memory). There were times when a process that normally took 15
minutes ran for more than 3 hours.
1. Sometimes a reboot would speed up the process. Evidently, SQL Server
would grow and grow far beyond the physical memory. The PC spent all its
time paging memory in and out. There is a property to prevent this and set
the maximum memory that SQL Server can use.
2. I also had to set logging to a simple type rather than the full logging.
This is not a production database and we perform daily backups since it is
small. Simple logging is all that is required. With full logging, my tiny
databases (under 50 MB) grew to almost a gigabyte.
3. Wrote a stored procedure that runs in the SQL Server job scheduler every
night that performs a reindex on every table, then gets rid of the log ,
then shrinks the database. Issue has pretty much gone away.
"Carlo" <marcocci@.ingv.it> wrote in message
news:utG3YN8fEHA.704@.TK2MSFTNGP09.phx.gbl...
> Hi
> i started a restore on a little db, it is still running (more than 15
mins),
> how can i understand what is it doing?
> Carlo
> --
>
>
Friday, March 9, 2012
restore problem
Hi
i started a restore on a little db, it is still running (more than 15 mins),
how can i understand what is it doing?
CarloHow little is the database?
In the future you can use the WITH STATS option to show you the percent
complete.
I am guessing that the SQL Server's disks are busy creating the database
files that will hold the data.
Keith
"Carlo" <marcocci@.ingv.it> wrote in message
news:utG3YN8fEHA.704@.TK2MSFTNGP09.phx.gbl...
> Hi
> i started a restore on a little db, it is still running (more than 15
mins),
> how can i understand what is it doing?
> Carlo
> --
>
>|||the size of db is around 9 mb, it is a test db, it is still loading, how can
i stop the restore'|||Are you able to see the new data/log files created in OS?
Also check to see if there's blocks.
"Carlo" <marcocci@.ingv.it> wrote in message
news:%23KaggZ8fEHA.2984@.tk2msftngp13.phx.gbl...
> the size of db is around 9 mb, it is a test db, it is still loading, how
can
> i stop the restore'
>|||9MB. That is darn small. It has not finished yet? 22 minutes have gone by
since your original post and your second post. That is a too long to wait
for a 9MB database.
What did you use to begin the restore? Query Analyzer? Enterprise Manager?
Perhaps you could try closing the app. You could also kill that specific
connection, but that would be harsh.
Keith
"Carlo" <marcocci@.ingv.it> wrote in message
news:%23KaggZ8fEHA.2984@.tk2msftngp13.phx.gbl...
> the size of db is around 9 mb, it is a test db, it is still loading, how
can
> i stop the restore'
>|||i stop the service and start it again, the db is alwais loading, i found in
the log this message:
Bypassing recovery for database 'db_rsnd' because it is marked IN LOAD.
what have i to do'
i'd like to know if i can delete the db from the server, but i cant do it
from the manager because it is loading and i suppose i cant delete it from
the console too. is there a way to delete the "row" in the master db that
let it know that my db exists? i hope you understand what i mean.
Carlo
****************************************
****
Carlo Marcocci
Istituto Nazionale di Geofisica e Vulcanologia
Via di Vigna Murata 605, 00143 Roma
Tel. +39-6-51860391
Fax. +39-6-51860507
mailto: marcocci@.ingv.it
****************************************
**** L&P
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> ha scritto nel messaggio
news:ufwtZL9fEHA.3024@.TK2MSFTNGP10.phx.gbl...
> 9MB. That is darn small. It has not finished yet? 22 minutes have gone
by
> since your original post and your second post. That is a too long to wait
> for a 9MB database.
> What did you use to begin the restore? Query Analyzer? Enterprise
Manager?
> Perhaps you could try closing the app. You could also kill that specific
> connection, but that would be harsh.
> --
> Keith
>
> "Carlo" <marcocci@.ingv.it> wrote in message
> news:%23KaggZ8fEHA.2984@.tk2msftngp13.phx.gbl...
> can
>|||may i use this '
UPDATE sysdatabases
SET status=16
WHERE name='daCancellare'
does it change anything'
****************************************
****
Carlo Marcocci
Istituto Nazionale di Geofisica e Vulcanologia
Via di Vigna Murata 605, 00143 Roma
Tel. +39-6-51860391
Fax. +39-6-51860507
mailto: marcocci@.ingv.it
****************************************
**** L&P
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> ha scritto nel messaggio
news:ufwtZL9fEHA.3024@.TK2MSFTNGP10.phx.gbl...
> 9MB. That is darn small. It has not finished yet? 22 minutes have gone
by
> since your original post and your second post. That is a too long to wait
> for a 9MB database.
> What did you use to begin the restore? Query Analyzer? Enterprise
Manager?
> Perhaps you could try closing the app. You could also kill that specific
> connection, but that would be harsh.
> --
> Keith
>
> "Carlo" <marcocci@.ingv.it> wrote in message
> news:%23KaggZ8fEHA.2984@.tk2msftngp13.phx.gbl...
> can
>|||I would try using DROP DATABASE
Information within Books Online (within the SQL Server program group).
Keith
"Carlo" <marcocci@.ingv.it> wrote in message
news:OyzQU09fEHA.3916@.TK2MSFTNGP11.phx.gbl...
> may i use this '
> UPDATE sysdatabases
> SET status=16
> WHERE name='daCancellare'
> does it change anything'
>
> --
>
> ****************************************
****
> Carlo Marcocci
> Istituto Nazionale di Geofisica e Vulcanologia
> Via di Vigna Murata 605, 00143 Roma
> Tel. +39-6-51860391
> Fax. +39-6-51860507
> mailto: marcocci@.ingv.it
> ****************************************
**** L&P
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> ha scritto nel messaggio
> news:ufwtZL9fEHA.3024@.TK2MSFTNGP10.phx.gbl...
gone[vbcol=seagreen]
> by
wait[vbcol=seagreen]
> Manager?
specific[vbcol=seagreen]
how[vbcol=seagreen]
>|||Read up on DROP DATABASE within Books Online (within the SQL Server program
group).
Keith
"Carlo" <marcocci@.ingv.it> wrote in message
news:ex%23jop9fEHA.3932@.TK2MSFTNGP09.phx.gbl...
> i stop the service and start it again, the db is alwais loading, i found
in
> the log this message:
> Bypassing recovery for database 'db_rsnd' because it is marked IN LOAD.
> what have i to do'
> i'd like to know if i can delete the db from the server, but i cant do it
> from the manager because it is loading and i suppose i cant delete it from
> the console too. is there a way to delete the "row" in the master db that
> let it know that my db exists? i hope you understand what i mean.
> Carlo
> --
>
> ****************************************
****
> Carlo Marcocci
> Istituto Nazionale di Geofisica e Vulcanologia
> Via di Vigna Murata 605, 00143 Roma
> Tel. +39-6-51860391
> Fax. +39-6-51860507
> mailto: marcocci@.ingv.it
> ****************************************
**** L&P
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> ha scritto nel messaggio
> news:ufwtZL9fEHA.3024@.TK2MSFTNGP10.phx.gbl...
gone[vbcol=seagreen]
> by
wait[vbcol=seagreen]
> Manager?
specific[vbcol=seagreen]
how[vbcol=seagreen]
>|||By now you have fixed your problem. Some added thoughts.
I had a similar issue running SQL Server on a small PC that runs 24x7 (far
too little memory). There were times when a process that normally took 15
minutes ran for more than 3 hours.
1. Sometimes a reboot would speed up the process. Evidently, SQL Server
would grow and grow far beyond the physical memory. The PC spent all its
time paging memory in and out. There is a property to prevent this and set
the maximum memory that SQL Server can use.
2. I also had to set logging to a simple type rather than the full logging.
This is not a production database and we perform daily backups since it is
small. Simple logging is all that is required. With full logging, my tiny
databases (under 50 MB) grew to almost a gigabyte.
3. Wrote a stored procedure that runs in the SQL Server job scheduler every
night that performs a reindex on every table, then gets rid of the log ,
then shrinks the database. Issue has pretty much gone away.
"Carlo" <marcocci@.ingv.it> wrote in message
news:utG3YN8fEHA.704@.TK2MSFTNGP09.phx.gbl...
> Hi
> i started a restore on a little db, it is still running (more than 15
mins),
> how can i understand what is it doing?
> Carlo
> --
>
>
i started a restore on a little db, it is still running (more than 15 mins),
how can i understand what is it doing?
CarloHow little is the database?
In the future you can use the WITH STATS option to show you the percent
complete.
I am guessing that the SQL Server's disks are busy creating the database
files that will hold the data.
Keith
"Carlo" <marcocci@.ingv.it> wrote in message
news:utG3YN8fEHA.704@.TK2MSFTNGP09.phx.gbl...
> Hi
> i started a restore on a little db, it is still running (more than 15
mins),
> how can i understand what is it doing?
> Carlo
> --
>
>|||the size of db is around 9 mb, it is a test db, it is still loading, how can
i stop the restore'|||Are you able to see the new data/log files created in OS?
Also check to see if there's blocks.
"Carlo" <marcocci@.ingv.it> wrote in message
news:%23KaggZ8fEHA.2984@.tk2msftngp13.phx.gbl...
> the size of db is around 9 mb, it is a test db, it is still loading, how
can
> i stop the restore'
>|||9MB. That is darn small. It has not finished yet? 22 minutes have gone by
since your original post and your second post. That is a too long to wait
for a 9MB database.
What did you use to begin the restore? Query Analyzer? Enterprise Manager?
Perhaps you could try closing the app. You could also kill that specific
connection, but that would be harsh.
Keith
"Carlo" <marcocci@.ingv.it> wrote in message
news:%23KaggZ8fEHA.2984@.tk2msftngp13.phx.gbl...
> the size of db is around 9 mb, it is a test db, it is still loading, how
can
> i stop the restore'
>|||i stop the service and start it again, the db is alwais loading, i found in
the log this message:
Bypassing recovery for database 'db_rsnd' because it is marked IN LOAD.
what have i to do'
i'd like to know if i can delete the db from the server, but i cant do it
from the manager because it is loading and i suppose i cant delete it from
the console too. is there a way to delete the "row" in the master db that
let it know that my db exists? i hope you understand what i mean.
Carlo
****************************************
****
Carlo Marcocci
Istituto Nazionale di Geofisica e Vulcanologia
Via di Vigna Murata 605, 00143 Roma
Tel. +39-6-51860391
Fax. +39-6-51860507
mailto: marcocci@.ingv.it
****************************************
**** L&P
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> ha scritto nel messaggio
news:ufwtZL9fEHA.3024@.TK2MSFTNGP10.phx.gbl...
> 9MB. That is darn small. It has not finished yet? 22 minutes have gone
by
> since your original post and your second post. That is a too long to wait
> for a 9MB database.
> What did you use to begin the restore? Query Analyzer? Enterprise
Manager?
> Perhaps you could try closing the app. You could also kill that specific
> connection, but that would be harsh.
> --
> Keith
>
> "Carlo" <marcocci@.ingv.it> wrote in message
> news:%23KaggZ8fEHA.2984@.tk2msftngp13.phx.gbl...
> can
>|||may i use this '
UPDATE sysdatabases
SET status=16
WHERE name='daCancellare'
does it change anything'
****************************************
****
Carlo Marcocci
Istituto Nazionale di Geofisica e Vulcanologia
Via di Vigna Murata 605, 00143 Roma
Tel. +39-6-51860391
Fax. +39-6-51860507
mailto: marcocci@.ingv.it
****************************************
**** L&P
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> ha scritto nel messaggio
news:ufwtZL9fEHA.3024@.TK2MSFTNGP10.phx.gbl...
> 9MB. That is darn small. It has not finished yet? 22 minutes have gone
by
> since your original post and your second post. That is a too long to wait
> for a 9MB database.
> What did you use to begin the restore? Query Analyzer? Enterprise
Manager?
> Perhaps you could try closing the app. You could also kill that specific
> connection, but that would be harsh.
> --
> Keith
>
> "Carlo" <marcocci@.ingv.it> wrote in message
> news:%23KaggZ8fEHA.2984@.tk2msftngp13.phx.gbl...
> can
>|||I would try using DROP DATABASE
Information within Books Online (within the SQL Server program group).
Keith
"Carlo" <marcocci@.ingv.it> wrote in message
news:OyzQU09fEHA.3916@.TK2MSFTNGP11.phx.gbl...
> may i use this '
> UPDATE sysdatabases
> SET status=16
> WHERE name='daCancellare'
> does it change anything'
>
> --
>
> ****************************************
****
> Carlo Marcocci
> Istituto Nazionale di Geofisica e Vulcanologia
> Via di Vigna Murata 605, 00143 Roma
> Tel. +39-6-51860391
> Fax. +39-6-51860507
> mailto: marcocci@.ingv.it
> ****************************************
**** L&P
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> ha scritto nel messaggio
> news:ufwtZL9fEHA.3024@.TK2MSFTNGP10.phx.gbl...
gone[vbcol=seagreen]
> by
wait[vbcol=seagreen]
> Manager?
specific[vbcol=seagreen]
how[vbcol=seagreen]
>|||Read up on DROP DATABASE within Books Online (within the SQL Server program
group).
Keith
"Carlo" <marcocci@.ingv.it> wrote in message
news:ex%23jop9fEHA.3932@.TK2MSFTNGP09.phx.gbl...
> i stop the service and start it again, the db is alwais loading, i found
in
> the log this message:
> Bypassing recovery for database 'db_rsnd' because it is marked IN LOAD.
> what have i to do'
> i'd like to know if i can delete the db from the server, but i cant do it
> from the manager because it is loading and i suppose i cant delete it from
> the console too. is there a way to delete the "row" in the master db that
> let it know that my db exists? i hope you understand what i mean.
> Carlo
> --
>
> ****************************************
****
> Carlo Marcocci
> Istituto Nazionale di Geofisica e Vulcanologia
> Via di Vigna Murata 605, 00143 Roma
> Tel. +39-6-51860391
> Fax. +39-6-51860507
> mailto: marcocci@.ingv.it
> ****************************************
**** L&P
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> ha scritto nel messaggio
> news:ufwtZL9fEHA.3024@.TK2MSFTNGP10.phx.gbl...
gone[vbcol=seagreen]
> by
wait[vbcol=seagreen]
> Manager?
specific[vbcol=seagreen]
how[vbcol=seagreen]
>|||By now you have fixed your problem. Some added thoughts.
I had a similar issue running SQL Server on a small PC that runs 24x7 (far
too little memory). There were times when a process that normally took 15
minutes ran for more than 3 hours.
1. Sometimes a reboot would speed up the process. Evidently, SQL Server
would grow and grow far beyond the physical memory. The PC spent all its
time paging memory in and out. There is a property to prevent this and set
the maximum memory that SQL Server can use.
2. I also had to set logging to a simple type rather than the full logging.
This is not a production database and we perform daily backups since it is
small. Simple logging is all that is required. With full logging, my tiny
databases (under 50 MB) grew to almost a gigabyte.
3. Wrote a stored procedure that runs in the SQL Server job scheduler every
night that performs a reindex on every table, then gets rid of the log ,
then shrinks the database. Issue has pretty much gone away.
"Carlo" <marcocci@.ingv.it> wrote in message
news:utG3YN8fEHA.704@.TK2MSFTNGP09.phx.gbl...
> Hi
> i started a restore on a little db, it is still running (more than 15
mins),
> how can i understand what is it doing?
> Carlo
> --
>
>
Restore only few tables of the database
HI
I'm designing a database. This database will be installed in a few servers.
Some of the tables in the database the user can't change only once a month
the "center" should send their data to all clients. No connectivity between
installations (Replication isn't relevant). What we thougth to do is one of
the 2:
1. To have 2 databases, one will have all the tables and the other only the
"from center" tables, and once a month the center will send a backup of it
to be restore all over. the poblem is that we won't be able to perform data
integrity with Foreign key this way.
2. The other idea was to backup/restore only part of the database, maybe
depents on a schema or filegroup or so.
Is their a way to perform the 2nd way'?
Thanks"Chedva" <chedvag@.matrix-it.co.il> wrote in message
news:eiHxSsXHHHA.3952@.TK2MSFTNGP02.phx.gbl...
> HI
> I'm designing a database. This database will be installed in a few
> servers.
> Some of the tables in the database the user can't change only once a month
> the "center" should send their data to all clients. No connectivity
> between
> installations (Replication isn't relevant). What we thougth to do is one
> of
> the 2:
> 1. To have 2 databases, one will have all the tables and the other only
> the
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform
> data
> integrity with Foreign key this way.
> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
> Is their a way to perform the 2nd way'?
Can't you write something into your software to get this via the web and
update the tables? I don't think either of the options you've suggested are
the best way to go and you should not need to have 2 databases on the
client's machines.
> Thanks
>|||> 1. To have 2 databases, one will have all the tables and the other only the">
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform dat
a
> integrity with Foreign key this way.
This is an option. And the downside you already know. You can consider using
triggers to maintain
referential integrity, but I'd opt for option 3 below instead.
> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
This *might* be doable assuming SQL Server 2005. In 2005, you would have to
separate these tables to
its own filegroup and set that filegroup to read only. You can now ship a fi
legroup backup and have
them restore that filegroup backup. However, I doubt it will work for you as
you probably do
structural changes for these tables and structural changes affects the syste
m tables which are in
the primary filegroup. In the end, the problem is that SQL Server won't let
you restore a database
so different parts of the database are from different points in time. This w
ould be an advanced
usage of the backup/restore features of SQL Server 2005, so you need to make
sure that you are very
familiar with backup/restore. I would *not* even consider this route.
3. Ship script files containing CREATE TABLE, ALTER TABLE etc that your clie
nts execute using OSQL,
SQLCMD or through your own app. This is the, IMO, most robust solution and w
ill give you the least
problems in the end.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chedva" <chedvag@.matrix-it.co.il> wrote in message news:eiHxSsXHHHA.3952@.TK2MSFTNGP02.phx.g
bl...
> HI
> I'm designing a database. This database will be installed in a few servers
.
> Some of the tables in the database the user can't change only once a month
> the "center" should send their data to all clients. No connectivity betwee
n
> installations (Replication isn't relevant). What we thougth to do is one o
f
> the 2:
> 1. To have 2 databases, one will have all the tables and the other only th
e
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform dat
a
> integrity with Foreign key this way.
> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
> Is their a way to perform the 2nd way'?
> Thanks
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:emjgcQdHHHA.3468@.TK2MSFTNGP04.phx.gbl...
> 3. Ship script files containing CREATE TABLE, ALTER TABLE etc that your
> clients execute using OSQL, SQLCMD or through your own app. This is the,
> IMO, most robust solution and will give you the least problems in the end.
Don't ALL apps of this nature (distributed to X no of clients) do this
anyway? Surely the time comes to update the structure of some tables and the
client surely doesn't do it themselves. We store a database version no and
run upgrades based on this version no. We also drop all stored procs, views,
indexes, functions, primary keys etc and recreate them. This last step is
probably not suitable for very large databases but works very well for us. I
find we very rarely do a release without some db change.
Michael
I'm designing a database. This database will be installed in a few servers.
Some of the tables in the database the user can't change only once a month
the "center" should send their data to all clients. No connectivity between
installations (Replication isn't relevant). What we thougth to do is one of
the 2:
1. To have 2 databases, one will have all the tables and the other only the
"from center" tables, and once a month the center will send a backup of it
to be restore all over. the poblem is that we won't be able to perform data
integrity with Foreign key this way.
2. The other idea was to backup/restore only part of the database, maybe
depents on a schema or filegroup or so.
Is their a way to perform the 2nd way'?
Thanks"Chedva" <chedvag@.matrix-it.co.il> wrote in message
news:eiHxSsXHHHA.3952@.TK2MSFTNGP02.phx.gbl...
> HI
> I'm designing a database. This database will be installed in a few
> servers.
> Some of the tables in the database the user can't change only once a month
> the "center" should send their data to all clients. No connectivity
> between
> installations (Replication isn't relevant). What we thougth to do is one
> of
> the 2:
> 1. To have 2 databases, one will have all the tables and the other only
> the
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform
> data
> integrity with Foreign key this way.
> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
> Is their a way to perform the 2nd way'?
Can't you write something into your software to get this via the web and
update the tables? I don't think either of the options you've suggested are
the best way to go and you should not need to have 2 databases on the
client's machines.
> Thanks
>|||> 1. To have 2 databases, one will have all the tables and the other only the">
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform dat
a
> integrity with Foreign key this way.
This is an option. And the downside you already know. You can consider using
triggers to maintain
referential integrity, but I'd opt for option 3 below instead.
> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
This *might* be doable assuming SQL Server 2005. In 2005, you would have to
separate these tables to
its own filegroup and set that filegroup to read only. You can now ship a fi
legroup backup and have
them restore that filegroup backup. However, I doubt it will work for you as
you probably do
structural changes for these tables and structural changes affects the syste
m tables which are in
the primary filegroup. In the end, the problem is that SQL Server won't let
you restore a database
so different parts of the database are from different points in time. This w
ould be an advanced
usage of the backup/restore features of SQL Server 2005, so you need to make
sure that you are very
familiar with backup/restore. I would *not* even consider this route.
3. Ship script files containing CREATE TABLE, ALTER TABLE etc that your clie
nts execute using OSQL,
SQLCMD or through your own app. This is the, IMO, most robust solution and w
ill give you the least
problems in the end.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chedva" <chedvag@.matrix-it.co.il> wrote in message news:eiHxSsXHHHA.3952@.TK2MSFTNGP02.phx.g
bl...
> HI
> I'm designing a database. This database will be installed in a few servers
.
> Some of the tables in the database the user can't change only once a month
> the "center" should send their data to all clients. No connectivity betwee
n
> installations (Replication isn't relevant). What we thougth to do is one o
f
> the 2:
> 1. To have 2 databases, one will have all the tables and the other only th
e
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform dat
a
> integrity with Foreign key this way.
> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
> Is their a way to perform the 2nd way'?
> Thanks
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:emjgcQdHHHA.3468@.TK2MSFTNGP04.phx.gbl...
> 3. Ship script files containing CREATE TABLE, ALTER TABLE etc that your
> clients execute using OSQL, SQLCMD or through your own app. This is the,
> IMO, most robust solution and will give you the least problems in the end.
Don't ALL apps of this nature (distributed to X no of clients) do this
anyway? Surely the time comes to update the structure of some tables and the
client surely doesn't do it themselves. We store a database version no and
run upgrades based on this version no. We also drop all stored procs, views,
indexes, functions, primary keys etc and recreate them. This last step is
probably not suitable for very large databases but works very well for us. I
find we very rarely do a release without some db change.
Michael
Restore of Transaction
Hi
I am trying to restore my transaction log backup, I restored the database from nights backup and trying to restore the transaction log backup which i carried out immediately after the full backup, It shoots me the error below..any comments
The log in this backup set begins at LSN 113294000001538200001, which is too late to apply to the database. An earlier log backup that includes LSN 112937000001085700001 can be restored.
When you perform the restore of your database, ensure that you mark the
"Leave database nonoperational but able to restore additional transaction
logs" option from the Options tab in the Restore Database screens.
You should then be able to apply your log.
Backup Notes:
When you perform a backup of the database, the backup backs up the database
as well as any transactions that were occurring during the backup process.
When you restore that database and select the default option of leave
database operational, it will restore the data and then roll forward
committed transactions that were part of the backup I described in the
previous paragraph.
When you restore the database and choose the option to leave the db
nonoperational, then you can restore your transaction logs.
The reason this is important is that after performing a database backup and
then performing a log backup, you will have some duplicate transactions in
both backups. To avoid running the same transactions twice, you need to do
your recovery in the appropriate order.
HTH
Rick Sawtell (co-author Teach Yourself Microsoft SQL Server in 21 days)
MCT, MCSD, MCDBA
"Rodger" <Rodger@.discussions.microsoft.com> wrote in message
news:5503FFA6-3545-4D44-9A56-94C3EE83BF49@.microsoft.com...
> Hi
> I am trying to restore my transaction log backup, I restored the database
from nights backup and trying to restore the transaction log backup which i
carried out immediately after the full backup, It shoots me the error
below..any comments
> The log in this backup set begins at LSN 113294000001538200001, which is
too late to apply to the database. An earlier log backup that includes LSN
112937000001085700001 can be restored.
I am trying to restore my transaction log backup, I restored the database from nights backup and trying to restore the transaction log backup which i carried out immediately after the full backup, It shoots me the error below..any comments
The log in this backup set begins at LSN 113294000001538200001, which is too late to apply to the database. An earlier log backup that includes LSN 112937000001085700001 can be restored.
When you perform the restore of your database, ensure that you mark the
"Leave database nonoperational but able to restore additional transaction
logs" option from the Options tab in the Restore Database screens.
You should then be able to apply your log.
Backup Notes:
When you perform a backup of the database, the backup backs up the database
as well as any transactions that were occurring during the backup process.
When you restore that database and select the default option of leave
database operational, it will restore the data and then roll forward
committed transactions that were part of the backup I described in the
previous paragraph.
When you restore the database and choose the option to leave the db
nonoperational, then you can restore your transaction logs.
The reason this is important is that after performing a database backup and
then performing a log backup, you will have some duplicate transactions in
both backups. To avoid running the same transactions twice, you need to do
your recovery in the appropriate order.
HTH
Rick Sawtell (co-author Teach Yourself Microsoft SQL Server in 21 days)
MCT, MCSD, MCDBA
"Rodger" <Rodger@.discussions.microsoft.com> wrote in message
news:5503FFA6-3545-4D44-9A56-94C3EE83BF49@.microsoft.com...
> Hi
> I am trying to restore my transaction log backup, I restored the database
from nights backup and trying to restore the transaction log backup which i
carried out immediately after the full backup, It shoots me the error
below..any comments
> The log in this backup set begins at LSN 113294000001538200001, which is
too late to apply to the database. An earlier log backup that includes LSN
112937000001085700001 can be restored.
Restore of Transaction
Hi
I am trying to restore my transaction log backup, I restored the database fr
om nights backup and trying to restore the transaction log backup which i ca
rried out immediately after the full backup, It shoots me the error below..a
ny comments
The log in this backup set begins at LSN 113294000001538200001, which is too
late to apply to the database. An earlier log backup that includes LSN 1129
37000001085700001 can be restored.When you perform the restore of your database, ensure that you mark the
"Leave database nonoperational but able to restore additional transaction
logs" option from the Options tab in the Restore Database screens.
You should then be able to apply your log.
Backup Notes:
When you perform a backup of the database, the backup backs up the database
as well as any transactions that were occurring during the backup process.
When you restore that database and select the default option of leave
database operational, it will restore the data and then roll forward
committed transactions that were part of the backup I described in the
previous paragraph.
When you restore the database and choose the option to leave the db
nonoperational, then you can restore your transaction logs.
The reason this is important is that after performing a database backup and
then performing a log backup, you will have some duplicate transactions in
both backups. To avoid running the same transactions twice, you need to do
your recovery in the appropriate order.
HTH
Rick Sawtell (co-author Teach Yourself Microsoft SQL Server in 21 days)
MCT, MCSD, MCDBA
"Rodger" <Rodger@.discussions.microsoft.com> wrote in message
news:5503FFA6-3545-4D44-9A56-94C3EE83BF49@.microsoft.com...
> Hi
> I am trying to restore my transaction log backup, I restored the database
from nights backup and trying to restore the transaction log backup which i
carried out immediately after the full backup, It shoots me the error
below..any comments
> The log in this backup set begins at LSN 113294000001538200001, which is
too late to apply to the database. An earlier log backup that includes LSN
112937000001085700001 can be restored.
I am trying to restore my transaction log backup, I restored the database fr
om nights backup and trying to restore the transaction log backup which i ca
rried out immediately after the full backup, It shoots me the error below..a
ny comments
The log in this backup set begins at LSN 113294000001538200001, which is too
late to apply to the database. An earlier log backup that includes LSN 1129
37000001085700001 can be restored.When you perform the restore of your database, ensure that you mark the
"Leave database nonoperational but able to restore additional transaction
logs" option from the Options tab in the Restore Database screens.
You should then be able to apply your log.
Backup Notes:
When you perform a backup of the database, the backup backs up the database
as well as any transactions that were occurring during the backup process.
When you restore that database and select the default option of leave
database operational, it will restore the data and then roll forward
committed transactions that were part of the backup I described in the
previous paragraph.
When you restore the database and choose the option to leave the db
nonoperational, then you can restore your transaction logs.
The reason this is important is that after performing a database backup and
then performing a log backup, you will have some duplicate transactions in
both backups. To avoid running the same transactions twice, you need to do
your recovery in the appropriate order.
HTH
Rick Sawtell (co-author Teach Yourself Microsoft SQL Server in 21 days)
MCT, MCSD, MCDBA
"Rodger" <Rodger@.discussions.microsoft.com> wrote in message
news:5503FFA6-3545-4D44-9A56-94C3EE83BF49@.microsoft.com...
> Hi
> I am trying to restore my transaction log backup, I restored the database
from nights backup and trying to restore the transaction log backup which i
carried out immediately after the full backup, It shoots me the error
below..any comments
> The log in this backup set begins at LSN 113294000001538200001, which is
too late to apply to the database. An earlier log backup that includes LSN
112937000001085700001 can be restored.
Monday, February 20, 2012
restore master
Hi
I want to restore master db(first) in another server
which has sql server 2000 before restoring user dbs,
to locate login and ...
but following error happended,what's wrong?
"resore database must be used in single user mode
when restoring master db"
and I found there's only one login name called "sa"
as system administrator,
but when I expand the user branch in master database
I found "sa" and "guest" there,
and when I tried to drop guest following error happened too:
"cannot drop the guest user from master or tempdb"
1 - does not restoring master related to guest?
2 - anyway,how can I drop guest?
any help would be greatly thanked.
thanks,
but can I just script my login definition and
relation between them and he users and run it in destination
instead of restoring master database?
On Sat, 10 Jul 2004 05:44:31 -0700, Thirumal <treddym@.yahoo.nospam.com>
wrote:
[vbcol=seagreen]
> Hi,
> 1]To restore master database u need to start SQL service
> in single user mode. navigate to appropriate SQL Server
> directory and issue the below at command prompt.
> sqlservr.exe -c -m
> 2]It is not recommended to drop guest login from master
> and tempdb.
> When users login into SQL Server, by default they have
> access to 'master' database. SQL Server internally uses
> guest for access , who has less permissions, so need not
> worry. If u drop 'guest' (assuming that it is allowed) you
> must be a 'sa' to access the master all the time.
>
> With Regards
> Thirumal
> www.thirumal.com
> too:
|||and how can I understand that I
"start SQL service in single user mode"?
On Sat, 10 Jul 2004 05:44:31 -0700, Thirumal <treddym@.yahoo.nospam.com>
wrote:
[vbcol=seagreen]
> Hi,
> 1]To restore master database u need to start SQL service
> in single user mode. navigate to appropriate SQL Server
> directory and issue the below at command prompt.
> sqlservr.exe -c -m
> 2]It is not recommended to drop guest login from master
> and tempdb.
> When users login into SQL Server, by default they have
> access to 'master' database. SQL Server internally uses
> guest for access , who has less permissions, so need not
> worry. If u drop 'guest' (assuming that it is allowed) you
> must be a 'sa' to access the master all the time.
>
> With Regards
> Thirumal
> www.thirumal.com
> too:
|||Hi,
Login can be copied from source server to destination server. But Master
database stores the other details like:-
1. Configuration parameters
2. Server Names ,..
So after creating the logins you have to manually sync. that as well.
See the below link to transfer logins from one server to another server with
the same password.
http://www.databasejournal.com/featu...le.php/2228611
Thanks
Hari
MCDBA
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsayeffn6hqligo@.msnews.microsoft.com...
> thanks,
> but can I just script my login definition and
> relation between them and he users and run it in destination
> instead of restoring master database?
>
> On Sat, 10 Jul 2004 05:44:31 -0700, Thirumal <treddym@.yahoo.nospam.com>
> wrote:
>
|||Hi,
Execute the below command from Query Analyzer:-
select serverproperty('IsSingleUser')
If the value returned is "1" then the server is in single user mode.
If the value returned is "0" then the server is in multi user mode.
Thanks
Hari
MCDBA
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsayehds6hqligo@.msnews.microsoft.com...
> and how can I understand that I
> "start SQL service in single user mode"?
> On Sat, 10 Jul 2004 05:44:31 -0700, Thirumal <treddym@.yahoo.nospam.com>
> wrote:
>
I want to restore master db(first) in another server
which has sql server 2000 before restoring user dbs,
to locate login and ...
but following error happended,what's wrong?
"resore database must be used in single user mode
when restoring master db"
and I found there's only one login name called "sa"
as system administrator,
but when I expand the user branch in master database
I found "sa" and "guest" there,
and when I tried to drop guest following error happened too:
"cannot drop the guest user from master or tempdb"
1 - does not restoring master related to guest?
2 - anyway,how can I drop guest?
any help would be greatly thanked.
thanks,
but can I just script my login definition and
relation between them and he users and run it in destination
instead of restoring master database?
On Sat, 10 Jul 2004 05:44:31 -0700, Thirumal <treddym@.yahoo.nospam.com>
wrote:
[vbcol=seagreen]
> Hi,
> 1]To restore master database u need to start SQL service
> in single user mode. navigate to appropriate SQL Server
> directory and issue the below at command prompt.
> sqlservr.exe -c -m
> 2]It is not recommended to drop guest login from master
> and tempdb.
> When users login into SQL Server, by default they have
> access to 'master' database. SQL Server internally uses
> guest for access , who has less permissions, so need not
> worry. If u drop 'guest' (assuming that it is allowed) you
> must be a 'sa' to access the master all the time.
>
> With Regards
> Thirumal
> www.thirumal.com
> too:
|||and how can I understand that I
"start SQL service in single user mode"?
On Sat, 10 Jul 2004 05:44:31 -0700, Thirumal <treddym@.yahoo.nospam.com>
wrote:
[vbcol=seagreen]
> Hi,
> 1]To restore master database u need to start SQL service
> in single user mode. navigate to appropriate SQL Server
> directory and issue the below at command prompt.
> sqlservr.exe -c -m
> 2]It is not recommended to drop guest login from master
> and tempdb.
> When users login into SQL Server, by default they have
> access to 'master' database. SQL Server internally uses
> guest for access , who has less permissions, so need not
> worry. If u drop 'guest' (assuming that it is allowed) you
> must be a 'sa' to access the master all the time.
>
> With Regards
> Thirumal
> www.thirumal.com
> too:
|||Hi,
Login can be copied from source server to destination server. But Master
database stores the other details like:-
1. Configuration parameters
2. Server Names ,..
So after creating the logins you have to manually sync. that as well.
See the below link to transfer logins from one server to another server with
the same password.
http://www.databasejournal.com/featu...le.php/2228611
Thanks
Hari
MCDBA
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsayeffn6hqligo@.msnews.microsoft.com...
> thanks,
> but can I just script my login definition and
> relation between them and he users and run it in destination
> instead of restoring master database?
>
> On Sat, 10 Jul 2004 05:44:31 -0700, Thirumal <treddym@.yahoo.nospam.com>
> wrote:
>
|||Hi,
Execute the below command from Query Analyzer:-
select serverproperty('IsSingleUser')
If the value returned is "1" then the server is in single user mode.
If the value returned is "0" then the server is in multi user mode.
Thanks
Hari
MCDBA
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsayehds6hqligo@.msnews.microsoft.com...
> and how can I understand that I
> "start SQL service in single user mode"?
> On Sat, 10 Jul 2004 05:44:31 -0700, Thirumal <treddym@.yahoo.nospam.com>
> wrote:
>
Subscribe to:
Posts (Atom)