Tag Archives: sqlcmd

MS SQL – method to backup all instances/databases through script

10 Feb

Sometimes you need to backup all of the instances and databases in a particular server. One way is to script everything and create a schedule task in Windows to run it.

 

1: Create a sql-file with:

DECLARE @name VARCHAR (100) — database name
DECLARE @path VARCHAR (256) — path for backup files
DECLARE @fileName VARCHAR (256) — filename for backup
DECLARE @fileDate VARCHAR (40) — used for file name

— specify database backup directory
SET @path = ‘E:\MSSQL\Backup\’

— specify filename format
–SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
SELECT @fileDate =
CONVERT (VARCHAR (20), GETDATE (), 112)
+ REPLACE (CONVERT (VARCHAR (20), GETDATE (), 108), ‘:’, ”)

DECLARE
db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN (‘master’,
‘model’,
‘msdb’,
‘tempdb’) — exclude these databases

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
–PRINT @name
FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

2: Create a cmd-file with (backup instances default, instanceaa, instancebb):

sqlcmd -S servernameXX -i”c:\temp\backup_all_databases.sql”
sqlcmd -S servernameXX\INSTANCEAA -i”c:\temp\backup_all_databases.sql”
sqlcmd -S servernameXX\INSTANCEBB-i”c:\temp\backup_all_databases.sql”

3: Create a schedule task to rund cmd-file e.g everyday

 

Read more

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

 

Reset sa password in MS SQL

22 Aug

If you have forgotten sa password and don’t have sysadmin rights, you can do the following:

  1. Download pstools (psexec) from http://technet.microsoft.com/en-us/sysinternals/bb897553.aspx
  2. Start cmd with administrator  (run as administrator) and run this command:
    • PsExec -s -i "C:\Program Files (x86)\...\ssms.exe"
  3. Login with the default suggested username (NT AUTHORITY\SYSTEM) and add yourself as sysadmin

 

Read more

http://blog.sqlauthority.com/2009/02/10/sql-server-start-sql-server-instance-in-single-user-mode/

http://blog.sqlauthority.com/2009/08/04/sql-server-forgot-the-password-of-username-sa/

http://www.wikihow.com/Reset-SA-Password-in-Sql-Server

http://sqlserver-help.com/category/security/sa-password-lost/

http://www.pressleypress.com/blog/?p=785

http://dba.stackexchange.com/questions/31660/cannot-connect-to-sql-server-in-single-user-mode/31666#31666

 http://v-consult.be/2011/05/26/recover-sa-password-microsoft-sql-server-2008-r2/