database compatibility between sql 2005 standard v/s sql 2005 64 bit

I am a newbie developing a web site. My local machine has sql 2005 standard edition on it. The host that I am trying to use only has sql 2005 64 bit version.

would I be able to do the use the same database between the two ( i.e. once my site is running at host, would I be able to download the database on my local machine, make changes to my site and then upload again on the hose without any glitches?). In addition, how about sql express doing the same with sql 2005 64 bit.

Thanks a lot for taking time to read the post.

get a new host lol...come to my employeer, www.hosting.com. we offer express & std 32BIT lol who only offers 64bit.

Database backup using T-Sql

Can anyone help me out here..I am experimenting with backing up a db using Transact-Sql Backup.
I want to backup the database to a local drive say c:\databaseBk and a remote Server.

I tried

BACKUP DATABASE Mydb TO AlternativeServer but am getting errors here.
I know there is more to it and I also tried as below...
Even backup to remote for now would be perfect...if anyone has the code to do this locally that would be great also...
I got this from BooksOnline.....

USE Mydb
EXEC sp_addumpdevice 'disk', AlternativeServer,
DISK ='c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Mydb.dat'

-- Back up
BACKUP DATABASE MyNwind TO MyNwind_1I've got another one for your collection:

DUMP DATABASE dbname TO device WITH NOUNLOAD , INIT , NOSKIP

Where is the problem lays?

Regards,
OBRP|||As far as I know you can not backup DB on the remote server - only on a local device (tape or drive).|||You can backup on a remote computer by :

backup database dbname to disk='\\remote\server\path\filename.dbk' with init,noskip,NOUNLOAD

This should help you to backup it up to a remote computer without any problem.

Thanks.|||Make sure yours sqlserver runs on domain wide account, or account with write rights on remore compute.

HTH,
OBRP|||Thanks for that...

Database Backup in 0.5 B and Transaction log backup in GBs

SQL gurus,
Simple question:
Our database is 470MB and transaction log has become 18GB! (Clustered). I
see local Fixed drives in both the SQL boxes from 'System Information' and
disk manager where data is being stored.
We are backing up database once in 24 hours and transaction logs every two
hours during business hours and full transaction log at night.
Simple Question is if we are backing full transaction log at night then
isn't supposed to make the transaction log to "zero" bytes or say clear the
transaction log automatically? After all when transaction logs have been
backup then why the transaction log continue to grow'
If the database is crashed what would be the sequence to recover:
Restore database in restore mode then restore first transaction log backup
taken after full backup of database and then restore the next backup taken
of transaction log and so on. (to apply in sequence starting from the first
one backup of transaction log taken).
Now question is :
Situation #1
1) if database is crashed and we still have transaction logs then can we
restore transaction logs (not backup of transaction logs) at the end of
procedure to restore as given above'
Situation #2
2) database is crashed and we do not have transaction logs then we can
restore as given in the procedure above except that in the last stage we
keep "no restore mode"'
Thanks
MeiNarendra
All of these issues are described on BOL very well.
Please refer to BOL.
"Narendra Talreja" <ntalreja@.no_spam_comcast.net> wrote in message
news:XpOcnbaESOUAmhiiXTWJhQ@.comcast.com...
> SQL gurus,
> Simple question:
> Our database is 470MB and transaction log has become 18GB! (Clustered). I
> see local Fixed drives in both the SQL boxes from 'System Information' and
> disk manager where data is being stored.
> We are backing up database once in 24 hours and transaction logs every two
> hours during business hours and full transaction log at night.
> Simple Question is if we are backing full transaction log at night then
> isn't supposed to make the transaction log to "zero" bytes or say clear
the
> transaction log automatically? After all when transaction logs have been
> backup then why the transaction log continue to grow'
> If the database is crashed what would be the sequence to recover:
> Restore database in restore mode then restore first transaction log backup
> taken after full backup of database and then restore the next backup taken
> of transaction log and so on. (to apply in sequence starting from the
first
> one backup of transaction log taken).
> Now question is :
> Situation #1
> 1) if database is crashed and we still have transaction logs then can we
> restore transaction logs (not backup of transaction logs) at the end of
> procedure to restore as given above'
> Situation #2
> 2) database is crashed and we do not have transaction logs then we can
> restore as given in the procedure above except that in the last stage we
> keep "no restore mode"'
> Thanks
> Mei
>|||see inline
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Narendra Talreja" <ntalreja@.no_spam_comcast.net> wrote in message
news:XpOcnbaESOUAmhiiXTWJhQ@.comcast.com...
> SQL gurus,
> Simple question:
> Our database is 470MB and transaction log has become 18GB! (Clustered). I
> see local Fixed drives in both the SQL boxes from 'System Information' and
> disk manager where data is being stored.
> We are backing up database once in 24 hours and transaction logs every two
> hours during business hours and full transaction log at night.
> Simple Question is if we are backing full transaction log at night then
> isn't supposed to make the transaction log to "zero" bytes or say clear
the
> transaction log automatically?
Backing up the log does truncate the log( making space available for
re-use), but it does not shrink the log. Long running transactions can cause
the log to grow larger than you intended. After backing up the log, you may
use dbcc shrinkfile on the log file to reduce its physical size.
After all when transaction logs have been
> backup then why the transaction log continue to grow'
The only explanation I can think of is long running transactions...
> If the database is crashed what would be the sequence to recover:
>
set the db to dbo use only and kick off all users
backup the log with truncate_only
Restore the last whole data backup
restore each transaction log in the proper sequence.
> Restore database in restore mode then restore first transaction log backup
> taken after full backup of database and then restore the next backup taken
> of transaction log and so on. (to apply in sequence starting from the
first
> one backup of transaction log taken).
> Now question is :
> Situation #1
> 1) if database is crashed and we still have transaction logs then can we
> restore transaction logs (not backup of transaction logs) at the end of
> procedure to restore as given above'
No, restore the log backups
> Situation #2
> 2) database is crashed and we do not have transaction logs then we can
> restore as given in the procedure above except that in the last stage we
> keep "no restore mode"'
Yes, you may restore the whole database backup and allow recovery to run.
The database will be available, but you will have lost all of the data which
was in the transaction logs which you did not restore.
> Thanks
> Mei
>

