Getting error MS SQL2000: backup

显示标签为“backup”的博文。显示所有博文

2012年3月29日星期四

Fulldatabase backup query

SQL Server full backup displays the following message on successful
completion of the full database backup.
Processed 1136 pages for database 'MyDB', file 'MyDB' on file 1.
Processed 3 pages for database 'MyDB', file 'MyDB_log' on file 1.
BACKUP DATABASE successfully processed 1139 pages in 0.902 seconds (10.336
MB/sec).
I understand SQL Server manages data files in the form of pages and thats
the reason on completion of backup it display message "Processed 1136 pages
for database 'MyDB', file 'MyDB' on file 1". But why does it display message
"Processed 3 pages for database 'MyDB', file 'MyDB_log' on file 1" when the
transaction log files are not managed in the form of pages.Hi
When BACKUP started , it reads (backups) all data pages and last step it
goes to LOG file and reads/backups all the data in the LOG from the point
the BACKUP was started
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:CA26E224-37C4-4C61-8F37-579C7A357DE7@.microsoft.com...
> SQL Server full backup displays the following message on successful
> completion of the full database backup.
> Processed 1136 pages for database 'MyDB', file 'MyDB' on file 1.
> Processed 3 pages for database 'MyDB', file 'MyDB_log' on file 1.
> BACKUP DATABASE successfully processed 1139 pages in 0.902 seconds (10.336
> MB/sec).
> I understand SQL Server manages data files in the form of pages and thats
> the reason on completion of backup it display message "Processed 1136
> pages
> for database 'MyDB', file 'MyDB' on file 1". But why does it display
> message
> "Processed 3 pages for database 'MyDB', file 'MyDB_log' on file 1" when
> the
> transaction log files are not managed in the form of pages.
>|||The backup database process includes the log records produced while the back
up was running. When you
do RESTORE, the log records are used to give you a consistent view of the da
ta (by doing REDO and
UNDO of those log records, just as each time the database is started),.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:CA26E224-37C4-4C61-8F37-579C7A357DE7@.microsoft.com...
> SQL Server full backup displays the following message on successful
> completion of the full database backup.
> Processed 1136 pages for database 'MyDB', file 'MyDB' on file 1.
> Processed 3 pages for database 'MyDB', file 'MyDB_log' on file 1.
> BACKUP DATABASE successfully processed 1139 pages in 0.902 seconds (10.336
> MB/sec).
> I understand SQL Server manages data files in the form of pages and thats
> the reason on completion of backup it display message "Processed 1136 page
s
> for database 'MyDB', file 'MyDB' on file 1". But why does it display messa
ge
> "Processed 3 pages for database 'MyDB', file 'MyDB_log' on file 1" when th
e
> transaction log files are not managed in the form of pages.
>

Fulldatabase backup query

SQL Server full backup displays the following message on successful
completion of the full database backup.
Processed 1136 pages for database 'MyDB', file 'MyDB' on file 1.
Processed 3 pages for database 'MyDB', file 'MyDB_log' on file 1.
BACKUP DATABASE successfully processed 1139 pages in 0.902 seconds (10.336
MB/sec).
I understand SQL Server manages data files in the form of pages and thats
the reason on completion of backup it display message "Processed 1136 pages
for database 'MyDB', file 'MyDB' on file 1". But why does it display message
"Processed 3 pages for database 'MyDB', file 'MyDB_log' on file 1" when the
transaction log files are not managed in the form of pages.Hi
When BACKUP started , it reads (backups) all data pages and last step it
goes to LOG file and reads/backups all the data in the LOG from the point
the BACKUP was started
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:CA26E224-37C4-4C61-8F37-579C7A357DE7@.microsoft.com...
> SQL Server full backup displays the following message on successful
> completion of the full database backup.
> Processed 1136 pages for database 'MyDB', file 'MyDB' on file 1.
> Processed 3 pages for database 'MyDB', file 'MyDB_log' on file 1.
> BACKUP DATABASE successfully processed 1139 pages in 0.902 seconds (10.336
> MB/sec).
> I understand SQL Server manages data files in the form of pages and thats
> the reason on completion of backup it display message "Processed 1136
> pages
> for database 'MyDB', file 'MyDB' on file 1". But why does it display
> message
> "Processed 3 pages for database 'MyDB', file 'MyDB_log' on file 1" when
> the
> transaction log files are not managed in the form of pages.
>|||The backup database process includes the log records produced while the backup was running. When you
do RESTORE, the log records are used to give you a consistent view of the data (by doing REDO and
UNDO of those log records, just as each time the database is started),.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:CA26E224-37C4-4C61-8F37-579C7A357DE7@.microsoft.com...
> SQL Server full backup displays the following message on successful
> completion of the full database backup.
> Processed 1136 pages for database 'MyDB', file 'MyDB' on file 1.
> Processed 3 pages for database 'MyDB', file 'MyDB_log' on file 1.
> BACKUP DATABASE successfully processed 1139 pages in 0.902 seconds (10.336
> MB/sec).
> I understand SQL Server manages data files in the form of pages and thats
> the reason on completion of backup it display message "Processed 1136 pages
> for database 'MyDB', file 'MyDB' on file 1". But why does it display message
> "Processed 3 pages for database 'MyDB', file 'MyDB_log' on file 1" when the
> transaction log files are not managed in the form of pages.
>

full, differential, transaction hwo to rotate the differential?

Being of the "point and click"-generation I managed to create some sort of a fairly simple backup plan for our databases. There are hourly transactional backups and daily full backups, which were set up with a database maintenance plan. As the databases store event records they are ever increasing around the hour. The daily backups are now of a size that holding them is not feasable anymore and I want to move to a differential backup scheme:

Weekly full backups

Daily differential backups

Hourly transactional log backups

The problem I have is that the database maintenance plans allow me to rotate backup files automatically as they included the date and time of the backup. For differential backups there is no option in the Database Maintenance plan so I want to create them manually. The problem now is that I can create one device, append the differential backup to it. But how do I rotate the differential backup device name, let's say bi-weekly?

(untested):

1. script out the diff backup plan.
2. create a sql job that runs every 2 week that drops the diff plan, creates a new one with the desired device name.|||

