Backup all SQL databases

The following script can be used to backup all databases from a SQL server. The system databases are excluded. If any other database should be excluded, add them to the “NOT IN” section.

For different date formats in the export, check: https://robbamforth.wordpress.com/2010/06/11/sql-date-formats-getdate-examples/

The script is exporting the databases now with the following format: databasename_YYMMDD.bak

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name
 
-- specify database backup directory
SET @path = 'C:\Backup\'  
 
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(8),GETDATE(),112) 
 
DECLARE db_cursor CURSOR READ_ONLY FOR  
SELECT name 
FROM master.sys.databases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
   SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
   BACKUP DATABASE @name TO DISK = @fileName  
 
   FETCH NEXT FROM db_cursor INTO @name   
END   
 
CLOSE db_cursor   
DEALLOCATE db_cursor

Administer WID instance with SQL Server Management Studio

Windows Server Update Services (WSUS) can use a Windows Internal Database (WID). This database is manged through WSUS, but occasionally you want to administer this with SQL Server Management Studio.

Install SQL Server Management Studion (Express) on the server where WSUS has been installed. Then connect to the WID database by using the following server name:

2008R2:
\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
2012R2: 
\\.\pipe\MICROSOFT##WID\tsql\query 

Now you can administer the instance.

If you prefer to administer the database from CLI, you can install the SQL Server Command Line Query Utility – SQLCMD.

To connec to to the database instance, run the sqlcmd utility by passing the instance name. If the database is set in single user mode you can change this by running the following commands:

Windows Server 2012R2:
Open Command Prompt with Administrator Rights
sqlcmd -S np:\\.\pipe\MICROSOFT##WID\tsql\query
alter database susdb
set multi_user
go 

or

Windows Server 2008R2:
sqlcmd -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
alter database susdb
set multi_user
go 

Please Note. It is possible to administer WID instances this way, but it is not recommended. Changes to the WID instance can break the product or changes can be overwritten by updates of the products.

MsSQL – Backup all SQL Server Databases

source: https://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/

Change @Path for the location of the backups.

DECLARE @name VARCHAR(50) — database name 
DECLARE @path VARCHAR(256) — path for backup files 
DECLARE @fileName VARCHAR(256) — filename for backup 
DECLARE @fileDate VARCHAR(20) — used for file name
 
— specify database backup directory
SET @path = ‘C:\Backup\’ 
 
— specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
 
— specify filename format Including Time
— SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + ‘_’ + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),’:’,”)
 
DECLARE db_cursor CURSOR READ_ONLY FOR 
SELECT name
FROM master.sys.databases
WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’)  — exclude these databases
AND state = 0 — database is online
AND is_in_standby = 0 — database is not read only for log shipping
 
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  
 
WHILE @@FETCH_STATUS = 0  
BEGIN  
   SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’ 
   BACKUP DATABASE @name TO DISK = @fileName 
 WITH STATS, COMPRESSION, COPY_ONLY
   FETCH NEXT FROM db_cursor INTO @name  
END  
 
 
CLOSE db_cursor  
DEALLOCATE db_cursor

MsSQL Backup a Database

Use the following query to backup a MsSQL database. Fill in the correct database name (@name) and backup location (@path).

This script uses the date for the filename, so if multiple backups are needed on the same day make sure to add some remarks to it (@filename).

Also a COPY_ONLY object is used, so this will not interfere with the normal backup job.



— Declare variables
DECLARE @fileName VARCHAR(256) — filename for backup  
DECLARE @path VARCHAR(256) — path for backup files  
DECLARE @name VARCHAR(50) — database name  
DECLARE @fileDate VARCHAR(20) — used for file name
— specify database name
SET @name = ‘databasename’
— specify database backup directory
SET @path = ‘backup location’
— specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
SET @fileName = @path + @fileDate + ‘-‘ + @name + ‘.BAK’
— start backup of the database
BACKUP DATABASE @name TO DISK = @fileName WITH STATS, COMPRESSION, COPY_ONLY

Copy SQL server database role

source: http://stackoverflow.com/questions/6300740/how-to-script-sql-server-database-role

 

With the following script a script is generated which can be used to copy a SQL database role

declare @RoleName varchar(50) = 'RoleName'

declare @Script varchar(max) = 'CREATE ROLE ' + @RoleName + char(13)
select @script = @script + 'GRANT ' + prm.permission_name + ' ON ' + OBJECT_NAME(major_id) + ' TO ' + rol.name + char(13) COLLATE Latin1_General_CI_AS 
from sys.database_permissions prm
    join sys.database_principals rol on
        prm.grantee_principal_id = rol.principal_id
