Restore Database: backup

Showing posts with label backup. Show all posts

Tuesday, March 27, 2012

Help with Bkup jobs

Hi,
I have a daily job programmed to backup two sets of dbs with their
respective BAK and TRN extensions.
Job 1 is executing normally creating the backup files BAK and TRN but job 2
is only creating the BAK file and not the TRN.
Both jobs were created with the db maintenance plans.
What could be possible wrong? Or where should I start looking?
Any help is appreciated.
Ana.
Here's the application's logger description:
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'DB Maintenance REGV'' (0xE61E1EABD380A84EBF33FB1AA12D6AD9) - Status:
Failed - Invoked on: 2006-09-17 13:27:11 - Message: The job failed. The Job
was invoked by User sa. The last step to run was step 1 (Step 1).
Job1:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
CF77F56D-B303-4429-AD22-D1FCADD80EEB -To
SQL_Errores" -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDBRepair -BkUpMedia
DISK -BkUpLog -UseDefDir -DelBkUps 4WEEKS -BkExt "TRN"'
Job2:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
25E1CF45-FDAA-4B0B-B209-60176367F5BF -To
SQL_Errores" -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDBRepair -BkUpMedia
DISK -BkUpLog -UseDefDir -DelBkUps 4WEEKS -BkExt "TRN"'Ana
I have already answered this question posted by you a few days ago
Please make sure that the second database is not set to SIMPLE recovery
mode
"Ana_T" <nospam@.yahoo.com> wrote in message
news:OKSW09J3GHA.1568@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a daily job programmed to backup two sets of dbs with their
> respective BAK and TRN extensions.
> Job 1 is executing normally creating the backup files BAK and TRN but job
> 2
> is only creating the BAK file and not the TRN.
> Both jobs were created with the db maintenance plans.
> What could be possible wrong? Or where should I start looking?
>
> Any help is appreciated.
> Ana.
>
>
> Here's the application's logger description:
>
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
> Plan
> 'DB Maintenance REGV'' (0xE61E1EABD380A84EBF33FB1AA12D6AD9) - Status:
> Failed - Invoked on: 2006-09-17 13:27:11 - Message: The job failed. The
> Job
> was invoked by User sa. The last step to run was step 1 (Step 1).
>
>
> Job1:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> CF77F56D-B303-4429-AD22-D1FCADD80EEB -To
> QL_Errores" -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDBRepair -BkUpMedia
> DISK -BkUpLog -UseDefDir -DelBkUps 4WEEKS -BkExt "TRN"'
> Job2:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 25E1CF45-FDAA-4B0B-B209-60176367F5BF -To
> QL_Errores" -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDBRepair -BkUpMedia
> DISK -BkUpLog -UseDefDir -DelBkUps 4WEEKS -BkExt "TRN"'
>|||Thank you Uri, I couldn't find the post.
Where is the recovery selection hidden at? I cannot find it. I used the
wizard to create the jobs.
"Uri Dimant" <urid@.iscar.co.il> escribió en el mensaje
news:eHeaaIK3GHA.1256@.TK2MSFTNGP04.phx.gbl...
> Ana
> I have already answered this question posted by you a few days ago
> Please make sure that the second database is not set to SIMPLE recovery
> mode
>
> "Ana_T" <nospam@.yahoo.com> wrote in message
> news:OKSW09J3GHA.1568@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I have a daily job programmed to backup two sets of dbs with their
>> respective BAK and TRN extensions.
>> Job 1 is executing normally creating the backup files BAK and TRN but job
>> 2
>> is only creating the BAK file and not the TRN.
>> Both jobs were created with the db maintenance plans.
>> What could be possible wrong? Or where should I start looking?
>>
>> Any help is appreciated.
>> Ana.
>>
>>
>> Here's the application's logger description:
>>
>> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
>> Plan
>> 'DB Maintenance REGV'' (0xE61E1EABD380A84EBF33FB1AA12D6AD9) - Status:
>> Failed - Invoked on: 2006-09-17 13:27:11 - Message: The job failed. The
>> Job
>> was invoked by User sa. The last step to run was step 1 (Step 1).
>>
>>
>> Job1:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> CF77F56D-B303-4429-AD22-D1FCADD80EEB -To
>>
>> L_Errores" -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDBRepair -BkUpMedia
>> DISK -BkUpLog -UseDefDir -DelBkUps 4WEEKS -BkExt "TRN"'
>> Job2:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> 25E1CF45-FDAA-4B0B-B209-60176367F5BF -To
>>
>> L_Errores" -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDBRepair -BkUpMedia
>> DISK -BkUpLog -UseDefDir -DelBkUps 4WEEKS -BkExt "TRN"'
>|||hi
make sure that your maintenance plane is not talking transactional log
backup of master database.
to get the actual error message query msdb.sysdbmaintplan_history table
Vinu
"Ana_T" wrote:
> Hi,
> I have a daily job programmed to backup two sets of dbs with their
> respective BAK and TRN extensions.
> Job 1 is executing normally creating the backup files BAK and TRN but job 2
> is only creating the BAK file and not the TRN.
> Both jobs were created with the db maintenance plans.
> What could be possible wrong? Or where should I start looking?
>
> Any help is appreciated.
> Ana.
>
>
> Here's the application's logger description:
>
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
> 'DB Maintenance REGV'' (0xE61E1EABD380A84EBF33FB1AA12D6AD9) - Status:
> Failed - Invoked on: 2006-09-17 13:27:11 - Message: The job failed. The Job
> was invoked by User sa. The last step to run was step 1 (Step 1).
>
>
> Job1:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> CF77F56D-B303-4429-AD22-D1FCADD80EEB -To
> SQL_Errores" -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDBRepair -BkUpMedia
> DISK -BkUpLog -UseDefDir -DelBkUps 4WEEKS -BkExt "TRN"'
> Job2:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 25E1CF45-FDAA-4B0B-B209-60176367F5BF -To
> SQL_Errores" -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDBRepair -BkUpMedia
> DISK -BkUpLog -UseDefDir -DelBkUps 4WEEKS -BkExt "TRN"'
>
>|||Ana
SELECT DATABASEPROPERTYEX('Northwind', 'Recovery')
"Ana_T" <nospam@.yahoo.com> wrote in message
news:OGS$voK3GHA.480@.TK2MSFTNGP06.phx.gbl...
> Thank you Uri, I couldn't find the post.
> Where is the recovery selection hidden at? I cannot find it. I used the
> wizard to create the jobs.
> "Uri Dimant" <urid@.iscar.co.il> escribió en el mensaje
> news:eHeaaIK3GHA.1256@.TK2MSFTNGP04.phx.gbl...
>> Ana
>> I have already answered this question posted by you a few days ago
>> Please make sure that the second database is not set to SIMPLE recovery
>> mode
>>
>> "Ana_T" <nospam@.yahoo.com> wrote in message
>> news:OKSW09J3GHA.1568@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I have a daily job programmed to backup two sets of dbs with their
>> respective BAK and TRN extensions.
>> Job 1 is executing normally creating the backup files BAK and TRN but
>> job 2
>> is only creating the BAK file and not the TRN.
>> Both jobs were created with the db maintenance plans.
>> What could be possible wrong? Or where should I start looking?
>>
>> Any help is appreciated.
>> Ana.
>>
>>
>> Here's the application's logger description:
>>
>> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
>> Plan
>> 'DB Maintenance REGV'' (0xE61E1EABD380A84EBF33FB1AA12D6AD9) - Status:
>> Failed - Invoked on: 2006-09-17 13:27:11 - Message: The job failed. The
>> Job
>> was invoked by User sa. The last step to run was step 1 (Step 1).
>>
>>
>> Job1:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> CF77F56D-B303-4429-AD22-D1FCADD80EEB -To
>>
>> _Errores" -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDBRepair -BkUpMedia
>> DISK -BkUpLog -UseDefDir -DelBkUps 4WEEKS -BkExt "TRN"'
>> Job2:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> 25E1CF45-FDAA-4B0B-B209-60176367F5BF -To
>>
>> _Errores" -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDBRepair -BkUpMedia
>> DISK -BkUpLog -UseDefDir -DelBkUps 4WEEKS -BkExt "TRN"'
>>
>