DataBase Backup

Hi,

I am using one database to store some information, which is now in my local system.Now i want to create a copy of the database, which is in my system to another system (including the data in the table's and the stored procedures i have created).

Is this possible. if yes please help

Thanks in advance....

If you want to use the database with data then you can RESTORE this A database with a new name.

If not you can script this database with all objects and use the same script to create another database with new name.

Refer to books online for RESTORE and SCRIPT for more information.

Database BackUp

Can we save Database Backup at our local system instead of at server machine?

Quote:

Originally Posted by sajithamol

Can we save Database Backup at our local system instead of at server machine?

I recomment design SSIS package and specify location of new backup file by input parameters or set path constantly.sql

Database & Flash Wear Management

Hello everyone,

I'm currently developping a windows .net compact framework application which is basically a local datalogger.

Since my application will log data to the database (located on compact flash card) a few times a second over long period, I wonder if SQL Server Compact Edition offers some mechanism to reduce disk access.
By example, can SQL Server compact edition wait let's say 5-10 "insert into" commands before actually write to the database located on the flash card ?.

Any ideas which could help me to reduce flash wear would be greatly appreciated !

Thanks

The storage engine introduced in SQL Mobile (v3.0) and currently in SQL CE is storage-card aware.

If you feel you need to queue up inserts to further reduce writes to the card, that would be something that

you would need to code into your logger application.

My suggestion, instead of queuing your DML commands would be to periodically perform a Verify

and if needed a Repair on the database itself. See the SqlCeEngine documentation for samples

of how to perform these checks. Repair will reorganize the database (reorder indexes, reclaim

unused page space, etc) and int he process the physical file will be rewritten on the storage card.

Regards,

Darren Shaffer

|||

Thank you very much ! It's very appreciated

Regards,

Emmanuel

Data Type Conversion Problem

I am not sure if this is the right place to post, although I am new to SQL Server.

I upsized a database from MS Access to SQL Server 2005 on my local machine. I am trying to change the primary key from int to uniqueidentifier, but I get this error: "conversion from int to uniqueidentifier is not supported on the connected database server". I have tried googling around to see if anyone has posted an answer, but haven't found a solution. I have also tried detaching the database, but that doesn't help either.

Can someone help me understand what the error is meaning by "connected database server", and how I can make it possible to change the data type?

Thank you.

Ben

Hi Ben,

uniqueidentifier is a GUID, that is a 128 bit value. To guarantee uniqueness they are created by a system call. So there is no way how an int could be transformed to be a uniqueidentifier.

Why do you want to change that column?

--
SvenC

|||

Sorry, I should have been more clear. There is no data in the database. I upsized just the schema, triggers, and such, but not the data. I was hoping to convert the primary key from int to the uniqueidentifier.

Is this possible?

|||

I think you have to manually do the conversion: delete the current PK column (remove the PK first) and create a new column with data type uniqueidentifier.

If your schema is large with foreign key constraints this might be a lot of work. You might try to script the database to a .sql file and change the data type with a text editor. Then create the database from that modified sql file.

--
SvenC

|||I didn't think about making a script and modifying the script. Thank you for that suggestion. I will give that a try.

Data Transfer Wizard - Not Copying properly

Here is the scenerio

I am trying to copy a database from a live sql server to a local server.

I wanted to copy the tables, triggers, procedures, functions, users

So I have "Copy tranfer of objects " in the DTS wizard.

But the process fails informing that the users are not there in the local server and fails the process.

Now what is my expectation is that why the user logins every thing is not copied to the local sever.

Help would be more appreciated. Pretty urgent.

The quick, easy and reliable way is to backup the live one and restore on the local server it moves everything and gives you the option to change the name of the database. In the backup and restore wizard choose restore from device option, known issues orphaned permissions just delete that restore and create a new one. The other valid option is to dettach and attach but the former is the best I know. Hope this helps.|||

I tried your solution.

This are the steps i follow to do the backup.

1. I select the database from the live server.

2. I wanted to give a file name and need to specify a location.

3. I am not able to specify a location of my local drive instead i am able to specify a location of the live server harddisk which is of no

use to me as i am not able to restore because i dont have access to the live server system.

Solution is pretty urgent.

|||

I think DTS not is properly executed when a field is having a datatype of image in a table.

As the "image" field is holding a binary value it is not properly performed.

How to Replicate the use.

1. Create a database and a table with a field "projectimage" and set it datatype as image.

2. Now try doing DTS and create a new database using DTS

3. I hope the error would be replicated.

::Error::
Transfer Status: Transferring Data: (Table '[dbo].[<tablename]')
Data truncation occurred in table [dbo].[tablename], column 1.

any solutions would be more appericated

|||Now to the correct way to use the backup and restore wizard, is because it is a backup in a different server you put the .bak in the backup subfolder in programs , Microsoft SQL Server, Backup you put the file there and then let Windows create the file path for you when you choose the restore from device option in the wizard that is the most important part because if you don't SQL Server gets confused. So try these instructions and post again. I am sorry I did not add this part in my first post, Hope this helps.|||

Hi caddre, I am looking for more help from you.

In the backup option of live server it shows the files and folder of the live server and not of local disk nor any shared directory. -This is the problem

Similarly in

the backup option of local server it shows the files and folder of the local server and not of the live server.

So I dont find any common place to restore the database.

Please consider that i dont have any access to live server.

|||

I have figured out the solution in msdn

http://support.microsoft.com/kb/257425

|||I am glad you got it resolved but you did not undersatnd my instruction because if you email me your .bak I can restore it in less than five minutes. You can put your .bak in a CD post it to me and I can still restore it in five minutes it is the most efficient way to move SQL Server databases.|||

I Found that was the actually problem but unable to resolve it.

The problem seems to be occur in sql server 7.0 and not in sql server 2000.

I am using sql server 2000 and win xp but still not able solve it.

I need a step by step details or screen sheets would be more helpful.

|||

I have restored SQL Server 7.0 in SQL Server 2000 and 2000 is very good it will ask you if you want to keep TDS(tabular data stream) 70 that is 7.0 compatibility and the .bak was emailed to me on the company network.

Here are the steps right click on the SQL Server 7.0 database go to all tasks and then backup, then use the backup and restore wizard to do a full backup, when you have completed the backup use any zip software like winzip and zip it. Then take the .bak file when you go into SQL Server 2000 in xp go to programs Microsoft SQL Server then there are two subfolders one is the Data folder where the MDF,LDF and IDF are stored the other one is the backup put your .bak there, then go to the backup and restore wizard back in Enterprise manager and click to create a restore it will ask for the location of the .bak file. This is the very important part when you choose restore from device it will want to know the file part click on the ellipse Windows will create the file part for you don't try to create it because if you do SQL Server will get confused, after Windows create the file part you are almost there, it will ask if you want to change the name of the database. There are many threads here where I have helped others and the problem is always the file path let Windows create it but also verify it is correct. Post again if you still have question.

|||

1Here are the steps right click on the SQL Server 7.0 database go to all tasks and then backup, then use the backup and restore wizard todo a full backup,

when you have completed the backup use any zip software like winzip and zip it. - This is the problem.

In the Select Backup Database (4th step in the wizard)

I need to give a file location and click the ellipse button to see the files and folder of live server .

I am not able to see the files and folder of local disk. - this is where i dont really understand

I hope now u have got my problem clearly.

|||

You need to take the .bak file physically to your local SQL Server Backup subfolder you cannot try to do the restore on your local drive from the live server, I know you cannot do that with the SQL Server backup and restore wizard.

Take the .bak put it in your local SQL Server backup folder in programs. I will look for the other thread so you may understand, because I think you are trying to restore on the local one from the live server and that is not correct.

|||

You need to take the .bak file physically to your local SQL Server Backup subfolder you cannot try to do the restore on your local drive from the live server, I know you cannot do that with the SQL Server backup and restore wizard - This is the problem.

I dont have the access to copy the .bak file from live server to local disk.

Is there is any work around.

|||

See if you can register the live server on your local SQL Server right click at the top of Enterprise Manager you will see register server if you can register it then it becomes local to you and you can just copy the .bak into this physical location. If you cannot then you have to create a blank database in your local XP and use DTS to move the data into your local machine. Hope this helps.

C:\Program Files\Microsoft SQL Server\MSSQL$SQLSERVER03\BACKUP\

|||

you can just copy the .bak into this physical location - this is the problem.

1. I am able to register the live server in my local server and able to create the .bak file but unable to copy the .bak into this physical location of local

disk. 2.I have tried using DTS, Then only I faced the problem of the image field

The first option is ruled out because iam not able to access it

ie i am using DTS and finding problem with image field which gives me the following error.

::Error::
Transfer Status: Transferring Data: (Table '[dbo].[tablename')
Data truncation occurred in table [dbo].[tablename], column 1

This is a problem in sql server 7.0 and getting solved using sp4 but still iam facing the same problem with sql 2000 and win xp

which i dont know why?

I hope now things clear and leaving for End of Day and hope to see u in the morning.