Tag » Database « @ 肖恩D++

One Script to Backup All SQL Databases

Posted on August 15th, 2011 Sean Add comments

It’s a nightmare for backup your DB if you have 100+ DB’s in SQL server. Just simply run a script while you taking a coffee:

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 

SET @path = "C:\Backup"

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb') 
--skip system database (optional)

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

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

SET @path = "C:\Backup"

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR

SELECT name

FROM master.dbo.sysdatabases

WHERE name NOT IN ('master','model','msdb','tempdb')

--skip system database (optional)

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

ASP.NET Database, SQL

(No Ratings Yet)

Visual Studio Website Administration Tool & SQL Server Express

Posted on June 24th, 2010 Sean 1 comment

In Visual Studio the Website Administration Tool lets you view and manage the Web site configuration through a simple Web interface. But sometimes there is problem which is when using the Website Administration Tool to make a new connection for a new website, for example, using the security tab of the Website Administration Tool to manage rules for securing specific resources in the web application or website, it will display an error message “Unable to connect to SQL Server database”, even you have SQL Server Express installed and no problem to connect to it.
To solve this problem, try one or more of the following ways:
Read the rest of this entry »

ASP.NET Database, SQL, Visual Studio

(No Ratings Yet)

ORACLE databse can not start by missing tablespace file

Posted on February 13th, 2010 Sean 1 comment

Today, as usual, trying to login to Oracle database do something, but when typing “sqlplus” and username/password, it showing:

ERROR :
ORA-01033: ORACLE initialization or shutdown in progress

1 2	ERROR : ORA-01033: ORACLE initialization or shutdown in progress

It seems like Oracle database was not starting somehow. After connect the database as SYSDBA, type some command for trying to restart the datadase:

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/home/XXX/XXX.dbf'

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 171966464 bytes

Fixed Size 787988 bytes

Variable Size 145750508 bytes

Database Buffers 25165824 bytes

Redo Buffers 262144 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file

ORA-01110: data file 6: '/home/XXX/XXX.dbf'

Now finally realized what happened and how this happened. There is a tablespace file “XXX.dbf” missing. To solve this issue, connect database as SYSDBA, then:

SQL> alter database datafile 'XXX.dbf' offline drop;
SQL> shutdown immediate;
SQL> startup;

SQL> alter database datafile 'XXX.dbf' offline drop;

SQL> shutdown immediate;

SQL> startup;

Unix/Linux Database

(No Ratings Yet)

Create tablespace on NFS based LINUX + Oracle

Posted on February 8th, 2010 Sean Add comments

When the first time to create tablespace on SQLplus, it shows:

“ORA-27040: file create error, unable to create file. Linux-x86_64 Error: 13: Permission denied”

So I just change my home directory to “777”, then getting the error

“ORA-27054: NFS file system where the file is created or resides is not mounted with correct options”

After check the “autofs” settings, I found the content of file “auto.home” has problem which is something wrong with NFS mount options. The solutions is change the original content

* -fstype=nfs,soft,intr,rsize=8192,wsize=8192,nosuid,tcp oracle.server.com:/users/&

to

* -rw,bg,hard,intr,rsize=32768,wsize=32768,suid,nfsver=3,tcp oracle.server.com:/users/&

Unix/Linux Database, Unix/Linux

(No Ratings Yet)

Calendar

April 2024
M	T	W	T	F	S	S
« Nov
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

Networks

Visitor Counter

Total Visitors 222,871
Visitors today 94
Page views today 418
Curently online 2
Page views of current page 463
Statistics
Counterstart 2014-12-11

One Script to Backup All SQL Databases

Visual Studio Website Administration Tool & SQL Server Express

ORACLE databse can not start by missing tablespace file

Create tablespace on NFS based LINUX + Oracle

Calendar

Networks

Categories

Archives

Recent Comments

Links

Who's Online

Visitor Counter

Meta