Help with Bkup jobs

Monday, March 26, 2012

Help with BCP

NOTE: Addresses, usernames, and passwords have been changed.

I am trying to do a Batch Backup of my SQL Server (sql6.alpha.net) to (sql4.alpha.net). So I go onto sql6, get to a dos prompt and type the following (in order to backup the first table, "Accounts").

BCP "AlphaOffice.sa.Accounts" out "AOBackup-Tuesday.Accounts" -S"sql4.alpha.net,10775" -U"username" -P"password"

I get the following Error:

SQLState = 37000, NativeError = 4060
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login 'AlphaOffice'. Login fails.

Now - I Know for a fact that the database "AlphaOffice" exists. My question is: the -P and -U parameters are for a username and password. Is this the username/password for the source DB (housed on the computer I am running BCP from), or the username/password for the destination server (Where the backup will eventually be housed)? If I use the User/Pass from the source, I get the above message. If I used the User/Pass of the destination, I get an error which states Access is Denied. Any thought?The -U -P refers to the server you are exporting data from or importing data to. In the example you have listed the -U -P is for sql4.alpha.net. When you bcp data to sql6.alpha.net you will use a uid/pwd for that box. You might also want to through a "-n" to extract the data in native mode.

Having said all that, DTS would be an easier way to go. You could DTS the data out of sql4 and then DTS the data into sql6 or go directly from sql4 to sql6.

btw, the error message you are getting suggests that the DB you are referencing does not exsist rather than a login problem.|||Personally I'd still prefer BCP over DTS (unless it's a Yukon DTS) simply because you can touch and feel the resulting file before you can send it to the destination. The trick there is to know whether the table contains an IDENTITY field on the destination, and if you need to retain the values from the source,- specify -E switch. And you definitely need to include either "-n" or "-c" switch, otherwise the utility would take you through a series of "employment application" type questions and offer to save the answers in a format file before you get to see the actual extraction process.

Help with BAK file

Hi all,
I'm not an expert with SQL2000 and need some help. I have a full backup "BAK
file" that was produced from an SQL2000 server. Is there any way that I can
use that file to rebuild that database on another SQL2000 server?
Thanks,
Clausdid you try to force the restore over the existing database?
cjobes wrote:
> Thanks for the quick answer.
> Well, I tried everything I can think of. I first created a Database with the
> same name as the database on the old server. Then I tried to restore, first
> selecting Device and pointing to the BAK file. I got a message "Device
> activation error. The physical file name xxx maybe incorect. File
> xxx_Data.mdf cannot be restored to yyy_Data.mdf. Use WITH MOVE to identify a
> valid location for the file." The same error msg follows with regard to the
> xxx_log.ldf file.
> Any idea?
> Claus
> "ilovesql" <ilovesql@.hotmail.com> wrote in message
> news:eFS3oBiSDHA.1724@.TK2MSFTNGP10.phx.gbl...
>>Sure, that what backups are for.
>>If the backup was created using single backup file then restore is even
>>easier.
>>To speedup restore and avoid network traffic, in case your database is
> big,
>>then copy the backup file to 2nd server and use the restore command.
>>One issue that you will face after restoring from different server is
>>security that you can deal with later, once restore is completed.
>>"cjobes" <cjobes@.nova-tech.org> wrote in message
>>news:#HHTC0hSDHA.632@.tk2msftngp13.phx.gbl...
>>Hi all,
>>I'm not an expert with SQL2000 and need some help. I have a full backup
>>"BAK
>>file" that was produced from an SQL2000 server. Is there any way that I
>>can
>>use that file to rebuild that database on another SQL2000 server?
>>Thanks,
>>Claus
>>
>>
>|||Yes I did check the checkbox.
"Maria" <maria.belli@.*nospam*rfsworld.com> wrote in message
news:%236X%232MiSDHA.1688@.TK2MSFTNGP11.phx.gbl...
> did you try to force the restore over the existing database?
> cjobes wrote:
> > Thanks for the quick answer.
> > Well, I tried everything I can think of. I first created a Database with
the
> > same name as the database on the old server. Then I tried to restore,
first
> > selecting Device and pointing to the BAK file. I got a message "Device
> > activation error. The physical file name xxx maybe incorect. File
> > xxx_Data.mdf cannot be restored to yyy_Data.mdf. Use WITH MOVE to
identify a
> > valid location for the file." The same error msg follows with regard to
the
> > xxx_log.ldf file.
> >
> > Any idea?
> > Claus
> >
> > "ilovesql" <ilovesql@.hotmail.com> wrote in message
> > news:eFS3oBiSDHA.1724@.TK2MSFTNGP10.phx.gbl...
> >
> >>Sure, that what backups are for.
> >>If the backup was created using single backup file then restore is even
> >>easier.
> >>To speedup restore and avoid network traffic, in case your database is
> >
> > big,
> >
> >>then copy the backup file to 2nd server and use the restore command.
> >>
> >>One issue that you will face after restoring from different server is
> >>security that you can deal with later, once restore is completed.
> >>
> >>"cjobes" <cjobes@.nova-tech.org> wrote in message
> >>news:#HHTC0hSDHA.632@.tk2msftngp13.phx.gbl...
> >>
> >>Hi all,
> >>
> >>I'm not an expert with SQL2000 and need some help. I have a full backup
> >>
> >>"BAK
> >>
> >>file" that was produced from an SQL2000 server. Is there any way that I
> >>
> >>can
> >>
> >>use that file to rebuild that database on another SQL2000 server?
> >>
> >>Thanks,
> >>Claus
> >>
> >>
> >>
> >>
> >
> >
>|||Have a look at the following KB articles
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546
INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/default.aspx?scid=kb;EN-US;221465
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"cjobes" <cjobes@.nova-tech.org> wrote in message
news:eQK1KTiSDHA.2480@.tk2msftngp13.phx.gbl...
Yes I did check the checkbox.
"Maria" <maria.belli@.*nospam*rfsworld.com> wrote in message
news:%236X%232MiSDHA.1688@.TK2MSFTNGP11.phx.gbl...
> did you try to force the restore over the existing database?
> cjobes wrote:
> > Thanks for the quick answer.
> > Well, I tried everything I can think of. I first created a Database with
the
> > same name as the database on the old server. Then I tried to restore,
first
> > selecting Device and pointing to the BAK file. I got a message "Device
> > activation error. The physical file name xxx maybe incorect. File
> > xxx_Data.mdf cannot be restored to yyy_Data.mdf. Use WITH MOVE to
identify a
> > valid location for the file." The same error msg follows with regard to
the
> > xxx_log.ldf file.
> >
> > Any idea?
> > Claus
> >
> > "ilovesql" <ilovesql@.hotmail.com> wrote in message
> > news:eFS3oBiSDHA.1724@.TK2MSFTNGP10.phx.gbl...
> >
> >>Sure, that what backups are for.
> >>If the backup was created using single backup file then restore is even
> >>easier.
> >>To speedup restore and avoid network traffic, in case your database is
> >
> > big,
> >
> >>then copy the backup file to 2nd server and use the restore command.
> >>
> >>One issue that you will face after restoring from different server is
> >>security that you can deal with later, once restore is completed.
> >>
> >>"cjobes" <cjobes@.nova-tech.org> wrote in message
> >>news:#HHTC0hSDHA.632@.tk2msftngp13.phx.gbl...
> >>
> >>Hi all,
> >>
> >>I'm not an expert with SQL2000 and need some help. I have a full backup
> >>
> >>"BAK
> >>
> >>file" that was produced from an SQL2000 server. Is there any way that I
> >>
> >>can
> >>
> >>use that file to rebuild that database on another SQL2000 server?
> >>
> >>Thanks,
> >>Claus
> >>
> >>
> >>
> >>
> >
> >
>|||Thanks for the help. This was exactly what I was looking for and it worked.
Claus
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:ePqDfmiSDHA.2256@.TK2MSFTNGP11.phx.gbl...
> Have a look at the following KB articles
> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546
> INF: Using the WITH MOVE Option with the RESTORE Statement
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;221465
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "cjobes" <cjobes@.nova-tech.org> wrote in message
> news:eQK1KTiSDHA.2480@.tk2msftngp13.phx.gbl...
> Yes I did check the checkbox.
>
> "Maria" <maria.belli@.*nospam*rfsworld.com> wrote in message
> news:%236X%232MiSDHA.1688@.TK2MSFTNGP11.phx.gbl...
> > did you try to force the restore over the existing database?
> >
> > cjobes wrote:
> > > Thanks for the quick answer.
> > > Well, I tried everything I can think of. I first created a Database
with
> the
> > > same name as the database on the old server. Then I tried to restore,
> first
> > > selecting Device and pointing to the BAK file. I got a message "Device
> > > activation error. The physical file name xxx maybe incorect. File
> > > xxx_Data.mdf cannot be restored to yyy_Data.mdf. Use WITH MOVE to
> identify a
> > > valid location for the file." The same error msg follows with regard
to
> the
> > > xxx_log.ldf file.
> > >
> > > Any idea?
> > > Claus
> > >
> > > "ilovesql" <ilovesql@.hotmail.com> wrote in message
> > > news:eFS3oBiSDHA.1724@.TK2MSFTNGP10.phx.gbl...
> > >
> > >>Sure, that what backups are for.
> > >>If the backup was created using single backup file then restore is
even
> > >>easier.
> > >>To speedup restore and avoid network traffic, in case your database is
> > >
> > > big,
> > >
> > >>then copy the backup file to 2nd server and use the restore command.
> > >>
> > >>One issue that you will face after restoring from different server is
> > >>security that you can deal with later, once restore is completed.
> > >>
> > >>"cjobes" <cjobes@.nova-tech.org> wrote in message
> > >>news:#HHTC0hSDHA.632@.tk2msftngp13.phx.gbl...
> > >>
> > >>Hi all,
> > >>
> > >>I'm not an expert with SQL2000 and need some help. I have a full
backup
> > >>
> > >>"BAK
> > >>
> > >>file" that was produced from an SQL2000 server. Is there any way that
I
> > >>
> > >>can
> > >>
> > >>use that file to rebuild that database on another SQL2000 server?
> > >>
> > >>Thanks,
> > >>Claus
> > >>
> > >>
> > >>
> > >>
> > >
> > >
> >
>
>

Help with backup strategy

Hi,
I was just asked by my management to implement a new backup strategy. They
want me to be able to go back at any point in time to recover data. Example
auditing purposes. What do you guys recommend? My database is 8gb and
growing. Currently I was making a complete backup daily but only for 6 days
MTWTFS. Now I must also be able to recover at the very most recent point in
time as well.
Thanks
Transaction log backups:
http://msdn.microsoft.com/library/de...kprst_565v.asp
http://msdn.microsoft.com/library/de...ackpc_5a61.asp
http://msdn.microsoft.com/library/de...ackpc_6pv6.asp
David Portas
SQL Server MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:63FAD062-5331-4F19-906E-B2B3E4F8223F@.microsoft.com...
> Hi,
> I was just asked by my management to implement a new backup strategy. They
> want me to be able to go back at any point in time to recover data.
> Example
> auditing purposes. What do you guys recommend? My database is 8gb and
> growing. Currently I was making a complete backup daily but only for 6
> days
> MTWTFS. Now I must also be able to recover at the very most recent point
> in
> time as well.
> Thanks
|||Chris wrote:
> Hi,
> I was just asked by my management to implement a new backup strategy. They
> want me to be able to go back at any point in time to recover data. Example
> auditing purposes. What do you guys recommend? My database is 8gb and
> growing. Currently I was making a complete backup daily but only for 6 days
> MTWTFS. Now I must also be able to recover at the very most recent point in
> time as well.
> Thanks
Hi Chris
You need to backup both your database and you logfiles. In this way, you
can always restore a database backup and then apply the logfiles up till
the time you want data restored.
You could e.g. do a full database backup at 22.00 and then logfiles
backup's at 10.00, 14.00 and 18.00. If you then need to restore data to
how it was at 12.00, you restore your full backup and then apply the log
from 10.00 and the log from 14.00 with the STOPAT 12.00 option.
The times is of course just examples, and can be anything you want. If
you just need this for auditing reasons, you could maybe go with only 1
logfile backup.
Try reading up on Backup and Restore in Books On Line - e.g. "Backing Up
and Restoring Databases", That might give you an idea on how to set up
the best plan that suits you.
Regards
Steen
|||Chris
http://vyaskn.tripod.com/sql_server_...ices.htm#Step1
--administaiting best practices
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:63FAD062-5331-4F19-906E-B2B3E4F8223F@.microsoft.com...
> Hi,
> I was just asked by my management to implement a new backup strategy. They
> want me to be able to go back at any point in time to recover data.
> Example
> auditing purposes. What do you guys recommend? My database is 8gb and
> growing. Currently I was making a complete backup daily but only for 6
> days
> MTWTFS. Now I must also be able to recover at the very most recent point
> in
> time as well.
> Thanks
|||Hi,
But would this allow me to go back say 6 months of a full year?
Thanks
"Chris" wrote:

> Hi,
> I was just asked by my management to implement a new backup strategy. They
> want me to be able to go back at any point in time to recover data. Example
> auditing purposes. What do you guys recommend? My database is 8gb and
> growing. Currently I was making a complete backup daily but only for 6 days
> MTWTFS. Now I must also be able to recover at the very most recent point in
> time as well.
> Thanks
|||Chris
Yes it would. Let say you do backups of the database every night and every
hour a log file. Well , due to a hardware failure or something like that you
losed the last full backup. Don't worry , restore a full backup database
from two weeks ago and the apply every log file backup ( the last one with
recovery option) .See, the data is in the database.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:AA41051A-A58E-4359-B3B3-5671B151F5DC@.microsoft.com...[vbcol=seagreen]
> Hi,
> But would this allow me to go back say 6 months of a full year?
> Thanks
> "Chris" wrote:

Help with backup strategy

Hi,
I was just asked by my management to implement a new backup strategy. They
want me to be able to go back at any point in time to recover data. Example
auditing purposes. What do you guys recommend? My database is 8gb and
growing. Currently I was making a complete backup daily but only for 6 days
MTWTFS. Now I must also be able to recover at the very most recent point in
time as well.
ThanksTransaction log backups:
http://msdn.microsoft.com/library/d... />
t_565v.asp
http://msdn.microsoft.com/library/d...>
kpc_5a61.asp
http://msdn.microsoft.com/library/d...>
kpc_6pv6.asp
David Portas
SQL Server MVP
--
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:63FAD062-5331-4F19-906E-B2B3E4F8223F@.microsoft.com...
> Hi,
> I was just asked by my management to implement a new backup strategy. They
> want me to be able to go back at any point in time to recover data.
> Example
> auditing purposes. What do you guys recommend? My database is 8gb and
> growing. Currently I was making a complete backup daily but only for 6
> days
> MTWTFS. Now I must also be able to recover at the very most recent point
> in
> time as well.
> Thanks|||Chris wrote:
> Hi,
> I was just asked by my management to implement a new backup strategy. They
> want me to be able to go back at any point in time to recover data. Exampl
e
> auditing purposes. What do you guys recommend? My database is 8gb and
> growing. Currently I was making a complete backup daily but only for 6 day
s
> MTWTFS. Now I must also be able to recover at the very most recent point i
n
> time as well.
> Thanks
Hi Chris
You need to backup both your database and you logfiles. In this way, you
can always restore a database backup and then apply the logfiles up till
the time you want data restored.
You could e.g. do a full database backup at 22.00 and then logfiles
backup's at 10.00, 14.00 and 18.00. If you then need to restore data to
how it was at 12.00, you restore your full backup and then apply the log
from 10.00 and the log from 14.00 with the STOPAT 12.00 option.
The times is of course just examples, and can be anything you want. If
you just need this for auditing reasons, you could maybe go with only 1
logfile backup.
Try reading up on Backup and Restore in Books On Line - e.g. "Backing Up
and Restoring Databases", That might give you an idea on how to set up
the best plan that suits you.
Regards
Steen|||Chris
http://vyaskn.tripod.com/ sql_serve...r />
.htm#Step1
--administaiting best practices
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:63FAD062-5331-4F19-906E-B2B3E4F8223F@.microsoft.com...
> Hi,
> I was just asked by my management to implement a new backup strategy. They
> want me to be able to go back at any point in time to recover data.
> Example
> auditing purposes. What do you guys recommend? My database is 8gb and
> growing. Currently I was making a complete backup daily but only for 6
> days
> MTWTFS. Now I must also be able to recover at the very most recent point
> in
> time as well.
> Thanks|||Hi,
But would this allow me to go back say 6 months of a full year?
Thanks
"Chris" wrote:

> Hi,
> I was just asked by my management to implement a new backup strategy. They
> want me to be able to go back at any point in time to recover data. Exampl
e
> auditing purposes. What do you guys recommend? My database is 8gb and
> growing. Currently I was making a complete backup daily but only for 6 day
s
> MTWTFS. Now I must also be able to recover at the very most recent point i
n
> time as well.
> Thanks|||Chris
Yes it would. Let say you do backups of the database every night and every
hour a log file. Well , due to a hardware failure or something like that you
losed the last full backup. Don't worry , restore a full backup database
from two weeks ago and the apply every log file backup ( the last one with
recovery option) .See, the data is in the database.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:AA41051A-A58E-4359-B3B3-5671B151F5DC@.microsoft.com...[vbcol=seagreen]
> Hi,
> But would this allow me to go back say 6 months of a full year?
> Thanks
> "Chris" wrote:
>

Help with backup strategy

Hi,
I was just asked by my management to implement a new backup strategy. They
want me to be able to go back at any point in time to recover data. Example
auditing purposes. What do you guys recommend? My database is 8gb and
growing. Currently I was making a complete backup daily but only for 6 days
MTWTFS. Now I must also be able to recover at the very most recent point in
time as well.
ThanksTransaction log backups:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_565v.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpc_5a61.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpc_6pv6.asp
--
David Portas
SQL Server MVP
--
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:63FAD062-5331-4F19-906E-B2B3E4F8223F@.microsoft.com...
> Hi,
> I was just asked by my management to implement a new backup strategy. They
> want me to be able to go back at any point in time to recover data.
> Example
> auditing purposes. What do you guys recommend? My database is 8gb and
> growing. Currently I was making a complete backup daily but only for 6
> days
> MTWTFS. Now I must also be able to recover at the very most recent point
> in
> time as well.
> Thanks|||Chris wrote:
> Hi,
> I was just asked by my management to implement a new backup strategy. They
> want me to be able to go back at any point in time to recover data. Example
> auditing purposes. What do you guys recommend? My database is 8gb and
> growing. Currently I was making a complete backup daily but only for 6 days
> MTWTFS. Now I must also be able to recover at the very most recent point in
> time as well.
> Thanks
Hi Chris
You need to backup both your database and you logfiles. In this way, you
can always restore a database backup and then apply the logfiles up till
the time you want data restored.
You could e.g. do a full database backup at 22.00 and then logfiles
backup's at 10.00, 14.00 and 18.00. If you then need to restore data to
how it was at 12.00, you restore your full backup and then apply the log
from 10.00 and the log from 14.00 with the STOPAT 12.00 option.
The times is of course just examples, and can be anything you want. If
you just need this for auditing reasons, you could maybe go with only 1
logfile backup.
Try reading up on Backup and Restore in Books On Line - e.g. "Backing Up
and Restoring Databases", That might give you an idea on how to set up
the best plan that suits you.
Regards
Steen|||Chris
http://vyaskn.tripod.com/sql_server_administration_best_practices.htm#Step1
--administaiting best practices
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:63FAD062-5331-4F19-906E-B2B3E4F8223F@.microsoft.com...
> Hi,
> I was just asked by my management to implement a new backup strategy. They
> want me to be able to go back at any point in time to recover data.
> Example
> auditing purposes. What do you guys recommend? My database is 8gb and
> growing. Currently I was making a complete backup daily but only for 6
> days
> MTWTFS. Now I must also be able to recover at the very most recent point
> in
> time as well.
> Thanks|||Hi,
But would this allow me to go back say 6 months of a full year?
Thanks
"Chris" wrote:
> Hi,
> I was just asked by my management to implement a new backup strategy. They
> want me to be able to go back at any point in time to recover data. Example
> auditing purposes. What do you guys recommend? My database is 8gb and
> growing. Currently I was making a complete backup daily but only for 6 days
> MTWTFS. Now I must also be able to recover at the very most recent point in
> time as well.
> Thanks|||Chris
Yes it would. Let say you do backups of the database every night and every
hour a log file. Well , due to a hardware failure or something like that you
losed the last full backup. Don't worry , restore a full backup database
from two weeks ago and the apply every log file backup ( the last one with
recovery option) .See, the data is in the database.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:AA41051A-A58E-4359-B3B3-5671B151F5DC@.microsoft.com...
> Hi,
> But would this allow me to go back say 6 months of a full year?
> Thanks
> "Chris" wrote:
>> Hi,
>> I was just asked by my management to implement a new backup strategy.
>> They
>> want me to be able to go back at any point in time to recover data.
>> Example
>> auditing purposes. What do you guys recommend? My database is 8gb and
>> growing. Currently I was making a complete backup daily but only for 6
>> days
>> MTWTFS. Now I must also be able to recover at the very most recent point
>> in
>> time as well.
>> Thankssql

Help with backup plan

Hi all,

I am new to SQL and was given a SQL server to administer. There are lots of databases on the server and no one seems to know which one is used and which one is not. I try to do some cleanning.

1. Can I find out if there is any activity on a database so I can delete them off and how?

2. There are backup jobs that run every night which do a full backup. I am thinking of changing it to do full backup once a week and differential every night. These databases are updated daily. Some have lots of activities, some have less. Does that sound reasonable? and if I do differential backup, should I create separate back up file or should I add on to the full backup file?

Any comments or suggestions are greatly appreciated.You can use perfmon, sp_who, "Current Activity" in enterprise manager and sql profiler (know that profiler is a performance consumer) to monitor database activity.

For backups, my question would be - is doing full backups taking too much time and/or requiring too much space ? If so, then do differentials. Are your backups to tape or disk ?|||look up sqlmaint.exe utility, it'll give you the flexibility and at the same time can help you standardize the retention period on disk for those backups. if backups don't take long, then i'd continue with full nightly, and do db health checks weekly, along with reindexing or updating stats. delete backups older than 2 or 1 days if you are short on disk, and do tape backup nightly of those backup files, and you'll be in good shape.

Help with backup (Full, differential and transactional) process

Hi everyone, i was hoping to obtain some help with my backup process.
Ideally I wish to make a weekly full backup, with a differential at the end
of each day, and transactionals every hour during business hours. My code is
below
note, each backup process (full, diff and trans) will be placed in a
separate job
--
-- Full backup scheduled on Monday at 2:00 AM
DECLARE @.str varchar(200)
SET @.str = 'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestFull\ZestLive'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12), 1, 5),
3, 1, '')
+ '.BAK'' '
EXEC (@.str)
--
-- Transactional backup scheduled every hour between 6AM and 11PM inclusive
DECLARE @.str varchar(200)
SET @.str = 'BACKUP LOG ZestLive TO DISK=''R:\BACKUP\ZestTLog\ZestLive'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12), 1, 5),
3, 1, '')
+ '.TRN'' '
EXEC (@.str)
--
-- Differential backup scheduled Tue, Wed, Thu, Fri, Sat, Sun at 2:00AM
DECLARE @.str varchar(200)
SET @.str = 'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestDiff\ZestLive'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12), 1, 5),
3, 1, '')
+ '.DIFF'' WITH DIFFERENTIAL'
EXEC (@.str)
--
My questions are:
1. I have noticed that my transaction log keeps growing. How do i keep it to
size with the above backup process. i.e. does the log shrink after taking a
transactional backup?
2. I noticed that there are other flags that can be appended to the above
backup commands. Do i really need them? i.e. INIT, NOINIT, STAT etc...
3. Will the database lock whilst performing transactional backups? I figure
the database will be locked during a full and differential backup.
Any help most appreciated.
Many thanks,
cheers, steve
*/If you are running these backups, then your log shouldn't really be growing
any more. Are you sure the job is executing successfully? You're probably
fine without any other flags. The database does nock lock while backups are
in progress.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"steve" <steve@.nospam.com> wrote in message
news:%23GD1eZvNGHA.2920@.TK2MSFTNGP10.phx.gbl...
Hi everyone, i was hoping to obtain some help with my backup process.
Ideally I wish to make a weekly full backup, with a differential at the end
of each day, and transactionals every hour during business hours. My code is
below
note, each backup process (full, diff and trans) will be placed in a
separate job
--
-- Full backup scheduled on Monday at 2:00 AM
DECLARE @.str varchar(200)
SET @.str ='BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestFull\ZestLive'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12), 1, 5),
3, 1, '')
+ '.BAK'' '
EXEC (@.str)
--
-- Transactional backup scheduled every hour between 6AM and 11PM inclusive
DECLARE @.str varchar(200)
SET @.str ='BACKUP LOG ZestLive TO DISK=''R:\BACKUP\ZestTLog\ZestLive'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12), 1, 5),
3, 1, '')
+ '.TRN'' '
EXEC (@.str)
--
-- Differential backup scheduled Tue, Wed, Thu, Fri, Sat, Sun at 2:00AM
DECLARE @.str varchar(200)
SET @.str ='BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestDiff\ZestLive'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12), 1, 5),
3, 1, '')
+ '.DIFF'' WITH DIFFERENTIAL'
EXEC (@.str)
--
My questions are:
1. I have noticed that my transaction log keeps growing. How do i keep it to
size with the above backup process. i.e. does the log shrink after taking a
transactional backup?
2. I noticed that there are other flags that can be appended to the above
backup commands. Do i really need them? i.e. INIT, NOINIT, STAT etc...
3. Will the database lock whilst performing transactional backups? I figure
the database will be locked during a full and differential backup.
Any help most appreciated.
Many thanks,
cheers, steve
*/|||Just to extend Tom's comments a bit,
I wouldn't worry about backups. They are just a means. What you need to do
is create a restore and recovery strategy. Determine how much data loss and
down time during possible recovery scenarios (full server loss, data
corruption, multiple hard disk failure, etc.) is acceptable. Plan backups
(full, differential, and transaction log) to support your recovery strategy.
Read the BOL section on database backup and restore so you will understand
how SQL backup works. It is not the same as a file system backup. Treating
it the same for restore purposes will lead to disappointment when you
actually have to use a backup.
Finally, test your restore/recovery plans, especially the actual backup
files. Until you test the process, you have a hope, not a plan.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"steve" <steve@.nospam.com> wrote in message
news:%23GD1eZvNGHA.2920@.TK2MSFTNGP10.phx.gbl...
> Hi everyone, i was hoping to obtain some help with my backup process.
> Ideally I wish to make a weekly full backup, with a differential at the
> end of each day, and transactionals every hour during business hours. My
> code is below
> note, each backup process (full, diff and trans) will be placed in a
> separate job
> --
> -- Full backup scheduled on Monday at 2:00 AM
> DECLARE @.str varchar(200)
> SET @.str => 'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestFull\ZestLive'
> + RTRIM(CONVERT(varchar, GETDATE(), 112))
> + '_'
> + STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12), 1,
> 5), 3, 1, '')
> + '.BAK'' '
> EXEC (@.str)
> --
> -- Transactional backup scheduled every hour between 6AM and 11PM
> inclusive
> DECLARE @.str varchar(200)
> SET @.str => 'BACKUP LOG ZestLive TO DISK=''R:\BACKUP\ZestTLog\ZestLive'
> + RTRIM(CONVERT(varchar, GETDATE(), 112))
> + '_'
> + STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12), 1,
> 5), 3, 1, '')
> + '.TRN'' '
> EXEC (@.str)
> --
> -- Differential backup scheduled Tue, Wed, Thu, Fri, Sat, Sun at 2:00AM
> DECLARE @.str varchar(200)
> SET @.str => 'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestDiff\ZestLive'
> + RTRIM(CONVERT(varchar, GETDATE(), 112))
> + '_'
> + STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12), 1,
> 5), 3, 1, '')
> + '.DIFF'' WITH DIFFERENTIAL'
> EXEC (@.str)
> --
> My questions are:
> 1. I have noticed that my transaction log keeps growing. How do i keep it
> to size with the above backup process. i.e. does the log shrink after
> taking a transactional backup?
> 2. I noticed that there are other flags that can be appended to the above
> backup commands. Do i really need them? i.e. INIT, NOINIT, STAT etc...
> 3. Will the database lock whilst performing transactional backups? I
> figure the database will be locked during a full and differential backup.
> Any help most appreciated.
> Many thanks,
> cheers, steve
> */
>

Help with backup (Full, differential and transactional) process

Hi everyone, i was hoping to obtain some help with my backup process.
Ideally I wish to make a weekly full backup, with a differential at the end
of each day, and transactionals every hour during business hours. My code is
below
note, each backup process (full, diff and trans) will be placed in a
separate job
-- Full backup scheduled on Monday at 2:00 AM
DECLARE @.str varchar(200)
SET @.str =
'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestFull\ZestLive'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12), 1, 5),
3, 1, '')
+ '.BAK'' '
EXEC (@.str)
-- Transactional backup scheduled every hour between 6AM and 11PM inclusive
DECLARE @.str varchar(200)
SET @.str =
'BACKUP LOG ZestLive TO DISK=''R:\BACKUP\ZestTLog\ZestLive'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12), 1, 5),
3, 1, '')
+ '.TRN'' '
EXEC (@.str)
-- Differential backup scheduled Tue, Wed, Thu, Fri, Sat, Sun at 2:00AM
DECLARE @.str varchar(200)
SET @.str =
'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestDiff\ZestLive'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12), 1, 5),
3, 1, '')
+ '.DIFF'' WITH DIFFERENTIAL'
EXEC (@.str)
My questions are:
1. I have noticed that my transaction log keeps growing. How do i keep it to
size with the above backup process. i.e. does the log shrink after taking a
transactional backup?
2. I noticed that there are other flags that can be appended to the above
backup commands. Do i really need them? i.e. INIT, NOINIT, STAT etc...
3. Will the database lock whilst performing transactional backups? I figure
the database will be locked during a full and differential backup.
Any help most appreciated.
Many thanks,
cheers, steve
*/
If you are running these backups, then your log shouldn't really be growing
any more. Are you sure the job is executing successfully? You're probably
fine without any other flags. The database does nock lock while backups are
in progress.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"steve" <steve@.nospam.com> wrote in message
news:%23GD1eZvNGHA.2920@.TK2MSFTNGP10.phx.gbl...
Hi everyone, i was hoping to obtain some help with my backup process.
Ideally I wish to make a weekly full backup, with a differential at the end
of each day, and transactionals every hour during business hours. My code is
below
note, each backup process (full, diff and trans) will be placed in a
separate job
-- Full backup scheduled on Monday at 2:00 AM
DECLARE @.str varchar(200)
SET @.str =
'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestFull\ZestLive'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12), 1, 5),
3, 1, '')
+ '.BAK'' '
EXEC (@.str)
-- Transactional backup scheduled every hour between 6AM and 11PM inclusive
DECLARE @.str varchar(200)
SET @.str =
'BACKUP LOG ZestLive TO DISK=''R:\BACKUP\ZestTLog\ZestLive'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12), 1, 5),
3, 1, '')
+ '.TRN'' '
EXEC (@.str)
-- Differential backup scheduled Tue, Wed, Thu, Fri, Sat, Sun at 2:00AM
DECLARE @.str varchar(200)
SET @.str =
'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestDiff\ZestLive'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12), 1, 5),
3, 1, '')
+ '.DIFF'' WITH DIFFERENTIAL'
EXEC (@.str)
My questions are:
1. I have noticed that my transaction log keeps growing. How do i keep it to
size with the above backup process. i.e. does the log shrink after taking a
transactional backup?
2. I noticed that there are other flags that can be appended to the above
backup commands. Do i really need them? i.e. INIT, NOINIT, STAT etc...
3. Will the database lock whilst performing transactional backups? I figure
the database will be locked during a full and differential backup.
Any help most appreciated.
Many thanks,
cheers, steve
*/
|||Just to extend Tom's comments a bit,
I wouldn't worry about backups. They are just a means. What you need to do
is create a restore and recovery strategy. Determine how much data loss and
down time during possible recovery scenarios (full server loss, data
corruption, multiple hard disk failure, etc.) is acceptable. Plan backups
(full, differential, and transaction log) to support your recovery strategy.
Read the BOL section on database backup and restore so you will understand
how SQL backup works. It is not the same as a file system backup. Treating
it the same for restore purposes will lead to disappointment when you
actually have to use a backup.
Finally, test your restore/recovery plans, especially the actual backup
files. Until you test the process, you have a hope, not a plan.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"steve" <steve@.nospam.com> wrote in message
news:%23GD1eZvNGHA.2920@.TK2MSFTNGP10.phx.gbl...
> Hi everyone, i was hoping to obtain some help with my backup process.
> Ideally I wish to make a weekly full backup, with a differential at the
> end of each day, and transactionals every hour during business hours. My
> code is below
> note, each backup process (full, diff and trans) will be placed in a
> separate job
> --
> -- Full backup scheduled on Monday at 2:00 AM
> DECLARE @.str varchar(200)
> SET @.str =
> 'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestFull\ZestLive'
> + RTRIM(CONVERT(varchar, GETDATE(), 112))
> + '_'
> + STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12), 1,
> 5), 3, 1, '')
> + '.BAK'' '
> EXEC (@.str)
> --
> -- Transactional backup scheduled every hour between 6AM and 11PM
> inclusive
> DECLARE @.str varchar(200)
> SET @.str =
> 'BACKUP LOG ZestLive TO DISK=''R:\BACKUP\ZestTLog\ZestLive'
> + RTRIM(CONVERT(varchar, GETDATE(), 112))
> + '_'
> + STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12), 1,
> 5), 3, 1, '')
> + '.TRN'' '
> EXEC (@.str)
> --
> -- Differential backup scheduled Tue, Wed, Thu, Fri, Sat, Sun at 2:00AM
> DECLARE @.str varchar(200)
> SET @.str =
> 'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestDiff\ZestLive'
> + RTRIM(CONVERT(varchar, GETDATE(), 112))
> + '_'
> + STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12), 1,
> 5), 3, 1, '')
> + '.DIFF'' WITH DIFFERENTIAL'
> EXEC (@.str)
> --
> My questions are:
> 1. I have noticed that my transaction log keeps growing. How do i keep it
> to size with the above backup process. i.e. does the log shrink after
> taking a transactional backup?
> 2. I noticed that there are other flags that can be appended to the above
> backup commands. Do i really need them? i.e. INIT, NOINIT, STAT etc...
> 3. Will the database lock whilst performing transactional backups? I
> figure the database will be locked during a full and differential backup.
> Any help most appreciated.
> Many thanks,
> cheers, steve
> */
>

Help with backup (Full, differential and transactional) process

Hi everyone, i was hoping to obtain some help with my backup process.
Ideally I wish to make a weekly full backup, with a differential at the end
of each day, and transactionals every hour during business hours. My code is
below
note, each backup process (full, diff and trans) will be placed in a
separate job
-- Full backup scheduled on Monday at 2:00 AM
DECLARE @.str varchar(200)
SET @.str =
'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestFull\ZestLive'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varc
har, GETDATE(), 113)), 12), 1, 5),
3, 1, '')
+ '.BAK'' '
EXEC (@.str)
-- Transactional backup scheduled every hour between 6AM and 11PM inclusive
DECLARE @.str varchar(200)
SET @.str =
'BACKUP LOG ZestLive TO DISK=''R:\BACKUP\ZestTLog\ZestLive'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varc
har, GETDATE(), 113)), 12), 1, 5),
3, 1, '')
+ '.TRN'' '
EXEC (@.str)
-- Differential backup scheduled Tue, Wed, Thu, Fri, Sat, Sun at 2:00AM
DECLARE @.str varchar(200)
SET @.str =
'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestDiff\ZestLive'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varc
har, GETDATE(), 113)), 12), 1, 5),
3, 1, '')
+ '.DIFF'' WITH DIFFERENTIAL'
EXEC (@.str)
My questions are:
1. I have noticed that my transaction log keeps growing. How do i keep it to
size with the above backup process. i.e. does the log shrink after taking a
transactional backup?
2. I noticed that there are other flags that can be appended to the above
backup commands. Do i really need them? i.e. INIT, NOINIT, STAT etc...
3. Will the database lock whilst performing transactional backups? I figure
the database will be locked during a full and differential backup.
Any help most appreciated.
Many thanks,
cheers, steve
*/If you are running these backups, then your log shouldn't really be growing
any more. Are you sure the job is executing successfully? You're probably
fine without any other flags. The database does nock lock while backups are
in progress.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"steve" <steve@.nospam.com> wrote in message
news:%23GD1eZvNGHA.2920@.TK2MSFTNGP10.phx.gbl...
Hi everyone, i was hoping to obtain some help with my backup process.
Ideally I wish to make a weekly full backup, with a differential at the end
of each day, and transactionals every hour during business hours. My code is
below
note, each backup process (full, diff and trans) will be placed in a
separate job
-- Full backup scheduled on Monday at 2:00 AM
DECLARE @.str varchar(200)
SET @.str =
'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestFull\ZestLive'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varc
har, GETDATE(), 113)), 12), 1, 5),
3, 1, '')
+ '.BAK'' '
EXEC (@.str)
-- Transactional backup scheduled every hour between 6AM and 11PM inclusive
DECLARE @.str varchar(200)
SET @.str =
'BACKUP LOG ZestLive TO DISK=''R:\BACKUP\ZestTLog\ZestLive'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varc
har, GETDATE(), 113)), 12), 1, 5),
3, 1, '')
+ '.TRN'' '
EXEC (@.str)
-- Differential backup scheduled Tue, Wed, Thu, Fri, Sat, Sun at 2:00AM
DECLARE @.str varchar(200)
SET @.str =
'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestDiff\ZestLive'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varc
har, GETDATE(), 113)), 12), 1, 5),
3, 1, '')
+ '.DIFF'' WITH DIFFERENTIAL'
EXEC (@.str)
My questions are:
1. I have noticed that my transaction log keeps growing. How do i keep it to
size with the above backup process. i.e. does the log shrink after taking a
transactional backup?
2. I noticed that there are other flags that can be appended to the above
backup commands. Do i really need them? i.e. INIT, NOINIT, STAT etc...
3. Will the database lock whilst performing transactional backups? I figure
the database will be locked during a full and differential backup.
Any help most appreciated.
Many thanks,
cheers, steve
*/|||Just to extend Tom's comments a bit,
I wouldn't worry about backups. They are just a means. What you need to do
is create a restore and recovery strategy. Determine how much data loss and
down time during possible recovery scenarios (full server loss, data
corruption, multiple hard disk failure, etc.) is acceptable. Plan backups
(full, differential, and transaction log) to support your recovery strategy.
Read the BOL section on database backup and restore so you will understand
how SQL backup works. It is not the same as a file system backup. Treating
it the same for restore purposes will lead to disappointment when you
actually have to use a backup.
Finally, test your restore/recovery plans, especially the actual backup
files. Until you test the process, you have a hope, not a plan.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"steve" <steve@.nospam.com> wrote in message
news:%23GD1eZvNGHA.2920@.TK2MSFTNGP10.phx.gbl...
> Hi everyone, i was hoping to obtain some help with my backup process.
> Ideally I wish to make a weekly full backup, with a differential at the
> end of each day, and transactionals every hour during business hours. My
> code is below
> note, each backup process (full, diff and trans) will be placed in a
> separate job
> --
> -- Full backup scheduled on Monday at 2:00 AM
> DECLARE @.str varchar(200)
> SET @.str =
> 'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestFull\ZestLive'
> + RTRIM(CONVERT(varchar, GETDATE(), 112))
> + '_'
> + STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varc
har, GETDATE(), 113)), 12), 1,
> 5), 3, 1, '')
> + '.BAK'' '
> EXEC (@.str)
> --
> -- Transactional backup scheduled every hour between 6AM and 11PM
> inclusive
> DECLARE @.str varchar(200)
> SET @.str =
> 'BACKUP LOG ZestLive TO DISK=''R:\BACKUP\ZestTLog\ZestLive'
> + RTRIM(CONVERT(varchar, GETDATE(), 112))
> + '_'
> + STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varc
har, GETDATE(), 113)), 12), 1,
> 5), 3, 1, '')
> + '.TRN'' '
> EXEC (@.str)
> --
> -- Differential backup scheduled Tue, Wed, Thu, Fri, Sat, Sun at 2:00AM
> DECLARE @.str varchar(200)
> SET @.str =
> 'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestDiff\ZestLive'
> + RTRIM(CONVERT(varchar, GETDATE(), 112))
> + '_'
> + STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varc
har, GETDATE(), 113)), 12), 1,
> 5), 3, 1, '')
> + '.DIFF'' WITH DIFFERENTIAL'
> EXEC (@.str)
> --
> My questions are:
> 1. I have noticed that my transaction log keeps growing. How do i keep it
> to size with the above backup process. i.e. does the log shrink after
> taking a transactional backup?
> 2. I noticed that there are other flags that can be appended to the above
> backup commands. Do i really need them? i.e. INIT, NOINIT, STAT etc...
> 3. Will the database lock whilst performing transactional backups? I
> figure the database will be locked during a full and differential backup.
> Any help most appreciated.
> Many thanks,
> cheers, steve
> */
>

