export sql server

rgb192
rgb192 used Ask the Experts™
on
in sql server 2005
how to export all the values in the database

how to in sql server 2008

is there a way to do multiple databases at the same time

and get all the values in the database
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
why dont you restore a backup copy
Rajkumar GsSoftware Engineer

Commented:
Better way to export all the values (data) is backuping database

If you prefer backup, to do backup of all databases, use this script.

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')  
 
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

And restore this backup.

--Raj

Author

Commented:
dont want to restore a backup

want to export into a text file
or maybe a sql file
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

AneeshDatabase Consultant
Top Expert 2009

Commented:
Chris LuttrellSenior Database Architect

Commented:
or use a third party tool like Red Gate SQL Comare and Data Compare.  They have a free 2 week trial to try out. I have them and use them all the time and you can create a text target to create all the scripts in.  http://www.Red-Gate.com (I am not selling it, just saying I do use it.)
Reza RadConsultant, Trainer

Commented:
You can use SSIS(SQL Server Integration Service)
go to start->SQL Server 2005/2008
go to Business Intelligence Development Studio
create a Integration service project
Use database transfer task

Author

Commented:
what type of file does this code generate

also
do I just place this code in the sql editor text box
and click f5 to run
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')  
 
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

Open in new window

Software Engineer
Commented:

I have created a Stored Procedure using this query. Either execute this Stored Procedure from code or do it manually in Query Window of SQL Server. This will backup all databases in SQL Server.

--Raj
CREATE PROCEDURE usp_BackupAllDatabases
AS
BEGIN
 
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')  
 
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
 
END
 
GO

Open in new window

Rajkumar GsSoftware Engineer

Commented:
In the same way, you can restore database using sql script.

RESTORE DATABASE YourDB
FROM DISK = 'C:\Backup.bak'

Check this link for more details
http://blog.sqlauthority.com/2007/02/25/sql-server-restore-database-backup-using-sql-script-t-sql/

--Raj
Chris LuttrellSenior Database Architect

Commented:
rgb192,
no one seems to be reading your comment that you "dont want to restore a backup, want to export into a text file." :)
I was just looking in 2008 and there are some wizards in 2008 that I do not think were in 2005.  If you right click on the database name in SSMS and go to Tasks and Generate Scripts, you can set the options to export the entire data base and there is an option to Script the data which defaults to False so you have to change it if you want.  Check it out but be careful as the file size will get large in a hurry if you have much data at all.
SQL2008-ScriptWizard.png
SQL2008-ScriptWizard1.png
Top Expert 2012

Commented:
>>no one seems to be reading your comment that you "dont want to restore a backup, want to export into a text file.<<
The problem is that there is a bit of contradiction here.  The author posted a duplicate question here:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_24839189.html

Notice the accepted solution.  So I am not entirely sure what they want and I will even go out on a limb and suggest that they may not know either:  They state "want to export into a text file or maybe a sql file", as if they were comparble, even if you could overlook the "sql file" part.

Author

Commented:
ran this file

and no file in c:\backup was created

how to change variable names

do I keep the
@ in front of the variable
for example
DECLARE @myDatabaseName VARCHAR(50) -- database name
or do I leave the script
DECLARE @name VARCHAR(50) -- database name

also,
I ran this twice
and the second time got error
Msg 2714, Level 16, State 3, Procedure usp_BackupAllDatabases, Line 31
There is already an object named 'usp_BackupAllDatabases' in the database.

how can I view this file, the data
CREATE PROCEDURE usp_BackupAllDatabases
AS
BEGIN
 
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')  
 
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
 
END
 
GO

Open in new window

Top Expert 2012

Commented:
Please don't take this the wrong way, but may I suggest you contract a reputable DBA to give you hands on training.  They can show you the basics of database maintenance (including backups) and specifically show you where backups are created on the server.  As well as the difference between creating and executing a Stored Procedure.
Chris LuttrellSenior Database Architect

Commented:
:)
rgb192: you havnt run the procedure yet only created it. you now have to execute it.
try
Exec usp_BackupAllDatabases

Author

Commented:
Exec usp_BackupAllDatabases

generated .bak files in folder c:\backup

