Backup to Network Drive: backing

Showing posts with label backing. Show all posts

Thursday, March 8, 2012

Backup-to-file SQL2k Question/NT Tape Backups

Dear All,
Hello!
Now that I am running a maintenance backup-to-file from
within SQL2K, should I just be backing up these
backup-to-file to an NT backup job? Or should I also be
backing up the live databases & their transact logs?
Thanks!
CarlosNormal practice would be to backup the backup files from SQL to tape. If you
are taking regular SQL backups, there is no need to backup the actual
database files themselves (which would require stopping SQL anyway as they
are always in use)
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Carlos" <anonymous@.discussions.microsoft.com> wrote in message
news:10ef101c3f49b$fb627db0$a001280a@.phx.gbl...
> Dear All,
> Hello!
> Now that I am running a maintenance backup-to-file from
> within SQL2K, should I just be backing up these
> backup-to-file to an NT backup job? Or should I also be
> backing up the live databases & their transact logs?
> Thanks!
> Carlos
>|||You don't have to backup the mdf, ndf and ldf files if you let SQL Server do
database and possibly transaction log backup to disk and then backup those
files to tape.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Carlos" <anonymous@.discussions.microsoft.com> wrote in message
news:10ef101c3f49b$fb627db0$a001280a@.phx.gbl...
> Dear All,
> Hello!
> Now that I am running a maintenance backup-to-file from
> within SQL2K, should I just be backing up these
> backup-to-file to an NT backup job? Or should I also be
> backing up the live databases & their transact logs?
> Thanks!
> Carlos
>

Backup-to-file SQL2k Question/NT Tape Backups

Dear All,
Hello!
Now that I am running a maintenance backup-to-file from
within SQL2K, should I just be backing up these
backup-to-file to an NT backup job? Or should I also be
backing up the live databases & their transact logs?
Thanks!
CarlosNormal practice would be to backup the backup files from SQL to tape. If you
are taking regular SQL backups, there is no need to backup the actual
database files themselves (which would require stopping SQL anyway as they
are always in use)
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Carlos" <anonymous@.discussions.microsoft.com> wrote in message
news:10ef101c3f49b$fb627db0$a001280a@.phx
.gbl...
> Dear All,
> Hello!
> Now that I am running a maintenance backup-to-file from
> within SQL2K, should I just be backing up these
> backup-to-file to an NT backup job? Or should I also be
> backing up the live databases & their transact logs?
> Thanks!
> Carlos
>|||You don't have to backup the mdf, ndf and ldf files if you let SQL Server do
database and possibly transaction log backup to disk and then backup those
files to tape.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Carlos" <anonymous@.discussions.microsoft.com> wrote in message
news:10ef101c3f49b$fb627db0$a001280a@.phx
.gbl...
> Dear All,
> Hello!
> Now that I am running a maintenance backup-to-file from
> within SQL2K, should I just be backing up these
> backup-to-file to an NT backup job? Or should I also be
> backing up the live databases & their transact logs?
> Thanks!
> Carlos
>

Backups to network share fail

I am having an odd failure when backing up my SQL 2000 SP3 server to a
network share. I receive the following error:
BackupDiskFile::CreateMedia: Backup device '\\server\share\master.BAK'
failed to create. Operating system error = 5(Access is denied.).
However, the following T-SQL command does run and create the appropriate
file in the same network folder:
EXEC xp_cmdshell 'dir \\server\share > \\server\share\test.txt'
Both the SQL Server and SQL Server Agent is running under a domain user
account which has the required permissions to this share. Other SQL Servers
(SP3 and SP4) which also run under the same domain user account are backing
up to the same share without any errors.
Any insight into resolving this issue would be appreciated.HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Richard Cote" <RichardCote@.discussions.microsoft.com> wrote in message
news:3BA4F72D-E6E3-434A-BDE1-95BC990A3B66@.microsoft.com...
>I am having an odd failure when backing up my SQL 2000 SP3 server to a
> network share. I receive the following error:
> BackupDiskFile::CreateMedia: Backup device '\\server\share\master.BAK'
> failed to create. Operating system error = 5(Access is denied.).
> However, the following T-SQL command does run and create the appropriate
> file in the same network folder:
> EXEC xp_cmdshell 'dir \\server\share > \\server\share\test.txt'
> Both the SQL Server and SQL Server Agent is running under a domain user
> account which has the required permissions to this share. Other SQL
> Servers
> (SP3 and SP4) which also run under the same domain user account are
> backing
> up to the same share without any errors.
> Any insight into resolving this issue would be appreciated.
>|||The maintenance plan is properly configured to backup to the UNC name - in
the exact same manner as all the other servers that are working properly.
"Geoff N. Hiten" wrote:
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?kbid=555128
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Richard Cote" <RichardCote@.discussions.microsoft.com> wrote in message
> news:3BA4F72D-E6E3-434A-BDE1-95BC990A3B66@.microsoft.com...
> >I am having an odd failure when backing up my SQL 2000 SP3 server to a
> > network share. I receive the following error:
> >
> > BackupDiskFile::CreateMedia: Backup device '\\server\share\master.BAK'
> > failed to create. Operating system error = 5(Access is denied.).
> >
> > However, the following T-SQL command does run and create the appropriate
> > file in the same network folder:
> >
> > EXEC xp_cmdshell 'dir \\server\share > \\server\share\test.txt'
> >
> > Both the SQL Server and SQL Server Agent is running under a domain user
> > account which has the required permissions to this share. Other SQL
> > Servers
> > (SP3 and SP4) which also run under the same domain user account are
> > backing
> > up to the same share without any errors.
> >
> > Any insight into resolving this issue would be appreciated.
> >
>|||I have found in those cases that it is usually either a name resolution
issue or a permissions issue.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Richard Cote" <RichardCote@.discussions.microsoft.com> wrote in message
news:53C105CD-2656-4A02-A708-4E6C43F4077F@.microsoft.com...
> The maintenance plan is properly configured to backup to the UNC name - in
> the exact same manner as all the other servers that are working properly.
> "Geoff N. Hiten" wrote:
>> HowTo: Backup to UNC name using Database Maintenance Wizard
>> http://support.microsoft.com/?kbid=555128
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>> "Richard Cote" <RichardCote@.discussions.microsoft.com> wrote in message
>> news:3BA4F72D-E6E3-434A-BDE1-95BC990A3B66@.microsoft.com...
>> >I am having an odd failure when backing up my SQL 2000 SP3 server to a
>> > network share. I receive the following error:
>> >
>> > BackupDiskFile::CreateMedia: Backup device '\\server\share\master.BAK'
>> > failed to create. Operating system error = 5(Access is denied.).
>> >
>> > However, the following T-SQL command does run and create the
>> > appropriate
>> > file in the same network folder:
>> >
>> > EXEC xp_cmdshell 'dir \\server\share > \\server\share\test.txt'
>> >
>> > Both the SQL Server and SQL Server Agent is running under a domain user
>> > account which has the required permissions to this share. Other SQL
>> > Servers
>> > (SP3 and SP4) which also run under the same domain user account are
>> > backing
>> > up to the same share without any errors.
>> >
>> > Any insight into resolving this issue would be appreciated.
>> >
>>|||Login using the SQLAgent Login and Try the same "EXEC xp_cmdshell 'dir
\\server\share > \\server\share\test.txt' "
On Apr 10, 4:16 pm, Richard Cote
<RichardC...@.discussions.microsoft.com> wrote:
> The maintenance plan is properly configured to backup to the UNC name - in
> the exact same manner as all the other servers that are working properly.
>
> "Geoff N. Hiten" wrote:
> > HowTo: Backup to UNC name using Database Maintenance Wizard
> >http://support.microsoft.com/?kbid=555128
> > --
> > Geoff N. Hiten
> > Senior Database Administrator
> > Microsoft SQL Server MVP
> > "Richard Cote" <RichardC...@.discussions.microsoft.com> wrote in message
> >news:3BA4F72D-E6E3-434A-BDE1-95BC990A3B66@.microsoft.com...
> > >I am having an odd failure when backing up my SQL 2000 SP3 server to a
> > > network share. I receive the following error:
> > > BackupDiskFile::CreateMedia: Backup device '\\server\share\master.BAK'
> > > failed to create. Operating system error = 5(Access is denied.).
> > > However, the following T-SQL command does run and create the appropriate
> > > file in the same network folder:
> > > EXEC xp_cmdshell 'dir \\server\share > \\server\share\test.txt'
> > > Both the SQL Server and SQL Server Agent is running under a domain user
> > > account which has the required permissions to this share. Other SQL
> > > Servers
> > > (SP3 and SP4) which also run under the same domain user account are
> > > backing
> > > up to the same share without any errors.
> > > Any insight into resolving this issue would be appreciated.- Hide quoted text -
> - Show quoted text -|||The xp_cmdshell command works for any sysadmin user and the SQL Server and
SQL Server Agent logins. However, backups still fail to the same network
share.
This problem seems to be isolated to this one server, and is very
perplexing. Turning on auditing on the file share does not reveal any audit
failures. Choosing a different share with full file and share permissions
granted to Everyone also results in the same access denied error.
"schal" wrote:
> Login using the SQLAgent Login and Try the same "EXEC xp_cmdshell 'dir
> \\server\share > \\server\share\test.txt' "
>
> On Apr 10, 4:16 pm, Richard Cote
> <RichardC...@.discussions.microsoft.com> wrote:
> > The maintenance plan is properly configured to backup to the UNC name - in
> > the exact same manner as all the other servers that are working properly.
> >
> >
> >
> > "Geoff N. Hiten" wrote:
> > > HowTo: Backup to UNC name using Database Maintenance Wizard
> > >http://support.microsoft.com/?kbid=555128
> >
> > > --
> > > Geoff N. Hiten
> > > Senior Database Administrator
> > > Microsoft SQL Server MVP
> >
> > > "Richard Cote" <RichardC...@.discussions.microsoft.com> wrote in message
> > >news:3BA4F72D-E6E3-434A-BDE1-95BC990A3B66@.microsoft.com...
> > > >I am having an odd failure when backing up my SQL 2000 SP3 server to a
> > > > network share. I receive the following error:
> >
> > > > BackupDiskFile::CreateMedia: Backup device '\\server\share\master.BAK'
> > > > failed to create. Operating system error = 5(Access is denied.).
> >
> > > > However, the following T-SQL command does run and create the appropriate
> > > > file in the same network folder:
> >
> > > > EXEC xp_cmdshell 'dir \\server\share > \\server\share\test.txt'
> >
> > > > Both the SQL Server and SQL Server Agent is running under a domain user
> > > > account which has the required permissions to this share. Other SQL
> > > > Servers
> > > > (SP3 and SP4) which also run under the same domain user account are
> > > > backing
> > > > up to the same share without any errors.
> >
> > > > Any insight into resolving this issue would be appreciated.- Hide quoted text -
> >
> > - Show quoted text -
>
>