where rol.name = @RoleName

print @script

 

sqlservr (4016) An attempt to open the file “C:\windows\system32\LogFiles\Sum\Api.chk” for read / write access failed with system error 5 (0x00000005): “Access is denied. “. The open file operation will fail with error -1032 (0xfffffbf8).

https://support.microsoft.com/en-us/kb/2811566

 

Symptoms

Consider the following scenario:

  • You install Microsoft SQL Server 2012 or SQL Server 2012 Analysis Services on a computer that is running Windows Server 2012.
  • You use the default account as a service account for these applications during the installation.
  • The installation is successful.
  • After the installation, the services for these programs start successfully.

In this scenario, you may find error messages in the Application log that resemble the following:

For instances of SQL Server (SQLServr.exe)

sqlservr (3472) An attempt to open the file “C:\Windows\system32\LogFiles\Sum\Api.log” for read only access failed with system error 5 (0x00000005): “Access is denied. “. The open file operation will fail with error -1032 (0xfffffbf8).
sqlservr (3472) Error -1032 (0xfffffbf8) occurred while opening logfile C:\Windows\system32\LogFiles\Sum\Api.log.

For instances of SQL Server Analysis Services (Msmdsrv.exe)

msmdsrv (4680) An attempt to open the file “C:\Windows\system32\LogFiles\Sum\Api.chk” for read / write access failed with system error 5 (0x00000005): “Access is denied. “. The open file operation will fail with error -1032 (0xfffffbf8).

msmdsrv (4680) Error -1032 (0xfffffbf8) occurred while opening logfile C:\Windows\system32\LogFiles\Sum\Api.log.

Cause

This problem occurs because of insufficient permissions for the service startup accounts for SQL Server and for SQL Server Analysis Services when the services access the following folder for logging as a part of the Software Usage Metrics feature:

C:\Windows\System32\LogFiles\Sum
Workaround

To work around this problem, add read/write permissions manually to the service accounts that are used by SQL Server (sqlservr.exe) and SQL Server Analysis Services (msmdsrv.exe) to access the \Windows\System32\LogFiles\Sum folder.

Install SQL 2012 on Core

To install Microsoft SQL 2012 on a Microsoft 2012 core edition, use the /UIMODE switch. This will bring op the old interface.

setup /UIMODE=EnableUIonServerCore

 

2725_xxxx_2

 

Other samples to install it through the command line (parameters or file) can be found on: https://www.mssqltips.com/sqlservertip/2725/installing-sql-server-2012-on-windows-server-core-part-3/

SQL Server 2008 R2 – Unattended Silent Install

To create an Unattended Silent Install, you first need a configuration file with all the right settings in it. To create the file, run a installation of SQL and select all required settings. Continue the installation until you reach the “Ready To Install” step. In this step you will see the location of the configuration file (see the image below).

 

In this case it is: C:\program files\microsoft sql server\100\setup bootstrap\log\20120524_103756\configurationfile.ini

You can cancel the setup proces now.

 

Edit the configuration file as follows:

 

  • Set QUIET to “True”. This specifies that Setup will run in a quiet mode without any user interface (i.e. unattended installation)QUIET=”True”
  • Add IACCEPTSQLSERVERLICENSETERMS and set its value to “True”. This is to required to acknowledge acceptance of the license terms when the /Q (i.e. QUIET) parameter is specified for unattended installations.ACCEPTSQLSERVERLICENSETERMS=”True”
  • Remove the UIMODE parameter as it can’t be used with the QUITE parameter.
  • IF used SQL as the Securitymode, use SAPWD to set a password for the SA account
    SAPWD=”a complex password”
  • Set TCPENABLED to yes if you want external connections to you SQL instance
    TCPENABLED=”1″

 

Now you have your configuration file ready run SQL with the following command:

“<path to SQL setup folder>\setup.exe” /ConfigurationFile=”<path to config file>”

Changing the default SQL Server backup folder

When installing SQL, you can change the default SQL Server backup folder. When you want to change this afterwards you can’t do this through a gui.

 

If you want to change the path open REGEDIT and navigat to the following key (or similar for your instance or SQL version:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.50.MSSQLSERVER\MSSQLServer

Change the BackupDirectory key to the desired path.

This default path will be used when backing up a database.