aiDeeva.com

Category: Backup & Recovery

  • Log Shipping Requirements

    Log Shipping Requirements

    Log shipping has the following requirements:
    * SQL Server 2005 Standard, SQL Server 2005 Workgroup, SQL Server 2005 Enterprise Edition, or a later version, must be installed on all server instances involved in log shipping.
    * The servers involved in log shipping should have the same case-sensitivity settings.
    * The databases in a log shipping configuration must use the full recovery model or bulk-logged recovery model.

    Permissions
    You must be a sysadmin on each server instance to enable log shipping. The backup and restore directories in your log shipping configuration must follow these requirements.
    * For the backup job, read/write permissions to the backup directory are required on the following:
    o The SQL Server service account on the primary server instance.
    o The proxy account of the backup job. By default, this is the SQL Server Agent account on the primary server instance.
    * For the copy job, read permissions to the backup directory and write permissions to the copy directory are required by the proxy account of the copy job. By default, this is the SQL Server Agent account on the secondary server instance.
    * For the restore job, read/write permission to the copy directory are required by the following:
    o The SQL Server service account on the secondary server instance.
    o The proxy account of the restore job. By default, this is the SQL Server Agent account on the secondary server instance.

  • Important Backup Options

    Important Backup options

    INIT

    Specifies that all backup sets should be overwritten, but preserves the media header. If INIT is specified, any existing backup set data on that device is overwritten.

    The backup media is not overwritten if any one of the following conditions is met:

    • All backup sets on the media have not yet expired. For more information, see the EXPIREDATE and RETAINDAYS options.
    • The backup set name given in the BACKUP statement, if provided, does not match the name on the backup media. For more information, see the NAME clause.

    Use the SKIP option to override these checks. For more information about interactions when using SKIP, NOSKIP, INIT, and NOINIT, see the Remarks section.

    Note If the backup media is password protected, SQL Server does not write to the media unless the media password is supplied. This check is not overridden by the SKIP option. Password-protected media may be overwritten only by reformatting it. For more information, see the FORMAT option.

  • How to take backup in Multiple files?

    Problem
    How to take backup in Multiple files (Or you can say how to split backup file in multiple backup files)?

    Solution

    Advantage:

    1. We can store backup on multiple drive if disk space is the issue

    2. Easy to transfer on network servers

    T-SQL for Backup

    BACKUP DATABASE [Northwind] TO

    DISK = ‘C:\Northwind_file1.bak’,

    DISK = ‘D:\Northwind_file2.bak’,

    DISK = ‘E:\Northwind_file3.bak’

    WITH INIT , NOUNLOAD , NAME = ‘Northwind backup’, NOSKIP , STATS = 10, NOFORMAT

    T-SQL for Restore

    RESTORE DATABASE [northwind] FROM

    DISK = N’C:\Northwind_file1.bak’,

    DISK = N’D:\Northwind_file2.bak’,

    DISK = N’E:\Northwind_file3.bak’ WITH FILE = 1, NOUNLOAD , STATS = 10, RECOVERY , REPLACE

  • How to change SQL Server Instance Name?

    First collect the output of the current instance configuration. You can get the instance name stored in the SQL Server metadata.

    Make sure you have backup of all the database if you are changing the production server instance name.

    	sp_helpserver
	select @@servername

    You can change the instance name using below query.
    Default Instance 

    	sp_dropserver 'old_name'
        go
        sp_addserver 'new_name','local'
	go

    Named Instance 

    	sp_dropserver 'Server Name\old_Instance_name'
        go
        sp_addserver 'ServerName\New Instance Name','local'
	go

    Verify sql server instance configuration by running below queries

    	sp_helpserver
	select @@servername

    Restart the SQL Server Services.

    	net stop MSSQLServer
	net start MSSQLServer

  • SQL Server Fixed Database-Level Roles

    SQL Server Fixed Database-Level Roles

    Fixed database roles are defined at the database level and exist in each database. Members of the db_owner and db_securityadmin database roles can manage fixed database role membership; however, only members of the db_owner database role can add members to the db_owner fixed database role.

    The fixed database roles are the following:
    * db_accessadmin
    * db_backupoperator
    * db_datareader
    * db_datawriter
    * db_ddladmin

    For the full version of article please visit http://sqldbpool.blogspot.com/