SQLAgentMail: restore

Showing posts with label restore. Show all posts

Wednesday, March 28, 2012

Question regarding restoring a Database

Hello,
I wish to restore a database from another SQL 7 server. The database
restores OK. The original server had a login called "LoginA", which was
enrolled as a user in the database. When I try and add the Login to my
server and enrol it in the restored database it says User already exists.
So, I tried setting us the Login before I restored the database, but the
users are not then enrolled in the restored database. (using enterprise
manager).
I know I can use sp_DropUser to remove it from the restored database and
then re-add it.
However, is there a correct way to restore databases from another Server and
keep the Login / User setting from the original.
Thanks
Tim,Tim
I use this script
Create.LoginsTable.sql
-- Creates table Logins in Northwind
-- containing all SQL Logins on the server
-- (but not 'sa', 'guest', or 'distributor_admin')
USE Northwind
GO
-- Create table Logins in Nortwind
CREATE TABLE [dbo].[Logins] (
[Name] [varchar] (30) NULL ,
[EncryptedPassword] [nvarchar] (128) NULL ,
[DefaultDB] [nvarchar] (128) NULL ,
[DefLanguage] [nvarchar] (128) NULL ,
[sid] [varbinary] (85) NULL ,
[EncryptOpt] [varchar] (30) NULL ,
[LoginName] [varchar] (50) NULL
) ON [PRIMARY]
GO
-- Insert information about the logins into table logins.
INSERT logins
SELECT name, [password], dbname, language, sid,
'skip_encryption', loginname
FROM master..syslogins
ORDER BY name
GO
-- Remove special SQL logins and all Windows logins.
dDELETE logins
WHERE loginname IN ('distributor_admin', 'guest', 'sa')
OR loginname LIKE '%\%'
GO
-- Look at the results.
SELECT name, defaultdb FROM logins
Create.logins.sql
-- Create source-server logins on target-server
USE Master
Go
DECLARE logincur CURSOR
FAST_FORWARD
FOR
SELECT [name], encryptedpassword, defaultdb,
deflanguage, sid, encryptopt
FROM Northwind..logins
DECLARE @.loginame varchar(30),
@.passwd nvarchar(128),
@.defdb nvarchar(128),
@.deflang nvarchar(128),
@.sid varbinary(85),
@.encryptopt varchar(30)
OPEN logincur
FETCH NEXT FROM logincur
INTO @.loginame, @.passwd, @.defdb,
@.deflang, @.sid, @.encryptopt
WHILE (@.@.fetch_status = 0)
BEGIN
EXEC master..sp_addlogin
@.loginame, @.passwd, @.defdb,
@.deflang, @.sid, @.encryptopt
FETCH NEXT FROM logincur
INTO @.loginame, @.passwd, @.defdb,
@.deflang, @.sid, @.encryptopt
END
CLOSE logincur
DEALLOCATE logincur
GO
----
--
--Identify Orphan Users
select u.name from master..syslogins l right join
sysusers u on l.sid = u.sid
where l.sid is null and issqlrole <> 1 and isapprole <> 1
and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'
and u.name <> 'system_function_schema')
"Tim Marsden" <TM@.UK.COM> wrote in message
news:OaMCXDb5DHA.2692@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I wish to restore a database from another SQL 7 server. The database
> restores OK. The original server had a login called "LoginA", which was
> enrolled as a user in the database. When I try and add the Login to my
> server and enrol it in the restored database it says User already exists.
> So, I tried setting us the Login before I restored the database, but the
> users are not then enrolled in the restored database. (using enterprise
> manager).
> I know I can use sp_DropUser to remove it from the restored database and
> then re-add it.
> However, is there a correct way to restore databases from another Server
and
> keep the Login / User setting from the original.
> Thanks
> Tim,
>|||Many Thanks
I will try it out.
Tim
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23MjE$Qb5DHA.1428@.TK2MSFTNGP12.phx.gbl...
> Tim
> I use this script
> Create.LoginsTable.sql
> -- Creates table Logins in Northwind
> -- containing all SQL Logins on the server
> -- (but not 'sa', 'guest', or 'distributor_admin')
>
> USE Northwind
> GO
> -- Create table Logins in Nortwind
> CREATE TABLE [dbo].[Logins] (
> [Name] [varchar] (30) NULL ,
> [EncryptedPassword] [nvarchar] (128) NULL ,
> [DefaultDB] [nvarchar] (128) NULL ,
> [DefLanguage] [nvarchar] (128) NULL ,
> [sid] [varbinary] (85) NULL ,
> [EncryptOpt] [varchar] (30) NULL ,
> [LoginName] [varchar] (50) NULL
> ) ON [PRIMARY]
> GO
> -- Insert information about the logins into table logins.
> INSERT logins
> SELECT name, [password], dbname, language, sid,
> 'skip_encryption', loginname
> FROM master..syslogins
> ORDER BY name
> GO
> -- Remove special SQL logins and all Windows logins.
> dDELETE logins
> WHERE loginname IN ('distributor_admin', 'guest', 'sa')
> OR loginname LIKE '%\%'
> GO
> -- Look at the results.
> SELECT name, defaultdb FROM logins
>
>
> Create.logins.sql
> -- Create source-server logins on target-server
> USE Master
> Go
> DECLARE logincur CURSOR
> FAST_FORWARD
> FOR
> SELECT [name], encryptedpassword, defaultdb,
> deflanguage, sid, encryptopt
> FROM Northwind..logins
> DECLARE @.loginame varchar(30),
> @.passwd nvarchar(128),
> @.defdb nvarchar(128),
> @.deflang nvarchar(128),
> @.sid varbinary(85),
> @.encryptopt varchar(30)
> OPEN logincur
> FETCH NEXT FROM logincur
> INTO @.loginame, @.passwd, @.defdb,
> @.deflang, @.sid, @.encryptopt
> WHILE (@.@.fetch_status = 0)
> BEGIN
> EXEC master..sp_addlogin
> @.loginame, @.passwd, @.defdb,
> @.deflang, @.sid, @.encryptopt
> FETCH NEXT FROM logincur
> INTO @.loginame, @.passwd, @.defdb,
> @.deflang, @.sid, @.encryptopt
> END
> CLOSE logincur
> DEALLOCATE logincur
> GO
> ----
--
> --
> --Identify Orphan Users
> select u.name from master..syslogins l right join
> sysusers u on l.sid = u.sid
> where l.sid is null and issqlrole <> 1 and isapprole <> 1
> and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'
> and u.name <> 'system_function_schema')
>
>
>
> "Tim Marsden" <TM@.UK.COM> wrote in message
> news:OaMCXDb5DHA.2692@.TK2MSFTNGP09.phx.gbl...
> > Hello,
> >
> > I wish to restore a database from another SQL 7 server. The database
> > restores OK. The original server had a login called "LoginA", which was
> > enrolled as a user in the database. When I try and add the Login to my
> > server and enrol it in the restored database it says User already
exists.
> > So, I tried setting us the Login before I restored the database, but the
> > users are not then enrolled in the restored database. (using enterprise
> > manager).
> > I know I can use sp_DropUser to remove it from the restored database and
> > then re-add it.
> > However, is there a correct way to restore databases from another Server
> and
> > keep the Login / User setting from the original.
> >
> > Thanks
> > Tim,
> >
> >
>|||Hi Tim,
Thank you for using the Newsgroup and it is my pleasure to help you with
you issue.
From my experience ,after restore a user database from one SQL Server to
another, the database user IDs need to be mapped to the existing sql logins
on the new server. You could use sp_change_users_login to link the security
account for a user in the current database to an existing login on the new
host SQL Server without losing the user permission.
Please look for 'sp_change_user_login (T-SQL) " in the SQL Server Books
Online for detailed information.
You could also run the following script to add the old logins into the new
one ( replace testdb with the name of your database)
use testdb --Change to your database name
go
declare @.UserName nvarchar(255)
declare orphanuser_cur cursor for select
UserName = name from sysusers where issqluser = 1 and
(sid is not null and sid <> 0x0) and suser_sname(sid) is null order by name
open orphanuser_cur fetch next from orphanuser_cur into @.UserName while
(@.@.fetch_status = 0)
begin
if not exists (select * from master..sysxlogins where name = @.UserName)
Begin
print @.UserName + ' does not exist in syslogins'
print @.UserName + ' being added to syslogins (reset password and default
database)'
EXEC sp_addlogin @.UserName, 'password', 'testdb' --Change to your
databasename
end
print @.UserName + ' user name being resynced'
EXEC sp_change_users_login 'Update_one', @.UserName, @.UserName
fetch next from orphanuser_cur into @.UserName
end
close orphanuser_cur
deallocate orphanuser_cur
Master database has the logins for the user database and they have been
resynced.
Hope this helps and if you still have questions, please feel free to post
new message here and I am glad to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.sql

Question regarding restoring a Database

quote:

> Hello,
> I wish to restore a database from another SQL 7 server. The database
> restores OK. The original server had a login called "LoginA", which was
> enrolled as a user in the database. When I try and add the Login to my
> server and enrol it in the restored database it says User already exists.
> So, I tried setting us the Login before I restored the database, but the
> users are not then enrolled in the restored database. (using enterprise
> manager).
> I know I can use sp_DropUser to remove it from the restored database and
> then re-add it.
> However, is there a correct way to restore databases from another Server

and

quote:

> keep the Login / User setting from the original.
> Thanks
> Tim,
>

|||Many Thanks
I will try it out.
Tim
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23MjE$Qb5DHA.1428@.TK2MSFTNGP12.phx.gbl...

quote:

> Tim
> I use this script
> Create.LoginsTable.sql
> -- Creates table Logins in Northwind
> -- containing all SQL Logins on the server
> -- (but not 'sa', 'guest', or 'distributor_admin')
>
> USE Northwind
> GO
> -- Create table Logins in Nortwind
> CREATE TABLE [dbo].[Logins] (
> [Name] [varchar] (30) NULL ,
> [EncryptedPassword] [nvarchar] (128) NULL ,
> [DefaultDB] [nvarchar] (128) NULL ,
> [DefLanguage] [nvarchar] (128) NULL ,
> [sid] [varbinary] (85) NULL ,
> [EncryptOpt] [varchar] (30) NULL ,
> [LoginName] [varchar] (50) NULL
> ) ON [PRIMARY]
> GO
> -- Insert information about the logins into table logins.
> INSERT logins
> SELECT name, [password], dbname, language, sid,
> 'skip_encryption', loginname
> FROM master..syslogins
> ORDER BY name
> GO
> -- Remove special SQL logins and all Windows logins.
> dDELETE logins
> WHERE loginname IN ('distributor_admin', 'guest', 'sa')
> OR loginname LIKE '%\%'
> GO
> -- Look at the results.
> SELECT name, defaultdb FROM logins
>
>
> Create.logins.sql
> -- Create source-server logins on target-server
> USE Master
> Go
> DECLARE logincur CURSOR
> FAST_FORWARD
> FOR
> SELECT [name], encryptedpassword, defaultdb,
> deflanguage, sid, encryptopt
> FROM Northwind..logins
> DECLARE @.loginame varchar(30),
> @.passwd nvarchar(128),
> @.defdb nvarchar(128),
> @.deflang nvarchar(128),
> @.sid varbinary(85),
> @.encryptopt varchar(30)
> OPEN logincur
> FETCH NEXT FROM logincur
> INTO @.loginame, @.passwd, @.defdb,
> @.deflang, @.sid, @.encryptopt
> WHILE (@.@.fetch_status = 0)
> BEGIN
> EXEC master..sp_addlogin
> @.loginame, @.passwd, @.defdb,
> @.deflang, @.sid, @.encryptopt
> FETCH NEXT FROM logincur
> INTO @.loginame, @.passwd, @.defdb,
> @.deflang, @.sid, @.encryptopt
> END
> CLOSE logincur
> DEALLOCATE logincur
> GO
> ----

quote:

> --
> --Identify Orphan Users
> select u.name from master..syslogins l right join
> sysusers u on l.sid = u.sid
> where l.sid is null and issqlrole <> 1 and isapprole <> 1
> and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'
> and u.name <> 'system_function_schema')
>
>
>
> "Tim Marsden" <TM@.UK.COM> wrote in message
> news:OaMCXDb5DHA.2692@.TK2MSFTNGP09.phx.gbl...
exists.[QUOTE]
> and
>

|||Hi Tim,
Thank you for using the Newsgroup and it is my pleasure to help you with
you issue.
From my experience ,after restore a user database from one SQL Server to
another, the database user IDs need to be mapped to the existing sql logins
on the new server. You could use sp_change_users_login to link the security
account for a user in the current database to an existing login on the new
host SQL Server without losing the user permission.
Please look for 'sp_change_user_login (T-SQL) " in the SQL Server Books
Online for detailed information.
You could also run the following script to add the old logins into the new
one ( replace testdb with the name of your database)
use testdb --Change to your database name
go
declare @.UserName nvarchar(255)
declare orphanuser_cur cursor for select
UserName = name from sysusers where issqluser = 1 and
(sid is not null and sid <> 0x0) and suser_sname(sid) is null order by name
open orphanuser_cur fetch next from orphanuser_cur into @.UserName while
(@.@.fetch_status = 0)
begin
if not exists (select * from master..sysxlogins where name = @.UserName)
Begin
print @.UserName + ' does not exist in syslogins'
print @.UserName + ' being added to syslogins (reset password and default
database)'
EXEC sp_addlogin @.UserName, 'password', 'testdb' --Change to your
databasename
end
print @.UserName + ' user name being resynced'
EXEC sp_change_users_login 'Update_one', @.UserName, @.UserName
fetch next from orphanuser_cur into @.UserName
end
close orphanuser_cur
deallocate orphanuser_cur
Master database has the logins for the user database and they have been
resynced.
Hope this helps and if you still have questions, please feel free to post
new message here and I am glad to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Monday, March 26, 2012

Question regarding backup of 2005 database to 2000.

I know it's not possible to backup a database in SQL2005, and restore to 2000. But, I've been asked if there are any 3rd party tools which can do this - such as SQL Litespeed - but I can't see anything regarding this on their website.

Does anyone know if litespeed or Redgate SQL Backup can restore to 2000 from 2005 ?

Thanks in advance,

You can do the following:

1. In SQL Management studio, generate the database script, you can specify that script be generated for SQL 2000.

2. Create the database and tables using the script.

3. DTS or SSIS the data from SQL 2005 tables to SQL 2000 tables.

4. Create the table contraints like indexes and foreign keys using the script generated in step 1.

5. Create rest of the objects.

hope that helps.

Jag

|||No. Every tool I have seen does a TSQL "BACKUP DATABASE" command to their own virtual file/drive.

The structure has changed and views, etc which are not compatible with 2000. There is no way to "backup" a 2005 and restore to 2000.

As mentioned you can create a script and output all the data and import it back into 2000.

Friday, March 23, 2012

Question on System and Data Restore

Does SQL Server have anything similar?

Thanks in advance.

GerryDataPro (datapro01@.yahoo.com) writes:

Quote:

Originally Posted by

I don't see why you would anything else than a normal database backup?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||In DB2, to save off data and structure we use utilties db2look and
db2export.

I can see that in SQL Server that I can script (ddl) the objects and
relationships and export the data. Its, I believe a matter of
redundancy more than anything else/

Thanks

Erland Sommarskog wrote:

Quote:

Originally Posted by

DataPro (datapro01@.yahoo.com) writes:

Quote:

Originally Posted by

Does SQL Server have anything similar?

>
I don't see why you would anything else than a normal database backup?
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||DataPro (datapro01@.yahoo.com) writes:

Quote:

Originally Posted by

In DB2, to save off data and structure we use utilties db2look and
db2export.
>
I can see that in SQL Server that I can script (ddl) the objects and
relationships and export the data. Its, I believe a matter of
redundancy more than anything else/

There are of course situations where you want to duplicate a schema or
copy the data from one database to another.

But since you talked about future recreation, it sounded more like a
backup to me.

Note that for development you should keep all your SQL code under
version control. If you do this, there is rarely any reason to script
from the database, since the version-control system holds the truth
about the system.

Tuesday, March 20, 2012

Question on Restore Script

I am trying to write a generic restore script which could be used for
all the similar restores we do on a dialy baisis. Basically the only
thing that changes are the database names and all these backup are from
all different servers.
So I understand I would have to write a restore script with the MOVE
option and I am planning on passing the parameter
@.databasename,@.backupFileLocation.
However is there anyway to determine the names of the file in the
backup so that I could use those in my Restore Command without any user
intervention? Unless I am able to do that I cannot get the entire
process automated. From everthing I have read so far it suggests that I
would have to run RESTORE FILELISTONLY command to get the file names
and then edit my T_SQL command for each restore operation.
Is there a cool way of doing without any intervention?
Any help in this regard will be appreciated.
Thanks
Check the code in http://www.karaszi.com/SQLServer/uti...l_in_file.asp. That should give
you a good starting point.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"shub" <shubtech@.gmail.com> wrote in message
news:1137513510.543961.83750@.g44g2000cwa.googlegro ups.com...
>I am trying to write a generic restore script which could be used for
> all the similar restores we do on a dialy baisis. Basically the only
> thing that changes are the database names and all these backup are from
> all different servers.
> So I understand I would have to write a restore script with the MOVE
> option and I am planning on passing the parameter
> @.databasename,@.backupFileLocation.
> However is there anyway to determine the names of the file in the
> backup so that I could use those in my Restore Command without any user
> intervention? Unless I am able to do that I cannot get the entire
> process automated. From everthing I have read so far it suggests that I
> would have to run RESTORE FILELISTONLY command to get the file names
> and then edit my T_SQL command for each restore operation.
> Is there a cool way of doing without any intervention?
> Any help in this regard will be appreciated.
> Thanks
>
|||This is exactly the kind of script I was looking for. Thank you very
much. I really appreciate it.

Question on Restore Script

I am trying to write a generic restore script which could be used for
all the similar restores we do on a dialy baisis. Basically the only
thing that changes are the database names and all these backup are from
all different servers.
So I understand I would have to write a restore script with the MOVE
option and I am planning on passing the parameter
@.databasename,@.backupFileLocation.
However is there anyway to determine the names of the file in the
backup so that I could use those in my Restore Command without any user
intervention? Unless I am able to do that I cannot get the entire
process automated. From everthing I have read so far it suggests that I
would have to run RESTORE FILELISTONLY command to get the file names
and then edit my T_SQL command for each restore operation.
Is there a cool way of doing without any intervention?
Any help in this regard will be appreciated.
ThanksCheck the code in http://www.karaszi.com/SQLServer/ut...ver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"shub" <shubtech@.gmail.com> wrote in message
news:1137513510.543961.83750@.g44g2000cwa.googlegroups.com...
>I am trying to write a generic restore script which could be used for
> all the similar restores we do on a dialy baisis. Basically the only
> thing that changes are the database names and all these backup are from
> all different servers.
> So I understand I would have to write a restore script with the MOVE
> option and I am planning on passing the parameter
> @.databasename,@.backupFileLocation.
> However is there anyway to determine the names of the file in the
> backup so that I could use those in my Restore Command without any user
> intervention? Unless I am able to do that I cannot get the entire
> process automated. From everthing I have read so far it suggests that I
> would have to run RESTORE FILELISTONLY command to get the file names
> and then edit my T_SQL command for each restore operation.
> Is there a cool way of doing without any intervention?
> Any help in this regard will be appreciated.
> Thanks
>|||This is exactly the kind of script I was looking for. Thank you very
much. I really appreciate it.

Question on Restore Script

I am trying to write a generic restore script which could be used for
all the similar restores we do on a dialy baisis. Basically the only
thing that changes are the database names and all these backup are from
all different servers.
So I understand I would have to write a restore script with the MOVE
option and I am planning on passing the parameter
@.databasename,@.backupFileLocation.
However is there anyway to determine the names of the file in the
backup so that I could use those in my Restore Command without any user
intervention? Unless I am able to do that I cannot get the entire
process automated. From everthing I have read so far it suggests that I
would have to run RESTORE FILELISTONLY command to get the file names
and then edit my T_SQL command for each restore operation.
Is there a cool way of doing without any intervention?
Any help in this regard will be appreciated.
ThanksCheck the code in http://www.karaszi.com/SQLServer/util_restore_all_in_file.asp. That should give
you a good starting point.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"shub" <shubtech@.gmail.com> wrote in message
news:1137513510.543961.83750@.g44g2000cwa.googlegroups.com...
>I am trying to write a generic restore script which could be used for
> all the similar restores we do on a dialy baisis. Basically the only
> thing that changes are the database names and all these backup are from
> all different servers.
> So I understand I would have to write a restore script with the MOVE
> option and I am planning on passing the parameter
> @.databasename,@.backupFileLocation.
> However is there anyway to determine the names of the file in the
> backup so that I could use those in my Restore Command without any user
> intervention? Unless I am able to do that I cannot get the entire
> process automated. From everthing I have read so far it suggests that I
> would have to run RESTORE FILELISTONLY command to get the file names
> and then edit my T_SQL command for each restore operation.
> Is there a cool way of doing without any intervention?
> Any help in this regard will be appreciated.
> Thanks
>|||This is exactly the kind of script I was looking for. Thank you very
much. I really appreciate it.

