Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Backup SQL 2008 Small DB-Need Profesional Help Expand / Collapse
Author
Message
Posted Saturday, June 15, 2013 7:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 22, 2014 4:36 AM
Points: 198, Visits: 668
drnorbert (6/15/2013)

This is a great solution.
This command are execute in a query?

It can be executed in a query to test it. First you would need to create the folders on the machines & share them with read/write access.
Once you've tested it successfully (for database & transaction log backups) then you can create a Maintenance Plan that will backup to those servers the databases (full; every night) and transaction logs (every 15 minutes).

drnorbert (6/15/2013)

No SMTP server. Can we use an live.com?
I can't find YouTube for configuring the Mail feature.

It seems you can. Read this page for details on setting up the Mail feature to send to your live.com account.



Dird // Junior DBA
11g OCA
10.5 newbie
Post #1463919
Posted Sunday, June 16, 2013 6:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, June 8, 2014 7:15 AM
Points: 12, Visits: 44
Hi Dird
I am trying to figure out how you are attaching my responses with your reply massages as well as the images such as:


Thanks for the details about setting up the Mail with live.com.

I am executing the query as per your instructions:

1)
Execute:
BACKUP DATABASE ISMDATA TO DISK='C:\ISMBACKUP\db.bak'

Message:
Processed 273864 pages for database 'ISMDATA', file 'ISMDATA_dat' on file 1.
Processed 1 pages for database 'ISMDATA', file 'ISMDATA_log' on file 1.
BACKUP DATABASE successfully processed 273865 pages in 58.470 seconds (36.592 MB/sec).

Is this means that the log is backup together with each execution or is a different command for the transaction log every 15 min?

2)
When add MIRROR I am getting an error msg.
Execute:
BACKUP DATABASE ISMDATA TO DISK='C:\ISMBACKUP\db.bak'
MIRROR TO DISK='\\Guardiavieja\ISMDATABU\db.bak'

Message:
Msg 3215, Level 16, State 1, Line 1
Use WITH FORMAT to create a new mirrored backup set.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

3)
Do I need to shrink the DB?
Post #1463947
Posted Sunday, June 16, 2013 6:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 22, 2014 4:36 AM
Points: 198, Visits: 668
drnorbert (6/16/2013)

BACKUP DATABASE ISMDATA TO DISK='C:\ISMBACKUP\db.bak'

Is this means that the log is backup together with each execution or is a different command for the transaction log every 15 min?

I don't understand your question. The statement you ran backed up the database. To backup the transaction log you would need to use a different command (BACKUP LOG) to a different file on disk. You will need to use a scheduled job to backup them automatically on a regular basis (e.g. database backup 1 time per day; log backup every 15 minutes).

drnorbert (6/16/2013)

BACKUP DATABASE ISMDATA TO DISK='C:\ISMBACKUP\db.bak'
MIRROR TO DISK='\\Guardiavieja\ISMDATABU\db.bak'

Message:
Use WITH FORMAT to create a new mirrored backup set.

Because you already used db.bak for a non-mirrored backup you must recreate the formatting. The first time you run it use:
BACKUP DATABASE ISMDATA TO DISK='C:\ISMBACKUP\db.bak'
MIRROR TO DISK='\\Guardiavieja\ISMDATABU\db.bak' WITH FORMAT

After the first time you can remove the WITH FORMAT option.

drnorbert (6/16/2013)
Do I need to shrink the DB?

No.

Edit: To attach the text you click the "Quote" button instead of "Reply" but then I make them shorter by adding extra (quote) (/quote) statements and deleting text that isn't important.



Dird // Junior DBA
11g OCA
10.5 newbie
Post #1463949
Posted Sunday, June 16, 2013 6:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, June 8, 2014 7:15 AM
Points: 12, Visits: 44
Dird (6/16/2013)
drnorbert (6/16/2013)

BACKUP DATABASE ISMDATA TO DISK='C:\ISMBACKUP\db.bak'

Is this means that the log is backup together with each execution or is a different command for the transaction log every 15 min?

I don't understand your question. The statement you ran backed up the database. To backup the transaction log you would need to use a different command (BACKUP LOG) to a different file on disk. You will need to use a scheduled job to backup them automatically on a regular basis (e.g. database backup 1 time per day; log backup every 15 minutes).

drnorbert (6/16/2013)

BACKUP DATABASE ISMDATA TO DISK='C:\ISMBACKUP\db.bak'
MIRROR TO DISK='\\Guardiavieja\ISMDATABU\db.bak'

Message:
Use WITH FORMAT to create a new mirrored backup set.

Because you already used db.bak for a non-mirrored backup you must recreate the formatting. The first time you run it use:
BACKUP DATABASE ISMDATA TO DISK='C:\ISMBACKUP\db.bak'
MIRROR TO DISK='\\Guardiavieja\ISMDATABU\db.bak' WITH FORMAT

After the first time you can remove the WITH FORMAT option.

drnorbert (6/16/2013)
Do I need to shrink the DB?

No.

Edit: To attach the text you click the "Quote" button instead of "Reply" but then I make them shorter by adding extra (quote) (/quote) statements and deleting text that isn't important.
Post #1463950
Posted Sunday, June 16, 2013 6:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, June 8, 2014 7:15 AM
Points: 12, Visits: 44
Thanks Dird you helped me a lot.
Can I get your contact info in case I need your professional services?
Post #1463951
Posted Sunday, June 16, 2013 6:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 22, 2014 4:36 AM
Points: 198, Visits: 668
I sent my email in a PM but you can just reply to this thread again & I will receive email notifications anyways


Dird // Junior DBA
11g OCA
10.5 newbie
Post #1463952
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse