It recently occurred to me that I should see if the Server backs up it's
logins somewhere/somehow. I make backups of my databases but not the logins
(I'm working on learing that export/import now). But, does SQL Server back u
p
its logins on its own, as part of its own maintenance?
--
Michael Hocksteinsystem databases
"michael" <howlinghound@.nospam.nospam> wrote in message
news:33514BD0-10D3-495E-AD7F-961BE209C322@.microsoft.com...
> It recently occurred to me that I should see if the Server backs up it's
> logins somewhere/somehow. I make backups of my databases but not the
> logins
> (I'm working on learing that export/import now). But, does SQL Server back
> up
> its logins on its own, as part of its own maintenance?
> --
> Michael Hockstein|||Logins are stored in master database, so you should back up the master
database to get a logins backup
"michael" <howlinghound@.nospam.nospam> escribi en el mensaje
news:33514BD0-10D3-495E-AD7F-961BE209C322@.microsoft.com...
> It recently occurred to me that I should see if the Server backs up it's
> logins somewhere/somehow. I make backups of my databases but not the
> logins
> (I'm working on learing that export/import now). But, does SQL Server back
> up
> its logins on its own, as part of its own maintenance?
> --
> Michael Hockstein|||Thanks.
Michael Hockstein
"David J. Cartwright" wrote:
> system databases
> "michael" <howlinghound@.nospam.nospam> wrote in message
> news:33514BD0-10D3-495E-AD7F-961BE209C322@.microsoft.com...
>
>|||It looks like the data I want to back up is specifically in dbo.sysxlogins.
I'll have to backup the master database.
Thanks
--
Michael Hockstein
"Antonio Soto" wrote:
> Logins are stored in master database, so you should back up the master
> database to get a logins backup
>
> "michael" <howlinghound@.nospam.nospam> escribió en el mensaje
> news:33514BD0-10D3-495E-AD7F-961BE209C322@.microsoft.com...
>
>
Showing posts with label backups. Show all posts
Showing posts with label backups. Show all posts
Tuesday, March 27, 2012
Thursday, March 22, 2012
does restore operation break tlog bckup chain?
Hi all!
I'm thinking of switching my production db to full recovery model.
Now, I've some questions regarding tlog backups chain. What can
possibly break chain of transaction log backups?
And yet another question. Does restore operation break transaction log
backups chain? I mean, should every restore operation be followed by
full db backup?
with regards
Maciej Szymanski> What can possibly break chain of transaction log backups?
In the FULL recovery model, truncating the log without creating a log backup
file (BACKUP LOG WITH TRUNCATE_ONLY or NO_LOG) will break the log backup
sequence.
> And yet another question. Does restore operation break transaction log
> backups chain? I mean, should every restore operation be followed by
> full db backup?
The script below illustrates that a backup isn't needed following a restore.
CREATE DATABASE MyDatabase
ALTER DATABASE MyDatabase
SET RECOVERY FULL
GO
USE MyDatabase
CREATE TABLE MyTable(Col1 int NOT NULL)
INSERT INTO MyTable VALUES(1)
BACKUP DATABASE MyDatabase
TO DISK='C:\Backups\MyDatabase.bak'
WITH INIT
INSERT INTO MyTable VALUES(2)
BACKUP LOG MyDatabase
TO DISK='C:\Backups\MyDatabase_Log1.bak'
WITH INIT
GO
USE master
RESTORE DATABASE MyDatabase
FROM DISK='C:\Backups\MyDatabase.bak'
WITH NORECOVERY
RESTORE LOG MyDatabase
FROM DISK='C:\Backups\MyDatabase_Log1.bak'
WITH RECOVERY
GO
USE MyDatabase
INSERT INTO MyTable VALUES(3)
BACKUP LOG MyDatabase
TO DISK='C:\Backups\MyDatabase_Log2.bak'
WITH INIT
GO
USE master
RESTORE DATABASE MyDatabase
FROM DISK='C:\Backups\MyDatabase.bak'
WITH NORECOVERY
RESTORE LOG MyDatabase
FROM DISK='C:\Backups\MyDatabase_Log1.bak'
WITH NORECOVERY
RESTORE LOG MyDatabase
FROM DISK='C:\Backups\MyDatabase_Log2.bak'
WITH RECOVERY
GO
USE MyDatabase
SELECT * FROM MyTable -- 3 rows returned
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Maciej Szymanski" <maciek@.kolobrzeg.com.pl> wrote in message
news:85a5f2.0401170757.4de3ca20@.posting.google.com...
> Hi all!
> I'm thinking of switching my production db to full recovery model.
> Now, I've some questions regarding tlog backups chain. What can
> possibly break chain of transaction log backups?
> And yet another question. Does restore operation break transaction log
> backups chain? I mean, should every restore operation be followed by
> full db backup?
> with regards
> Maciej Szymanski
I'm thinking of switching my production db to full recovery model.
Now, I've some questions regarding tlog backups chain. What can
possibly break chain of transaction log backups?
And yet another question. Does restore operation break transaction log
backups chain? I mean, should every restore operation be followed by
full db backup?
with regards
Maciej Szymanski> What can possibly break chain of transaction log backups?
In the FULL recovery model, truncating the log without creating a log backup
file (BACKUP LOG WITH TRUNCATE_ONLY or NO_LOG) will break the log backup
sequence.
> And yet another question. Does restore operation break transaction log
> backups chain? I mean, should every restore operation be followed by
> full db backup?
The script below illustrates that a backup isn't needed following a restore.
CREATE DATABASE MyDatabase
ALTER DATABASE MyDatabase
SET RECOVERY FULL
GO
USE MyDatabase
CREATE TABLE MyTable(Col1 int NOT NULL)
INSERT INTO MyTable VALUES(1)
BACKUP DATABASE MyDatabase
TO DISK='C:\Backups\MyDatabase.bak'
WITH INIT
INSERT INTO MyTable VALUES(2)
BACKUP LOG MyDatabase
TO DISK='C:\Backups\MyDatabase_Log1.bak'
WITH INIT
GO
USE master
RESTORE DATABASE MyDatabase
FROM DISK='C:\Backups\MyDatabase.bak'
WITH NORECOVERY
RESTORE LOG MyDatabase
FROM DISK='C:\Backups\MyDatabase_Log1.bak'
WITH RECOVERY
GO
USE MyDatabase
INSERT INTO MyTable VALUES(3)
BACKUP LOG MyDatabase
TO DISK='C:\Backups\MyDatabase_Log2.bak'
WITH INIT
GO
USE master
RESTORE DATABASE MyDatabase
FROM DISK='C:\Backups\MyDatabase.bak'
WITH NORECOVERY
RESTORE LOG MyDatabase
FROM DISK='C:\Backups\MyDatabase_Log1.bak'
WITH NORECOVERY
RESTORE LOG MyDatabase
FROM DISK='C:\Backups\MyDatabase_Log2.bak'
WITH RECOVERY
GO
USE MyDatabase
SELECT * FROM MyTable -- 3 rows returned
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Maciej Szymanski" <maciek@.kolobrzeg.com.pl> wrote in message
news:85a5f2.0401170757.4de3ca20@.posting.google.com...
> Hi all!
> I'm thinking of switching my production db to full recovery model.
> Now, I've some questions regarding tlog backups chain. What can
> possibly break chain of transaction log backups?
> And yet another question. Does restore operation break transaction log
> backups chain? I mean, should every restore operation be followed by
> full db backup?
> with regards
> Maciej Szymanski
does restore operation break tlog bckup chain?
Hi all!
I'm thinking of switching my production db to full recovery model.
Now, I've some questions regarding tlog backups chain. What can
possibly break chain of transaction log backups?
And yet another question. Does restore operation break transaction log
backups chain? I mean, should every restore operation be followed by
full db backup?
with regards
Maciej Szymanski> What can possibly break chain of transaction log backups?
In the FULL recovery model, truncating the log without creating a log backup
file (BACKUP LOG WITH TRUNCATE_ONLY or NO_LOG) will break the log backup
sequence.
The script below illustrates that a backup isn't needed following a restore.
CREATE DATABASE MyDatabase
ALTER DATABASE MyDatabase
SET RECOVERY FULL
GO
USE MyDatabase
CREATE TABLE MyTable(Col1 int NOT NULL)
INSERT INTO MyTable VALUES(1)
BACKUP DATABASE MyDatabase
TO DISK='C:\Backups\MyDatabase.bak'
WITH INIT
INSERT INTO MyTable VALUES(2)
BACKUP LOG MyDatabase
TO DISK='C:\Backups\MyDatabase_Log1.bak'
WITH INIT
GO
USE master
RESTORE DATABASE MyDatabase
FROM DISK='C:\Backups\MyDatabase.bak'
WITH NORECOVERY
RESTORE LOG MyDatabase
FROM DISK='C:\Backups\MyDatabase_Log1.bak'
WITH RECOVERY
GO
USE MyDatabase
INSERT INTO MyTable VALUES(3)
BACKUP LOG MyDatabase
TO DISK='C:\Backups\MyDatabase_Log2.bak'
WITH INIT
GO
USE master
RESTORE DATABASE MyDatabase
FROM DISK='C:\Backups\MyDatabase.bak'
WITH NORECOVERY
RESTORE LOG MyDatabase
FROM DISK='C:\Backups\MyDatabase_Log1.bak'
WITH NORECOVERY
RESTORE LOG MyDatabase
FROM DISK='C:\Backups\MyDatabase_Log2.bak'
WITH RECOVERY
GO
USE MyDatabase
SELECT * FROM MyTable -- 3 rows returned
Hope this helps.
Dan Guzman
SQL Server MVP
"Maciej Szymanski" <maciek@.kolobrzeg.com.pl> wrote in message
news:85a5f2.0401170757.4de3ca20@.posting.google.com...
I'm thinking of switching my production db to full recovery model.
Now, I've some questions regarding tlog backups chain. What can
possibly break chain of transaction log backups?
And yet another question. Does restore operation break transaction log
backups chain? I mean, should every restore operation be followed by
full db backup?
with regards
Maciej Szymanski> What can possibly break chain of transaction log backups?
In the FULL recovery model, truncating the log without creating a log backup
file (BACKUP LOG WITH TRUNCATE_ONLY or NO_LOG) will break the log backup
sequence.
quote:
> And yet another question. Does restore operation break transaction log
> backups chain? I mean, should every restore operation be followed by
> full db backup?
The script below illustrates that a backup isn't needed following a restore.
CREATE DATABASE MyDatabase
ALTER DATABASE MyDatabase
SET RECOVERY FULL
GO
USE MyDatabase
CREATE TABLE MyTable(Col1 int NOT NULL)
INSERT INTO MyTable VALUES(1)
BACKUP DATABASE MyDatabase
TO DISK='C:\Backups\MyDatabase.bak'
WITH INIT
INSERT INTO MyTable VALUES(2)
BACKUP LOG MyDatabase
TO DISK='C:\Backups\MyDatabase_Log1.bak'
WITH INIT
GO
USE master
RESTORE DATABASE MyDatabase
FROM DISK='C:\Backups\MyDatabase.bak'
WITH NORECOVERY
RESTORE LOG MyDatabase
FROM DISK='C:\Backups\MyDatabase_Log1.bak'
WITH RECOVERY
GO
USE MyDatabase
INSERT INTO MyTable VALUES(3)
BACKUP LOG MyDatabase
TO DISK='C:\Backups\MyDatabase_Log2.bak'
WITH INIT
GO
USE master
RESTORE DATABASE MyDatabase
FROM DISK='C:\Backups\MyDatabase.bak'
WITH NORECOVERY
RESTORE LOG MyDatabase
FROM DISK='C:\Backups\MyDatabase_Log1.bak'
WITH NORECOVERY
RESTORE LOG MyDatabase
FROM DISK='C:\Backups\MyDatabase_Log2.bak'
WITH RECOVERY
GO
USE MyDatabase
SELECT * FROM MyTable -- 3 rows returned
Hope this helps.
Dan Guzman
SQL Server MVP
"Maciej Szymanski" <maciek@.kolobrzeg.com.pl> wrote in message
news:85a5f2.0401170757.4de3ca20@.posting.google.com...
quote:
> Hi all!
> I'm thinking of switching my production db to full recovery model.
> Now, I've some questions regarding tlog backups chain. What can
> possibly break chain of transaction log backups?
> And yet another question. Does restore operation break transaction log
> backups chain? I mean, should every restore operation be followed by
> full db backup?
> with regards
> Maciej Szymanski
Subscribe to:
Posts (Atom)