Backups to network share fail

Wednesday, March 7, 2012

BackUps not deleting

I am backing up databases on SQL Server 2000 using a Database BackUp Plan. I
have set it to delete files older than 1 day. This used to work, but now just
backs up without deleting the old files.
Any help would be appreciated,
Craig
Below KB might help:
http://support.microsoft.com/default...&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:B509ECB9-DAE6-49CD-8CD4-4A9F4905DA23@.microsoft.com...
>I am backing up databases on SQL Server 2000 using a Database BackUp Plan. I
> have set it to delete files older than 1 day. This used to work, but now just
> backs up without deleting the old files.
> Any help would be appreciated,
> Craig
|||I also have the same problem. Our maintenance plan was working however now it
doesnt.
The Service Account has full control to the backup location and nothing is
running at the same time as the backup. Any ideas?
Andrew
"Tibor Karaszi" wrote:

> Below KB might help:
> http://support.microsoft.com/default...&Product=sql2k
>
> Also, check out below great troubleshooting suggestions from Bill H at MS:
>
> -- Log files don't delete --
> This is likely to be either a permissions problem or a sharing violation
> problem. The maintenance plan is run as a job, and jobs are run by the
> SQLServerAgent service.
> Permissions:
> 1. Determine the startup account for the SQLServerAgent service
> (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
> account is the security context for jobs, and thus the maintenance plan.
> 2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
> account) then skip step 3.
> 3. On that box, log onto NT as that account. Using Explorer, attempt to
> delete an expired backup. If that succeeds then go to Sharing Violation
> section.
> 4. Log onto NT with an account that is an administrator and use Explorer to
> look at the Properties|Security of the folder (where the backups reside)
> and ensure the SQLServerAgent startup account has Full Control. If the
> SQLServerAgent startup account is LocalSystem, then the account to consider
> is SYSTEM.
> 5. In NT, if an account is a member of an NT group, and if that group has
> Access is Denied, then that account will have Access is Denied, even if
> that account is also a member of the Administrators group. Thus you may
> need to check group permissions (if the Startup Account is a member of a
> group).
> 6. Keep in mind that permissions (by default) are inherited from a parent
> folder. Thus, if the backups are stored in C:\bak, and if someone had
> denied permission to the SQLServerAgent startup account for C:\, then
> C:\bak will inherit access is denied.
> Sharing violation:
> This is likely to be rooted in a timing issue, with the most likely cause
> being another scheduled process (such as NT Backup or Anti-Virus software)
> having the backup file open at the time when the SQLServerAgent (i.e., the
> maintenance plan job) tried to delete it.
> 1. Download filemon and handle from www.sysinternals.com.
> 2. I am not sure whether filemon can be scheduled, or you might be able to
> use NT scheduling services to start filemon just before the maintenance
> plan job is started, but the filemon log can become very large, so it would
> be best to start it some short time before the maintenance plan starts.
> 3. Inspect the filemon log for another process that has that backup file
> open (if your lucky enough to have started filemon before this other
> process grabs the backup folder), and inspect the log for the results when
> the SQLServerAgent agent attempts to open that same file.
> 4. Schedule the job or that other process to do their work at different
> times.
> 5. You can use the handle utility if you are around at the time when the
> job is scheduled to run.
> If the backup files are going to a \\share or a mapped drive (as opposed to
> local drive), then you will need to modify the above (with respect to where
> the tests and utilities are run).
> Finally, inspection of the maintenance plan's history report might be
> useful.
> Thanks,
> Bill Hollinshead
> Microsoft, SQL Server
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
> news:B509ECB9-DAE6-49CD-8CD4-4A9F4905DA23@.microsoft.com...
>
>

BackUps not deleting

I am backing up databases on SQL Server 2000 using a database maintenance
plan. I have set it to remove files older than 1 day (for both the database
and transaction log backups). This used to delete the old backup files, but
now it just backs up, without deleting anything.
Any help would be appreciated,
CraigSorry, I posted this in the wrong place. I have moved it to sqlserver.server
http://msdn.microsoft.com/newsgroup...r&lang=en&cr=US
"Craig HB" wrote:

> I am backing up databases on SQL Server 2000 using a database maintenance
> plan. I have set it to remove files older than 1 day (for both the databas
e
> and transaction log backups). This used to delete the old backup files, bu
t
> now it just backs up, without deleting anything.
> Any help would be appreciated,
> Craig

BackUps not deleting

I am backing up databases on SQL Server 2000 using a Database BackUp Plan. I
have set it to delete files older than 1 day. This used to work, but now jus
t
backs up without deleting the old files.
Any help would be appreciated,
CraigBelow KB might help:
http://support.microsoft.com/defaul...2&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:B509ECB9-DAE6-49CD-8CD4-4A9F4905DA23@.microsoft.com...
>I am backing up databases on SQL Server 2000 using a Database BackUp Plan.
I
> have set it to delete files older than 1 day. This used to work, but now j
ust
> backs up without deleting the old files.
> Any help would be appreciated,
> Craig|||I also have the same problem. Our maintenance plan was working however now i
t
doesnt.
The Service Account has full control to the backup location and nothing is
running at the same time as the backup. Any ideas?
Andrew
"Tibor Karaszi" wrote:

