backup

If you’re working for a big company, chances are that your IT already has a strategy and tools for dealing with backups. Many objects need to backed up (files, emails, virtual machines, databases…) and vendors are happy to provide software solutions for all those needs. Usually, the first type of object that has to be protected is files: every company, even the smaller ones, have file servers with lots of data that has to be regularly backed up, so the data protection solution found in the majority of companies is typically built around the capabilities and features of backup tools designed and engineered for protecting the file system. [Read More]

Last week I was in the process of migrating a couple of SQL Server instances from 2008 R2 to 2012. In order to let the migration complete quickly, I set up log shipping from the old instance to the new instance. Obviously, the existing backup jobs had to be disabled, otherwise they would have broken the log chain. That got me thinking: was there a way to keep both “regular” transaction log backups (taken by the backup tool) and the transaction log backups taken by log shipping? [Read More]

Sometimes you have to copy all user databases from a source server to a destination server. Copying from development to test could be one reason, but I’m sure there are others. Since the question came up on the forums at SQLServerCentral, I decided to modify a script I published some months ago to accomplish this task. Here is the code: ## ============================================= ## Author: Gianluca Sartori - @spaghettidba ## Create date: 2013-10-07 ## Description: Copy user databases to a destination ## server ## ============================================= cls sl "c:\" $ErrorActionPreference = "Stop" # Input your parameters here $source = "SourceServer\Instance" $sourceServerUNC = "SourceServer" $destination = "DestServer\Instance" # Shared folder on the destination server # For instance "\\DestServer\D$" $sharedFolder = "\\DestServer\sharedfolder" # Path to the shared folder on the destination server # For instance "D:" $remoteSharedFolder = "PathOfSharedFolderOnDestServer" $ts = Get-Date -Format yyyyMMdd # # Read default backup path of the source from the registry # $SQL_BackupDirectory = @" EXEC master. [Read More]

Looks like a silly question, doesn’t it? - Well, you would be surprised to know it’s not. Obviously, you don’t need to be a sysadmin to simply issue a BACKUP statement. If you look up the BACKUP statement on BOL you’ll see in the “Security” section that BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles. But there's more to it than just permissions on the database itself: in order to complete successfully, the backup device must be accessible: [. [Read More]

One of the features found in the Enterprise Edition of SQL Server is the ability to take mirrored backups. Basically, taking a mirrored backup means creating additional copies of the backup media (up to three) using a single BACKUP command, eliminating the need to perform the copies with copy or robocopy. The idea behind is that you can backup to multiple locations and increase the protection level by having additional copies of the backup set. [Read More]

Stanislav Kamaletdin (twitter) today asked on #sqlhelp how to backup all user databases with TDP for SQL Server: My first thought was to use the “*” wildcard, but this actually means all databases, not just user databases. I ended up adapting a small batch file I’ve been using for a long time to take backups of all user databases with full recovery model: @ECHO OFF SQLCMD -E -Q "SET NOCOUNT ON; SELECT name FROM sys. [Read More]