SQL Server backup solution

SQL Server backup solution

In order to reduce data loss caused by database server problems, the database is generally backed up, depending on the importance of data and performance compromise using different backup solutions.
The general backup solution is:
Global backup (once a week) + Incremental Backup (once a day) + transaction log backup (once every two hours, depending on the importance of data)
In this case, even if a problem occurs, the data loss range is two hours.

Global backup:
Declare @ Backpath Nvarchar ( 1000 )
Set @ Backpath = N ' C: \ Program Files \ Microsoft SQL Server \ mssql.4 \ MSSQL \ backup \ testdb \ testdb_backup_200907281015.bak '
Declare @ Backname Nvarchar ( 200 )
Set @ Backname = N ' Testdb_backup_20090728101556 '

Backup Database [Testdb] ToDisk = @ Backpath 
WithNoformat, noinit, name=, Skip, rewind, nounload, stats= 10

Incremental Backup:
Declare @ Backpath Nvarchar ( 1000 )
Set @ Backpath = N ' C: \ Program Files \ Microsoft SQL Server \ mssql.4 \ MSSQL \ backup \ testdb \ testdb_backup_200907281017.bak '
Declare @ Backname Nvarchar ( 200 )
Set @ Backname = N ' Testdb_backup_20090728101729 '

Backup database [ testdb ] to disk = @ backpath
with differential, noformat, noinit, name = @ backname , Skip, rewind, nounload, stats = 10

Truncate logs during Incremental backup to prevent excessive logs.--Transaction Log truncation:

Backup LogNorthwind
WithNo_log

--Shrink log files:

DBCCShrinkdatabase (northwind)

Back up transaction logsBackup LogNorthwindcs
To Disk='G: \ backup \ northwindcs_log_20070908.bak' 
WithNo_truncate

Perform operations on different frequencies.

If there are different databases on the same server, it is obviously inappropriate to perform the above jobs on each data. In this way, you can use the SQL server2005 maintenance plan to maintain all databases.