> Below KB might help:
> http://support.microsoft.com/defaul...sinternals.com.
> 2. I am not sure whether filemon can be scheduled, or you might be able to
> use NT scheduling services to start filemon just before the maintenance
> plan job is started, but the filemon log can become very large, so it woul
d
> be best to start it some short time before the maintenance plan starts.
> 3. Inspect the filemon log for another process that has that backup file
> open (if your lucky enough to have started filemon before this other
> process grabs the backup folder), and inspect the log for the results when
> the SQLServerAgent agent attempts to open that same file.
> 4. Schedule the job or that other process to do their work at different
> times.
> 5. You can use the handle utility if you are around at the time when the
> job is scheduled to run.
> If the backup files are going to a \\share or a mapped drive (as opposed t
o
> local drive), then you will need to modify the above (with respect to wher
e
> the tests and utilities are run).
> Finally, inspection of the maintenance plan's history report might be
> useful.
> Thanks,
> Bill Hollinshead
> Microsoft, SQL Server
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
> news:B509ECB9-DAE6-49CD-8CD4-4A9F4905DA23@.microsoft.com...
>
>

BackUps not deleting

I am backing up databases on SQL Server 2000 using a Database BackUp Plan. I
have set it to delete files older than 1 day. This used to work, but now just
backs up without deleting the old files.
Any help would be appreciated,
CraigBelow KB might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:B509ECB9-DAE6-49CD-8CD4-4A9F4905DA23@.microsoft.com...
>I am backing up databases on SQL Server 2000 using a Database BackUp Plan. I
> have set it to delete files older than 1 day. This used to work, but now just
> backs up without deleting the old files.
> Any help would be appreciated,
> Craig|||I also have the same problem. Our maintenance plan was working however now it
doesnt.
The Service Account has full control to the backup location and nothing is
running at the same time as the backup. Any ideas?
Andrew
"Tibor Karaszi" wrote:
> Below KB might help:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
>
> Also, check out below great troubleshooting suggestions from Bill H at MS:
>
> -- Log files don't delete --
> This is likely to be either a permissions problem or a sharing violation
> problem. The maintenance plan is run as a job, and jobs are run by the
> SQLServerAgent service.
> Permissions:
> 1. Determine the startup account for the SQLServerAgent service
> (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
> account is the security context for jobs, and thus the maintenance plan.
> 2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
> account) then skip step 3.
> 3. On that box, log onto NT as that account. Using Explorer, attempt to
> delete an expired backup. If that succeeds then go to Sharing Violation
> section.
> 4. Log onto NT with an account that is an administrator and use Explorer to
> look at the Properties|Security of the folder (where the backups reside)
> and ensure the SQLServerAgent startup account has Full Control. If the
> SQLServerAgent startup account is LocalSystem, then the account to consider
> is SYSTEM.
> 5. In NT, if an account is a member of an NT group, and if that group has
> Access is Denied, then that account will have Access is Denied, even if
> that account is also a member of the Administrators group. Thus you may
> need to check group permissions (if the Startup Account is a member of a
> group).
> 6. Keep in mind that permissions (by default) are inherited from a parent
> folder. Thus, if the backups are stored in C:\bak, and if someone had
> denied permission to the SQLServerAgent startup account for C:\, then
> C:\bak will inherit access is denied.
> Sharing violation:
> This is likely to be rooted in a timing issue, with the most likely cause
> being another scheduled process (such as NT Backup or Anti-Virus software)
> having the backup file open at the time when the SQLServerAgent (i.e., the
> maintenance plan job) tried to delete it.
> 1. Download filemon and handle from www.sysinternals.com.
> 2. I am not sure whether filemon can be scheduled, or you might be able to
> use NT scheduling services to start filemon just before the maintenance
> plan job is started, but the filemon log can become very large, so it would
> be best to start it some short time before the maintenance plan starts.
> 3. Inspect the filemon log for another process that has that backup file
> open (if your lucky enough to have started filemon before this other
> process grabs the backup folder), and inspect the log for the results when
> the SQLServerAgent agent attempts to open that same file.
> 4. Schedule the job or that other process to do their work at different
> times.
> 5. You can use the handle utility if you are around at the time when the
> job is scheduled to run.
> If the backup files are going to a \\share or a mapped drive (as opposed to
> local drive), then you will need to modify the above (with respect to where
> the tests and utilities are run).
> Finally, inspection of the maintenance plan's history report might be
> useful.
> Thanks,
> Bill Hollinshead
> Microsoft, SQL Server
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
> news:B509ECB9-DAE6-49CD-8CD4-4A9F4905DA23@.microsoft.com...
> >I am backing up databases on SQL Server 2000 using a Database BackUp Plan. I
> > have set it to delete files older than 1 day. This used to work, but now just
> > backs up without deleting the old files.
> >
> > Any help would be appreciated,
> > Craig
>
>

Backups Best Practices

It looks like I have been presented with 2 options for backing up to tape.
1. Use veritas SQL Agent and back up the full every <blank> days and logs ev
ery <blank> days
2. Backup using native SQL agent to disk and using veritas to backup the BAK
files to tape every night.
Anyone have an opinion on which is best and why?I prefer native and than pick up the files to tape. Just be aware that you
have 24 hours potential loss of data of the local backup files are lost
before they are backed up to tape. I generally let the SQL backup copy the
files to another machine directly after the backup is takes, when possible.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"mannie" <anonymous@.discussions.microsoft.com> wrote in message
news:A03011F7-A2F7-46F9-9E10-40EE2A695832@.microsoft.com...
> It looks like I have been presented with 2 options for backing up to tape.
> 1. Use veritas SQL Agent and back up the full every <blank> days and logs
every <blank> days
> 2. Backup using native SQL agent to disk and using veritas to backup the
BAK files to tape every night.
> Anyone have an opinion on which is best and why?|||I agree with Tibor. I prefer to not have to deal with the tape agents if I
can avoid it.
Andrew J. Kelly
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u9hpqvW9DHA.1504@.TK2MSFTNGP12.phx.gbl...
> I prefer native and than pick up the files to tape. Just be aware that you
> have 24 hours potential loss of data of the local backup files are lost
> before they are backed up to tape. I generally let the SQL backup copy the
> files to another machine directly after the backup is takes, when
possible.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
>
> "mannie" <anonymous@.discussions.microsoft.com> wrote in message
> news:A03011F7-A2F7-46F9-9E10-40EE2A695832@.microsoft.com...
tape.
logs
> every <blank> days
the
> BAK files to tape every night.
>|||Thanks for your input..
What is your reason for this preference?
Speed?
You are more comfortable with SQL native agent?
Reliability?
Frequency of backups required?
Are you trying to save I/O over the backup next work?
Any more areas you have to add to this list of things to consider?|||I agree with Tibor, except that I prefer to backup directly to the remote
file system using a unc name, so I don't to coordinate the file
copies...(although the backup itself will run slower and eat network
bandwidth.)
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"mannie" <anonymous@.discussions.microsoft.com> wrote in message
news:A03011F7-A2F7-46F9-9E10-40EE2A695832@.microsoft.com...
> It looks like I have been presented with 2 options for backing up to tape.
> 1. Use veritas SQL Agent and back up the full every <blank> days and logs
every <blank> days
> 2. Backup using native SQL agent to disk and using veritas to backup the
BAK files to tape every night.
> Anyone have an opinion on which is best and why?|||For me it is simple: I prefer to not have my SQL Server data in the hands on
some 3:rd party vendor. Backup from SQL Server has been around for ages and
we all know that it work and how it work... :-)
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"mannie" <anonymous@.discussions.microsoft.com> wrote in message
news:E26131EA-B631-4482-A033-B6AD4215D0B9@.microsoft.com...
> Thanks for your input..
> What is your reason for this preference?
> Speed?
> You are more comfortable with SQL native agent?
> Reliability?
> Frequency of backups required?
> Are you trying to save I/O over the backup next work?
> Any more areas you have to add to this list of things to consider?|||I am with Wayne and Tibor on this one. SQL backups (using SQLLiteSpeed for
the really big databases) to a UNC share on another machine. I then have
daily, weekly and monthly rotations to tape with the monthly tapes removed
and archived.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:%23kaRt6W9DHA.2308@.TK2MSFTNGP11.phx.gbl...
> I agree with Tibor, except that I prefer to backup directly to the remote
> file system using a unc name, so I don't to coordinate the file
> copies...(although the backup itself will run slower and eat network
> bandwidth.)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "mannie" <anonymous@.discussions.microsoft.com> wrote in message
> news:A03011F7-A2F7-46F9-9E10-40EE2A695832@.microsoft.com...
tape.
logs
> every <blank> days
the
> BAK files to tape every night.
>|||If you have a copy of the backup on a local machine (by local meaning
accessible by UNC) you can restore in the quickest possible time where as
with tape it may be a while to get the tape loaded etc.
Andrew J. Kelly
SQL Server MVP
"mannie" <anonymous@.discussions.microsoft.com> wrote in message
news:E26131EA-B631-4482-A033-B6AD4215D0B9@.microsoft.com...
> Thanks for your input..
> What is your reason for this preference?
> Speed?
> You are more comfortable with SQL native agent?
> Reliability?
> Frequency of backups required?
> Are you trying to save I/O over the backup next work?
> Any more areas you have to add to this list of things to consider?|||Also, some of the tape software components don't support all the backup and
restore options. Especially the WITH MOVE option specifying where each file
gets placed. This is especially important with very large databases where
you will have to spread the data out on multiple devices but you don't want
to overwrite the original database.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23Rhsx0X9DHA.2696@.TK2MSFTNGP10.phx.gbl...
> If you have a copy of the backup on a local machine (by local meaning
> accessible by UNC) you can restore in the quickest possible time where as
> with tape it may be a while to get the tape loaded etc.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "mannie" <anonymous@.discussions.microsoft.com> wrote in message
> news:E26131EA-B631-4482-A033-B6AD4215D0B9@.microsoft.com...
>|||we are getting ready to buy sql lite speed too
testing the demo for backups is impressive wtih decrease file size and speed
restores seem to take the same amount of time as with native sql versus the
VDI Sqllitespeed
is this what other are seeing
we routinely backup DBs in the 100-250 gig range and restore them on another
server for analytical use
experimenting with ways for smallest over time window for this
things like backup to local SAN -- restore to other server across network
back to UNC path on other server -- restore from local SAN
Trying to copy such large files are a network in windows is too slow -- it
is faster to just backup in SQL and then restore in SQL or move the file
with a tape library
We soon may have a disked based backup system to try DX30 from quantum
comments on getting shortest back and restore windows
example -- a large DB we have create a 170gig backup file the backup restore
rebuild indexes on this puppy takes 15-20 hours -- we do this once a month
as that is the refresh for new data loads -- new data is about 4-6 gig a
month
"Geoff N.Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:%23nQaoqX9DHA.2832@.tk2msftngp13.phx.gbl...
> I am with Wayne and Tibor on this one. SQL backups (using SQLLiteSpeed
for
> the really big databases) to a UNC share on another machine. I then have
> daily, weekly and monthly rotations to tape with the monthly tapes removed
> and archived.
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
> news:%23kaRt6W9DHA.2308@.TK2MSFTNGP11.phx.gbl...
remote
> tape.
> logs
> the
>