Do you mean with the SQL-DMO? Or is there another way

oj wrote:
(untested):
1. script out the diff backup plan.
2. create a sql job that runs every 2 week that drops the diff plan, creates a new one with the desired device name.

|||

So basically I have no clue

1) how to script out a diff backup plan

2) how to create a sql job that drops the diff plan, creates a new one with the desired device name.

|||

I am not in front of a sqlserver so can't verify. but if:

1. create a differential backup job just as you would normally.

2. go to sql jobs under sqlagent, right click on the job and script it out. This is where you get the definition for the diff job (i.e. job id, name, steps, etc.).

3. take note of the step id and steps definition.

4. create a new sql job that runs every 4 week. In the jobstep definition for this new job (say, switchdevice), you'd want to execute

sp_update_jobstep @.jobname='the_diff_jobname',@.stepid=<the stepid of the diff job>,@.command='new_backup_command_pointing_to_a_desired_device'

5. create another job that does the same thing as the #4 but with a different device name. Be sure to schedule 2 weeks before or after the job in #4.

With the combo of #4 and #5, you essentially have a device name changed every 2 week.

|||thanks for your elaborat answer oj. one thing is still puzzling me. If I create the diff backup to append to the device it will append even after switching. So the device will be ever growing. We can live with a device per week and manually cleanup old one's. Would you know a way to automatically generate these devices? Or should I stop trying and just create them by hand?|||

You can take a look at sp_addumpdevice and sp_dropdevice in sql book online.

Cheers,

|||

Ok, Thanks for the support here. What we end up doing is the following:

We've created two backup devices per database

Two bi-weekly (every odd/even week) scheduled job performs a full back with INIT
BACKUP DATABASE base
TO base_wk<n>
WITH INIT

A daily job backs up a differential
BACKUP DATABASE base
TO base_wk<n>
WITH DIFFERENTIAL

Every half hour a transaction log back is performed
BACKUP LOG base
TO base_wk<n>

In each case <n> is either 1 or 0 for odd and even weeks. Numerous links I've found on backups seem to fail to see the real possibility that during a full backup due to a hardware failure both database and backup are lost, not really disastor save, which backing up is all about.

So there are in total three scheduled jobs: one weekly, one daily and one every half hour. schedule in bi-weekly intervals writing to base_wk0 and base_wk1 alternatively. Furthermore for historic reasons a monthly full backup is scheduled using the wizard which stores a unique backup file for every month.

Let the disastors happen...

full, differential, transaction hwo to rotate the differential?

Weekly full backups

Daily differential backups

Hourly transactional log backups

(untested):

1. script out the diff backup plan.
2. create a sql job that runs every 2 week that drops the diff plan, creates a new one with the desired device name.|||

Do you mean with the SQL-DMO? Or is there another way

oj wrote:
(untested):
1. script out the diff backup plan.
2. create a sql job that runs every 2 week that drops the diff plan, creates a new one with the desired device name.

|||

So basically I have no clue

1) how to script out a diff backup plan

2) how to create a sql job that drops the diff plan, creates a new one with the desired device name.

|||

I am not in front of a sqlserver so can't verify. but if:

1. create a differential backup job just as you would normally.

2. go to sql jobs under sqlagent, right click on the job and script it out. This is where you get the definition for the diff job (i.e. job id, name, steps, etc.).

3. take note of the step id and steps definition.

4. create a new sql job that runs every 4 week. In the jobstep definition for this new job (say, switchdevice), you'd want to execute

sp_update_jobstep @.jobname='the_diff_jobname',@.stepid=<the stepid of the diff job>,@.command='new_backup_command_pointing_to_a_desired_device'

5. create another job that does the same thing as the #4 but with a different device name. Be sure to schedule 2 weeks before or after the job in #4.

With the combo of #4 and #5, you essentially have a device name changed every 2 week.

You can take a look at sp_addumpdevice and sp_dropdevice in sql book online.

Cheers,

|||

Ok, Thanks for the support here. What we end up doing is the following:

We've created two backup devices per database

Two bi-weekly (every odd/even week) scheduled job performs a full back with INIT
BACKUP DATABASE base
TO base_wk<n>
WITH INIT

A daily job backs up a differential
BACKUP DATABASE base
TO base_wk<n>
WITH DIFFERENTIAL

Every half hour a transaction log back is performed
BACKUP LOG base
TO base_wk<n>

Let the disastors happen...

full, differential, transaction hwo to rotate the differential?

Weekly full backups

Daily differential backups

Hourly transactional log backups

(untested):

1. script out the diff backup plan.
2. create a sql job that runs every 2 week that drops the diff plan, creates a new one with the desired device name.|||

Do you mean with the SQL-DMO? Or is there another way

oj wrote:
(untested):
1. script out the diff backup plan.
2. create a sql job that runs every 2 week that drops the diff plan, creates a new one with the desired device name.

|||

So basically I have no clue

1) how to script out a diff backup plan

2) how to create a sql job that drops the diff plan, creates a new one with the desired device name.

|||

I am not in front of a sqlserver so can't verify. but if:

1. create a differential backup job just as you would normally.

2. go to sql jobs under sqlagent, right click on the job and script it out. This is where you get the definition for the diff job (i.e. job id, name, steps, etc.).

3. take note of the step id and steps definition.

4. create a new sql job that runs every 4 week. In the jobstep definition for this new job (say, switchdevice), you'd want to execute

sp_update_jobstep @.jobname='the_diff_jobname',@.stepid=<the stepid of the diff job>,@.command='new_backup_command_pointing_to_a_desired_device'

5. create another job that does the same thing as the #4 but with a different device name. Be sure to schedule 2 weeks before or after the job in #4.

With the combo of #4 and #5, you essentially have a device name changed every 2 week.

You can take a look at sp_addumpdevice and sp_dropdevice in sql book online.

Cheers,

|||

Ok, Thanks for the support here. What we end up doing is the following:

We've created two backup devices per database

Two bi-weekly (every odd/even week) scheduled job performs a full back with INIT
BACKUP DATABASE base
TO base_wk<n>
WITH INIT

A daily job backs up a differential
BACKUP DATABASE base
TO base_wk<n>
WITH DIFFERENTIAL