question on recovery sql server 2000

Hi
I am using SQL 2000 std edition and hence using custom scripts to
restore the DB using standby file. I am backing up T-LOGs every 30 mins on
production and copying them on a network location which is accessible to
standby machine.
I run a job on standby machine which applies the transaction logs every 30
mins by checking in backmediaset & backupmediafamily tables of Production. DB
is running in read only and is working.
Question : In case my Production machine goes down and does not come up how
can I start the Standby "with data loss" if it has already appiled the last
T-logs generated on production.
Since Produciton (primary) is down I can not backup the last T-log.
Thanks
MangeshFrom BOL
This example sets up the MyNwind database on a standby server. The database
can be used in read-only mode between restore operations.
-- Restore the initial database backup on the standby server.
USE master
GO
RESTORE DATABASE MyNwind
FROM MyNwind_1
WITH STANDBY = 'c:\undo.ldf'
GO
-- Apply the first transaction log backup.
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH STANDBY = 'c:\undo.ldf'
GO
-- Apply the next transaction log backup.
RESTORE LOG MyNwind
FROM MyNwind_log2
WITH STANDBY = 'c:\undo.ldf'
GO
-- Repeat for each transaction log backup created on the
-- primary server.
--
-- Time elapses.. .. ..
--
-- The primary server has failed. Back up the
-- active transaction log on the primary server.
BACKUP LOG MyNwind
TO MyNwind_log3
WITH NO_TRUNCATE
GO
-- Apply the final (active) transaction log backup
-- to the standby server. All preceding transaction
-- log backups must have been already applied.
RESTORE LOG MyNwind
FROM MyNwind_log3
WITH STANDBY = 'c:\undo.ldf'
GO
-- Recover the database on the standby server,
-- making it available for normal operations.
RESTORE DATABASE MyNwind
WITH RECOVERY
GO
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:72D84926-67E3-4A3F-A011-E5F5A6ACA8CC@.microsoft.com...
> Hi
> I am using SQL 2000 std edition and hence using custom scripts to
> restore the DB using standby file. I am backing up T-LOGs every 30 mins on
> production and copying them on a network location which is accessible to
> standby machine.
> I run a job on standby machine which applies the transaction logs every 30
> mins by checking in backmediaset & backupmediafamily tables of Production.
DB
> is running in read only and is working.
> Question : In case my Production machine goes down and does not come up
how
> can I start the Standby "with data loss" if it has already appiled the
last
> T-logs generated on production.
> Since Produciton (primary) is down I can not backup the last T-log.
> Thanks
> Mangesh
>|||So you are continuously doing RESTORE WITH STANDBY? And you now wand to make the database fully
accessible? If so:
RESTORE DATABASE dbname WITH RECOVERY
No actual restore is performed, only the recovery work to get it out of standby mode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in message
news:72D84926-67E3-4A3F-A011-E5F5A6ACA8CC@.microsoft.com...
> Hi
> I am using SQL 2000 std edition and hence using custom scripts to
> restore the DB using standby file. I am backing up T-LOGs every 30 mins on
> production and copying them on a network location which is accessible to
> standby machine.
> I run a job on standby machine which applies the transaction logs every 30
> mins by checking in backmediaset & backupmediafamily tables of Production. DB
> is running in read only and is working.
> Question : In case my Production machine goes down and does not come up how
> can I start the Standby "with data loss" if it has already appiled the last
> T-logs generated on production.
> Since Produciton (primary) is down I can not backup the last T-log.
> Thanks
> Mangesh
>|||Yes. I am doing a continuous restoration of my standby database using standby
file.
Thanks for help.
"Tibor Karaszi" wrote:
> So you are continuously doing RESTORE WITH STANDBY? And you now wand to make the database fully
> accessible? If so:
> RESTORE DATABASE dbname WITH RECOVERY
> No actual restore is performed, only the recovery work to get it out of standby mode.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in message
> news:72D84926-67E3-4A3F-A011-E5F5A6ACA8CC@.microsoft.com...
> > Hi
> >
> > I am using SQL 2000 std edition and hence using custom scripts to
> > restore the DB using standby file. I am backing up T-LOGs every 30 mins on
> > production and copying them on a network location which is accessible to
> > standby machine.
> >
> > I run a job on standby machine which applies the transaction logs every 30
> > mins by checking in backmediaset & backupmediafamily tables of Production. DB
> > is running in read only and is working.
> >
> > Question : In case my Production machine goes down and does not come up how
> > can I start the Standby "with data loss" if it has already appiled the last
> > T-logs generated on production.
> > Since Produciton (primary) is down I can not backup the last T-log.
> >
> > Thanks
> > Mangesh
> >
> >
>
>

Wednesday, March 7, 2012

Question on Differencial Backup

