:::: MENU ::::

Posts Categorized / Backup and Restore

  • Jun 09 / 2010
  • 0
Backup and Restore, dbDigger, Encryption, Security and Permissions, T-SQL Scripts

The backup set holds a backup of a database other than the existing ‘*’ database.

Recently i applied password protected backups for my SQL Server databases as explained in my article Using passwords with SQL Server database backup files. Backups and file removal task worked successfully in test environment. Before moving the encrypted backups setup to production systems i just performed a hands on activity to be smooth with restore process of password protected backups.
I created password protected full backup of AdventureWorks with following script to work with restore scenarios.

After creating backup successfully in ‘D:Testing’ directory, i planned it to restore over an existing database AdvTest.


It is notable that password protected backups may not be restored through SSMS, so we have to use T-SQL scripts for this purpose.

I got password used for backup by following select statement

select replace(convert(varchar, getdate(), 102),'.','-')+'-MSSQLTips'

Using following script, i tried to restore AdventureWorks backup on existing AdvTest database

Use master
FROM DISK='D:TestingAdventureWorks-2010-05-31 15-04-53.bak'

Restoration of password protected backup failed on existing database failed with following error message
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing ‘AdvTest’ database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

After some research on issue i came to know a work around. It is required to use WITH REPLACE option for restore of backup on another database with different name. So i modified my script as

Use master

FROM DISK='<span style="color: #3333ff; font-family: courier new; font-size: small;">D:TestingAdventureWorks-2010-05-31 15-04-53.bak</span><span style="color: blue; font-family: 'Courier New',Courier,monospace; font-size: small;">'
WITH REPLACE, MEDIAPASSWORD='</span><span style="color: #3333ff; font-family: courier new; font-size: small;">2010-05-31-MSSQLTips</span><span style="color: blue; font-family: 'Courier New',Courier,monospace; font-size: small;">',
MOVE 'AdventureWorks_data' TO 'D:DB FilesAdvTest.mdf',
MOVE 'AdventureWorks_log' TO 'D:DB FilesAdvTest_log.ldf'

Here i have specified the data and log files of source and target. It is important to note that if database is being restored on same database to which it belongs then move option is not required to be specified.
For example if we are required to restore our AdventureWorks backup on AdventureWorks database then following script would work

Use master

FROM DISK=’D:TestingAdventureWorks-2010-05-31 15-04-53.bak‘

To get logical and physical file names of any database for use in above scripts, use following command

Use AdvTest

  • Apr 29 / 2010
  • 0
Backup and Restore, dbDigger, SSMS tips and tricks

Managing frequent restore of last backup through SSMS

While connected to test server through SQL Server management studio (SSMS), i have to restore any particular database several times. It is time consuming to browse and select the backup file each time. If you have same problem then consider the restore of last backup by this way.
SQL Server provides option to restore last backup of any database on same server without knowing the name or location of backup file. This method can be used in test environments when excessive restores are being performed for same backup. Follow these steps

  • Click on database on which backup is to be restored, or even you can do this by database which is to be restored. A database can be restored on itself. In my case it is last backup of AdventureWorks that i would restore on itself

Sleect DB for easy restore of last backup

  • A frame would appear select database on which back is to be restored. As i mentioned before it may be any database on your server. Even you can create new one from here. Also select the database whose backup is required to be restored. Automatically list of last backups would appear in frame. Select the backup that is required to be restored. It may be full, differential or last backup.
  • Also have a look at options pane of frame to configure any parameter.

Configure easy restore of last backup

At the end click the Ok button and last backup of selected database would be restored at required database.
This method would prevent you to keep track of last backup. But make sure that backup file is located at place where it was created.

  • Apr 28 / 2010
  • 0
Changing passwords for logins used with SQL Server maintenance plans
Backup and Restore, dbDigger, Encryption, Publications of Atif Shehzad on MSSQLTips.com, Security and Permissions

Configure SQL server password protected backups creation and cleanup

Securing the backup and data files is more important than to establish optimal rights and privileges for database users. SQL Server backup file contains data and log files and it can easily be restored once in hand. It is critical to device a mechanism where backup file should be safe at disk and also another layer of protection is required in backup file itself.
Several tools can be found for backup file encryption, but i have discussed an SQL Server facility to create password protected backup files. It may be used with SQL Server 2000/2005/2008. You can read full article on this topic here.
There are two considerations in this regard

  • The applied password encryption on backup file would not be as reliable as in strongly encrypted files
  • This feature may be removed in future versions of SQL Server, so use it till SQL Server 2008 only
  • May 26 / 2009
  • 1
