SQL server, SQLServerPedia Syndication

SQL backup mirroring (and splitting).

Did you know that you can mirror your SQL backups? This is good for those times when you need multiple backup copies. Like one local copy and another one on a network share.

You can even split backups over multiple files. If you have an IO bottleneck during backup, or if you are low on disk space, this backup splitting feature will come in handy. Below you can see my tests.

The backup file is 5.2 GB in size.

1)     Doing a normal backup:

backup database backupdb_dev to disk=’c:\dba\db_normal_bakup.bak’

Processed 656248 pages for database 'backupdb_dev', file 'Backupdb' on file 1.
Processed 2 pages for database 'backupdb_dev', file 'Backupdb_log' on file 1.
BACKUP DATABASE successfully processed 656250 pages in 67.109 seconds (76.397 MB/sec).

 

2)     Backuping up to two files on the same spindle:

backup database backupdb_dev

to disk=’c:\dba\db_bakup_spindle1_file1.bak’,

disk=’c:\dba\db_bakup_spindle1_file2.bak’

Processed 656248 pages for database ‘backupdb_dev’, file ‘Backupdb’ on file 1.

Processed 1 pages for database 'backupdb_dev', file 'Backupdb_log' on file 1.
BACKUP DATABASE successfully processed 656249 pages in 63.322 seconds (80.966 MB/sec).
Each backup file was 2.6 GB in size.

 

3)     Backing up to two files on two different spindles:

backup database backupdb_dev

to disk=’c:\dba\db_bakup_spindle1_file1.bak’,

disk=’f:\dba\db_bakup_spindle2_file1.bak’

Processed 656248 pages for database 'backupdb_dev', file 'Backupdb' on file 1.
Processed 1 pages for database 'backupdb_dev', file 'Backupdb_log' on file 1.
BACKUP DATABASE successfully processed 656249 pages in 62.775 seconds (81.671 MB/sec).

 

Theoratically, the backups

4)     Doing a backup and mirroring to the same spindle.

backup database backupdb_dev

to disk=’c:\dba\db_backup_mirror1.bak’

mirror to disk=’c:\dba\db_backup_mirror2.bak’

with format

Processed 656248 pages for database 'backupdb_dev', file 'Backupdb' on file 1.
Processed 1 pages for database 'backupdb_dev', file 'Backupdb_log' on file 1.
BACKUP DATABASE successfully processed 656249 pages in 95.500 seconds (53.685 MB/sec).

Both files were 5.2 GB in size. No surprise here. The backup took only 50% longer.

5)      Doing a backup and mirroring to different spindles.

backup database backupdb_dev

to disk=’c:\dba\db_backup_mirror1.bak’

mirror to disk=’f:\dba\db_backup_mirror2.bak’

with format

Processed 656248 pages for database ‘backupdb_dev’, file ‘Backupdb’ on file 1.

Processed 1 pages for database 'backupdb_dev', file 'Backupdb_log' on file 1.
BACKUP DATABASE successfully processed 656249 pages in 97.538 seconds (52.563 MB/sec).
Standard
SQL server

Moving a database between SQL instances.

Moving a DB between two SQL instances can be done with a simple backup and restore, but there are a number of dependencies on the DB that are required to be working to have a full cycle connection between the end user and the DB. One example is the server level logins which are not moved with the DB. This data is saved in the master DB and will have to be explicitly copied over and linked with the DB users in the new instance. Another example is the SQL jobs there were running on the first instance. These jobs are saved in MSDB  and need to be recreated on the second instance. There are others like encryption keys, full text settings, etc.

Below is a list of entities/objects that are stored outside the DB:

  • Server configuration settings
  • Credentials
  • Cross-database queries
  • Distributed queries/linked servers
  • Encrypted data
  • User-defined error messages
  • Event notifications and Windows Management Instrumentation (WMI) events (at server level)
  • Extended stored procedures
  • Full-text engine for SQL Server (MSFTESQL) service properties
  • Jobs
  • Logins
  • Permissions
  • Replication settings
  • Service Broker applications
  • Startup procedures
  • Triggers (at server level)

This list was compiled on BOL and the details of these items can be viewed here.

Standard
SQL server, SQLServerPedia Syndication

Backup all the databases on a server.

We were decommissioning one of our old servers. All the DBs on the server had to be backed up and archived. Hence, I wrote a script to backup all the DBs to a fixed drive. The script uses cursors and only the location to the drive needs to be modified. In my script the location is ‘v:\stemp\’, change this to your location. The script is below:

declare @vDBname as varchar(100)
declare @sqlstring as varchar(200)
declare dbnamelist cursor for
select name from sys.databases where name not in('tempdb')
open dbnamelist
fetch next from dbnamelist into @vDBname
while @@fetch_status=0
begin
set @sqlstring = 'backup database ['+@vDBname+'] to disk=N''v:\stemp\'+@vDBname+'_'+convert(VARCHAR,getdate(),112)+'.full.bak'' with name=N'''+@vDBname+' Full backup.'''
EXECUTE(@sqlstring)
fetch next from dbnamelist into @vDBname
end
close dbnamelist
deallocate dbnamelist

I should have created a variable for the backup path, maybe I will do it for the next time. Also note that the variables for @dbname and @sqlstring may have to be increased based on your environment. Tempdb has been excluded as it cannot be backed up. This script was tested for SQL2005.

You will also have to backup the resources DB mssqlsystemresource.mdf that is located in the same directory as the master DB. This cannot be done natively from SQL and should be done at the file system level. Click here to see some commands for the resource DB.

Standard
SQL server, SQLServerPedia Syndication

Backup file information.

There are times when I see a large backup file with a very ambiguous name like ‘20100102_backup_000001.bak’. These may be the names created by the SQL backup jobs. When free space has to be created, deleting some of these huge backups will help. But which of these files can I delete? A simple query for this is:

RESTORE HEADERONLY
FROM DISK = N’e:\stemp\DB_backup_201007270001.bak’
WITH NOUNLOAD;

This query will show only the header information of the SQL backup file.  Some of the useful columns for this post are: ServerName, DatabaseName, MachineName, and BackupFinishDate. With this data, I get some information as to when the backup was taken and from where. Then I can decide on weather to keep or turf the backup.

There was a situation in which I could not restore a DB via SSMS. I was creating my TSQL to do the restore when I realized: How do I know what are the names of the different logical files of the DB. Luckily there is another backup command that can be used to see a list of the logical files of the database:

RESTORE FILELISTONLY
FROM DISK = N’e:\stemp\DB_backup_201108020001.bak’

Now that I know the logical names, I can use them in my restore script and place the files at the proper file system location.

If you come across a backup file that you cannot restore, you can verify the integrity of the backup. The command I use is: restore verifyonly from disk=N’Z:\backup\DB_backup_201108020001.bak’. This will return an error if the backup file is corrupt.

Standard