part of the .bak file is readable
and a portion is unreadble characters

is there a way to import this .bak file to create the same database
Chris LuttrellSenior Database Architect

Commented:
This is the restore command to create a new database from a backup file.  You have to put in the name of your newdatabase the 2 places where I have yourNewDBName, the name of the backup file that was created and stored at N'C:\backup\yourbackupfilename.bak, the OldDBName because there are internal things it needs to rename when doing this, and the location where you store the mdf files for databases on your server where I have 'D:\SQL Data\MSSQL.1\MSSQL\Data\yourNewDBName.mdf'

RESTORE DATABASE [yourNewDBName] FROM  DISK = N'C:\backup\yourbackupfilename.bak' WITH  FILE = 1,  MOVE N'OldDBName' TO N'D:\SQL Data\MSSQL.1\MSSQL\Data\yourNewDBName.mdf',  NOUNLOAD,  STATS = 10
GO
Chris LuttrellSenior Database Architect

Commented:
sorry, you said "is there a way to import this .bak file to create the same database" do you really mean the "same database" as in doing a restore and overwriting the existing database or creating a new database with the same data as the old, which is what I described above?

Author

Commented:
creating a new database with the same data as the old, which is what I described above?
Chris LuttrellSenior Database Architect

Commented:
then my example in http://#25698634 covers what to do.
good luck

Author

Commented:
>> 'D:\SQL Data\MSSQL.1\MSSQL\Data\yourNewDBName.mdf'
how is .mdf file created
I only created a .bak file
Chris LuttrellSenior Database Architect

Commented:
the restore command will create the .mdf file in the location you give it here, with the name you give it using the .bak file specified.
Chris LuttrellSenior Database Architect

Commented:
If you are doing these operations in a production environment, acperkins may have been very correct in recommending getting a reputable DBA to give you some help, you could really shoot yourself in the foot if you screw up backup and restore commands in a production environment.  It sounds like you are very new to all this.

Author

Commented:
the new database is called 'small'
the old database was backed up as 'C:\Backup\owsite_20091029.BAK'

want the database 'small'
to have all the values as 'C:\Backup\owsite_20091029.BAK'


Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'small' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
RESTORE DATABASE [small] FROM  DISK = N'C:\Backup\owsite_20091029.BAK'

Author

Commented:
correction:

this was the command I typed into the sql query editor
RESTORE DATABASE [small] FROM  DISK = N'C:\Backup\owsite_20091029.BAK'





the new database is called 'small'
the old database was backed up as 'C:\Backup\owsite_20091029.BAK'

want the database 'small'
to have all the values as 'C:\Backup\owsite_20091029.BAK'


Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'small' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
RESTORE DATABASE [small] FROM  DISK = N'C:\Backup\owsite_20091029.BAK'



Chris LuttrellSenior Database Architect

Commented:
you did not follow everything in the line of code, you forgot the MOVE part that is what renames the database.  I think your original database was just owsite, if not then fix the name in the MOVE statement.
RESTORE DATABASE [small] FROM  DISK = N'C:\Backup\owsite_20091029.BAK' WITH  FILE = 1,  MOVE N'owsite' TO N'D:\SQL Data\MSSQL.1\MSSQL\Data\small.mdf',  NOUNLOAD,  STATS = 10
GO

Open in new window

Author

Commented:
old database: owsite
new database: small


I dont have this folder
D:\SQL Data\
or
C:\SQL Data\

I typed in the command you told me to
RESTORE DATABASE [small] FROM  DISK = N'C:\Backup\owsite_20091029.BAK' WITH  FILE = 1,  MOVE N'owsite' TO N'D:\SQL Data\MSSQL.1\MSSQL\Data\small.mdf',  NOUNLOAD,  STATS = 10
GO

generates  error
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'small' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Author

Commented:
found small.mdf file

RESTORE DATABASE [small] FROM  DISK = N'C:\Backup\owsite_20091029.BAK' WITH  FILE = 1,  MOVE N'owsite' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\small.mdf',  NOUNLOAD,  STATS = 10
GO

but getting same error

Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'small' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Author

Commented:
this code created a successful export into a .bak file

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial