export sql server

why dont you restore a backup copy

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

dont want to restore a backup

want to export into a text file
or maybe a sql file

rgb192,

Go for BCP

http://www.databasejournal.com/features/mssql/article.php/3391761/Utilize-BCP-with-SQL-Server-2000.htm


Aneesh

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

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

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

Select allOpen in new window

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

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

>>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:
https://www.experts-exchange.com/questions/24839189/export-one-database-sql-server.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.

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

Select allOpen in new window

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.

:)

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

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

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

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?

creating a new database with the same data as the old, which is what I described above?

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

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

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.

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.

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'

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'

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

Select allOpen in new window

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.

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.

this code created a successful export into a .bak file