Every half hour a transaction log back is performed
BACKUP LOG base
TO base_wk<n>

Let the disastors happen...

sql

2012年3月11日星期日

full text indexing backup

Hi,
Is it possible to backup and restore full text indexing?
If Yes, how to do it?
Can we restore that backup when full text index
is 'recovering'?
Help would be appreciated.
Thanks in advance.
Rajah V.
http://support.microsoft.com/default...b;en-us;240867
"Rajah" <rajah.venkat@.analytics-usa.com> wrote in message
news:1d71101c4231b$cc05d540$a101280a@.phx.gbl...
> Hi,
> Is it possible to backup and restore full text indexing?
> If Yes, how to do it?
> Can we restore that backup when full text index
> is 'recovering'?
> Help would be appreciated.
> Thanks in advance.
> Rajah V.

2012年3月7日星期三

Full text catalog is paused

I have 2 full text catalogs, and one of them (sometimes both) goes to pause
status occasionally. I suspect this occurs during a backup.
I checked the status of the Microsoft search service and it states that it
is "started", but I've found that restarting the service unpauses the
catalog.
This is an improvement on my previous solution of rebuilding the catalog...
Any ideas what may be causing the pausing?
Or failing that, how can I issue a restart command to the service?
I have VB6 apps running that can detect the paused catalog, so restarting
would be simple if I knew how.
TIA
Message posted via http://www.sqlmonster.com
I should have mentioned that the error from the event log when the pausing
occurs is:
The entry
<MSSQL75://SQLSERVER/619B8048/38323766373135392D356666392D343238342D626235382D30 3738356466653130323562>
cannot be inserted into the history for project <SQLServer SQL0000700006>.
Error: 80070426 - The service has not been started. .
Message posted via http://www.sqlmonster.com
|||Is this a cluster?
"Roger Hocking via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:fea4a010a3224b3aab9c310c37f33da8@.SQLMonster.c om...
>I should have mentioned that the error from the event log when the pausing
> occurs is:
> The entry
> <MSSQL75://SQLSERVER/619B8048/38323766373135392D356666392D343238342D626235382D30 3738356466653130323562>
> cannot be inserted into the history for project <SQLServer SQL0000700006>.
> Error: 80070426 - The service has not been started. .
> --
> Message posted via http://www.sqlmonster.com
|||No, just a single server.
Message posted via http://www.sqlmonster.com

Full Sql Server Backup

Hi,
I'm trying to make a Full backup of my Sql Server. I use Tivoli Storage
Manager for my backups. First i do a full backup of my files on the server,
then i use an Agent to create a backup of the databases. My backup works
fine. But when i do a restore i can't start my MSSQLSERVER services because
my database Master isn't there, and i can't restore my db Master because
MSSQLSERVER isn't running.
I got a few errors before that, but it was the folder name that was changed
to 8.3 naming in the registry, i fixed it.
Anyone have an idea how a can do a full backup of my server so that i can
restore it completly to a new server?
ThanksStop SQL Services and perform file backup. Restore file backups with sql
services on new server stopped. Clean up.
"Phil" wrote:
> Hi,
> I'm trying to make a Full backup of my Sql Server. I use Tivoli Storage
> Manager for my backups. First i do a full backup of my files on the server,
> then i use an Agent to create a backup of the databases. My backup works
> fine. But when i do a restore i can't start my MSSQLSERVER services because
> my database Master isn't there, and i can't restore my db Master because
> MSSQLSERVER isn't running.
> I got a few errors before that, but it was the folder name that was changed
> to 8.3 naming in the registry, i fixed it.
> Anyone have an idea how a can do a full backup of my server so that i can
> restore it completly to a new server?
> Thanks|||Ok that's on solution, but is there a way to do it without stopping the
services?
"Jeffrey K. Ericson" wrote:
> Stop SQL Services and perform file backup. Restore file backups with sql
> services on new server stopped. Clean up.
> "Phil" wrote:
> > Hi,
> >
> > I'm trying to make a Full backup of my Sql Server. I use Tivoli Storage
> > Manager for my backups. First i do a full backup of my files on the server,
> > then i use an Agent to create a backup of the databases. My backup works
> > fine. But when i do a restore i can't start my MSSQLSERVER services because
> > my database Master isn't there, and i can't restore my db Master because
> > MSSQLSERVER isn't running.
> > I got a few errors before that, but it was the folder name that was changed
> > to 8.3 naming in the registry, i fixed it.
> > Anyone have an idea how a can do a full backup of my server so that i can
> > restore it completly to a new server?
> >
> > Thanks|||Hi
That is not a supported way of backing up and restoring a SQL Server
installation.
You must backup your databases to disk via the backup command in SQL Server.
Thne backup the resulatnt backup files to tape.
On the other SQL Server, restore the backups using the restore command.
To restore master, you must follow "Restoring the master Database from a
Current Backup" in books online.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:69E8A7C7-CDA0-4A1B-9863-74C23B855133@.microsoft.com...
> Ok that's on solution, but is there a way to do it without stopping the
> services?
>
> "Jeffrey K. Ericson" wrote:
>> Stop SQL Services and perform file backup. Restore file backups with sql
>> services on new server stopped. Clean up.
>> "Phil" wrote:
>> > Hi,
>> >
>> > I'm trying to make a Full backup of my Sql Server. I use Tivoli Storage
>> > Manager for my backups. First i do a full backup of my files on the
>> > server,
>> > then i use an Agent to create a backup of the databases. My backup
>> > works
>> > fine. But when i do a restore i can't start my MSSQLSERVER services
>> > because
>> > my database Master isn't there, and i can't restore my db Master
>> > because
>> > MSSQLSERVER isn't running.
>> > I got a few errors before that, but it was the folder name that was
>> > changed
>> > to 8.3 naming in the registry, i fixed it.
>> > Anyone have an idea how a can do a full backup of my server so that i
>> > can
>> > restore it completly to a new server?
>> >
>> > Thanks|||Ok what your telling me is that it's impossible to do a full backup a my SQL
server, and a backup of the database, and to restore to a new server(with
nothing on)?
Is that correct?
I would need to backup my DBs, then install a new SQL Server and then
restore my DB's?
Thanks
Philip
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> That is not a supported way of backing up and restoring a SQL Server
> installation.
> You must backup your databases to disk via the backup command in SQL Server.
> Thne backup the resulatnt backup files to tape.
> On the other SQL Server, restore the backups using the restore command.
> To restore master, you must follow "Restoring the master Database from a
> Current Backup" in books online.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Phil" <Phil@.discussions.microsoft.com> wrote in message
> news:69E8A7C7-CDA0-4A1B-9863-74C23B855133@.microsoft.com...
> > Ok that's on solution, but is there a way to do it without stopping the
> > services?
> >
> >
> > "Jeffrey K. Ericson" wrote:
> >
> >> Stop SQL Services and perform file backup. Restore file backups with sql
> >> services on new server stopped. Clean up.
> >>
> >> "Phil" wrote:
> >>
> >> > Hi,
> >> >
> >> > I'm trying to make a Full backup of my Sql Server. I use Tivoli Storage
> >> > Manager for my backups. First i do a full backup of my files on the
> >> > server,
> >> > then i use an Agent to create a backup of the databases. My backup
> >> > works
> >> > fine. But when i do a restore i can't start my MSSQLSERVER services
> >> > because
> >> > my database Master isn't there, and i can't restore my db Master
> >> > because
> >> > MSSQLSERVER isn't running.
> >> > I got a few errors before that, but it was the folder name that was
> >> > changed
> >> > to 8.3 naming in the registry, i fixed it.
> >> > Anyone have an idea how a can do a full backup of my server so that i
> >> > can
> >> > restore it completly to a new server?
> >> >
> >> > Thanks
>
>