Hi,
I am setting an differencial backup/restore for the SQL Server, I have two
questions need help,
1. If I do a whole database backup on the first day of each month, and the
time is 3:00AM, then how to configure the differencial backup, should I
exclude the day of doing the whole database backup? How to do a whole month
differencial backup except only one day?
2. If the differencial backup is to use the append to media, how to control
the size of the back up? (I plan to backup to the hard disk).
Thanks in advance
Frank
Frank
Pls read this article
<http://vyaskn.tripod.com/sql_server_...ices.htm#Step1
> --administaiting best practices
"Frank" <wangping@.lucent.com> wrote in message
news:O9JHP8$CFHA.3924@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am setting an differencial backup/restore for the SQL Server, I have two
> questions need help,
> 1. If I do a whole database backup on the first day of each month, and the
> time is 3:00AM, then how to configure the differencial backup, should I
> exclude the day of doing the whole database backup? How to do a whole
month
> differencial backup except only one day?
> 2. If the differencial backup is to use the append to media, how to
control
> the size of the back up? (I plan to backup to the hard disk).
> Thanks in advance
> Frank
>
|||The differential contains all changes since the last full backup so there's
no point in doing one straight after the full (but it wouldn't hurt just
won't have much to do).
I wouldn't append the backups as if you have a corrupt file you lose all of
them. It's easier to handle if every backup is in it's own file with a
datestamp.
see
http://www.mindsdoor.net/SQLAdmin/Ba...Databases.html
Which will do full, log and diff backups for all databases and delete old
backup files.
A full backup once a month is not usual though - why have you chosen that?
Usually it is during a quiete period so every night or at weekends.
Often if you leave too long between full backups then a lot of pages get
changed and the diff ends up backing up nearly the whole database anyway.
Very important.
Make sure you test restore the full backup. This is even more important with
your scenario as if you don't have log backups then a corrupt full backup
will invalidate all the following diffs and you could end up losing a couple
of months of data.
"Frank" wrote:

> Hi,
> I am setting an differencial backup/restore for the SQL Server, I have two
> questions need help,
> 1. If I do a whole database backup on the first day of each month, and the
> time is 3:00AM, then how to configure the differencial backup, should I
> exclude the day of doing the whole database backup? How to do a whole month
> differencial backup except only one day?
> 2. If the differencial backup is to use the append to media, how to control
> the size of the back up? (I plan to backup to the hard disk).
> Thanks in advance
> Frank
>
>
|||Thanks Uri but I could not open the linked website.
Frank
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uBJJaxCDFHA.3540@.TK2MSFTNGP14.phx.gbl...
> Frank
> Pls read this article
>
<http://vyaskn.tripod.com/sql_server_...ices.htm#Step1[vbcol=seagreen]
>
>
>
>
> "Frank" <wangping@.lucent.com> wrote in message
> news:O9JHP8$CFHA.3924@.TK2MSFTNGP15.phx.gbl...
two[vbcol=seagreen]
the
> month
> control
>
|||Thanks so much for your detailed information.
Frank
"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:27A5A43B-8C56-4554-BC25-4A212364C921@.microsoft.com...
> The differential contains all changes since the last full backup so
there's
> no point in doing one straight after the full (but it wouldn't hurt just
> won't have much to do).
> I wouldn't append the backups as if you have a corrupt file you lose all
of
> them. It's easier to handle if every backup is in it's own file with a
> datestamp.
> see
> http://www.mindsdoor.net/SQLAdmin/Ba...Databases.html
> Which will do full, log and diff backups for all databases and delete old
> backup files.
> A full backup once a month is not usual though - why have you chosen that?
> Usually it is during a quiete period so every night or at weekends.
> Often if you leave too long between full backups then a lot of pages get
> changed and the diff ends up backing up nearly the whole database anyway.
> Very important.
> Make sure you test restore the full backup. This is even more important
with
> your scenario as if you don't have log backups then a corrupt full backup
> will invalidate all the following diffs and you could end up losing a
couple[vbcol=seagreen]
> of months of data.
>
> "Frank" wrote:
two[vbcol=seagreen]
the[vbcol=seagreen]
month[vbcol=seagreen]
control[vbcol=seagreen]
|||Nigel,
There are many useful tips and articles in your webpage.
Thanks again
Frank
"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:27A5A43B-8C56-4554-BC25-4A212364C921@.microsoft.com...
> The differential contains all changes since the last full backup so
there's
> no point in doing one straight after the full (but it wouldn't hurt just
> won't have much to do).
> I wouldn't append the backups as if you have a corrupt file you lose all
of
> them. It's easier to handle if every backup is in it's own file with a
> datestamp.
> see
> http://www.mindsdoor.net/SQLAdmin/Ba...Databases.html
> Which will do full, log and diff backups for all databases and delete old
> backup files.
> A full backup once a month is not usual though - why have you chosen that?
> Usually it is during a quiete period so every night or at weekends.
> Often if you leave too long between full backups then a lot of pages get
> changed and the diff ends up backing up nearly the whole database anyway.
> Very important.
> Make sure you test restore the full backup. This is even more important
with
> your scenario as if you don't have log backups then a corrupt full backup
> will invalidate all the following diffs and you could end up losing a
couple[vbcol=seagreen]
> of months of data.
>
> "Frank" wrote:
two[vbcol=seagreen]
the[vbcol=seagreen]
month[vbcol=seagreen]
control[vbcol=seagreen]

Question on Differencial Backup

Hi,
I am setting an differencial backup/restore for the SQL Server, I have two
questions need help,
1. If I do a whole database backup on the first day of each month, and the
time is 3:00AM, then how to configure the differencial backup, should I
exclude the day of doing the whole database backup? How to do a whole month
differencial backup except only one day?
2. If the differencial backup is to use the append to media, how to control
the size of the back up? (I plan to backup to the hard disk).
Thanks in advance
FrankFrank
Pls read this article
<http://vyaskn.tripod.com/sql_server_administration_best_practices.htm#Step1
> --administaiting best practices
"Frank" <wangping@.lucent.com> wrote in message
news:O9JHP8$CFHA.3924@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am setting an differencial backup/restore for the SQL Server, I have two
> questions need help,
> 1. If I do a whole database backup on the first day of each month, and the
> time is 3:00AM, then how to configure the differencial backup, should I
> exclude the day of doing the whole database backup? How to do a whole
month
> differencial backup except only one day?
> 2. If the differencial backup is to use the append to media, how to
control
> the size of the back up? (I plan to backup to the hard disk).
> Thanks in advance
> Frank
>|||The differential contains all changes since the last full backup so there's
no point in doing one straight after the full (but it wouldn't hurt just
won't have much to do).
I wouldn't append the backups as if you have a corrupt file you lose all of
them. It's easier to handle if every backup is in it's own file with a
datestamp.
see
http://www.mindsdoor.net/SQLAdmin/BackupAllDatabases.html
Which will do full, log and diff backups for all databases and delete old
backup files.
A full backup once a month is not usual though - why have you chosen that?
Usually it is during a quiete period so every night or at weekends.
Often if you leave too long between full backups then a lot of pages get
changed and the diff ends up backing up nearly the whole database anyway.
Very important.
Make sure you test restore the full backup. This is even more important with
your scenario as if you don't have log backups then a corrupt full backup
will invalidate all the following diffs and you could end up losing a couple
of months of data.
"Frank" wrote:
> Hi,
> I am setting an differencial backup/restore for the SQL Server, I have two
> questions need help,
> 1. If I do a whole database backup on the first day of each month, and the
> time is 3:00AM, then how to configure the differencial backup, should I
> exclude the day of doing the whole database backup? How to do a whole month
> differencial backup except only one day?
> 2. If the differencial backup is to use the append to media, how to control
> the size of the back up? (I plan to backup to the hard disk).
> Thanks in advance
> Frank
>
>|||Thanks Uri but I could not open the linked website.
Frank
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uBJJaxCDFHA.3540@.TK2MSFTNGP14.phx.gbl...
> Frank
> Pls read this article
>
<http://vyaskn.tripod.com/sql_server_administration_best_practices.htm#Step1
> > --administaiting best practices
>
>
>
>
> "Frank" <wangping@.lucent.com> wrote in message
> news:O9JHP8$CFHA.3924@.TK2MSFTNGP15.phx.gbl...
> > Hi,
> > I am setting an differencial backup/restore for the SQL Server, I have
two
> > questions need help,
> > 1. If I do a whole database backup on the first day of each month, and
the
> > time is 3:00AM, then how to configure the differencial backup, should I
> > exclude the day of doing the whole database backup? How to do a whole
> month
> > differencial backup except only one day?
> > 2. If the differencial backup is to use the append to media, how to
> control
> > the size of the back up? (I plan to backup to the hard disk).
> >
> > Thanks in advance
> > Frank
> >
> >
>|||Thanks so much for your detailed information.
Frank
"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:27A5A43B-8C56-4554-BC25-4A212364C921@.microsoft.com...
> The differential contains all changes since the last full backup so
there's
> no point in doing one straight after the full (but it wouldn't hurt just
> won't have much to do).
> I wouldn't append the backups as if you have a corrupt file you lose all
of
> them. It's easier to handle if every backup is in it's own file with a
> datestamp.
> see
> http://www.mindsdoor.net/SQLAdmin/BackupAllDatabases.html
> Which will do full, log and diff backups for all databases and delete old
> backup files.
> A full backup once a month is not usual though - why have you chosen that?
> Usually it is during a quiete period so every night or at weekends.
> Often if you leave too long between full backups then a lot of pages get
> changed and the diff ends up backing up nearly the whole database anyway.
> Very important.
> Make sure you test restore the full backup. This is even more important
with
> your scenario as if you don't have log backups then a corrupt full backup
> will invalidate all the following diffs and you could end up losing a
couple
> of months of data.
>
> "Frank" wrote:
> > Hi,
> > I am setting an differencial backup/restore for the SQL Server, I have
two
> > questions need help,
> > 1. If I do a whole database backup on the first day of each month, and
the
> > time is 3:00AM, then how to configure the differencial backup, should I
> > exclude the day of doing the whole database backup? How to do a whole
month
> > differencial backup except only one day?
> > 2. If the differencial backup is to use the append to media, how to
control
> > the size of the back up? (I plan to backup to the hard disk).
> >
> > Thanks in advance
> > Frank
> >
> >
> >|||Nigel,
There are many useful tips and articles in your webpage.
Thanks again
Frank
"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:27A5A43B-8C56-4554-BC25-4A212364C921@.microsoft.com...
> The differential contains all changes since the last full backup so
there's
> no point in doing one straight after the full (but it wouldn't hurt just
> won't have much to do).
> I wouldn't append the backups as if you have a corrupt file you lose all
of
> them. It's easier to handle if every backup is in it's own file with a
> datestamp.
> see
> http://www.mindsdoor.net/SQLAdmin/BackupAllDatabases.html
> Which will do full, log and diff backups for all databases and delete old
> backup files.
> A full backup once a month is not usual though - why have you chosen that?
> Usually it is during a quiete period so every night or at weekends.
> Often if you leave too long between full backups then a lot of pages get
> changed and the diff ends up backing up nearly the whole database anyway.
> Very important.
> Make sure you test restore the full backup. This is even more important
with
> your scenario as if you don't have log backups then a corrupt full backup
> will invalidate all the following diffs and you could end up losing a
couple
> of months of data.
>
> "Frank" wrote:
> > Hi,
> > I am setting an differencial backup/restore for the SQL Server, I have
two
> > questions need help,
> > 1. If I do a whole database backup on the first day of each month, and
the
> > time is 3:00AM, then how to configure the differencial backup, should I
> > exclude the day of doing the whole database backup? How to do a whole
month
> > differencial backup except only one day?
> > 2. If the differencial backup is to use the append to media, how to
control
> > the size of the back up? (I plan to backup to the hard disk).
> >
> > Thanks in advance
> > Frank
> >
> >
> >

Question on Differencial Backup

Hi,
I am setting an differencial backup/restore for the SQL Server, I have two
questions need help,
1. If I do a whole database backup on the first day of each month, and the
time is 3:00AM, then how to configure the differencial backup, should I
exclude the day of doing the whole database backup? How to do a whole month
differencial backup except only one day?
2. If the differencial backup is to use the append to media, how to control
the size of the back up? (I plan to backup to the hard disk).
Thanks in advance
FrankFrank
Pls read this article
<http://vyaskn.tripod.com/ sql_serve...r />
.htm#Step1
> --administaiting best practices
"Frank" <wangping@.lucent.com> wrote in message
news:O9JHP8$CFHA.3924@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am setting an differencial backup/restore for the SQL Server, I have two
> questions need help,
> 1. If I do a whole database backup on the first day of each month, and the
> time is 3:00AM, then how to configure the differencial backup, should I
> exclude the day of doing the whole database backup? How to do a whole
month
> differencial backup except only one day?
> 2. If the differencial backup is to use the append to media, how to
control
> the size of the back up? (I plan to backup to the hard disk).
> Thanks in advance
> Frank
>|||The differential contains all changes since the last full backup so there's
no point in doing one straight after the full (but it wouldn't hurt just
won't have much to do).
I wouldn't append the backups as if you have a corrupt file you lose all of
them. It's easier to handle if every backup is in it's own file with a
datestamp.
see
http://www.mindsdoor.net/SQLAdmin/B...lDatabases.html
Which will do full, log and diff backups for all databases and delete old
backup files.
A full backup once a month is not usual though - why have you chosen that?
Usually it is during a quiete period so every night or at weekends.
Often if you leave too long between full backups then a lot of pages get
changed and the diff ends up backing up nearly the whole database anyway.
Very important.
Make sure you test restore the full backup. This is even more important with
your scenario as if you don't have log backups then a corrupt full backup
will invalidate all the following diffs and you could end up losing a couple
of months of data.
"Frank" wrote:

> Hi,
> I am setting an differencial backup/restore for the SQL Server, I have two
> questions need help,
> 1. If I do a whole database backup on the first day of each month, and the
> time is 3:00AM, then how to configure the differencial backup, should I
> exclude the day of doing the whole database backup? How to do a whole mont
h
> differencial backup except only one day?
> 2. If the differencial backup is to use the append to media, how to contro
l
> the size of the back up? (I plan to backup to the hard disk).
> Thanks in advance
> Frank
>
>|||Thanks Uri but I could not open the linked website.
Frank
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uBJJaxCDFHA.3540@.TK2MSFTNGP14.phx.gbl...
> Frank
> Pls read this article
>
<http://vyaskn.tripod.com/ sql_serve...r />
.htm#Step1
>
>
>
>
> "Frank" <wangping@.lucent.com> wrote in message
> news:O9JHP8$CFHA.3924@.TK2MSFTNGP15.phx.gbl...
two[vbcol=seagreen]
the[vbcol=seagreen]
> month
> control
>|||Thanks so much for your detailed information.
Frank
"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:27A5A43B-8C56-4554-BC25-4A212364C921@.microsoft.com...
> The differential contains all changes since the last full backup so
there's
> no point in doing one straight after the full (but it wouldn't hurt just
> won't have much to do).
> I wouldn't append the backups as if you have a corrupt file you lose all
of
> them. It's easier to handle if every backup is in it's own file with a
> datestamp.
> see
> http://www.mindsdoor.net/SQLAdmin/B...lDatabases.html
> Which will do full, log and diff backups for all databases and delete old
> backup files.
> A full backup once a month is not usual though - why have you chosen that?
> Usually it is during a quiete period so every night or at weekends.
> Often if you leave too long between full backups then a lot of pages get
> changed and the diff ends up backing up nearly the whole database anyway.
> Very important.
> Make sure you test restore the full backup. This is even more important
with
> your scenario as if you don't have log backups then a corrupt full backup
> will invalidate all the following diffs and you could end up losing a
couple[vbcol=seagreen]
> of months of data.
>
> "Frank" wrote:
>
two[vbcol=seagreen]
the[vbcol=seagreen]
month[vbcol=seagreen]
control[vbcol=seagreen]|||Nigel,
There are many useful tips and articles in your webpage.
Thanks again
Frank
"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:27A5A43B-8C56-4554-BC25-4A212364C921@.microsoft.com...
> The differential contains all changes since the last full backup so
there's
> no point in doing one straight after the full (but it wouldn't hurt just
> won't have much to do).
> I wouldn't append the backups as if you have a corrupt file you lose all
of
> them. It's easier to handle if every backup is in it's own file with a
> datestamp.
> see
> http://www.mindsdoor.net/SQLAdmin/B...lDatabases.html
> Which will do full, log and diff backups for all databases and delete old
> backup files.
> A full backup once a month is not usual though - why have you chosen that?
> Usually it is during a quiete period so every night or at weekends.
> Often if you leave too long between full backups then a lot of pages get
> changed and the diff ends up backing up nearly the whole database anyway.
> Very important.
> Make sure you test restore the full backup. This is even more important
with
> your scenario as if you don't have log backups then a corrupt full backup
> will invalidate all the following diffs and you could end up losing a
couple[vbcol=seagreen]
> of months of data.
>
> "Frank" wrote:
>
two[vbcol=seagreen]
the[vbcol=seagreen]
month[vbcol=seagreen]
control[vbcol=seagreen]

Saturday, February 25, 2012

Question on Data/structure restore

new to SQL Server 2000. We have an obsolete database that we need to
save off for x number of years. DB2 has utilities (DB2Look/Export)
that allows for the export of the data along with a schema and script
that enables the future recreation of the structure of the databases
and tables to include RI etc. You can save off the architecture and
relationships of the tables as well as the data.

Does SQL Server have anything similar?

Failing that, our plan is to backup the data and logs then image the entire disk.

Thanks in advance.

GerryNot exactly sure what you want.

SQL Server 2000 of course has backup and restore capability, and the Enterprise Manager utility has the ability to script database objects and relationships.

A lot depends upon why you are archiving the data and what its intended use is.|||OK. If you had to save off a database...both the data and the 'structure' of the tables, relationships between tables etc....for possible recreation years down the road...how would you do it?|||sql server has a backup wizard, backup the DB to a BKF file and put it wherever, you can restore it just as easily with the wizard - table structures and all the data

also, the design of the database should be documented in the technical specs in a word doc. so you could recreate the whole thing from documentation if necessary|||Look up BACKUP in Books online

Do you have the SQL Server Client tools installed?|||Also look here

http://weblogs.sqlteam.com/tarad/archive/2004/08/04/1876.aspx|||also, the design of the database should be documented in the technical specs in a word doc. so you could recreate the whole thing from documentation if necessaryIn a Word document?
Just script the database ddl to a text file.|||I believe the scipting of the ddl of the objects in addition to a backup AND a data export is a desired redundancy. I belive the scripting capabilties of SS should do the job. That's for all the interesting info .

Gerry|||Should read Thanks :)

Monday, February 20, 2012

Question on Backup/Restore

New to SQL Server. is it true, as it seems to be, that SQL Server
cannot do a point in time recovery to a point in time that would exceed
the time of the last transaction log backup?
That is, it cannot use the active log in conjunction with the archived
logs?
Second question, if the server fails and is rebooted, will the system
catalog tables 'remember' the times of the transaction log backups?
Thanks in advance.
GerryDataPro wrote:
> New to SQL Server. is it true, as it seems to be, that SQL Server
> cannot do a point in time recovery to a point in time that would exceed
> the time of the last transaction log backup?
> That is, it cannot use the active log in conjunction with the archived
> logs?
> Second question, if the server fails and is rebooted, will the system
> catalog tables 'remember' the times of the transaction log backups?
> Thanks in advance.
> Gerry
>
1. No, it cannot use the "active" log for this. You must take a final
transaction log backup, which will contain the data from the active log,
which you can then use in your point-in-time restore.
2. Yes, this information is retained between reboots.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||
> 1. No, it cannot use the "active" log for this. You must take a final
> transaction log backup, which will contain the data from the active log,
> which you can then use in your point-in-time restore.
> 2. Yes, this information is retained between reboots.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Thanks Tracy|||Note, though that even though you need to do a log backup (you cannot use th
e active log), you still
have plenty of options. What is required is that the log file exists. I'm su
re this is the same as
in other systems. If the database is inaccessible, you can still produce a l
og backup using the
NO_TRUNCATE option of the BACKUP LOG command. And even if you can't start th
e SQL Server, you can
handle this situation:
Create a database on some other SQL Server.
Stop that SQL Server.
Delete the database files.
Copy your "production" ldf file where the newly created log file used to be.
Start SQL Server.
Do a log backup using NO_TRUNCATE.
You have now produced that "last log backup".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1165419474.732740.263160@.f1g2000cwa.googlegroups.com...
>
> Thanks Tracy
>|||Thanks Tibor;;
OK after I do the restore of a user database...even though the restore
is marked complete on the percentage bar graph the database was marked
as 'Loading'
Baffled me...thought it was because it was being rolled forward
through transaction logs.
I ended up issuing a
RESTORE DATABASE <Database name> WITH RECOVERY and that cleared it up.
But that does not leave me with a warm and fuzzy.
Is this typical behavior in SQL Server?[vbcol=seagreen]|||When you do RESTORE, you specify NORECOVERY on all restores (db, log, log et
c) but the last one.
Until you've done restore using RECOVERY, the database is in "restoring" sta
te (reported as
"loading" by earlier tools). If you forgot to do RECOVERY on your last backu
p, you can handle the
situation in just the way you did. If you have a case where you do, for inst
ance:
RESTORE DATABASE ... WITH NORECOVERY
RESTORE LOG ... WITH NORECOVERY
RESTORE LOG ... WITH NORECOVERY
RESTORE LOG ... WITH RECOVERY
And the database is still in "restoring" state (inaccessible, and not only a
refresh problem in EM),
you should report this as a bug to MS.
Technical sidenote: The only difference between NORECOVERY and RECOVERY is w
hether the UNDO phase is
performed by the restore process. The prior REDO phase is always performed.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1165424865.858239.168580@.73g2000cwn.googlegroups.com...
> Thanks Tibor;;
> OK after I do the restore of a user database...even though the restore
> is marked complete on the percentage bar graph the database was marked
> as 'Loading'
> Baffled me...thought it was because it was being rolled forward
> through transaction logs.
> I ended up issuing a
> RESTORE DATABASE <Database name> WITH RECOVERY and that cleared it up.
> But that does not leave me with a warm and fuzzy.
> Is this typical behavior in SQL Server?
>|||Tibor Karaszi wrote:
> When you do RESTORE, you specify NORECOVERY on all restores (db, log, log
etc) but the last one.
> Until you've done restore using RECOVERY, the database is in "restoring" s
tate (reported as
> "loading" by earlier tools). If you forgot to do RECOVERY on your last bac
kup, you can handle the
> situation in just the way you did. If you have a case where you do, for in
stance:
> RESTORE DATABASE ... WITH NORECOVERY
> RESTORE LOG ... WITH NORECOVERY
> RESTORE LOG ... WITH NORECOVERY
> RESTORE LOG ... WITH RECOVERY
> And the database is still in "restoring" state (inaccessible, and not only
a refresh problem in EM),
> you should report this as a bug to MS.
> Technical sidenote: The only difference between NORECOVERY and RECOVERY is
whether the UNDO phase is
> performed by the restore process. The prior REDO phase is always performed
.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Thanks Tibor

Question on Backup/Restore

New to SQL Server. is it true, as it seems to be, that SQL Server
cannot do a point in time recovery to a point in time that would exceed
the time of the last transaction log backup?
That is, it cannot use the active log in conjunction with the archived
logs?
Second question, if the server fails and is rebooted, will the system
catalog tables 'remember' the times of the transaction log backups?
Thanks in advance.
GerryDataPro wrote:
> New to SQL Server. is it true, as it seems to be, that SQL Server
> cannot do a point in time recovery to a point in time that would exceed
> the time of the last transaction log backup?
> That is, it cannot use the active log in conjunction with the archived
> logs?
> Second question, if the server fails and is rebooted, will the system
> catalog tables 'remember' the times of the transaction log backups?
> Thanks in advance.
> Gerry
>
1. No, it cannot use the "active" log for this. You must take a final
transaction log backup, which will contain the data from the active log,
which you can then use in your point-in-time restore.
2. Yes, this information is retained between reboots.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||> 1. No, it cannot use the "active" log for this. You must take a final
> transaction log backup, which will contain the data from the active log,
> which you can then use in your point-in-time restore.
> 2. Yes, this information is retained between reboots.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Thanks Tracy|||Note, though that even though you need to do a log backup (you cannot use the active log), you still
have plenty of options. What is required is that the log file exists. I'm sure this is the same as
in other systems. If the database is inaccessible, you can still produce a log backup using the
NO_TRUNCATE option of the BACKUP LOG command. And even if you can't start the SQL Server, you can
handle this situation:
Create a database on some other SQL Server.
Stop that SQL Server.
Delete the database files.
Copy your "production" ldf file where the newly created log file used to be.
Start SQL Server.
Do a log backup using NO_TRUNCATE.
You have now produced that "last log backup".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1165419474.732740.263160@.f1g2000cwa.googlegroups.com...
>> 1. No, it cannot use the "active" log for this. You must take a final
>> transaction log backup, which will contain the data from the active log,
>> which you can then use in your point-in-time restore.
>> 2. Yes, this information is retained between reboots.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
> Thanks Tracy
>|||Thanks Tibor;;
OK after I do the restore of a user database...even though the restore
is marked complete on the percentage bar graph the database was marked
as 'Loading'
Baffled me...thought it was because it was being rolled forward
through transaction logs.
I ended up issuing a
RESTORE DATABASE <Database name> WITH RECOVERY and that cleared it up.
But that does not leave me with a warm and fuzzy.
Is this typical behavior in SQL Server?
> >|||When you do RESTORE, you specify NORECOVERY on all restores (db, log, log etc) but the last one.
Until you've done restore using RECOVERY, the database is in "restoring" state (reported as
"loading" by earlier tools). If you forgot to do RECOVERY on your last backup, you can handle the
situation in just the way you did. If you have a case where you do, for instance:
RESTORE DATABASE ... WITH NORECOVERY
RESTORE LOG ... WITH NORECOVERY
RESTORE LOG ... WITH NORECOVERY
RESTORE LOG ... WITH RECOVERY
And the database is still in "restoring" state (inaccessible, and not only a refresh problem in EM),
you should report this as a bug to MS.
Technical sidenote: The only difference between NORECOVERY and RECOVERY is whether the UNDO phase is
performed by the restore process. The prior REDO phase is always performed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1165424865.858239.168580@.73g2000cwn.googlegroups.com...
> Thanks Tibor;;
> OK after I do the restore of a user database...even though the restore
> is marked complete on the percentage bar graph the database was marked
> as 'Loading'
> Baffled me...thought it was because it was being rolled forward
> through transaction logs.
> I ended up issuing a
> RESTORE DATABASE <Database name> WITH RECOVERY and that cleared it up.
> But that does not leave me with a warm and fuzzy.
> Is this typical behavior in SQL Server?
>> >
>|||Tibor Karaszi wrote:
> When you do RESTORE, you specify NORECOVERY on all restores (db, log, log etc) but the last one.
> Until you've done restore using RECOVERY, the database is in "restoring" state (reported as
> "loading" by earlier tools). If you forgot to do RECOVERY on your last backup, you can handle the
> situation in just the way you did. If you have a case where you do, for instance:
> RESTORE DATABASE ... WITH NORECOVERY
> RESTORE LOG ... WITH NORECOVERY
> RESTORE LOG ... WITH NORECOVERY
> RESTORE LOG ... WITH RECOVERY
> And the database is still in "restoring" state (inaccessible, and not only a refresh problem in EM),
> you should report this as a bug to MS.
> Technical sidenote: The only difference between NORECOVERY and RECOVERY is whether the UNDO phase is
> performed by the restore process. The prior REDO phase is always performed.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Thanks Tibor

Wednesday, March 28, 2012

Question regarding restoring a Database

Question regarding restoring a Database

Monday, March 26, 2012

Question regarding backup of 2005 database to 2000.

Friday, March 23, 2012

Question on System and Data Restore

Tuesday, March 20, 2012

Question on Restore Script

Question on Restore Script

Question on Restore Script

question on recovery sql server 2000

Wednesday, March 7, 2012

Question on Differencial Backup

Question on Differencial Backup

Question on Differencial Backup

Saturday, February 25, 2012

Question on Data/structure restore

Monday, February 20, 2012

Question on Backup/Restore

Question on Backup/Restore

Question on Backup/Restore

SQLAgentMail

Blog Archive

About Me