Running backups and restores is a pretty straightforward process, but issues do arise on occasion. The following is a list of five common problems and steps to help you resolve them.
Writing or reading backups from a share
In the context of your responsibilities on the job, you probably need to write backups to a file share or directory on another server. When using SQL Server's Enterprise Manager, only the local hard drives on the server are exposed. Mapped drives do not appear, nor do drives on other servers that may exist in the network.
To write your backup to another server you need to use the UNC path instead of the hard drive letter. For example, instead of typing "G:\Northwind.BAK" you would specify the server name and path like so: \\TestServer\G$\Northwind.BAK". This can be done both with Enterprise Manager and in your backup command as follows:
BACKUP DATABASE Northwind
TO DISK '\\TestServer\G$\Northwind.BAK/'
[IMAGE]
To restore a backup, you would do the same thing. Just reference the file using the UNC path instead of the drive letter.
Accessing file systems
One of the biggest obstacles you'll encounter when creating a backup file is not having the proper access rights to the file system. When you log in to Enterprise Manager or Query Analyzer and execute the backup command, it uses the credentials of the service accounts, not the rights that you have as a user. Even if you have the necessary rights to add or delete files, it does not mean the service account does.
When running a command without sufficient rights you will see this error:
Cannot open backup device '\\TestServer\G$\Test.bak'. Device error or device off-line. See the SQL Server error log for more details.
To check the rights, you may use XP_CMDSHELL and run a directory command to determine whether you can see the directory conte...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
nts or get access denied.
xp_cmdshell 'dir \\TestServer\G$'
If the service account does not have access you will get an "Access is denied" message -- otherwise you will see the directory contents.
Make sure your service accounts have the appropriate access levels or change to a share where the account does have the necessary rights to create new files.
Matching logins and users after restoring from another server
When restoring backups from another server, you will most likely find mismatched login and user information. The login information is stored in the sysxlogins table in the master database. This table contains a column that holds the SID (security identifier), which is tied to a specific login. A corresponding table, sysusers, stored in each user database employs the SID to determine if a login has database access. You may already have the same logins on your new server, but the SID may not match. What's more confusing is that you can see the actual names of the logins and users, so you would think they automatically match up, but it is the SID that ties the security together -- not the name.
To reconnect the users and logins, refer to my tip, Restoring a database from another SQL Server, which describes how to resolve this problem.
Restoring the wrong backup
Multiple backups can be written to one file using the NOINT parameter. This can be done using Enterprise Manager (append) or from T-SQL. The backups are then sequentially numbered within this backup file. When doing a restore using Enterprise Manager, you do not have the option to select which backup you want to restore. It just assumes you want to restore data from the last full backup and then any differential or transaction logs that occurred after that.
If you want to go further back in the file and restore an earlier backup, you must use T-SQL. First, determine what is in the file using this command:
RESTORE HEADERONLY
FROM DISK = N'\\TestServer\G$\Tran.BAK'
Once you know what is in the file, specify the backup you want to restore using this command:
RESTORE DATABASE Northwind
FROM DISK = N'\\TestServer\G$\Tran.BAK'
WITH FILE = 4
Getting the database out of LOADING state
When restoring multiple backups, each backup needs to use the NORECOVERY option to keep the database in a loading state so additional backups can be restored. However, because the database is still in LOADING, you may encounter an unusable transaction log backup, leaving you unable to restore the file. To reset the status of the database, you need to use T-SQL to issue a BACKUP command:
RESTORE DATABASE Northwind
WITH RECOVERY
This command resets the status of the database and allows users to access it. No additional restores can be issued after this command is executed.
These are just a few of the issues that you may encounter during backup and restore practices. Plenty of other problems may occur, too, so look for future tips to solve SQL Server backup issues.
About the author: Greg Robidoux is the president and founder of Edgewood Solutions LLC, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and has delivered presentations at regional SQL Server users' groups and national SQL Server events. Robidoux, who also serves as the SearchSQLServer.com Backup and Recovery expert, welcomes your questions.
More information from SearchSQLServer.com
Tip: Worst practices for SQL Server backup and recovery
Tip: Restoring a database from another SQL Server
RSS: Sign up for our RSS feed to receive expert advice everyday
