SQL Server Administration: Backup database to one file or multiple files. Use these commands to back your databases up without using a maintenance plan. Backing up to multiple files is good to use for large databases and will break up a single large backup file to several smaller files. Good to use when large backups are hard to move across the network.
–
– Backup a database to a single backup file
–
DECLARE @BackupFile NVARCHAR(200)
DECLARE @Timestamp VARCHAR(50)
– create a timestamp for a unique name
SET @Timestamp = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(), 20),’-',”),’:',”),’ ‘,”)
– create backup file string
SET @BackupFile = ‘D:\SQLBackups\AdventureWorks2012_Full1_’+@Timestamp+’.bak’
– backup the database
BACKUP DATABASE AdventureWorks2012
TO DISK = @BackupFile
WITH NOFORMAT, NOINIT, NAME = N’AdventureWorks2012-Full Database Backup’,
SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO
–
– Backup a database to multiple backup files
–
DECLARE @BackupFile1 NVARCHAR(200)
DECLARE @BackupFile2 NVARCHAR(200)
DECLARE @BackupFile3 NVARCHAR(200)
DECLARE @BackupFile4 NVARCHAR(200)
DECLARE @Timestamp VARCHAR(50)
– create a timestamp for a unique name
SET @Timestamp = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(), 20),’-',”),’:',”),’ ‘,”)
– create backup file strings
SET @BackupFile1 = ‘D:\SQLBackups\AdventureWorks2012_Full1_’+@Timestamp+’.bak’
SET @BackupFile2 = ‘D:\SQLBackups\AdventureWorks2012_Full2_’+@Timestamp+’.bak’
SET @BackupFile3 = ‘D:\SQLBackups\AdventureWorks2012_Full3_’+@Timestamp+’.bak’
SET @BackupFile4 = ‘D:\SQLBackups\AdventureWorks2012_Full4_’+@Timestamp+’.bak’
– backup the database
BACKUP DATABASE [AdventureWorks2012]
TO DISK = @BackupFile1,
DISK = @BackupFile2,
DISK = @BackupFile3,
DISK = @BackupFile4
WITH NOFORMAT, NOINIT, NAME = N’AdventureWorks2012-Full Database Backup’,
SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO

SQL Server Administration: Find out when statistics were last updated. Out of date statistics are a common cause of poor performance. Tables that are active should have statistics updated regularly. Use the query below to find out if your databases have old statistics.