Backup and Restore, dbDigger, Indexes, Performance Tunning and Optimization

Change recovery model of SQL Srever database before index rebuild

Index rebuild may produce a lot of transaction logs. Hence it may cause of heavily populate your log file. So it would be better option to change recovery model of that specific database to simple before index rebuild process in it. Once index rebuild process is finished database may again be reverted to recovery model full. Assuming that AdventureWorks is in single user mode, we may run following commands to change and revert back the recovery model of a database for index rebuild operation.

USE AdventureWorks  
 -- Set recovery mode to simple before index rebuild  
 ALTER DATABASE AdventureWorks  
 SET recovery SIMPLE  
 -- Begin index rebuild process for selected tables  
 DBCC DBREINDEX ("HumanResources.Employee",'',90)  
 -- Set recovery model back to full  
 ALTER DATABASE AdventureWorks  
 SET recovery FULL  

While implementing above mentioned procedure make sure to reattain your backup sequence as without a new full backup and later log backup you will not be able to recover to the point if required. The reason is that backup sequence of log becomes disturb while changing recovery model.

  • May 13 / 2009
  • 0
Analysis Services SSAS, Backup and Restore, dbDigger

Backup and Recovery plans for SQL Server Analysis Services

Before you back up your Analysis Services data, you must ensure that Analysis Services is not processing any dimensions, partitions, or mining models. Because Analysis Services performs some processing tasks as background processes, determining when all processing has been completed can sometimes be difficult. Also, you must ensure that another administrator is not changing any of the meta data while you are performing a backup. One way to ensure Analysis Services is quiescent is to stop Analysis Services before you perform the backup
Backup Options Analysis Services provides two techniques for backing up an Analysis Services database: archiving and copying files.


You can archive an Analysis Services database and the repository to one or more .cab file using the msmdarch command (msmdarch.exe), either from within Analysis Manager or from a command prompt. Msmdarch uses .cab storage algorithms, which limits the size of any single .cab file to 2 GB. As a result, no individual file in the Data folder (such as any single partition) can exceed 2 GB, or else msmdarch cannot be used for backup.
When using msmdarch, always specify a log file location to capture any messages generated during the archive process. If the archive process fails, these messages can help you determine why the archive process failed. However, msmdarch does not back up the query log. To back up the query log, perform a file-based backup of the SMDQLOG.mdb file. If you do not, a new query log is created from scratch when you start a restored instance.

Copying Files

You can use Analysis Manager to copy the meta data for an Analysis Services database from one instance of Analysis Services to another Analysis Services instance, provided that both instances are registered in Analysis Manager. Because only the meta data is copied to the target server using this method for release management, you will need to process the Analysis Services database on the destination server (after updating the data source properties, if necessary) before users can query the data in the new location. Because copying and pasting is so easy and quick, and because you are frequently only working with a subset of data in the development environment, copying and pasting is generally the quickest way to deploy an Analysis Services database on a different server. The downside is that it requires all of the dimensions, cubes, and partitions to be processed (which must be incurred anyway if the data sets are different). To determine your preferred approach, you need to compare the time and overhead of fully reprocessing the database with the msmdarch.exe archive and restore time. In most cases, full reprocessing is the fastest method. But you will find that it varies based on the underlying infrastructure.

If you cannot use msmdarch, you can use a file copy program, such as Windows Backup, to back up all the files in the Data folder. With a file copy backup, you back up all databases on the server. With msmdarch, you can back up a single Analysis Services database. In addition, the file copy technique does not back up the repository or the query log file. If you use the file copy technique, you must back up the repository at the same
time you back up the Data folder, to ensure that the repository and the data in the Data folder remain synchronized. You must also back up the query log (the SMDQLOG.mdb file), or begin capturing query information from scratch. While it is possible to back up just an individual database (by copying the contents of its Data sub folder and the .DBO file). You cannot back up individual portions of the repository. The repository is needed on a full restore, or if the meta data has changed since the last backup.

File-Based Backup and Restore

If neither of the previous two methods is suitable for your situation, you can also use a file-based backup program to back up the entire Data folder and then restore it to the destination folder. With this method, you must deploy all databases within an Analysis Services instance, rather than a single database. If you use this method, you must also back up the repository and then restore the repository on the destination server. While the repository is technically not required to run the OLAP service, it is required to run Analysis Manager and thus to properly administer the server. The meta data in the repository must match the contents and structure of the Data folder. If you have data in remote partitions (a feature that is rarely used) and in writeback tables, you must first back up the data in remote partitions using a file-based backup method and the writeback tables using SQL Server backup, and then restore them before you bring your database back online.

Consult us to explore the Databases. Contact us