Friday, March 23, 2012

Help with an error message

I keep getting this error message when I try to restore a database backup that I have made.

System.Data.SqlClient.SqlError: Directory lookup for the file "D:\Databases\GTT_Data.MDF" failed with the operating system error 5(error not found). (Microsoft.SqlServer.Express.Smo)

What I have done is created a backup of my sql sever database and I am now trying to restore it into sql server express for someone to play around with without it affecting my actual database. This is where the error messages are coming from.

Please help

Are you getting this during the restore ? If the machines are not the same for backup and restore you might not have the drives / folders available as they are on the original system. If so you either have to create those folders or Restore the database with the WITH MOVE option to specify a new destination. (thats because the original location is tored in the backup file).

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Yes its during the restore. Yes the machines are not the same. Please could you give me further details on how to restore the databse with the WITH MOVE option.

Thanks for your help

|||Hi,

look in your BOL (Books online, the Online help of SQL Server)

There is a point Restore a database and move Files which will help you

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/877ecd57-3f2e-4237-890a-08f16e944ef1.htm

RESTORE DATABASE MyAdvWorks
FROM MyAdvWorks_1
WITH NORECOVERY,
MOVE 'MyAdvWorks' TO
'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.mdf',
MOVE 'MyAdvWorksLog1'
TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.ldf'
RESTORE LOG MyAdvWorks
FROM MyAdvWorksLog1
WITH RECOVERY

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Monday, March 12, 2012

Help w/backup jobs

Friday, March 9, 2012

Help w/backup jobs

Sunday, February 26, 2012

Help SVP: How to connect to external server and backup db with SSMSExpress

Bonjour
You'll have to be gentil with me, because until today I had never worked with MS SQL (I was developing with Access).

Scenario & question:
I have installed a phpBB forum running on MS SQL on my clients server. Since Saturday this forum just won't load anymore. I have tested the connection to the SQL database within Macromedia Dreamweaver and it's fine. I can view the table and the data contained in them. I would like to re-install phpBB but I need to back up the db first.

I have installed SQL SERVER MANAGEMENT STUDIO EXPRESS, and all the other things like .NET 2.0 etc. It seems to work, as I can look at local SQL database samples, their table and data on my PC running XP Professional.

Question 1: How can connect to the external database? I have all the info necessary like ip address, name of the db, user name and password; but I can't seem to find a way to create an external connection from within the SSMSExpress. Could you please help.
Question 2: Once I am connected how can I backup the SQL db?

Many thanks in advance

When you run SSMS Express, it should give you a login screen where you can pick the server you want to log in to. You can also add servers in the Registered Servers windows.

To backup, you can go to Databases in the Object Explorer, select the database you want, right click on it and choose backup.

Thanks< MJ

|||

Thanks, done that and it works

Help SVP: How to connect to external server and backup db with SSMSExpress

Bonjour
You'll have to be gentil with me, because until today I had never worked with MS SQL (I was developing with Access).

Many thanks in advance

When you run SSMS Express, it should give you a login screen where you can pick the server you want to log in to. You can also add servers in the Registered Servers windows.

To backup, you can go to Databases in the Object Explorer, select the database you want, right click on it and choose backup.

Thanks< MJ

|||

Thanks, done that and it works

Help SVP: How to connect to external server and backup db with SSMSExpress

Bonjour
You'll have to be gentil with me, because until today I had never worked with MS SQL (I was developing with Access).

Many thanks in advance

When you run SSMS Express, it should give you a login screen where you can pick the server you want to log in to. You can also add servers in the Registered Servers windows.

To backup, you can go to Databases in the Object Explorer, select the database you want, right click on it and choose backup.

Thanks< MJ

|||

Thanks, done that and it works

Friday, February 24, 2012

Help scripting backup devices

Hi guys

I am relatively new to SQL Server admin and I have been handed a task of creating backup devices for a particular SQL Server which has 204 databases.

Rather than go through and create Full, Incramental and Transaction Log backup devices for each database is ther anyway of doing the following

Create a script to go through and for each database name in 'sysdatabases'

create a folder e.g \sqldata\backup\%databasename%

and within each folder create a backup device called

%databasename%_full
%databasename%_inc
%databasename%_log

Also all these databases are running in 'Simple' recovery mode so obviously I need to change this to 'Full' to enable incramental and log backups - is this possible using the same script.

Hope someone can help as the thought of doing all of this individually for each database scares me silly!!! :)

Thanks in advance for any help

Hanleynot sure if this is an option but could you create a database maintenance plan inwhich the option 'All databases' is set and does the backup?|||I could do that but I need to create the backup devices first, that is my problem, I need to automate the creation of 3 backup devices for each database (240 of them)

:)|||This is 100% untested, but it should give you an idea or two:DECLARE @.cDb sysname

DECLARE zDb CURSOR FOR SELECT
sd.name
FROM master.dbo.sysdatabases AS sd

OPEN zDb
FETCH zDb INTO @.cDb

WHILE 0 = @.@.fetch_status
BEGIN
EXECUTE ('EXECUTE master.dbo.xp_cmdshell ''mkdir z:\sqldata\backup\'
+ @.cDb + '''')

EXECUTE ('EXECUTE sp_adddumpdevice ''disk'', '''
+ @.cDb + '_full'', ''z:\sqldata\backup\' + @.cDb + '_full.dmp''')
EXECUTE ('EXECUTE sp_adddumpdevice ''disk'', '''
+ @.cDb + '_inc'', ''z:\sqldata\backup\' + @.cDb + '_inc.dmp''')
EXECUTE ('EXECUTE sp_adddumpdevice ''disk'', '''
+ @.cDb + '_log'', ''z:\sqldata\backup\' + @.cDb + '_log.dmp''')

EXECUTE ('ALTER DATABASE ' + @.cDb + ' SET RECOVERY FULL')

FETCH zDb INTO @.cDb
END

CLOSE zDb
DEALLOCATE zDb-PatP|||Pat P

Thanks very much for that

I'll give it a try on Monday

Much appreciated

:)|||Pat P

Script was successful, exactly what I was looking for.

Many thanks

Hanley

:) :)

Tuesday, March 27, 2012

Help with Bkup jobs

Help with Bkup jobs

Help with Bkup jobs

Monday, March 26, 2012

Help with BCP

Help with BAK file

Help with backup strategy

Help with backup strategy

Help with backup strategy

Help with backup plan

Help with backup (Full, differential and transactional) process

Help with backup (Full, differential and transactional) process

Help with backup (Full, differential and transactional) process

Friday, March 23, 2012

Help with an error message

Monday, March 12, 2012

Help w/backup jobs

Friday, March 9, 2012

Help w/backup jobs

Help w/backup jobs

Sunday, February 26, 2012

Help SVP: How to connect to external server and backup db with SSMSExpress

Help SVP: How to connect to external server and backup db with SSMSExpress

Help SVP: How to connect to external server and backup db with SSMSExpress

Friday, February 24, 2012

Help scripting backup devices

Restore Database

Blog Archive

About Me