backups and restore

I am backing up SQL databases two ways, once through the Enterprise Manager
and also with Veritas. I need to do a test restore on a database and am
uncertain of the best way to go about it. Is it easier to restore from
Veritas or SQL backups? How do I do it without overwriting the existing
database? Any insights or help would be greatly appreciated.
Here is some info:
Windows 2003 Server
SQL 2000
Veritas 9.1
Thanks"NOBI" schrieb:
> I am backing up SQL databases two ways, once through the Enterprise Manage
r
> and also with Veritas. I need to do a test restore on a database and am
> uncertain of the best way to go about it. Is it easier to restore from
> Veritas or SQL backups? How do I do it without overwriting the existing
> database? Any insights or help would be greatly appreciated.
> Here is some info:
> Windows 2003 Server
> SQL 2000
> Veritas 9.1
> Thanks
During the restore process you can define the name of the restored database.
There is no problem with overwriting the existing one (if you just choose a
different name). Don't forget to use the 'move ... to ...' option to give th
e
files a different location from the original!|||Hi Nobi,
You can use the RESTORE DATABASE command from query analyzer to restore the
database with a new name.
STEPS:-
1. Identify the logical name associated with MDf AND LDF using RESTORE
FILELISTONLY (see books online)
2. Use the RESTORE DATABASE command with MOVE options (see books online) to
create and restore the backupfile with new
database name. In the MOVE option for the physical name give a new name.
Thanks
Hari
SQL Server MVP
"NOBI" <NOBI@.discussions.microsoft.com> wrote in message
news:8AAA6DF0-3FCA-4F3B-9BFE-6FEA042E28EB@.microsoft.com...
>I am backing up SQL databases two ways, once through the Enterprise Manager
> and also with Veritas. I need to do a test restore on a database and am
> uncertain of the best way to go about it. Is it easier to restore from
> Veritas or SQL backups? How do I do it without overwriting the existing
> database? Any insights or help would be greatly appreciated.
> Here is some info:
> Windows 2003 Server
> SQL 2000
> Veritas 9.1
> Thanks|||For full backups you shouldn't have any problems. But be carefull with
Transaction Log backups. If you run TLog backups from both sides (SQL Serve
r
and Veritas) it will be very difficult, almost impossible to restore a DB to
the Point of Failure. TLog backups have a sequence number used to know in
what order they have to be restored after the full backup. Running TLog
backups from both tools will assign a sequence number for every backup
created, then you will have something like this:
FULL BACKUP
SQLTLog(1)
SQLTLog(2)
VERITASTLog(3)
VERITASTLog(4)
SQLTLog(5)
VERITASTLog(6)
Restoring from Full Backup up to the point 6 would require SQL and VERITAS
TLogs. This can be very difficult to achieve depending on the frequency of
TLog backups.
Hope it helps
Lionel Chacon
"NOBI" wrote:

> I am backing up SQL databases two ways, once through the Enterprise Manage
r
> and also with Veritas. I need to do a test restore on a database and am
> uncertain of the best way to go about it. Is it easier to restore from
> Veritas or SQL backups? How do I do it without overwriting the existing
> database? Any insights or help would be greatly appreciated.
> Here is some info:
> Windows 2003 Server
> SQL 2000
> Veritas 9.1
> Thanks

backups and restore

I am backing up SQL databases two ways, once through the Enterprise Manager
and also with Veritas. I need to do a test restore on a database and am
uncertain of the best way to go about it. Is it easier to restore from
Veritas or SQL backups? How do I do it without overwriting the existing
database? Any insights or help would be greatly appreciated.
Here is some info:
Windows 2003 Server
SQL 2000
Veritas 9.1
Thanks
"NOBI" schrieb:
> I am backing up SQL databases two ways, once through the Enterprise Manager
> and also with Veritas. I need to do a test restore on a database and am
> uncertain of the best way to go about it. Is it easier to restore from
> Veritas or SQL backups? How do I do it without overwriting the existing
> database? Any insights or help would be greatly appreciated.
> Here is some info:
> Windows 2003 Server
> SQL 2000
> Veritas 9.1
> Thanks
During the restore process you can define the name of the restored database.
There is no problem with overwriting the existing one (if you just choose a
different name). Don't forget to use the 'move ... to ...' option to give the
files a different location from the original!
|||Hi Nobi,
You can use the RESTORE DATABASE command from query analyzer to restore the
database with a new name.
STEPS:-
1. Identify the logical name associated with MDf AND LDF using RESTORE
FILELISTONLY (see books online)
2. Use the RESTORE DATABASE command with MOVE options (see books online) to
create and restore the backupfile with new
database name. In the MOVE option for the physical name give a new name.
Thanks
Hari
SQL Server MVP
"NOBI" <NOBI@.discussions.microsoft.com> wrote in message
news:8AAA6DF0-3FCA-4F3B-9BFE-6FEA042E28EB@.microsoft.com...
>I am backing up SQL databases two ways, once through the Enterprise Manager
> and also with Veritas. I need to do a test restore on a database and am
> uncertain of the best way to go about it. Is it easier to restore from
> Veritas or SQL backups? How do I do it without overwriting the existing
> database? Any insights or help would be greatly appreciated.
> Here is some info:
> Windows 2003 Server
> SQL 2000
> Veritas 9.1
> Thanks
|||For full backups you shouldn't have any problems. But be carefull with
Transaction Log backups. If you run TLog backups from both sides (SQL Server
and Veritas) it will be very difficult, almost impossible to restore a DB to
the Point of Failure. TLog backups have a sequence number used to know in
what order they have to be restored after the full backup. Running TLog
backups from both tools will assign a sequence number for every backup
created, then you will have something like this:
FULL BACKUP
SQLTLog(1)
SQLTLog(2)
VERITASTLog(3)
VERITASTLog(4)
SQLTLog(5)
VERITASTLog(6)
Restoring from Full Backup up to the point 6 would require SQL and VERITAS
TLogs. This can be very difficult to achieve depending on the frequency of
TLog backups.
Hope it helps
Lionel Chacon
"NOBI" wrote:

> I am backing up SQL databases two ways, once through the Enterprise Manager
> and also with Veritas. I need to do a test restore on a database and am
> uncertain of the best way to go about it. Is it easier to restore from
> Veritas or SQL backups? How do I do it without overwriting the existing
> database? Any insights or help would be greatly appreciated.
> Here is some info:
> Windows 2003 Server
> SQL 2000
> Veritas 9.1
> Thanks

backups and restore

I am backing up SQL databases two ways, once through the Enterprise Manager
and also with Veritas. I need to do a test restore on a database and am
uncertain of the best way to go about it. Is it easier to restore from
Veritas or SQL backups? How do I do it without overwriting the existing
database? Any insights or help would be greatly appreciated.
Here is some info:
Windows 2003 Server
SQL 2000
Veritas 9.1
Thanks"NOBI" schrieb:
> I am backing up SQL databases two ways, once through the Enterprise Manager
> and also with Veritas. I need to do a test restore on a database and am
> uncertain of the best way to go about it. Is it easier to restore from
> Veritas or SQL backups? How do I do it without overwriting the existing
> database? Any insights or help would be greatly appreciated.
> Here is some info:
> Windows 2003 Server
> SQL 2000
> Veritas 9.1
> Thanks
During the restore process you can define the name of the restored database.
There is no problem with overwriting the existing one (if you just choose a
different name). Don't forget to use the 'move ... to ...' option to give the
files a different location from the original!|||Hi Nobi,
You can use the RESTORE DATABASE command from query analyzer to restore the
database with a new name.
STEPS:-
1. Identify the logical name associated with MDf AND LDF using RESTORE
FILELISTONLY (see books online)
2. Use the RESTORE DATABASE command with MOVE options (see books online) to
create and restore the backupfile with new
database name. In the MOVE option for the physical name give a new name.
Thanks
Hari
SQL Server MVP
"NOBI" <NOBI@.discussions.microsoft.com> wrote in message
news:8AAA6DF0-3FCA-4F3B-9BFE-6FEA042E28EB@.microsoft.com...
>I am backing up SQL databases two ways, once through the Enterprise Manager
> and also with Veritas. I need to do a test restore on a database and am
> uncertain of the best way to go about it. Is it easier to restore from
> Veritas or SQL backups? How do I do it without overwriting the existing
> database? Any insights or help would be greatly appreciated.
> Here is some info:
> Windows 2003 Server
> SQL 2000
> Veritas 9.1
> Thanks|||For full backups you shouldn't have any problems. But be carefull with
Transaction Log backups. If you run TLog backups from both sides (SQL Server
and Veritas) it will be very difficult, almost impossible to restore a DB to
the Point of Failure. TLog backups have a sequence number used to know in
what order they have to be restored after the full backup. Running TLog
backups from both tools will assign a sequence number for every backup
created, then you will have something like this:
FULL BACKUP
SQLTLog(1)
SQLTLog(2)
VERITASTLog(3)
VERITASTLog(4)
SQLTLog(5)
VERITASTLog(6)
Restoring from Full Backup up to the point 6 would require SQL and VERITAS
TLogs. This can be very difficult to achieve depending on the frequency of
TLog backups.
Hope it helps
Lionel Chacon
"NOBI" wrote:
> I am backing up SQL databases two ways, once through the Enterprise Manager
> and also with Veritas. I need to do a test restore on a database and am
> uncertain of the best way to go about it. Is it easier to restore from
> Veritas or SQL backups? How do I do it without overwriting the existing
> database? Any insights or help would be greatly appreciated.
> Here is some info:
> Windows 2003 Server
> SQL 2000
> Veritas 9.1
> Thanks

Saturday, February 25, 2012

Backups

Hi Guys,

I'm pretty new to SQL Server. I have a ? regarding backups.

I have a database which I'm backing up everyday using BACKUP DATABASE db TO db_bak WITH INIT. This job runs everyday successfully, but I'm having an evergrowing transaction log file. I presumed a full database backup will apply transaction logs and will truncate the log files. Isnt it so, or am i missing some concepts? This database can have a data loss of 1 day which is covered by my full database backup job. I dont need a transaction log backup. But to reduce log file size should I do a log backup?

-Sunil.Depends...(love that answer)

What's your recovery model? Simple would do what you're thinking...

I have a feeling it's FULL...in which case you need to dump the tranny too...

Look at the maint plan wizard to help you out

Also you need to determine what type of strategy you need to employ...

Basically, how much time/data can you afford to lose...|||Thanks Brett,

My recovery model is FULL and i prefer to keep it that way. So, i reckon I should set transaction log backups aswell. I have set database backup to run every 6PM, if i create another job to backup transaction logs at 7PM, will it do? Also, since my database is big (It is MSProject database) database backup is done everyday in overwrite mode to avoid appending 200MB everyday to the backup file. Could transaction log backup be done in overwrite mode aswell or is it necessary to be appended every day? If there is a recovery scenario, will it all add well?

I'm still wondering why isnt transaction log truncated after a successful full backup. Why do we need transaction logs if the database itself is fully backed up.

-Sunil.|||Originally posted by sunilthomas98
I'm still wondering why isnt transaction log truncated after a successful full backup. Why do we need transaction logs if the database itself is fully backed up.
-Sunil.

Because it is a point in time backup...logs are usually dumped periodically to recover data throughout the daya (I do mine every 10 minutes)

Why not first go through the db maint wizard...it'll show you all the things you can do, without having to code them in T-SQL...it'll even schedule the jobs...

Then by a book...one of my favorites is SQL Server 911 by Brian Knight..good book

Here's a real story...

HR "dba" (the quotes are on purpose) had the type of scenario you mention..

It was time to enter in all the work for the bonuses...which got done...

Now scrub boy messed something up...we think he had a glitch a decided to recover the db from last nights dump...effectivley wiping out all the work the clerks had enetered...soooooo no bonuses...right?

Wrong...the vp called ALL of the clerks in plus IT staff to re-enter the data...so at about 4:00 am it was done...and the file went out the next day...

He's not with us anymore...|||Thanks Brett, I'll do that not to miss my next bonus (if it comes)

Backups

When backing up a database in SQL 2000 I get a failure on
the transaction logs. The database is configured
as 'simple' would this expalin the error? Are the
transaction logs only backed up for 'full' databases.yes full and Bulk.
But can you post error?
Yovan Fernandez
"Sarah Scott" <SarahScott_1@.hotmail.com> wrote in message
news:592101c376dd$858790d0$a401280a@.phx.gbl...
> When backing up a database in SQL 2000 I get a failure on
> the transaction logs. The database is configured
> as 'simple' would this expalin the error? Are the
> transaction logs only backed up for 'full' databases.|||Short answers are: 1. Yes. 2. Question is not meaninful
Please have a look in BOL for the different recovery models. This is an
important decision and you should understand the ramifications of the
various options available to you.
"Sarah Scott" <SarahScott_1@.hotmail.com> wrote in message
news:592101c376dd$858790d0$a401280a@.phx.gbl...
> When backing up a database in SQL 2000 I get a failure on
> the transaction logs. The database is configured
> as 'simple' would this expalin the error? Are the
> transaction logs only backed up for 'full' databases.

Sunday, February 19, 2012

Backup/Restore all databases

