I faced an issue during restoration of very large db in our development database \ server. Issue is restoration takes very long time even though we had latest hardware and recent version of SQL Server (it’s SQL 2008 Ent. Ed). So to troubleshoot it I have used the trace flag 3004 which print all internal things happen during backup and restoration operation.
Tag Archives: Backup and Restore
Troubleshoot backup \ restore issues with trace flag 3004
Posted by Vidhya Sagar
on April 19, 2011
No comments
SQL Backup on NTFS Compressed folder
Posted by Vidhya Sagar
on May 3, 2009
5 comments
I’m starting this topic since last week I had faced a backup issue with compressed drive. In one of our server we have used compressed folder to take backup of our SQL databases. This is working fine and the db is used for OLTP application and it had grown about 73GB. Now we have started facing backup failures only for that database alone all other databases (less than 10GB) are running fine in the maintenance plan. I’ve checked the machine performance and SQL Server is the only process using 50% of CPU and 3GB of memory, other than that the server is not under stress and in fact that box is dedicated to SQL Server.
Backup Script with Retention
Posted by Vidhya Sagar
on June 28, 2008
Comments Off
Introduction:
SQL Backup script is available in all online resources. I couldn’t find a script to take care of retention also, so I’m writing this script as procedure to take care of retention too. This script will take full backup of all the databases.
Applies to:
- SQL Server 2000
- SQL Server 2005
- SQL Server 2008
Pre-requisites:
- Xp_cmdshell procedure should be enabled both in SQL 2005 & SQL 2008
Script:
Dowload the script HERE
Usage:
EXEC master.dbo.usp_backup ‘F:\Bkup\’,1
Output:
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
$ SERVERNAME: SAGARSYS — DATE: Jun 28 2008 10:00AM $
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
Retention
^^^^^^^^^
Deleting the below backup files as part of retention plan……
output
———————————————————
master_20080627.bak
model_20080627.bak
msdb_20080627.bak
NULL
output
———————————————————-
NULL
Database Backups Started
^^^^^^^^^^^^^^^^^^^^^^^^
************Processing master Backup… **************
Processed 352 pages for database ‘master’, file ‘master’ on file 1.
Processed 3 pages for database ‘master’, file ‘mastlog’ on file 1.
BACKUP DATABASE successfully processed 355 pages in 0.747 seconds (3.887 MB/sec).
Backed up to F:\Bkup\master_20080628.bak
********************************************************
************Processing model Backup… **************
Processed 152 pages for database ‘model’, file ‘modeldev’ on file 1.
Processed 2 pages for database ‘model’, file ‘modellog’ on file 1.
BACKUP DATABASE successfully processed 154 pages in 0.344 seconds (3.655 MB/sec).
Backed up to F:\Bkup\model_20080628.bak
********************************************************
************Processing msdb Backup… **************
Processed 608 pages for database ‘msdb’, file ‘MSDBData’ on file 1.
Processed 2 pages for database ‘msdb’, file ‘MSDBLog’ on file 1.
BACKUP DATABASE successfully processed 610 pages in 0.874 seconds (5.712 MB/sec).
Backed up to F:\Bkup\msdb_20080628.bak
********************************************************
============Backup Completed Successfully============
Discussion:
All your comments are highly appreciated, please post your comments @ FORUMS section