Full Sql Server Backup

full server backup

is that possible to make a full server backup which include all databases in
the current server , DTS packages and everything else in one single backup
file?No. The backup restore mechanism works at database level, not server level.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
is that possible to make a full server backup which include all databases in
the current server , DTS packages and everything else in one single backup
file?|||Hi,Vyas
He can use undocumented stored procedure to do that
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
BTW. What is about .NET examples on your site that you have promised us some
time ago :-)
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> No. The backup restore mechanism works at database level, not server
> level.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> is that possible to make a full server backup which include all databases
> in
> the current server , DTS packages and everything else in one single backup
> file?
>|||Uri, your script is still creating multiple backup files, which is not what
the OP wanted. Anyway, you could still write multiple database backups to a
single backup file though.
Anyway, I just wanted to point out that you cannot run a single command, and
create a single backup with everything that is there on the server.
Regarding the .Net code samples, hopefully before end of this year :-)
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uqh7DY3kFHA.3256@.TK2MSFTNGP12.phx.gbl...
Hi,Vyas
He can use undocumented stored procedure to do that
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
BTW. What is about .NET examples on your site that you have promised us some
time ago :-)
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> No. The backup restore mechanism works at database level, not server
> level.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> is that possible to make a full server backup which include all databases
> in
> the current server , DTS packages and everything else in one single backup
> file?
>|||is that code will backup the DTS packeges too?
Regards.
OreN101
"Uri Dimant" wrote:
> Hi,Vyas
> He can use undocumented stored procedure to do that
>
> EXEC sp_MSforeachdb
> 'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
> ''pubs'')
> BEGIN
> DECLARE @.sql varchar(1000);
> set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK => ''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
> EXEC (@.sql)
> END'
> BTW. What is about .NET examples on your site that you have promised us some
> time ago :-)
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> > No. The backup restore mechanism works at database level, not server
> > level.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> >
> >
> > "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> > news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> > is that possible to make a full server backup which include all databases
> > in
> > the current server , DTS packages and everything else in one single backup
> > file?
> >
> >
>
>|||Yes, if your DTS packages are stored in SQL Server.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:0937D676-861F-4FFB-9174-156131BFC4E8@.microsoft.com...
is that code will backup the DTS packeges too?
Regards.
OreN101
"Uri Dimant" wrote:
> Hi,Vyas
> He can use undocumented stored procedure to do that
>
> EXEC sp_MSforeachdb
> 'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
> ''pubs'')
> BEGIN
> DECLARE @.sql varchar(1000);
> set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK => ''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
> EXEC (@.sql)
> END'
> BTW. What is about .NET examples on your site that you have promised us
some
> time ago :-)
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> > No. The backup restore mechanism works at database level, not server
> > level.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> >
> >
> > "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> > news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> > is that possible to make a full server backup which include all
databases
> > in
> > the current server , DTS packages and everything else in one single
backup
> > file?
> >
> >
>
>|||how come? the script above make backups to the databases in the sql server
instance ,not to any other objects(like DTS)?
"Oren101" wrote:
> is that code will backup the DTS packeges too?
> Regards.
> OreN101
> "Uri Dimant" wrote:
> > Hi,Vyas
> > He can use undocumented stored procedure to do that
> >
> >
> > EXEC sp_MSforeachdb
> > 'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
> > ''pubs'')
> > BEGIN
> > DECLARE @.sql varchar(1000);
> > set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK => > ''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
> > EXEC (@.sql)
> > END'
> >
> > BTW. What is about .NET examples on your site that you have promised us some
> > time ago :-)
> >
> >
> >
> > "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> > news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> > > No. The backup restore mechanism works at database level, not server
> > > level.
> > > --
> > > HTH,
> > > Vyas, MVP (SQL Server)
> > > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> > >
> > >
> > > "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> > > news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> > > is that possible to make a full server backup which include all databases
> > > in
> > > the current server , DTS packages and everything else in one single backup
> > > file?
> > >
> > >
> >
> >
> >|||I agree - how does this grab the DTS packages? The DTS packages are
saved to the server - not to a database. The backup command only
references a specific database.|||If you save DTS packages to SQL Server, then they get stored in the msdb
database.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122560371.634115.202710@.z14g2000cwz.googlegroups.com...
I agree - how does this grab the DTS packages? The DTS packages are
saved to the server - not to a database. The backup command only
references a specific database.|||Well, now there's something I didn't realize. These newsgroups are
helpful aren't they. :)|||where exactly can i c it un the msdb?
"Narayana Vyas Kondreddi" wrote:
> If you save DTS packages to SQL Server, then they get stored in the msdb
> database.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> <unc27932@.yahoo.com> wrote in message
> news:1122560371.634115.202710@.z14g2000cwz.googlegroups.com...
> I agree - how does this grab the DTS packages? The DTS packages are
> saved to the server - not to a database. The backup command only
> references a specific database.
>
>|||sysdtspackages
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:7C78600B-9414-48A8-9B9B-2E8312D9A223@.microsoft.com...
> where exactly can i c it un the msdb?
> "Narayana Vyas Kondreddi" wrote:
>> If you save DTS packages to SQL Server, then they get stored in the msdb
>> database.
>> --
>> HTH,
>> Vyas, MVP (SQL Server)
>> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>>
>> <unc27932@.yahoo.com> wrote in message
>> news:1122560371.634115.202710@.z14g2000cwz.googlegroups.com...
>> I agree - how does this grab the DTS packages? The DTS packages are
>> saved to the server - not to a database. The backup command only
>> references a specific database.
>>|||sysdtspackages
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:7C78600B-9414-48A8-9B9B-2E8312D9A223@.microsoft.com...
where exactly can i c it un the msdb?
"Narayana Vyas Kondreddi" wrote:
> If you save DTS packages to SQL Server, then they get stored in the msdb
> database.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> <unc27932@.yahoo.com> wrote in message
> news:1122560371.634115.202710@.z14g2000cwz.googlegroups.com...
> I agree - how does this grab the DTS packages? The DTS packages are
> saved to the server - not to a database. The backup command only
> references a specific database.
>
>|||The closest thing to that would be to stop the server and to zip all the
database files (mdf and ldf) to 1 archive.
--
Cheers,
Wojtek
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> is that possible to make a full server backup which include all databases
> in
> the current server , DTS packages and everything else in one single backup
> file?|||It would be much easier and supported to just do regular database backups of each database, all
going to the same backup file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wojtek Garwol" <garwol@.usunto.poczta.fm> wrote in message news:dcb893$r69$1@.nemesis.news.tpi.pl...
> The closest thing to that would be to stop the server and to zip all the database files (mdf and
> ldf) to 1 archive.
> --
> Cheers,
> Wojtek
>
> "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
>> is that possible to make a full server backup which include all databases in
>> the current server , DTS packages and everything else in one single backup
>> file?
>

