I am but a lowly DBA unworthy of this task...
I have a large (200+ GB) database with many (100+) files. Please don't ask me why I did it this way; I inherited this database -- really, it wasn't my idea.
My predecessor also seemd to think that backups were unnecessary; there have been no backups of this database -- ever.
While we cast about for a good long term solution, I am trying various short-term options. One I want to explore is to back the database up in chunks -- ie, by backing up individual files. I created a test database with five files (there is only one filegroup on the production server). Here is the DDL:
-- =============================================
-- Create database on mulitple file groups
-- =============================================
IF EXISTS (SELECT *
FROM master..sysdatabases
WHERE name = N'MultiFile')
DROP DATABASE MultiFile
GO
CREATE DATABASE MultiFile
ON PRIMARY
( NAME = MultiFile,
FILENAME = N'e:\MSSQL\Data\MultiFile.mdf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = MultiFile2,
FILENAME = N'e:\MSSQL\Data\MultiFile2.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = MultiFile3,
FILENAME = N'e:\MSSQL\Data\MultiFile3.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = MultiFile4,
FILENAME = N'e:\MSSQL\Data\MultiFile4.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = MultiFile5,
FILENAME = N'e:\MSSQL\Data\MultiFile5.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%)
LOG ON
( NAME = MultiFile_Log,
FILENAME = N'e:\MSSQL\Data\MultiFile_Log.ldf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%)
GO
I have tried the following backup script:
BACKUP DATABASE MultiFile
FILE = 'MultiFile',
FILE = 'MultiFile2'
TO Backup01
WITH
INIT
BACKUP DATABASE MultiFile
FILE = 'MultiFile3',
FILE = 'MultiFile4'
TO Backup02
WITH
INIT
BACKUP DATABASE MultiFile
FILE = 'MultiFile5'
TO Backup03
WITH
INIT
And here is the restore script:
RESTORE DATABASE MultiFile2
FILE = 'MultiFile',
FILE = 'MultiFile2',
FILE = 'MultiFile3',
FILE = 'MultiFile4',
FILE = 'MultiFile5'
FROM Backup01, Backup02, Backup03
WITH MOVE 'MultiFile' TO 'E:\MSSQL\Data\aMultfile.mdf',
MOVE 'MultiFile2' TO 'E:\MSSQL\Data\aMultifile2.mdf',
MOVE 'MultiFile2' TO 'E:\MSSQL\Data\aMultifile3.mdf',
MOVE 'MultiFile2' TO 'E:\MSSQL\Data\aMultifile4.mdf',
MOVE 'MultiFile2' TO 'E:\MSSQL\Data\aMultifile5.mdf',
MOVE 'MultiFile_log' TO 'E:\MSSQL\aMultFile_Log.ldf'
However, running the Restore script generates the following error:
Server: Msg 3259, Level 16, State 1, Line 1
The volume on device 'Backup02' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I'm not sure what to make of this. What do I need to alter in either the backup script or the restore script to make this work?
I am trying this because my objectives are to:
1. Limit the amount of work that the server is performing during any one given backup session. The idea that I have is to backup the database in chunks using a rolling 3-5 day window.
2. The database must be up and operational 7x24x365 (except for one 4 hour window each month)
3. This is not the long-term solution; but I need something to tide us over until we can purchase additional storage capacity.
I appreciate any thoughts and or guidance you can provide.
Regards,
hmscottYou may need something like
RESTORE DATABASE MultiFile2
'MultiFile',
'MultiFile2',
'MultiFile3',
'MultiFile4',
'MultiFile5'
FROM Backup01, Backup02, Backup03
WITH MOVE 'MultiFile' TO 'E:\MSSQL\Data\aMultfile.mdf',
MOVE 'MultiFile2' ...
Some cautions, though. You will need to keep all of your transaction logs, if you back up the files on separate nights. I have not tried to do a file by file restore, so I am not sure how easy it would be.|||You might consider a backup directly to tape... You can do a backup while the server is running with negligable impact, and it would allow you to relatively quickly and easily get a backup (or two) made and SENT OFF SITE before you have a cornary! You'll eat a couple of tapes, but that doesn't even rank as a HK at this point in time!
I don't know of any good way to backup part of a filegroup. It just isn't a good plan in my experience.
You could also BCP the tables out to flat files, and back those up. The down side to this approach is that there isn't any synchronization, so you'll never get a full backup made that you can really truly trust.
-PatP
Showing posts with label dba. Show all posts
Showing posts with label dba. Show all posts
Monday, March 19, 2012
Wednesday, March 7, 2012
Backup and restore to a different machine
I am using SQL Server 2000. I backup database dbA in machine A. I would like
to restore this backup in another machine (machine B).
In Machine B I map network drive to machine A (say to drive X).
So, in Enterprise Manager on the SQL Server for machine B, I created a
database called dbA, then I go to task - restore database.
In restore database I do not see drive X to restore the database from.
Can I restore dbA that is located on machine A to dbA on machine B without
copying the backup for dbA to machine B ?
Thank you.
It's best to do this by executing the RESTORE DATABASE statement, and by
using UNC instead of a mapped drive letter.
First, find out the database files used by the database on Machine A. You
can execute RESTORE FILELISTONLY against the MacbineB instance as follows to
find the logical names of these database files (assuming that the backup file
DBA.bak is in C:\junk):
restore filelistonly from disk='\\MachineA\c$\junk\DBA.bak'
Then, you can execute RESTORE DATABASE against the MachineB instance as
follows to restore the database (assuming that the database file logical
names are 'dba' and 'dba_log'):
restore database DBA from disk='\\MachineA\c$\junk\DBA.bak'
with recovery, move 'dba' to 'd:\junk\dba.mdf',
move 'dba_log' to 'd:\junk\dba_log.ldf'
Linchi
"fniles" wrote:
> I am using SQL Server 2000. I backup database dbA in machine A. I would like
> to restore this backup in another machine (machine B).
> In Machine B I map network drive to machine A (say to drive X).
> So, in Enterprise Manager on the SQL Server for machine B, I created a
> database called dbA, then I go to task - restore database.
> In restore database I do not see drive X to restore the database from.
> Can I restore dbA that is located on machine A to dbA on machine B without
> copying the backup for dbA to machine B ?
> Thank you.
>
>
|||In addition to Linchi's suggestion you also need to copy the logins to make
sure you won't have problems with accessing those databases once they are
restored on the other machine. Creating those same users on the other
machine manually won't do the trick as accounts are mapped using SID values
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:98E7782B-40E4-40AE-A891-7A03143BAD84@.microsoft.com...[vbcol=seagreen]
> It's best to do this by executing the RESTORE DATABASE statement, and by
> using UNC instead of a mapped drive letter.
> First, find out the database files used by the database on Machine A. You
> can execute RESTORE FILELISTONLY against the MacbineB instance as follows
> to
> find the logical names of these database files (assuming that the backup
> file
> DBA.bak is in C:\junk):
> restore filelistonly from disk='\\MachineA\c$\junk\DBA.bak'
> Then, you can execute RESTORE DATABASE against the MachineB instance as
> follows to restore the database (assuming that the database file logical
> names are 'dba' and 'dba_log'):
> restore database DBA from disk='\\MachineA\c$\junk\DBA.bak'
> with recovery, move 'dba' to 'd:\junk\dba.mdf',
> move 'dba_log' to 'd:\junk\dba_log.ldf'
> Linchi
> "fniles" wrote:
to restore this backup in another machine (machine B).
In Machine B I map network drive to machine A (say to drive X).
So, in Enterprise Manager on the SQL Server for machine B, I created a
database called dbA, then I go to task - restore database.
In restore database I do not see drive X to restore the database from.
Can I restore dbA that is located on machine A to dbA on machine B without
copying the backup for dbA to machine B ?
Thank you.
It's best to do this by executing the RESTORE DATABASE statement, and by
using UNC instead of a mapped drive letter.
First, find out the database files used by the database on Machine A. You
can execute RESTORE FILELISTONLY against the MacbineB instance as follows to
find the logical names of these database files (assuming that the backup file
DBA.bak is in C:\junk):
restore filelistonly from disk='\\MachineA\c$\junk\DBA.bak'
Then, you can execute RESTORE DATABASE against the MachineB instance as
follows to restore the database (assuming that the database file logical
names are 'dba' and 'dba_log'):
restore database DBA from disk='\\MachineA\c$\junk\DBA.bak'
with recovery, move 'dba' to 'd:\junk\dba.mdf',
move 'dba_log' to 'd:\junk\dba_log.ldf'
Linchi
"fniles" wrote:
> I am using SQL Server 2000. I backup database dbA in machine A. I would like
> to restore this backup in another machine (machine B).
> In Machine B I map network drive to machine A (say to drive X).
> So, in Enterprise Manager on the SQL Server for machine B, I created a
> database called dbA, then I go to task - restore database.
> In restore database I do not see drive X to restore the database from.
> Can I restore dbA that is located on machine A to dbA on machine B without
> copying the backup for dbA to machine B ?
> Thank you.
>
>
|||In addition to Linchi's suggestion you also need to copy the logins to make
sure you won't have problems with accessing those databases once they are
restored on the other machine. Creating those same users on the other
machine manually won't do the trick as accounts are mapped using SID values
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:98E7782B-40E4-40AE-A891-7A03143BAD84@.microsoft.com...[vbcol=seagreen]
> It's best to do this by executing the RESTORE DATABASE statement, and by
> using UNC instead of a mapped drive letter.
> First, find out the database files used by the database on Machine A. You
> can execute RESTORE FILELISTONLY against the MacbineB instance as follows
> to
> find the logical names of these database files (assuming that the backup
> file
> DBA.bak is in C:\junk):
> restore filelistonly from disk='\\MachineA\c$\junk\DBA.bak'
> Then, you can execute RESTORE DATABASE against the MachineB instance as
> follows to restore the database (assuming that the database file logical
> names are 'dba' and 'dba_log'):
> restore database DBA from disk='\\MachineA\c$\junk\DBA.bak'
> with recovery, move 'dba' to 'd:\junk\dba.mdf',
> move 'dba_log' to 'd:\junk\dba_log.ldf'
> Linchi
> "fniles" wrote:
Backup and restore to a different machine
I am using SQL Server 2000. I backup database dbA in machine A. I would like
to restore this backup in another machine (machine B).
In Machine B I map network drive to machine A (say to drive X).
So, in Enterprise Manager on the SQL Server for machine B, I created a
database called dbA, then I go to task - restore database.
In restore database I do not see drive X to restore the database from.
Can I restore dbA that is located on machine A to dbA on machine B without
copying the backup for dbA to machine B ?
Thank you.It's best to do this by executing the RESTORE DATABASE statement, and by
using UNC instead of a mapped drive letter.
First, find out the database files used by the database on Machine A. You
can execute RESTORE FILELISTONLY against the MacbineB instance as follows to
find the logical names of these database files (assuming that the backup file
DBA.bak is in C:\junk):
restore filelistonly from disk='\\MachineA\c$\junk\DBA.bak'
Then, you can execute RESTORE DATABASE against the MachineB instance as
follows to restore the database (assuming that the database file logical
names are 'dba' and 'dba_log'):
restore database DBA from disk='\\MachineA\c$\junk\DBA.bak'
with recovery, move 'dba' to 'd:\junk\dba.mdf',
move 'dba_log' to 'd:\junk\dba_log.ldf'
Linchi
"fniles" wrote:
> I am using SQL Server 2000. I backup database dbA in machine A. I would like
> to restore this backup in another machine (machine B).
> In Machine B I map network drive to machine A (say to drive X).
> So, in Enterprise Manager on the SQL Server for machine B, I created a
> database called dbA, then I go to task - restore database.
> In restore database I do not see drive X to restore the database from.
> Can I restore dbA that is located on machine A to dbA on machine B without
> copying the backup for dbA to machine B ?
> Thank you.
>
>|||In addition to Linchi's suggestion you also need to copy the logins to make
sure you won't have problems with accessing those databases once they are
restored on the other machine. Creating those same users on the other
machine manually won't do the trick as accounts are mapped using SID values
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:98E7782B-40E4-40AE-A891-7A03143BAD84@.microsoft.com...
> It's best to do this by executing the RESTORE DATABASE statement, and by
> using UNC instead of a mapped drive letter.
> First, find out the database files used by the database on Machine A. You
> can execute RESTORE FILELISTONLY against the MacbineB instance as follows
> to
> find the logical names of these database files (assuming that the backup
> file
> DBA.bak is in C:\junk):
> restore filelistonly from disk='\\MachineA\c$\junk\DBA.bak'
> Then, you can execute RESTORE DATABASE against the MachineB instance as
> follows to restore the database (assuming that the database file logical
> names are 'dba' and 'dba_log'):
> restore database DBA from disk='\\MachineA\c$\junk\DBA.bak'
> with recovery, move 'dba' to 'd:\junk\dba.mdf',
> move 'dba_log' to 'd:\junk\dba_log.ldf'
> Linchi
> "fniles" wrote:
>> I am using SQL Server 2000. I backup database dbA in machine A. I would
>> like
>> to restore this backup in another machine (machine B).
>> In Machine B I map network drive to machine A (say to drive X).
>> So, in Enterprise Manager on the SQL Server for machine B, I created a
>> database called dbA, then I go to task - restore database.
>> In restore database I do not see drive X to restore the database from.
>> Can I restore dbA that is located on machine A to dbA on machine B
>> without
>> copying the backup for dbA to machine B ?
>> Thank you.
>>
>>
to restore this backup in another machine (machine B).
In Machine B I map network drive to machine A (say to drive X).
So, in Enterprise Manager on the SQL Server for machine B, I created a
database called dbA, then I go to task - restore database.
In restore database I do not see drive X to restore the database from.
Can I restore dbA that is located on machine A to dbA on machine B without
copying the backup for dbA to machine B ?
Thank you.It's best to do this by executing the RESTORE DATABASE statement, and by
using UNC instead of a mapped drive letter.
First, find out the database files used by the database on Machine A. You
can execute RESTORE FILELISTONLY against the MacbineB instance as follows to
find the logical names of these database files (assuming that the backup file
DBA.bak is in C:\junk):
restore filelistonly from disk='\\MachineA\c$\junk\DBA.bak'
Then, you can execute RESTORE DATABASE against the MachineB instance as
follows to restore the database (assuming that the database file logical
names are 'dba' and 'dba_log'):
restore database DBA from disk='\\MachineA\c$\junk\DBA.bak'
with recovery, move 'dba' to 'd:\junk\dba.mdf',
move 'dba_log' to 'd:\junk\dba_log.ldf'
Linchi
"fniles" wrote:
> I am using SQL Server 2000. I backup database dbA in machine A. I would like
> to restore this backup in another machine (machine B).
> In Machine B I map network drive to machine A (say to drive X).
> So, in Enterprise Manager on the SQL Server for machine B, I created a
> database called dbA, then I go to task - restore database.
> In restore database I do not see drive X to restore the database from.
> Can I restore dbA that is located on machine A to dbA on machine B without
> copying the backup for dbA to machine B ?
> Thank you.
>
>|||In addition to Linchi's suggestion you also need to copy the logins to make
sure you won't have problems with accessing those databases once they are
restored on the other machine. Creating those same users on the other
machine manually won't do the trick as accounts are mapped using SID values
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:98E7782B-40E4-40AE-A891-7A03143BAD84@.microsoft.com...
> It's best to do this by executing the RESTORE DATABASE statement, and by
> using UNC instead of a mapped drive letter.
> First, find out the database files used by the database on Machine A. You
> can execute RESTORE FILELISTONLY against the MacbineB instance as follows
> to
> find the logical names of these database files (assuming that the backup
> file
> DBA.bak is in C:\junk):
> restore filelistonly from disk='\\MachineA\c$\junk\DBA.bak'
> Then, you can execute RESTORE DATABASE against the MachineB instance as
> follows to restore the database (assuming that the database file logical
> names are 'dba' and 'dba_log'):
> restore database DBA from disk='\\MachineA\c$\junk\DBA.bak'
> with recovery, move 'dba' to 'd:\junk\dba.mdf',
> move 'dba_log' to 'd:\junk\dba_log.ldf'
> Linchi
> "fniles" wrote:
>> I am using SQL Server 2000. I backup database dbA in machine A. I would
>> like
>> to restore this backup in another machine (machine B).
>> In Machine B I map network drive to machine A (say to drive X).
>> So, in Enterprise Manager on the SQL Server for machine B, I created a
>> database called dbA, then I go to task - restore database.
>> In restore database I do not see drive X to restore the database from.
>> Can I restore dbA that is located on machine A to dbA on machine B
>> without
>> copying the backup for dbA to machine B ?
>> Thank you.
>>
>>
Tuesday, February 14, 2012
backup
I am not a sql server DBA, so please be gentle.
To se tthe scene. We are using hardware plication , and point in time copy
technolgoy for backup (EMC SRDF for rpelication, and Business Copies for
Backup). For backup we shut the DB down, split the BC Disk, mount that disk
to a backup server, and then start the SQL DB up.
Bearing this in mind is it possible to roll forward/backup using the logs,
post a restore proceedure? Is it possible to get back to a point in time
during the day when the DB may of corrupted, or lost data? I know I can get
back to the point when the Business Copy was taken, but unsure of the
flexability within SQL 2000. I know this is possible on Oracle DB's.Disregarding the SAN for a moment:
In SQL Server you basically have a database backup (full backup) and transac
tion log backups (backup
of changes). When you restore, you do the most recent db backup and then the
subsequent log backups-
For a log backup, you have a STOPAT parameter to stop at desired time.
What you need is for your SAN to communicate that a snapshot is taken to SQL
Server so that SQL
server considers this a db backup. Because this will allow you to do log bac
kups. So you can now use
the db backup (which really is a snapshot) and then apply the log backups wi
th above mentioned
STOPAT.
Ask your SAN vendor for SQL Server support, and this is most probably suppor
ted as a "VDI device"
(SQL Server terminology).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"visaya" <visaya@.discussions.microsoft.com> wrote in message
news:55E2C2D4-509B-429B-9C38-5CDD5D5CB111@.microsoft.com...
>I am not a sql server DBA, so please be gentle.
> To se tthe scene. We are using hardware plication , and point in time copy
> technolgoy for backup (EMC SRDF for rpelication, and Business Copies for
> Backup). For backup we shut the DB down, split the BC Disk, mount that dis
k
> to a backup server, and then start the SQL DB up.
> Bearing this in mind is it possible to roll forward/backup using the logs,
> post a restore proceedure? Is it possible to get back to a point in time
> during the day when the DB may of corrupted, or lost data? I know I can ge
t
> back to the point when the Business Copy was taken, but unsure of the
> flexability within SQL 2000. I know this is possible on Oracle DB's.|||Tibor
Thanks, so just to clarify;
If, using EMC, I use Replciationm Manager (and this does do SQL support), in
a restore I would do dthe following.
Stop the DB
Use my BC, and reverse merge back to the original DATA disk. (please bear in
mind that the data, and logs are on different disk, and BCs)
Use my LOGS BV to replay the logs woth the STOPAT parameter? This would get
me back to a point in time?
"Tibor Karaszi" wrote:
> Disregarding the SAN for a moment:
> In SQL Server you basically have a database backup (full backup) and trans
action log backups (backup
> of changes). When you restore, you do the most recent db backup and then t
he subsequent log backups-
> For a log backup, you have a STOPAT parameter to stop at desired time.
> What you need is for your SAN to communicate that a snapshot is taken to S
QL Server so that SQL
> server considers this a db backup. Because this will allow you to do log b
ackups. So you can now use
> the db backup (which really is a snapshot) and then apply the log backups
with above mentioned
> STOPAT.
> Ask your SAN vendor for SQL Server support, and this is most probably supp
orted as a "VDI device"
> (SQL Server terminology).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "visaya" <visaya@.discussions.microsoft.com> wrote in message
> news:55E2C2D4-509B-429B-9C38-5CDD5D5CB111@.microsoft.com...
>
>|||You have to read the documentation with your SAN for how to do restore. Or p
erhaps someone who have
used those products might jump in here.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"visaya" <visaya@.discussions.microsoft.com> wrote in message
news:EF560F6D-0E77-40D9-AD51-1EB853ABE6BF@.microsoft.com...[vbcol=seagreen]
> Tibor
> Thanks, so just to clarify;
> If, using EMC, I use Replciationm Manager (and this does do SQL support),
in
> a restore I would do dthe following.
> Stop the DB
> Use my BC, and reverse merge back to the original DATA disk. (please bear
in
> mind that the data, and logs are on different disk, and BCs)
> Use my LOGS BV to replay the logs woth the STOPAT parameter? This would ge
t
> me back to a point in time?
> "Tibor Karaszi" wrote:
>
To se tthe scene. We are using hardware plication , and point in time copy
technolgoy for backup (EMC SRDF for rpelication, and Business Copies for
Backup). For backup we shut the DB down, split the BC Disk, mount that disk
to a backup server, and then start the SQL DB up.
Bearing this in mind is it possible to roll forward/backup using the logs,
post a restore proceedure? Is it possible to get back to a point in time
during the day when the DB may of corrupted, or lost data? I know I can get
back to the point when the Business Copy was taken, but unsure of the
flexability within SQL 2000. I know this is possible on Oracle DB's.Disregarding the SAN for a moment:
In SQL Server you basically have a database backup (full backup) and transac
tion log backups (backup
of changes). When you restore, you do the most recent db backup and then the
subsequent log backups-
For a log backup, you have a STOPAT parameter to stop at desired time.
What you need is for your SAN to communicate that a snapshot is taken to SQL
Server so that SQL
server considers this a db backup. Because this will allow you to do log bac
kups. So you can now use
the db backup (which really is a snapshot) and then apply the log backups wi
th above mentioned
STOPAT.
Ask your SAN vendor for SQL Server support, and this is most probably suppor
ted as a "VDI device"
(SQL Server terminology).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"visaya" <visaya@.discussions.microsoft.com> wrote in message
news:55E2C2D4-509B-429B-9C38-5CDD5D5CB111@.microsoft.com...
>I am not a sql server DBA, so please be gentle.
> To se tthe scene. We are using hardware plication , and point in time copy
> technolgoy for backup (EMC SRDF for rpelication, and Business Copies for
> Backup). For backup we shut the DB down, split the BC Disk, mount that dis
k
> to a backup server, and then start the SQL DB up.
> Bearing this in mind is it possible to roll forward/backup using the logs,
> post a restore proceedure? Is it possible to get back to a point in time
> during the day when the DB may of corrupted, or lost data? I know I can ge
t
> back to the point when the Business Copy was taken, but unsure of the
> flexability within SQL 2000. I know this is possible on Oracle DB's.|||Tibor
Thanks, so just to clarify;
If, using EMC, I use Replciationm Manager (and this does do SQL support), in
a restore I would do dthe following.
Stop the DB
Use my BC, and reverse merge back to the original DATA disk. (please bear in
mind that the data, and logs are on different disk, and BCs)
Use my LOGS BV to replay the logs woth the STOPAT parameter? This would get
me back to a point in time?
"Tibor Karaszi" wrote:
> Disregarding the SAN for a moment:
> In SQL Server you basically have a database backup (full backup) and trans
action log backups (backup
> of changes). When you restore, you do the most recent db backup and then t
he subsequent log backups-
> For a log backup, you have a STOPAT parameter to stop at desired time.
> What you need is for your SAN to communicate that a snapshot is taken to S
QL Server so that SQL
> server considers this a db backup. Because this will allow you to do log b
ackups. So you can now use
> the db backup (which really is a snapshot) and then apply the log backups
with above mentioned
> STOPAT.
> Ask your SAN vendor for SQL Server support, and this is most probably supp
orted as a "VDI device"
> (SQL Server terminology).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "visaya" <visaya@.discussions.microsoft.com> wrote in message
> news:55E2C2D4-509B-429B-9C38-5CDD5D5CB111@.microsoft.com...
>
>|||You have to read the documentation with your SAN for how to do restore. Or p
erhaps someone who have
used those products might jump in here.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"visaya" <visaya@.discussions.microsoft.com> wrote in message
news:EF560F6D-0E77-40D9-AD51-1EB853ABE6BF@.microsoft.com...[vbcol=seagreen]
> Tibor
> Thanks, so just to clarify;
> If, using EMC, I use Replciationm Manager (and this does do SQL support),
in
> a restore I would do dthe following.
> Stop the DB
> Use my BC, and reverse merge back to the original DATA disk. (please bear
in
> mind that the data, and logs are on different disk, and BCs)
> Use my LOGS BV to replay the logs woth the STOPAT parameter? This would ge
t
> me back to a point in time?
> "Tibor Karaszi" wrote:
>
backup
I am not a sql server DBA, so please be gentle.
To se tthe scene. We are using Hardware plication , and point in time copy
technolgoy for backup (EMC SRDF for rpelication, and Business Copies for
Backup). For backup we shut the DB down, split the BC Disk, mount that disk
to a backup server, and then start the SQL DB up.
Bearing this in mind is it possible to roll forward/backup using the logs,
post a restore proceedure? Is it possible to get back to a point in time
during the day when the DB may of corrupted, or lost data? I know I can get
back to the point when the Business Copy was taken, but unsure of the
flexability within SQL 2000. I know this is possible on Oracle DB's.
Disregarding the SAN for a moment:
In SQL Server you basically have a database backup (full backup) and transaction log backups (backup
of changes). When you restore, you do the most recent db backup and then the subsequent log backups-
For a log backup, you have a STOPAT parameter to stop at desired time.
What you need is for your SAN to communicate that a snapshot is taken to SQL Server so that SQL
server considers this a db backup. Because this will allow you to do log backups. So you can now use
the db backup (which really is a snapshot) and then apply the log backups with above mentioned
STOPAT.
Ask your SAN vendor for SQL Server support, and this is most probably supported as a "VDI device"
(SQL Server terminology).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"visaya" <visaya@.discussions.microsoft.com> wrote in message
news:55E2C2D4-509B-429B-9C38-5CDD5D5CB111@.microsoft.com...
>I am not a sql server DBA, so please be gentle.
> To se tthe scene. We are using Hardware plication , and point in time copy
> technolgoy for backup (EMC SRDF for rpelication, and Business Copies for
> Backup). For backup we shut the DB down, split the BC Disk, mount that disk
> to a backup server, and then start the SQL DB up.
> Bearing this in mind is it possible to roll forward/backup using the logs,
> post a restore proceedure? Is it possible to get back to a point in time
> during the day when the DB may of corrupted, or lost data? I know I can get
> back to the point when the Business Copy was taken, but unsure of the
> flexability within SQL 2000. I know this is possible on Oracle DB's.
|||Tibor
Thanks, so just to clarify;
If, using EMC, I use Replciationm Manager (and this does do SQL support), in
a restore I would do dthe following.
Stop the DB
Use my BC, and reverse merge back to the original DATA disk. (please bear in
mind that the data, and logs are on different disk, and BCs)
Use my LOGS BV to replay the logs woth the STOPAT parameter? This would get
me back to a point in time?
"Tibor Karaszi" wrote:
> Disregarding the SAN for a moment:
> In SQL Server you basically have a database backup (full backup) and transaction log backups (backup
> of changes). When you restore, you do the most recent db backup and then the subsequent log backups-
> For a log backup, you have a STOPAT parameter to stop at desired time.
> What you need is for your SAN to communicate that a snapshot is taken to SQL Server so that SQL
> server considers this a db backup. Because this will allow you to do log backups. So you can now use
> the db backup (which really is a snapshot) and then apply the log backups with above mentioned
> STOPAT.
> Ask your SAN vendor for SQL Server support, and this is most probably supported as a "VDI device"
> (SQL Server terminology).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "visaya" <visaya@.discussions.microsoft.com> wrote in message
> news:55E2C2D4-509B-429B-9C38-5CDD5D5CB111@.microsoft.com...
>
>
|||You have to read the documentation with your SAN for how to do restore. Or perhaps someone who have
used those products might jump in here.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"visaya" <visaya@.discussions.microsoft.com> wrote in message
news:EF560F6D-0E77-40D9-AD51-1EB853ABE6BF@.microsoft.com...[vbcol=seagreen]
> Tibor
> Thanks, so just to clarify;
> If, using EMC, I use Replciationm Manager (and this does do SQL support), in
> a restore I would do dthe following.
> Stop the DB
> Use my BC, and reverse merge back to the original DATA disk. (please bear in
> mind that the data, and logs are on different disk, and BCs)
> Use my LOGS BV to replay the logs woth the STOPAT parameter? This would get
> me back to a point in time?
> "Tibor Karaszi" wrote:
To se tthe scene. We are using Hardware plication , and point in time copy
technolgoy for backup (EMC SRDF for rpelication, and Business Copies for
Backup). For backup we shut the DB down, split the BC Disk, mount that disk
to a backup server, and then start the SQL DB up.
Bearing this in mind is it possible to roll forward/backup using the logs,
post a restore proceedure? Is it possible to get back to a point in time
during the day when the DB may of corrupted, or lost data? I know I can get
back to the point when the Business Copy was taken, but unsure of the
flexability within SQL 2000. I know this is possible on Oracle DB's.
Disregarding the SAN for a moment:
In SQL Server you basically have a database backup (full backup) and transaction log backups (backup
of changes). When you restore, you do the most recent db backup and then the subsequent log backups-
For a log backup, you have a STOPAT parameter to stop at desired time.
What you need is for your SAN to communicate that a snapshot is taken to SQL Server so that SQL
server considers this a db backup. Because this will allow you to do log backups. So you can now use
the db backup (which really is a snapshot) and then apply the log backups with above mentioned
STOPAT.
Ask your SAN vendor for SQL Server support, and this is most probably supported as a "VDI device"
(SQL Server terminology).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"visaya" <visaya@.discussions.microsoft.com> wrote in message
news:55E2C2D4-509B-429B-9C38-5CDD5D5CB111@.microsoft.com...
>I am not a sql server DBA, so please be gentle.
> To se tthe scene. We are using Hardware plication , and point in time copy
> technolgoy for backup (EMC SRDF for rpelication, and Business Copies for
> Backup). For backup we shut the DB down, split the BC Disk, mount that disk
> to a backup server, and then start the SQL DB up.
> Bearing this in mind is it possible to roll forward/backup using the logs,
> post a restore proceedure? Is it possible to get back to a point in time
> during the day when the DB may of corrupted, or lost data? I know I can get
> back to the point when the Business Copy was taken, but unsure of the
> flexability within SQL 2000. I know this is possible on Oracle DB's.
|||Tibor
Thanks, so just to clarify;
If, using EMC, I use Replciationm Manager (and this does do SQL support), in
a restore I would do dthe following.
Stop the DB
Use my BC, and reverse merge back to the original DATA disk. (please bear in
mind that the data, and logs are on different disk, and BCs)
Use my LOGS BV to replay the logs woth the STOPAT parameter? This would get
me back to a point in time?
"Tibor Karaszi" wrote:
> Disregarding the SAN for a moment:
> In SQL Server you basically have a database backup (full backup) and transaction log backups (backup
> of changes). When you restore, you do the most recent db backup and then the subsequent log backups-
> For a log backup, you have a STOPAT parameter to stop at desired time.
> What you need is for your SAN to communicate that a snapshot is taken to SQL Server so that SQL
> server considers this a db backup. Because this will allow you to do log backups. So you can now use
> the db backup (which really is a snapshot) and then apply the log backups with above mentioned
> STOPAT.
> Ask your SAN vendor for SQL Server support, and this is most probably supported as a "VDI device"
> (SQL Server terminology).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "visaya" <visaya@.discussions.microsoft.com> wrote in message
> news:55E2C2D4-509B-429B-9C38-5CDD5D5CB111@.microsoft.com...
>
>
|||You have to read the documentation with your SAN for how to do restore. Or perhaps someone who have
used those products might jump in here.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"visaya" <visaya@.discussions.microsoft.com> wrote in message
news:EF560F6D-0E77-40D9-AD51-1EB853ABE6BF@.microsoft.com...[vbcol=seagreen]
> Tibor
> Thanks, so just to clarify;
> If, using EMC, I use Replciationm Manager (and this does do SQL support), in
> a restore I would do dthe following.
> Stop the DB
> Use my BC, and reverse merge back to the original DATA disk. (please bear in
> mind that the data, and logs are on different disk, and BCs)
> Use my LOGS BV to replay the logs woth the STOPAT parameter? This would get
> me back to a point in time?
> "Tibor Karaszi" wrote:
Sunday, February 12, 2012
backup
I am not a sql server DBA, so please be gentle.
To se tthe scene. We are using Hardware plication , and point in time copy
technolgoy for backup (EMC SRDF for rpelication, and Business Copies for
Backup). For backup we shut the DB down, split the BC Disk, mount that disk
to a backup server, and then start the SQL DB up.
Bearing this in mind is it possible to roll forward/backup using the logs,
post a restore proceedure? Is it possible to get back to a point in time
during the day when the DB may of corrupted, or lost data? I know I can get
back to the point when the Business Copy was taken, but unsure of the
flexability within SQL 2000. I know this is possible on Oracle DB's.Disregarding the SAN for a moment:
In SQL Server you basically have a database backup (full backup) and transaction log backups (backup
of changes). When you restore, you do the most recent db backup and then the subsequent log backups-
For a log backup, you have a STOPAT parameter to stop at desired time.
What you need is for your SAN to communicate that a snapshot is taken to SQL Server so that SQL
server considers this a db backup. Because this will allow you to do log backups. So you can now use
the db backup (which really is a snapshot) and then apply the log backups with above mentioned
STOPAT.
Ask your SAN vendor for SQL Server support, and this is most probably supported as a "VDI device"
(SQL Server terminology).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"visaya" <visaya@.discussions.microsoft.com> wrote in message
news:55E2C2D4-509B-429B-9C38-5CDD5D5CB111@.microsoft.com...
>I am not a sql server DBA, so please be gentle.
> To se tthe scene. We are using Hardware plication , and point in time copy
> technolgoy for backup (EMC SRDF for rpelication, and Business Copies for
> Backup). For backup we shut the DB down, split the BC Disk, mount that disk
> to a backup server, and then start the SQL DB up.
> Bearing this in mind is it possible to roll forward/backup using the logs,
> post a restore proceedure? Is it possible to get back to a point in time
> during the day when the DB may of corrupted, or lost data? I know I can get
> back to the point when the Business Copy was taken, but unsure of the
> flexability within SQL 2000. I know this is possible on Oracle DB's.|||Tibor
Thanks, so just to clarify;
If, using EMC, I use Replciationm Manager (and this does do SQL support), in
a restore I would do dthe following.
Stop the DB
Use my BC, and reverse merge back to the original DATA disk. (please bear in
mind that the data, and logs are on different disk, and BCs)
Use my LOGS BV to replay the logs woth the STOPAT parameter? This would get
me back to a point in time?
"Tibor Karaszi" wrote:
> Disregarding the SAN for a moment:
> In SQL Server you basically have a database backup (full backup) and transaction log backups (backup
> of changes). When you restore, you do the most recent db backup and then the subsequent log backups-
> For a log backup, you have a STOPAT parameter to stop at desired time.
> What you need is for your SAN to communicate that a snapshot is taken to SQL Server so that SQL
> server considers this a db backup. Because this will allow you to do log backups. So you can now use
> the db backup (which really is a snapshot) and then apply the log backups with above mentioned
> STOPAT.
> Ask your SAN vendor for SQL Server support, and this is most probably supported as a "VDI device"
> (SQL Server terminology).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "visaya" <visaya@.discussions.microsoft.com> wrote in message
> news:55E2C2D4-509B-429B-9C38-5CDD5D5CB111@.microsoft.com...
> >I am not a sql server DBA, so please be gentle.
> >
> > To se tthe scene. We are using Hardware plication , and point in time copy
> > technolgoy for backup (EMC SRDF for rpelication, and Business Copies for
> > Backup). For backup we shut the DB down, split the BC Disk, mount that disk
> > to a backup server, and then start the SQL DB up.
> >
> > Bearing this in mind is it possible to roll forward/backup using the logs,
> > post a restore proceedure? Is it possible to get back to a point in time
> > during the day when the DB may of corrupted, or lost data? I know I can get
> > back to the point when the Business Copy was taken, but unsure of the
> > flexability within SQL 2000. I know this is possible on Oracle DB's.
>
>|||You have to read the documentation with your SAN for how to do restore. Or perhaps someone who have
used those products might jump in here.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"visaya" <visaya@.discussions.microsoft.com> wrote in message
news:EF560F6D-0E77-40D9-AD51-1EB853ABE6BF@.microsoft.com...
> Tibor
> Thanks, so just to clarify;
> If, using EMC, I use Replciationm Manager (and this does do SQL support), in
> a restore I would do dthe following.
> Stop the DB
> Use my BC, and reverse merge back to the original DATA disk. (please bear in
> mind that the data, and logs are on different disk, and BCs)
> Use my LOGS BV to replay the logs woth the STOPAT parameter? This would get
> me back to a point in time?
> "Tibor Karaszi" wrote:
>> Disregarding the SAN for a moment:
>> In SQL Server you basically have a database backup (full backup) and transaction log backups
>> (backup
>> of changes). When you restore, you do the most recent db backup and then the subsequent log
>> backups-
>> For a log backup, you have a STOPAT parameter to stop at desired time.
>> What you need is for your SAN to communicate that a snapshot is taken to SQL Server so that SQL
>> server considers this a db backup. Because this will allow you to do log backups. So you can now
>> use
>> the db backup (which really is a snapshot) and then apply the log backups with above mentioned
>> STOPAT.
>> Ask your SAN vendor for SQL Server support, and this is most probably supported as a "VDI device"
>> (SQL Server terminology).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "visaya" <visaya@.discussions.microsoft.com> wrote in message
>> news:55E2C2D4-509B-429B-9C38-5CDD5D5CB111@.microsoft.com...
>> >I am not a sql server DBA, so please be gentle.
>> >
>> > To se tthe scene. We are using Hardware plication , and point in time copy
>> > technolgoy for backup (EMC SRDF for rpelication, and Business Copies for
>> > Backup). For backup we shut the DB down, split the BC Disk, mount that disk
>> > to a backup server, and then start the SQL DB up.
>> >
>> > Bearing this in mind is it possible to roll forward/backup using the logs,
>> > post a restore proceedure? Is it possible to get back to a point in time
>> > during the day when the DB may of corrupted, or lost data? I know I can get
>> > back to the point when the Business Copy was taken, but unsure of the
>> > flexability within SQL 2000. I know this is possible on Oracle DB's.
>>
To se tthe scene. We are using Hardware plication , and point in time copy
technolgoy for backup (EMC SRDF for rpelication, and Business Copies for
Backup). For backup we shut the DB down, split the BC Disk, mount that disk
to a backup server, and then start the SQL DB up.
Bearing this in mind is it possible to roll forward/backup using the logs,
post a restore proceedure? Is it possible to get back to a point in time
during the day when the DB may of corrupted, or lost data? I know I can get
back to the point when the Business Copy was taken, but unsure of the
flexability within SQL 2000. I know this is possible on Oracle DB's.Disregarding the SAN for a moment:
In SQL Server you basically have a database backup (full backup) and transaction log backups (backup
of changes). When you restore, you do the most recent db backup and then the subsequent log backups-
For a log backup, you have a STOPAT parameter to stop at desired time.
What you need is for your SAN to communicate that a snapshot is taken to SQL Server so that SQL
server considers this a db backup. Because this will allow you to do log backups. So you can now use
the db backup (which really is a snapshot) and then apply the log backups with above mentioned
STOPAT.
Ask your SAN vendor for SQL Server support, and this is most probably supported as a "VDI device"
(SQL Server terminology).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"visaya" <visaya@.discussions.microsoft.com> wrote in message
news:55E2C2D4-509B-429B-9C38-5CDD5D5CB111@.microsoft.com...
>I am not a sql server DBA, so please be gentle.
> To se tthe scene. We are using Hardware plication , and point in time copy
> technolgoy for backup (EMC SRDF for rpelication, and Business Copies for
> Backup). For backup we shut the DB down, split the BC Disk, mount that disk
> to a backup server, and then start the SQL DB up.
> Bearing this in mind is it possible to roll forward/backup using the logs,
> post a restore proceedure? Is it possible to get back to a point in time
> during the day when the DB may of corrupted, or lost data? I know I can get
> back to the point when the Business Copy was taken, but unsure of the
> flexability within SQL 2000. I know this is possible on Oracle DB's.|||Tibor
Thanks, so just to clarify;
If, using EMC, I use Replciationm Manager (and this does do SQL support), in
a restore I would do dthe following.
Stop the DB
Use my BC, and reverse merge back to the original DATA disk. (please bear in
mind that the data, and logs are on different disk, and BCs)
Use my LOGS BV to replay the logs woth the STOPAT parameter? This would get
me back to a point in time?
"Tibor Karaszi" wrote:
> Disregarding the SAN for a moment:
> In SQL Server you basically have a database backup (full backup) and transaction log backups (backup
> of changes). When you restore, you do the most recent db backup and then the subsequent log backups-
> For a log backup, you have a STOPAT parameter to stop at desired time.
> What you need is for your SAN to communicate that a snapshot is taken to SQL Server so that SQL
> server considers this a db backup. Because this will allow you to do log backups. So you can now use
> the db backup (which really is a snapshot) and then apply the log backups with above mentioned
> STOPAT.
> Ask your SAN vendor for SQL Server support, and this is most probably supported as a "VDI device"
> (SQL Server terminology).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "visaya" <visaya@.discussions.microsoft.com> wrote in message
> news:55E2C2D4-509B-429B-9C38-5CDD5D5CB111@.microsoft.com...
> >I am not a sql server DBA, so please be gentle.
> >
> > To se tthe scene. We are using Hardware plication , and point in time copy
> > technolgoy for backup (EMC SRDF for rpelication, and Business Copies for
> > Backup). For backup we shut the DB down, split the BC Disk, mount that disk
> > to a backup server, and then start the SQL DB up.
> >
> > Bearing this in mind is it possible to roll forward/backup using the logs,
> > post a restore proceedure? Is it possible to get back to a point in time
> > during the day when the DB may of corrupted, or lost data? I know I can get
> > back to the point when the Business Copy was taken, but unsure of the
> > flexability within SQL 2000. I know this is possible on Oracle DB's.
>
>|||You have to read the documentation with your SAN for how to do restore. Or perhaps someone who have
used those products might jump in here.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"visaya" <visaya@.discussions.microsoft.com> wrote in message
news:EF560F6D-0E77-40D9-AD51-1EB853ABE6BF@.microsoft.com...
> Tibor
> Thanks, so just to clarify;
> If, using EMC, I use Replciationm Manager (and this does do SQL support), in
> a restore I would do dthe following.
> Stop the DB
> Use my BC, and reverse merge back to the original DATA disk. (please bear in
> mind that the data, and logs are on different disk, and BCs)
> Use my LOGS BV to replay the logs woth the STOPAT parameter? This would get
> me back to a point in time?
> "Tibor Karaszi" wrote:
>> Disregarding the SAN for a moment:
>> In SQL Server you basically have a database backup (full backup) and transaction log backups
>> (backup
>> of changes). When you restore, you do the most recent db backup and then the subsequent log
>> backups-
>> For a log backup, you have a STOPAT parameter to stop at desired time.
>> What you need is for your SAN to communicate that a snapshot is taken to SQL Server so that SQL
>> server considers this a db backup. Because this will allow you to do log backups. So you can now
>> use
>> the db backup (which really is a snapshot) and then apply the log backups with above mentioned
>> STOPAT.
>> Ask your SAN vendor for SQL Server support, and this is most probably supported as a "VDI device"
>> (SQL Server terminology).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "visaya" <visaya@.discussions.microsoft.com> wrote in message
>> news:55E2C2D4-509B-429B-9C38-5CDD5D5CB111@.microsoft.com...
>> >I am not a sql server DBA, so please be gentle.
>> >
>> > To se tthe scene. We are using Hardware plication , and point in time copy
>> > technolgoy for backup (EMC SRDF for rpelication, and Business Copies for
>> > Backup). For backup we shut the DB down, split the BC Disk, mount that disk
>> > to a backup server, and then start the SQL DB up.
>> >
>> > Bearing this in mind is it possible to roll forward/backup using the logs,
>> > post a restore proceedure? Is it possible to get back to a point in time
>> > during the day when the DB may of corrupted, or lost data? I know I can get
>> > back to the point when the Business Copy was taken, but unsure of the
>> > flexability within SQL 2000. I know this is possible on Oracle DB's.
>>
Friday, February 10, 2012
Backing Up System Databases...
I am rather new to the area of performing DBA type activities (so don't be
too hard on me).
When I create a maintenance plan, which of the system databases should I
choose to back up (if it is not necessary or recommended to back all of them
up)?
Thank you,
Jason
Both master and msdb should be done daily. You could do model every once in
a while. Generally, it doesn't change - unless you choose to add an object
that you want to appear in all future DB's.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jason Richmeier" <JasonRichmeier@.discussions.microsoft.com> wrote in
message news:179C142F-CEBC-4E16-870D-FCC20881E5F3@.microsoft.com...
I am rather new to the area of performing DBA type activities (so don't be
too hard on me).
When I create a maintenance plan, which of the system databases should I
choose to back up (if it is not necessary or recommended to back all of them
up)?
Thank you,
Jason
|||Or just include model for daily backup anyway since it's so tiny.
Linchi
"Tom Moreau" wrote:
> Both master and msdb should be done daily. You could do model every once in
> a while. Generally, it doesn't change - unless you choose to add an object
> that you want to appear in all future DB's.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Jason Richmeier" <JasonRichmeier@.discussions.microsoft.com> wrote in
> message news:179C142F-CEBC-4E16-870D-FCC20881E5F3@.microsoft.com...
> I am rather new to the area of performing DBA type activities (so don't be
> too hard on me).
> When I create a maintenance plan, which of the system databases should I
> choose to back up (if it is not necessary or recommended to back all of them
> up)?
> Thank you,
> Jason
>
too hard on me).
When I create a maintenance plan, which of the system databases should I
choose to back up (if it is not necessary or recommended to back all of them
up)?
Thank you,
Jason
Both master and msdb should be done daily. You could do model every once in
a while. Generally, it doesn't change - unless you choose to add an object
that you want to appear in all future DB's.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jason Richmeier" <JasonRichmeier@.discussions.microsoft.com> wrote in
message news:179C142F-CEBC-4E16-870D-FCC20881E5F3@.microsoft.com...
I am rather new to the area of performing DBA type activities (so don't be
too hard on me).
When I create a maintenance plan, which of the system databases should I
choose to back up (if it is not necessary or recommended to back all of them
up)?
Thank you,
Jason
|||Or just include model for daily backup anyway since it's so tiny.
Linchi
"Tom Moreau" wrote:
> Both master and msdb should be done daily. You could do model every once in
> a while. Generally, it doesn't change - unless you choose to add an object
> that you want to appear in all future DB's.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Jason Richmeier" <JasonRichmeier@.discussions.microsoft.com> wrote in
> message news:179C142F-CEBC-4E16-870D-FCC20881E5F3@.microsoft.com...
> I am rather new to the area of performing DBA type activities (so don't be
> too hard on me).
> When I create a maintenance plan, which of the system databases should I
> choose to back up (if it is not necessary or recommended to back all of them
> up)?
> Thank you,
> Jason
>
Subscribe to:
Posts (Atom)