Hi Folks,
Is there a syntax for backing up ALL databases for the
backup and restore database commands. The only examples I
see are for one database at a time.
Thanks
LarryNope. You have to loop through the master..sysdatabases table with a cursor
or pseudo-cursor to backup/restore all the databases.
Jacco Schalkwijk
SQL Server MVP
"lmorando" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9ee01c45499$7161f0b0$a601280a@.phx
.gbl...
> Hi Folks,
> Is there a syntax for backing up ALL databases for the
> backup and restore database commands. The only examples I
> see are for one database at a time.
> Thanks
> Larry|||Hi,
I suggest you to perform a FULL database backup of all databases (User
databases, System databases such as Master (Includes logins),msdb).
COmpile the below stored procedure in Master database and define the folder
in which backup needs to be taken.
schedule this procedure using SQL Agent -- Jobs. This procedure will backup
all the databases with a unique name place it in the
folder your are passing.
Unique name will be: SERVERNAME_DBNAME_DD_MM_YYYY_DUMP.BAK
Script to Backup all databases
CREATE PROCEDURE BACKUP_SP @.Folder VARCHAR(100)
AS
begin
DECLARE @.NAME VARCHAR(100),
@.DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM sysdatabases where name not
in('model','pubs','tempdb','northwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
@.NAME=ltrim(rtrim(@.folder))+@.@.SERVERNAME
+'_'+@.DBNAME+'_'+ltrim(rtrim(convert
(char,getdate(),105)))+'Dump.bak'
BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD , NAME =
@.DBNAME, NOSKIP , STATS = 10, NOFORMAT
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
END
CLOSE BACKUP_CUR
DEALLOCATE BACKUP_CUR
end
How to schedule
--
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job.
Give a name to the Job and in Job step menthon this procedure with
foldername as parameter and
scdule the job to be executed based on requirement
Thanks
Hari
MCDBA
"lmorando" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9ee01c45499$7161f0b0$a601280a@.phx
.gbl...
> Hi Folks,
> Is there a syntax for backing up ALL databases for the
> backup and restore database commands. The only examples I
> see are for one database at a time.
> Thanks
> Larry

Backup/Restore all databases

Hi Folks,
Is there a syntax for backing up ALL databases for the
backup and restore database commands. The only examples I
see are for one database at a time.
Thanks
LarryNope. You have to loop through the master..sysdatabases table with a cursor
or pseudo-cursor to backup/restore all the databases.
--
Jacco Schalkwijk
SQL Server MVP
"lmorando" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9ee01c45499$7161f0b0$a601280a@.phx.gbl...
> Hi Folks,
> Is there a syntax for backing up ALL databases for the
> backup and restore database commands. The only examples I
> see are for one database at a time.
> Thanks
> Larry|||Hi,
I suggest you to perform a FULL database backup of all databases (User
databases, System databases such as Master (Includes logins),msdb).
COmpile the below stored procedure in Master database and define the folder
in which backup needs to be taken.
schedule this procedure using SQL Agent -- Jobs. This procedure will backup
all the databases with a unique name place it in the
folder your are passing.
Unique name will be: SERVERNAME_DBNAME_DD_MM_YYYY_DUMP.BAK
Script to Backup all databases
CREATE PROCEDURE BACKUP_SP @.Folder VARCHAR(100)
AS
begin
DECLARE @.NAME VARCHAR(100),
@.DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM sysdatabases where name not
in('model','pubs','tempdb','northwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
@.NAME=ltrim(rtrim(@.folder))+@.@.SERVERNAME+'_'+@.DBNAME+'_'+ltrim(rtrim(convert
(char,getdate(),105)))+'Dump.bak'
BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD , NAME =@.DBNAME, NOSKIP , STATS = 10, NOFORMAT
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
END
CLOSE BACKUP_CUR
DEALLOCATE BACKUP_CUR
end
How to schedule
--
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job.
Give a name to the Job and in Job step menthon this procedure with
foldername as parameter and
scdule the job to be executed based on requirement
Thanks
Hari
MCDBA
"lmorando" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9ee01c45499$7161f0b0$a601280a@.phx.gbl...
> Hi Folks,
> Is there a syntax for backing up ALL databases for the
> backup and restore database commands. The only examples I
> see are for one database at a time.
> Thanks
> Larry

Backup/Restore all databases

Hi Folks,
Is there a syntax for backing up ALL databases for the
backup and restore database commands. The only examples I
see are for one database at a time.
Thanks
Larry
Nope. You have to loop through the master..sysdatabases table with a cursor
or pseudo-cursor to backup/restore all the databases.
Jacco Schalkwijk
SQL Server MVP
"lmorando" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9ee01c45499$7161f0b0$a601280a@.phx.gbl...
> Hi Folks,
> Is there a syntax for backing up ALL databases for the
> backup and restore database commands. The only examples I
> see are for one database at a time.
> Thanks
> Larry
|||Hi,
I suggest you to perform a FULL database backup of all databases (User
databases, System databases such as Master (Includes logins),msdb).
COmpile the below stored procedure in Master database and define the folder
in which backup needs to be taken.
schedule this procedure using SQL Agent -- Jobs. This procedure will backup
all the databases with a unique name place it in the
folder your are passing.
Unique name will be: SERVERNAME_DBNAME_DD_MM_YYYY_DUMP.BAK
Script to Backup all databases
CREATE PROCEDURE BACKUP_SP @.Folder VARCHAR(100)
AS
begin
DECLARE @.NAME VARCHAR(100),
@.DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM sysdatabases where name not
in('model','pubs','tempdb','northwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
@.NAME=ltrim(rtrim(@.folder))+@.@.SERVERNAME+'_'+@.DBNA ME+'_'+ltrim(rtrim(convert
(char,getdate(),105)))+'Dump.bak'
BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD , NAME =
@.DBNAME, NOSKIP , STATS = 10, NOFORMAT
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
END
CLOSE BACKUP_CUR
DEALLOCATE BACKUP_CUR
end
How to schedule
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job.
Give a name to the Job and in Job step menthon this procedure with
foldername as parameter and
scdule the job to be executed based on requirement
Thanks
Hari
MCDBA
"lmorando" <anonymous@.discussions.microsoft.com> wrote in message
news:1d9ee01c45499$7161f0b0$a601280a@.phx.gbl...
> Hi Folks,
> Is there a syntax for backing up ALL databases for the
> backup and restore database commands. The only examples I
> see are for one database at a time.
> Thanks
> Larry

Backup/restore

We have a large database (30 gigs) that we want to break
up into multiple files. I tried backing it up, and
restoring it to a database created with multiple data
files, but just dumps 30 gigs into the first data file,
and leaves the second one blank.
Any suggestions on breaking up a large database?
Fred...
That's not the way to do it. First, use DBCC SHRINKFILE on the larger file.
Next use ALTER DATABASE to increase the size of the same file.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Fred" <fkutz@.surebridge.com> wrote in message
news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
We have a large database (30 gigs) that we want to break
up into multiple files. I tried backing it up, and
restoring it to a database created with multiple data
files, but just dumps 30 gigs into the first data file,
and leaves the second one blank.
Any suggestions on breaking up a large database?
Fred...
|||The database is already shrunk.
Why would I increase the single large datafile to make it
larger? I need to break this single large datafile into
multiple parts, so that I can put it on multiple disks.
Fred...

>--Original Message--
>That's not the way to do it. First, use DBCC SHRINKFILE
on the larger file.
>Next use ALTER DATABASE to increase the size of the same
file.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Fred" <fkutz@.surebridge.com> wrote in message
>news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
>We have a large database (30 gigs) that we want to break
>up into multiple files. I tried backing it up, and
>restoring it to a database created with multiple data
>files, but just dumps 30 gigs into the first data file,
>and leaves the second one blank.
>Any suggestions on breaking up a large database?
>Fred...
>.
>
|||OK, let's say that You have 2 files - File1 and File2 - both in the same
filegroup. File1 has all of your data and is 10 GB. (Assume File1 is
full.) File2 is also 10 GB and has no data. Run DBCC SHRINKFILE (File1,
5210). What should happen is 5GB moves from File1 to File2, since they are
both in the same filegroup. Now, FIle1 is 5GB and full, while File2 is 10GB
and half-empty. At this point, you can shrink File2 and have two 5GB files
or you can expand File1 to, say, 10 GB and have 2 equally empty files.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
<fkutz@.surebridge.com> wrote in message
news:523e01c42c91$94f65c30$a101280a@.phx.gbl...
The database is already shrunk.
Why would I increase the single large datafile to make it
larger? I need to break this single large datafile into
multiple parts, so that I can put it on multiple disks.
Fred...

>--Original Message--
>That's not the way to do it. First, use DBCC SHRINKFILE
on the larger file.
>Next use ALTER DATABASE to increase the size of the same
file.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Fred" <fkutz@.surebridge.com> wrote in message
>news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
>We have a large database (30 gigs) that we want to break
>up into multiple files. I tried backing it up, and
>restoring it to a database created with multiple data
>files, but just dumps 30 gigs into the first data file,
>and leaves the second one blank.
>Any suggestions on breaking up a large database?
>Fred...
>.
>
|||Cool!
Thank you *very* much. That makes much more sense.
Fred...

>--Original Message--
>OK, let's say that You have 2 files - File1 and File2 -
both in the same
>filegroup. File1 has all of your data and is 10 GB.
(Assume File1 is
>full.) File2 is also 10 GB and has no data. Run DBCC
SHRINKFILE (File1,
>5210). What should happen is 5GB moves from File1 to
File2, since they are
>both in the same filegroup. Now, FIle1 is 5GB and full,
while File2 is 10GB
>and half-empty. At this point, you can shrink File2 and
have two 5GB files
>or you can expand File1 to, say, 10 GB and have 2
equally empty files.
>--
>Tom
>----
--[vbcol=seagreen]
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
><fkutz@.surebridge.com> wrote in message
>news:523e01c42c91$94f65c30$a101280a@.phx.gbl...
>The database is already shrunk.
>Why would I increase the single large datafile to make it
>larger? I need to break this single large datafile into
>multiple parts, so that I can put it on multiple disks.
>Fred...
>on the larger file.
>file.
-
>--
>.
>
|||Hey Tom, I tried your suggestion, and it's not working.
Here is the command I'm using and the results:
DBCC SHRINKFILE (EPMROPRD_Data, 15360);
11 1 3925712 128 3925648 3925648
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
No data is moving, and the file is not shrinking. My
second file, in the same file group is set to 15360MB.
Am I missing something?
Fred...

>--Original Message--
>OK, let's say that You have 2 files - File1 and File2 -
both in the same
>filegroup. File1 has all of your data and is 10 GB.
(Assume File1 is
>full.) File2 is also 10 GB and has no data. Run DBCC
SHRINKFILE (File1,
>5210). What should happen is 5GB moves from File1 to
File2, since they are
>both in the same filegroup. Now, FIle1 is 5GB and full,
while File2 is 10GB
>and half-empty. At this point, you can shrink File2 and
have two 5GB files
>or you can expand File1 to, say, 10 GB and have 2
equally empty files.
>--
>Tom
>----
--[vbcol=seagreen]
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
><fkutz@.surebridge.com> wrote in message
>news:523e01c42c91$94f65c30$a101280a@.phx.gbl...
>The database is already shrunk.
>Why would I increase the single large datafile to make it
>larger? I need to break this single large datafile into
>multiple parts, so that I can put it on multiple disks.
>Fred...
>on the larger file.
>file.
-
>--
>.
>
|||I'll check into that. Could you please confirm that neither of these files
is the primary file and that the filegroup is not the primary filegroup? If
we're not talking about the primary file/filegroup, then I have an
alternative.
Let's say that you have a filegroup that has the one 30GB file - File1.
Now, add two more files to the filegroup - File1 and File2 - each 15GB in
size. Here's the clincher. Now run:
DBCC SHRINKFILE (File1, EMPTYFILE)
This will force the move and then disallow any further addition of data to
File1. Now run:
ALTER DATABASE MyDB
REMOVE FILE File1
At this point, you can rename File3 to File1:
ALTER DATABASE MyDB
MODIFY FILE (NAME = File3, NEWNAME = File1)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Fred" <fkutz@.surebridge.com> wrote in message
news:57cf01c42d28$007dc240$a001280a@.phx.gbl...
Hey Tom, I tried your suggestion, and it's not working.
Here is the command I'm using and the results:
DBCC SHRINKFILE (EPMROPRD_Data, 15360);
11 1 3925712 128 3925648 3925648
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
No data is moving, and the file is not shrinking. My
second file, in the same file group is set to 15360MB.
Am I missing something?
Fred...

>--Original Message--
>OK, let's say that You have 2 files - File1 and File2 -
both in the same
>filegroup. File1 has all of your data and is 10 GB.
(Assume File1 is
>full.) File2 is also 10 GB and has no data. Run DBCC
SHRINKFILE (File1,
>5210). What should happen is 5GB moves from File1 to
File2, since they are
>both in the same filegroup. Now, FIle1 is 5GB and full,
while File2 is 10GB
>and half-empty. At this point, you can shrink File2 and
have two 5GB files
>or you can expand File1 to, say, 10 GB and have 2
equally empty files.
>--
>Tom
>----
--[vbcol=seagreen]
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
><fkutz@.surebridge.com> wrote in message
>news:523e01c42c91$94f65c30$a101280a@.phx.gbl...
>The database is already shrunk.
>Why would I increase the single large datafile to make it
>larger? I need to break this single large datafile into
>multiple parts, so that I can put it on multiple disks.
>Fred...
>on the larger file.
>file.
-
>--
>.
>
|||It's a good practice to use the primary file and filegroup just for system
objects. What you're now left with is to move data from your primary
filegroup to your application filegroup. This means rebuilding indexes:
CREATE DATABASE Sales
ON PRIMARY
( NAME = SPri1_dat,
FILENAME = 'c:\temp\SPri1dat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
FILEGROUP SalesGroup1
( NAME = SGrp1Fi1_dat,
FILENAME = 'c:\temp\SG1Fi1dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
( NAME = SGrp1Fi2_dat,
FILENAME = 'c:\temp\SG1Fi2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
FILENAME = 'c:\temp\salelog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
go
use Sales
go
create table t
(
id int constraint PK_t primary key
)
go
insert t
select id
from Northwind..sysobjects
go
create unique clustered index PK_t on t (id) with drop_existing on
SalesGroup1
go
sp_help t
go
use master
go
drop database Sales
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Fred" <fkutz@.surebridge.com> wrote in message
news:578601c42d30$82c6e710$a301280a@.phx.gbl...
Unfortunately they are the primaries. The big one is the
primary file on the primary group. Till now, we've just
been running with a single large file. I had thought of
your alternative as well, but I wasn't sure if it could
be done with the primary file. Though the help files
seem to imply it could. Right now I'm just playing with
a test db, so I can do anything with no worries.
Fred...

>--Original Message--
>I'll check into that. Could you please confirm that
neither of these files
>is the primary file and that the filegroup is not the
primary filegroup? If
>we're not talking about the primary file/filegroup, then
I have an
>alternative.
>Let's say that you have a filegroup that has the one
30GB file - File1.
>Now, add two more files to the filegroup - File1 and
File2 - each 15GB in
>size. Here's the clincher. Now run:
>DBCC SHRINKFILE (File1, EMPTYFILE)
>This will force the move and then disallow any further
addition of data to
>File1. Now run:
>ALTER DATABASE MyDB
>REMOVE FILE File1
>At this point, you can rename File3 to File1:
>ALTER DATABASE MyDB
>MODIFY FILE (NAME = File3, NEWNAME = File1)
>--
>Tom
>----
--[vbcol=seagreen]
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Fred" <fkutz@.surebridge.com> wrote in message
>news:57cf01c42d28$007dc240$a001280a@.phx.gbl...
>Hey Tom, I tried your suggestion, and it's not working.
>Here is the command I'm using and the results:
>DBCC SHRINKFILE (EPMROPRD_Data, 15360);
>11 1 3925712 128 3925648 3925648
>(1 row(s) affected)
>DBCC execution completed. If DBCC printed error messages,
>contact your system administrator.
>No data is moving, and the file is not shrinking. My
>second file, in the same file group is set to 15360MB.
>Am I missing something?
>Fred...
>both in the same
>(Assume File1 is
>SHRINKFILE (File1,
>File2, since they are
>while File2 is 10GB
>have two 5GB files
>equally empty files.
-[vbcol=seagreen]
>--
it[vbcol=seagreen]
SHRINKFILE[vbcol=seagreen]
same[vbcol=seagreen]
-[vbcol=seagreen]
>-
break
>.
>
|||In your script, I see nothing indicating a preference for
where your table is built, nor where the data will be
put. Only on the index build do you specify a file
group. How are you proposing I move data between file
groups? (keeping in mind, between tables and views I have
close to 40,000 objects in the database).

>--Original Message--
>It's a good practice to use the primary file and
filegroup just for system
>objects. What you're now left with is to move data from
your primary
>filegroup to your application filegroup. This means
rebuilding indexes:
>CREATE DATABASE Sales
>ON PRIMARY
>( NAME = SPri1_dat,
> FILENAME = 'c:\temp\SPri1dat.mdf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 15% ),
>FILEGROUP SalesGroup1
>( NAME = SGrp1Fi1_dat,
> FILENAME = 'c:\temp\SG1Fi1dt.ndf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 5 ),
>( NAME = SGrp1Fi2_dat,
> FILENAME = 'c:\temp\SG1Fi2dt.ndf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 5 )
>LOG ON
>( NAME = 'Sales_log',
> FILENAME = 'c:\temp\salelog.ldf',
> SIZE = 5MB,
> MAXSIZE = 25MB,
> FILEGROWTH = 5MB )
>go
>use Sales
>go
>create table t
>(
> id int constraint PK_t primary key
>)
>go
>insert t
>select id
>from Northwind..sysobjects
>go
>create unique clustered index PK_t on t (id) with
drop_existing on
>SalesGroup1
>go
>sp_help t
>go
>use master
>go
>drop database Sales
>
>--
>Tom
>----
--[vbcol=seagreen]
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Fred" <fkutz@.surebridge.com> wrote in message
>news:578601c42d30$82c6e710$a301280a@.phx.gbl...
>Unfortunately they are the primaries. The big one is the
>primary file on the primary group. Till now, we've just
>been running with a single large file. I had thought of
>your alternative as well, but I wasn't sure if it could
>be done with the primary file. Though the help files
>seem to imply it could. Right now I'm just playing with
>a test db, so I can do anything with no worries.
>Fred...
>
>neither of these files
>primary filegroup? If
>I have an
>30GB file - File1.
>File2 - each 15GB in
>addition of data to
-[vbcol=seagreen]
>--
3925648[vbcol=seagreen]
messages,[vbcol=seagreen]
full,[vbcol=seagreen]
and[vbcol=seagreen]
-[vbcol=seagreen]
>-
>it
into[vbcol=seagreen]
>SHRINKFILE
>same
-[vbcol=seagreen]
>-
>break
file,
>.
>
|||When I built the original table t, it was in the default filegroup. Since I
did not explicitly change the default filegroup when I built the DB, it was
the primary filegroup. I implicitly created a clustered index by adding a
primary key to the CREATE TABLE script. If you ran sp_help right after that
table was built, you'd see that the table existed in the primary filegroup.
When I re-created the index, using the drop_existing option, I explicitly
created it on SalesGroup1 by adding "on SalesGroup1". This forces the
clustered index - and hence, the data - to be moved to the new filegroup.
As for doing this on an entire database, you can use EM to script out your
tables, including indexes. Throw away the table stuff and keep the index
scripts. Change all references from the primary filegroup to the new
filegroup. For primary keys, the following will generate your script:
create function CreateIndexScript
(
@.Table sysname
, @.FileGroup sysname
)
returns varchar (8000)
as
begin
declare
@.str varchar (8000)
, @.constraint sysname
, @.IsClustered char (1)
, @.Column sysname
select
@.constraint = CONSTRAINT_NAME
, @.IsClustered = case when indexproperty (object_id (@.Table),
CONSTRAINT_NAME, 'IsClustered') = 1
then 'Y' else 'N' end
from
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
where
TABLE_NAME = @.Table
and objectproperty (object_id (constraint_name), 'IsPrimaryKey') = 1
select
@.str = 'create unique ' + case when @.IsClustered = 'Y' then 'clustered '
else '' end
+ 'index ' + @.constraint + ' on [' + @.Table + '] ('
declare c cursor fast_forward for
select
COLUMN_NAME
from
information_schema.key_column_usage
where
TABLE_NAME = @.Table
and CONSTRAINT_NAME = @.constraint
order by
ORDINAL_POSITION
open c
fetch c into
@.Column
while @.@.fetch_status = 0
begin
set
@.str = @.str + '[' + @.Column + '], '
fetch c into
@.Column
end
close c
deallocate c
set
@.str = left (@.str, len (@.str) - 1) + ') with drop_existing on [' +
@.FileGroup + ']'
return (@.str)
end
go
select
dbo.CreateIndexScript (TABLE_NAME, 'MyFG')
from
INFORMATION_SCHEMA.TABLES
where
TABLE_TYPE = 'BASE TABLE'
go
drop function CreateIndexScript
You don't need to do anything for your views.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Fred" <fkutz@.surebridge.com> wrote in message
news:59bd01c42d3e$a4603fd0$a101280a@.phx.gbl...
In your script, I see nothing indicating a preference for
where your table is built, nor where the data will be
put. Only on the index build do you specify a file
group. How are you proposing I move data between file
groups? (keeping in mind, between tables and views I have
close to 40,000 objects in the database).

>--Original Message--
>It's a good practice to use the primary file and
filegroup just for system
>objects. What you're now left with is to move data from
your primary
>filegroup to your application filegroup. This means
rebuilding indexes:
>CREATE DATABASE Sales
>ON PRIMARY
>( NAME = SPri1_dat,
> FILENAME = 'c:\temp\SPri1dat.mdf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 15% ),
>FILEGROUP SalesGroup1
>( NAME = SGrp1Fi1_dat,
> FILENAME = 'c:\temp\SG1Fi1dt.ndf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 5 ),
>( NAME = SGrp1Fi2_dat,
> FILENAME = 'c:\temp\SG1Fi2dt.ndf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 5 )
>LOG ON
>( NAME = 'Sales_log',
> FILENAME = 'c:\temp\salelog.ldf',
> SIZE = 5MB,
> MAXSIZE = 25MB,
> FILEGROWTH = 5MB )
>go
>use Sales
>go
>create table t
>(
> id int constraint PK_t primary key
>)
>go
>insert t
>select id
>from Northwind..sysobjects
>go
>create unique clustered index PK_t on t (id) with
drop_existing on
>SalesGroup1
>go
>sp_help t
>go
>use master
>go
>drop database Sales
>
>--
>Tom
>----
--[vbcol=seagreen]
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Fred" <fkutz@.surebridge.com> wrote in message
>news:578601c42d30$82c6e710$a301280a@.phx.gbl...
>Unfortunately they are the primaries. The big one is the
>primary file on the primary group. Till now, we've just
>been running with a single large file. I had thought of
>your alternative as well, but I wasn't sure if it could
>be done with the primary file. Though the help files
>seem to imply it could. Right now I'm just playing with
>a test db, so I can do anything with no worries.
>Fred...
>
>neither of these files
>primary filegroup? If
>I have an
>30GB file - File1.
>File2 - each 15GB in
>addition of data to
-[vbcol=seagreen]
>--
3925648[vbcol=seagreen]
messages,[vbcol=seagreen]
full,[vbcol=seagreen]
and[vbcol=seagreen]
-[vbcol=seagreen]
>-
>it
into[vbcol=seagreen]
>SHRINKFILE
>same
-[vbcol=seagreen]
>-
>break
file,
>.
>

Thursday, March 8, 2012

Backup-to-file SQL2k Question/NT Tape Backups

Backup-to-file SQL2k Question/NT Tape Backups

Backups to network share fail

Backups to network share fail

Backups to network share fail

Wednesday, March 7, 2012

BackUps not deleting

BackUps not deleting

BackUps not deleting

BackUps not deleting

Backups Best Practices

backups and restore

backups and restore

backups and restore

Saturday, February 25, 2012

Backups

Backups

Sunday, February 19, 2012

Backup/Restore all databases

Backup/Restore all databases

Backup/Restore all databases

Backup/restore

Backup to Network Drive

Blog Archive

About Me