full server backup

2012年2月26日星期日

Full Restore Question

Did a point in time restore on Win2k Server EE MSSQL 2K EE sp3a
Database does a full back at 1am
Then from 4am on does a trans log backup
So had all file from 1am to about 5pm ( when the restore was attempted
)
I personally dont feel good about PITR but it was not my call.
Did the point in time recovery to 1206pm on that same day.
The restore ended up being right back at the time the restore was
started.
Any ideas?
I noticed doing the restore from Enterprise Manager when the checkbox
for point in time was clicked the list of backup files with checks by
them did not change.
Dont think they are supposed to.
How can I find out at what point in time the database actually did the
restore too?EM has a but in the PITR dialog, as it natializes that datetime display and sends that string into
SQL Server, which in turn can be misinterpreted. Do the restore from QA and use a language neutral
way of expressing the datetime literals (STOPAT value), like 'YYYYMMDD hh:mm:ss'.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136608836.006898.300840@.g44g2000cwa.googlegroups.com...
> Did a point in time restore on Win2k Server EE MSSQL 2K EE sp3a
> Database does a full back at 1am
> Then from 4am on does a trans log backup
> So had all file from 1am to about 5pm ( when the restore was attempted
> )
> I personally dont feel good about PITR but it was not my call.
> Did the point in time recovery to 1206pm on that same day.
> The restore ended up being right back at the time the restore was
> started.
> Any ideas?
> I noticed doing the restore from Enterprise Manager when the checkbox
> for point in time was clicked the list of backup files with checks by
> them did not change.
> Dont think they are supposed to.
> How can I find out at what point in time the database actually did the
> restore too?
>|||Thanks for the reply.
Question:
How buggy is just applying the full backup and then the transaction
logs that I need?
I have
1 full DB backup 1AM
- trans log at 4AM, 5, 6, 7, 8, 9, 10, 11,
12pm, etc
I wanted to go back to the 12PM trans log
so I can check all from 1AM to that 12pm trans log and I should be
good? Or does it natialize that also?|||The dialog problem is datetime related. So if you don't do point in time restore, you don't specify
the date and time, and you will not see that problem. But why not just execute the RESTORE command
from Query Analyzer instead?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136772868.780191.238640@.f14g2000cwb.googlegroups.com...
> Thanks for the reply.
> Question:
> How buggy is just applying the full backup and then the transaction
> logs that I need?
> I have
> 1 full DB backup 1AM
> - trans log at 4AM, 5, 6, 7, 8, 9, 10, 11,
> 12pm, etc
> I wanted to go back to the 12PM trans log
> so I can check all from 1AM to that 12pm trans log and I should be
> good? Or does it natialize that also?
>|||why not do it from where I am? I am not being smart, I just dont know
the benefits vs disadvantages|||Well, since you don't do PITR, you will not get your most recent data. Also, as you have noticed,
there's a bug in EM, and such we will find from time to time. Learning writing and executing TSQL
makes us handle those situations quickly and solve the problem with minimal impact on your
production system. I think that Aaron has an article on EM vs. QA on www.aspfaq.com.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136843293.506230.8680@.g14g2000cwa.googlegroups.com...
> why not do it from where I am? I am not being smart, I just dont know
> the benefits vs disadvantages
>|||Thank You Tibor for your time and your professionalism.
I will read that article.
I will continue my walk away from the Oracle world !!!

Full recovery mode and large log

Hi,
I have several databases using full recovery mode.
Once an hour I backup the log by
USE master
GO
BACKUP LOG [Prozess_Daten] TO [sich_Prozess_Daten] WITH NOINIT , NOUNLOAD
, NAME = N'Prozess_Daten Sicherung LOG', NOSKIP , STATS = 10, NOFORMAT
and once a day I backup the full db by
USE Prozess_Daten
GO
DBCC CHECKDB ('Prozess_Daten') WITH NO_INFOMSGS
GO
USE master
GO
BACKUP DATABASE [Prozess_Daten] TO [sich_Prozess_Daten] WITH INIT ,
NOUNLOAD , NAME = N'Prozess_Daten Sicherung', NOSKIP , STATS = 10,
NOFORMAT
Nevertheless the log file increases up to several GB (as large as the
datafile!). Only a tiny part of that space is used.
DBCC SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY)
does not help. I must switch to simple recovery mode first, then DBCC
SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY) and switch back to full
recovery mode.
I don't understand why, cause most of the Logfile is unused and could be
truncated(?)
Should I change the way I perform my backups? Maybe:
DBCC CHECKDB ...
BACKUP DATABASE ...
ALTER DATABASE ... (simple)
DBCC SHRINKFILE ... (log)
ALTER DATABASE ... (full)
What's best practice?
Thanks for your help!
--
Regards, InspektorDerrickMost probably you are doing work in between two log backup occasions, and the work you do requires
this amount of log space. My guess is index rebuilds. If you do require this amount of log space, do
not shrink the file regularly, as this comes with a penalty, see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"InspektorDerrick" <inspektor.derrick@.kstp.at> wrote in message
news:2F653064-7990-40B1-82C9-0BF53CE40D53@.microsoft.com...
> Hi,
> I have several databases using full recovery mode.
> Once an hour I backup the log by
> USE master
> GO
> BACKUP LOG [Prozess_Daten] TO [sich_Prozess_Daten] WITH NOINIT , NOUNLOAD
> , NAME = N'Prozess_Daten Sicherung LOG', NOSKIP , STATS = 10, NOFORMAT
> and once a day I backup the full db by
> USE Prozess_Daten
> GO
> DBCC CHECKDB ('Prozess_Daten') WITH NO_INFOMSGS
> GO
> USE master
> GO
> BACKUP DATABASE [Prozess_Daten] TO [sich_Prozess_Daten] WITH INIT ,
> NOUNLOAD , NAME = N'Prozess_Daten Sicherung', NOSKIP , STATS = 10,
> NOFORMAT
> Nevertheless the log file increases up to several GB (as large as the
> datafile!). Only a tiny part of that space is used.
> DBCC SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY)
> does not help. I must switch to simple recovery mode first, then DBCC
> SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY) and switch back to full
> recovery mode.
> I don't understand why, cause most of the Logfile is unused and could be
> truncated(?)
> Should I change the way I perform my backups? Maybe:
> DBCC CHECKDB ...
> BACKUP DATABASE ...
> ALTER DATABASE ... (simple)
> DBCC SHRINKFILE ... (log)
> ALTER DATABASE ... (full)
> What's best practice?
> Thanks for your help!
> --
> Regards, InspektorDerrick

Full recovery mode and large log

Hi,
I have several databases using full recovery mode.
Once an hour I backup the log by
USE master
GO
BACKUP LOG [Prozess_Daten] TO [sich_Prozess_Daten] WITH NOINIT , N
OUNLOAD
, NAME = N'Prozess_Daten Sicherung LOG', NOSKIP , STATS = 10, NOFORMAT
and once a day I backup the full db by
USE Prozess_Daten
GO
DBCC CHECKDB ('Prozess_Daten') WITH NO_INFOMSGS
GO
USE master
GO
BACKUP DATABASE [Prozess_Daten] TO [sich_Prozess_Daten] WITH INIT ,
NOUNLOAD , NAME = N'Prozess_Daten Sicherung', NOSKIP , STATS = 10,
NOFORMAT
Nevertheless the log file increases up to several GB (as large as the
datafile!). Only a tiny part of that space is used.
DBCC SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY)
does not help. I must switch to simple recovery mode first, then DBCC
SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY) and switch back to full
recovery mode.
I don't understand why, cause most of the Logfile is unused and could be
truncated(?)
Should I change the way I perform my backups? Maybe:
DBCC CHECKDB ...
BACKUP DATABASE ...
ALTER DATABASE ... (simple)
DBCC SHRINKFILE ... (log)
ALTER DATABASE ... (full)
What's best practice?
Thanks for your help!
--
Regards, InspektorDerrickMost probably you are doing work in between two log backup occasions, and th
e work you do requires
this amount of log space. My guess is index rebuilds. If you do require this
amount of log space, do
not shrink the file regularly, as this comes with a penalty, see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"InspektorDerrick" <inspektor.derrick@.kstp.at> wrote in message
news:2F653064-7990-40B1-82C9-0BF53CE40D53@.microsoft.com...
> Hi,
> I have several databases using full recovery mode.
> Once an hour I backup the log by
> USE master
> GO
> BACKUP LOG [Prozess_Daten] TO [sich_Prozess_Daten] WITH NOINIT ,
NOUNLOAD
> , NAME = N'Prozess_Daten Sicherung LOG', NOSKIP , STATS = 10, NOFORMAT
> and once a day I backup the full db by
> USE Prozess_Daten
> GO
> DBCC CHECKDB ('Prozess_Daten') WITH NO_INFOMSGS
> GO
> USE master
> GO
> BACKUP DATABASE [Prozess_Daten] TO [sich_Prozess_Daten] WITH INIT
,
> NOUNLOAD , NAME = N'Prozess_Daten Sicherung', NOSKIP , STATS = 10,
> NOFORMAT
> Nevertheless the log file increases up to several GB (as large as the
> datafile!). Only a tiny part of that space is used.
> DBCC SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY)
> does not help. I must switch to simple recovery mode first, then DBCC
> SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY) and switch back to full
> recovery mode.
> I don't understand why, cause most of the Logfile is unused and could be
> truncated(?)
> Should I change the way I perform my backups? Maybe:
> DBCC CHECKDB ...
> BACKUP DATABASE ...
> ALTER DATABASE ... (simple)
> DBCC SHRINKFILE ... (log)
> ALTER DATABASE ... (full)
> What's best practice?
> Thanks for your help!
> --
> Regards, InspektorDerrick

Full recovery mode and large log

Hi,
I have several databases using full recovery mode.
Once an hour I backup the log by
USE master
GO
BACKUP LOG [Prozess_Daten] TO [sich_Prozess_Daten] WITH NOINIT , NOUNLOAD
, NAME = N'Prozess_Daten Sicherung LOG', NOSKIP , STATS = 10, NOFORMAT
and once a day I backup the full db by
USE Prozess_Daten
GO
DBCC CHECKDB ('Prozess_Daten') WITH NO_INFOMSGS
GO
USE master
GO
BACKUP DATABASE [Prozess_Daten] TO [sich_Prozess_Daten] WITH INIT ,
NOUNLOAD , NAME = N'Prozess_Daten Sicherung', NOSKIP , STATS = 10,
NOFORMAT
Nevertheless the log file increases up to several GB (as large as the
datafile!). Only a tiny part of that space is used.
DBCC SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY)
does not help. I must switch to simple recovery mode first, then DBCC
SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY) and switch back to full
recovery mode.
I don't understand why, cause most of the Logfile is unused and could be
truncated(?)
Should I change the way I perform my backups? Maybe:
DBCC CHECKDB ...
BACKUP DATABASE ...
ALTER DATABASE ... (simple)
DBCC SHRINKFILE ... (log)
ALTER DATABASE ... (full)
What's best practice?
Thanks for your help!
Regards, InspektorDerrick
Most probably you are doing work in between two log backup occasions, and the work you do requires
this amount of log space. My guess is index rebuilds. If you do require this amount of log space, do
not shrink the file regularly, as this comes with a penalty, see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"InspektorDerrick" <inspektor.derrick@.kstp.at> wrote in message
news:2F653064-7990-40B1-82C9-0BF53CE40D53@.microsoft.com...
> Hi,
> I have several databases using full recovery mode.
> Once an hour I backup the log by
> USE master
> GO
> BACKUP LOG [Prozess_Daten] TO [sich_Prozess_Daten] WITH NOINIT , NOUNLOAD
> , NAME = N'Prozess_Daten Sicherung LOG', NOSKIP , STATS = 10, NOFORMAT
> and once a day I backup the full db by
> USE Prozess_Daten
> GO
> DBCC CHECKDB ('Prozess_Daten') WITH NO_INFOMSGS
> GO
> USE master
> GO
> BACKUP DATABASE [Prozess_Daten] TO [sich_Prozess_Daten] WITH INIT ,
> NOUNLOAD , NAME = N'Prozess_Daten Sicherung', NOSKIP , STATS = 10,
> NOFORMAT
> Nevertheless the log file increases up to several GB (as large as the
> datafile!). Only a tiny part of that space is used.
> DBCC SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY)
> does not help. I must switch to simple recovery mode first, then DBCC
> SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY) and switch back to full
> recovery mode.
> I don't understand why, cause most of the Logfile is unused and could be
> truncated(?)
> Should I change the way I perform my backups? Maybe:
> DBCC CHECKDB ...
> BACKUP DATABASE ...
> ALTER DATABASE ... (simple)
> DBCC SHRINKFILE ... (log)
> ALTER DATABASE ... (full)
> What's best practice?
> Thanks for your help!
> --
> Regards, InspektorDerrick

Full recovery backup

Hi,
I've just discovered the 'Recovery model options' in properties page of a
table in Enterprise manager.
I checked books on line which stated that Full recovery mode ' can recover
from any point in time'. Are they referring to backup/restores? If so how can
this be done at any point in time. Otherwise, what are they referring to?
Thanks for your time in answering this question
Ant
Hello,
Point in time:-
A point in time recovery is restoring a database to a specified date and
time. When you have completed a point in time recovery, your database
will be in the state it was at the specific date and time you identified
when restoring your database. A point in time recovery is a method to
recover
your database to any point in time since the last database backup
Take a look into below URL for datails:-
http://www.databasejournal.com/features/mssql/article.php/3530616
http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how
> can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant
|||Thank you for your time,
When I had a look at the restore dialog, the Point in time check box was
disabled. I checked the proertiers to find that it was set to full recovery
mode. Why would the check box not be enabled?
Thanks very much for your assistance
Ant
"Hari Prasad" wrote:

> Hello,
> Point in time:-
> A point in time recovery is restoring a database to a specified date and
> time. When you have completed a point in time recovery, your database
> will be in the state it was at the specific date and time you identified
> when restoring your database. A point in time recovery is a method to
> recover
> your database to any point in time since the last database backup
> Take a look into below URL for datails:-
> http://www.databasejournal.com/features/mssql/article.php/3530616
> http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
>
> Thanks
> Hari
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
>
>
|||Hello,
That will get enabled onky when you restore a transaction log backup over a
non recovered database.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:1A36B0F1-0D04-404F-8E16-2CF68903BE29@.microsoft.com...[vbcol=seagreen]
> Thank you for your time,
> When I had a look at the restore dialog, the Point in time check box was
> disabled. I checked the proertiers to find that it was set to full
> recovery
> mode. Why would the check box not be enabled?
> Thanks very much for your assistance
> Ant
> "Hari Prasad" wrote:
|||On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant
Point-in-time recovery makes use of full backups and transaction log
backups to allow you to restore a database to virtually any point in
time. Consider the following backup scenario:
9:00pm - full backup
10:00pm - log backup
11:00pm - log backup
12:00am - log backup
1:00am - log backup
...
...
...
3:00pm - log backup
You discover that at 2:30pm, a malicious user updated a large portion
of your data with junk, and management has decided it's not safe to
continue with the data as-is, and they ask you to restore the database
to some point prior to the user's update. Without the log backups,
your only recourse would be to restore the 9:00pm full backup, losing
the entire day's activity.
However, with the log backups, you can restore the database literally
up to the minute of the bad update. You first restore the full
backup, specifying WITH NORECOVERY in the restore command. You then
restore the log backups from 10:00pm through 2:00pm, specifying WITH
NORECOVERY for each restore. Finally, you restore the 3:00pm log
backup, this time using the STOPAT clause on the restore command.
With STOPAT, you specify a specific time that you want the restore to
stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
the bad update.
Does that help?
|||Brilliant! that makes it clear.
Thank you for your answer.
Ant
"Tracy McKibben" wrote:

> On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Point-in-time recovery makes use of full backups and transaction log
> backups to allow you to restore a database to virtually any point in
> time. Consider the following backup scenario:
> 9:00pm - full backup
> 10:00pm - log backup
> 11:00pm - log backup
> 12:00am - log backup
> 1:00am - log backup
> ...
> ...
> ...
> 3:00pm - log backup
> You discover that at 2:30pm, a malicious user updated a large portion
> of your data with junk, and management has decided it's not safe to
> continue with the data as-is, and they ask you to restore the database
> to some point prior to the user's update. Without the log backups,
> your only recourse would be to restore the 9:00pm full backup, losing
> the entire day's activity.
> However, with the log backups, you can restore the database literally
> up to the minute of the bad update. You first restore the full
> backup, specifying WITH NORECOVERY in the restore command. You then
> restore the log backups from 10:00pm through 2:00pm, specifying WITH
> NORECOVERY for each restore. Finally, you restore the 3:00pm log
> backup, this time using the STOPAT clause on the restore command.
> With STOPAT, you specify a specific time that you want the restore to
> stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
> the bad update.
> Does that help?
>

Full recovery backup

Hi,
I've just discovered the 'Recovery model options' in properties page of a
table in Enterprise manager.
I checked books on line which stated that Full recovery mode ' can recover
from any point in time'. Are they referring to backup/restores? If so how ca
n
this be done at any point in time. Otherwise, what are they referring to?
Thanks for your time in answering this question
AntHello,
Point in time:-
A point in time recovery is restoring a database to a specified date and
time. When you have completed a point in time recovery, your database
will be in the state it was at the specific date and time you identified
when restoring your database. A point in time recovery is a method to
recover
your database to any point in time since the last database backup
Take a look into below URL for datails:-
http://www.databasejournal.com/feat...cle.php/3530616
http://www.devx.com/getHelpOn/10Min...32/1954?pf=true
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how
> can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant|||Thank you for your time,
When I had a look at the restore dialog, the Point in time check box was
disabled. I checked the proertiers to find that it was set to full recovery
mode. Why would the check box not be enabled?
Thanks very much for your assistance
Ant
"Hari Prasad" wrote:

> Hello,
> Point in time:-
> A point in time recovery is restoring a database to a specified date and
> time. When you have completed a point in time recovery, your database
> will be in the state it was at the specific date and time you identified
> when restoring your database. A point in time recovery is a method to
> recover
> your database to any point in time since the last database backup
> Take a look into below URL for datails:-
> http://www.databasejournal.com/feat...cle.php/3530616
> http://www.devx.com/getHelpOn/10Min...32/1954?pf=true
>
> Thanks
> Hari
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
>
>|||Hello,
That will get enabled onky when you restore a transaction log backup over a
non recovered database.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:1A36B0F1-0D04-404F-8E16-2CF68903BE29@.microsoft.com...[vbcol=seagreen]
> Thank you for your time,
> When I had a look at the restore dialog, the Point in time check box was
> disabled. I checked the proertiers to find that it was set to full
> recovery
> mode. Why would the check box not be enabled?
> Thanks very much for your assistance
> Ant
> "Hari Prasad" wrote:
>|||On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how
can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant
Point-in-time recovery makes use of full backups and transaction log
backups to allow you to restore a database to virtually any point in
time. Consider the following backup scenario:
9:00pm - full backup
10:00pm - log backup
11:00pm - log backup
12:00am - log backup
1:00am - log backup
...
...
...
3:00pm - log backup
You discover that at 2:30pm, a malicious user updated a large portion
of your data with junk, and management has decided it's not safe to
continue with the data as-is, and they ask you to restore the database
to some point prior to the user's update. Without the log backups,
your only recourse would be to restore the 9:00pm full backup, losing
the entire day's activity.
However, with the log backups, you can restore the database literally
up to the minute of the bad update. You first restore the full
backup, specifying WITH NORECOVERY in the restore command. You then
restore the log backups from 10:00pm through 2:00pm, specifying WITH
NORECOVERY for each restore. Finally, you restore the 3:00pm log
backup, this time using the STOPAT clause on the restore command.
With STOPAT, you specify a specific time that you want the restore to
stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
the bad update.
Does that help?|||Brilliant! that makes it clear.
Thank you for your answer.
Ant
"Tracy McKibben" wrote:

> On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Point-in-time recovery makes use of full backups and transaction log
> backups to allow you to restore a database to virtually any point in
> time. Consider the following backup scenario:
> 9:00pm - full backup
> 10:00pm - log backup
> 11:00pm - log backup
> 12:00am - log backup
> 1:00am - log backup
> ...
> ...
> ...
> 3:00pm - log backup
> You discover that at 2:30pm, a malicious user updated a large portion
> of your data with junk, and management has decided it's not safe to
> continue with the data as-is, and they ask you to restore the database
> to some point prior to the user's update. Without the log backups,
> your only recourse would be to restore the 9:00pm full backup, losing
> the entire day's activity.
> However, with the log backups, you can restore the database literally
> up to the minute of the bad update. You first restore the full
> backup, specifying WITH NORECOVERY in the restore command. You then
> restore the log backups from 10:00pm through 2:00pm, specifying WITH
> NORECOVERY for each restore. Finally, you restore the 3:00pm log
> backup, this time using the STOPAT clause on the restore command.
> With STOPAT, you specify a specific time that you want the restore to
> stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
> the bad update.
> Does that help?
>

订阅：博文 (Atom)

2012年3月29日星期四

Fulldatabase backup query

Fulldatabase backup query

Fulldatabase backup query

full, differential, transaction hwo to rotate the differential?

full, differential, transaction hwo to rotate the differential?

full, differential, transaction hwo to rotate the differential?

2012年3月11日星期日

full text indexing backup

2012年3月7日星期三

Full text catalog is paused

Full Sql Server Backup

Full Sql Server Backup

Full Sql Server Backup

full server backup

full server backup

full server backup

2012年2月26日星期日

Full Restore Question

Full recovery mode and large log

Full recovery mode and large log

Full recovery mode and large log

Full recovery backup

Full recovery backup

Getting error MS SQL2000

Blog Archive

About Me